B-tree 索引提高 MySQL 查询效率的原理

在MySQL中,我们常用的存储引擎 InnoDB 和 MyISAM 的索引都是B-Tree 索引。大家都知道,建立索引的目的便是优化慢查询,那么慢查询究竟慢在哪里呢?

查询时间

一个sql查询的时间分为 等待时间执行时间

等待时间,即sql执行之前所等待的时间。对于mysql数据库来说,在执行某些操作时,InnoDB会锁行,MyISAM会锁表。当即将开始一个新查询时,如果查询所操作的表或者行正被锁定,那么就会有等待时间(一般时间相当短,可以忽略。如果过长,应检查数据库)。

执行时间,sql查询的执行时间分为2个步骤:
查询:找到符合要求的行。
取出:将找到的行的数据取回。

时间分布如下图:

这里写图片描述

建立索引的目的便是为了减少执行时间。

B-tree索引

B-tree是一种常见的数据结构。对于 InnoDB 和 MyISAM 来说,在未加索引的情况下,查询一条数据只能进行全表扫描。效率十分低下,添加索引后,通过索引检索数据,效率则会提高千倍万倍。

那么b-tree的查询为何如此快速呢?
B-tree 可以说是多阶的二叉树,下面我们以二叉树查找来演示,原理与b-tree基本相同:

二叉树具有以下性质:
- 任意节点的左子树不空,则左子树上所有结点的值均小于它的根结点的值;
- 任意节点的右子树不空,则右子树上所有结点的值均大于它的根结点的值;
- 任意节点的左、右子树也分别为二叉查找树;
- 没有键值相等的节点。

假设将主键索引存入一颗二叉树,那么在查找主键为27的数据时,二叉树查找与队列查找对比如下:
这里写图片描述
(图中step数忽略了顶层比较)

使用二叉树后,比较次数由11次降到了4次。

在大数据时这一点更加明显:如图,27所在二叉树的第四层,需要进行4次比较操作。
假设数据量够大,要查找的目标id在第32层存储,那么则需要比较32次便可找到目标记录的id。那么11层二叉树存储了多少数据呢?二叉树每层存储量都会翻倍,那么第32层则存储了 2^32个数据,再加上前10层的数据,总数据量达到了 2^(33)-2 个,大约40多亿条。假设没有索引,直接全表扫描,那么平均则需要检索20亿次,才能找到目标数据。

索引排序

建立索引,不仅能够大大提高查询的效率。对排序效率也有很大提高。
在进行排序操作时,索引树由根节点开始展开,左子树排在父节点之前,右子树排在父节点之后,组成一个整体,再与更上一层父节点重复操作。如图:

这里写图片描述

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值