mysql索引实现原理_Mysql索引原理

1、二分查找法

二分法，也叫二分查找法，是一种高效的查找算法。

如下一个有序数列，如果我们需要从中找到1这个元素，这个过程需要查找几次？

【1，2，3，4，5，6，7，8，9，10】

对于这个数列查找过程是：

第一次：在【1，2，3，4，5，6，7，8，9，10】

中找到中位数5。

判断5比1大还是比1小还是等于1

如果比1大保留【1，2，3，4】

如果比1小保留【6，7，8，9，10】

如果等于1则返回结果

这个例子属于第二种情况--剩下【1，2，3，4】

第二次：在【1，2，3，4】

中找到中位数2。

判断2比1大还是比1小还是等于1

如果比1大保留【1】

如果比1小保留【3，4】

如果等于1则返回结果

这个例子属于第二种情况--剩下【1】

第三次：在【1】

中找到中位数1。

判断1比1大还是比1小还是等于1

如果比1大保留【1】

如果比1小保留【3，4】

如果等于1则返回结果

这个例子属于第三种情况--返回结果。

从例子上在10个元素中找某个元素只需要3次即可，在这个过程中二分法的思想即“每次找中位数的过程都能排除掉一半的情况”，这也就是它高效的秘密所在。

同理，在1到100中定位一个元素，1到1000中定位一个元素，都只需要查找几次。

但这个例子要注意的是，数据太少是不适合使用二分查找法的，直接遍历查找会更加简单。

反过来也就是说，数据量足够多，且数据有序的数列使用二分查找法的性价比最高。

2、二叉树

二叉排序树（Binary Sort Tree），又称二叉查找树（Binary Search Tree），亦称二叉搜索树，是一种用于存储数据的数据结构。

二叉搜索树有如下组织规则：

1. 若左子树不空，则左子树上所有结点的值均小于或等于它的根结点的值;
2. 若右子树不空，则右子树上所有结点的值均大于或等于它的根结点的值;
3. 左、右子树也分别为二叉排序树;

总结就一句话：左小右大

下图为一颗二叉排序树：

在这副图里，如果我们要找一个元素那么就得从8开始找起。

• 如果找8， 我们查找只需要1次.
• 如果找4， 我们查找2次。
• 如果找12，我们查找2次。
• 如果找3， 我们查找3次。
• 如果找2， 我们查找4。

所以此树的平均查找次数为(1+2+2+3+4)/ 5= 2.4次

在这里我先要了解树的几个概念——高度，深度，层数

1. 高度：由下往上数，从0开始，上图的树的高度就是0，1，2，3
2. 深度：由下往上数，从0开始，上图的树的深度就是0，1，2，3
3. 层数：由上往下数，从一开始，上图树的层数为1，2，3，4

理解了这三个概念之后我们再回过头来，对比于线性的数据结构如：

• 如果找1，我们查找1次.
• 如果找2，我们查找2次.
• 如果找3，我们查找3次.
• 如果找4，我们查找4次.
• 如果找5，我们查找5次.

所以此结构的平均查找次数为(1+2+3+4+5)/ 5= 3次

显然如果以树结构对数据进行组合查找效率会更加高效。在上面的例子上如果数据量放大到10000倍，那么2者差距会更加明显。

但是为了是查找的效率更高，我们可以再去优化下树结构。

3、二叉平衡树

平衡二叉搜索树（Self-balancing binary search tree）又被称为AVL树。

它有如下性质：

1. 它是一颗平衡树之前必须是一颗二叉排序树
2. 它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

要点：两个子树的高度差的绝对值不超过1

下图为一颗二叉平衡树：

• 如果找8，我们查找1次.
• 如果找3，我们查找2次.
• 如果找12，我们查找2次.
• 如果找2，我们查找3次.
• 如果找4，我们查找3次.

此树的平均查找次数为(1+2+2+3+3)/ 5= 2.2次

可以看到二叉平衡树的查找效率是比二叉查找树要高的。

现在我们已经知道树的2种形态，那么为了就可以介绍下面的多叉查找树啦。

4、B-树

B-tree是一种多叉平衡查找树

它具有如下性质：

1. 每个节点至多有m棵子树。
2. 除根节点外, 其他每个分支节点至少有ceil(m/2)棵子树。
3. 根节点至少有2棵子树(除非B树只包含一个节点)。
4. 所有叶节点在同一层上。
5. B树的叶节点可以看成一种外部节点, 不包含任何信息。
6. 有j个孩子的非叶节点恰好有j-1个关键码, 关键码按递增排序。

B-tree也是一种查找树, 包括上面2种树。所有的树都是用来解决查找效率低的问题, 那么为什么B-tree会被设计出来，原因在于各个存储媒介的读取数据是不一样的。

在内存中，因为内存I/O性能的速度比较高,我们以AVL树的方式存储数据和操作数据开销是能被接受的。 但是在硬盘中用AVL树格式来存储数据的话, 开销无疑是巨大的。

试想一下，如果有一颗深度为100的AVL树, 那么在这颗AVL查找某个节点最多有可能要进行100 次 I/O操作，目前服务器主流为10000转, 一次I/O耗时大概10ms左右，大部分时间花在寻道上，那么总花费的时间需要1秒钟。

而内存中100次I/O操作大概1ms，2者几乎是1千倍的差距。

所以为了提高像磁盘这样速度比较慢的外存储设备的查询速度。B-tree就被设计出来了

如下图是一颗B -tree:

图中可知：

1. 凡是在有指针的磁盘块中的节点我们称为非叶子节点。
2. 没有指针的磁盘块中的节点我们称为叶子节点。
3. 全部数据并没有存储在叶子节点上, 这是与B+tree的差异之一。

声明：对比上面的2种树可以看到，真正的差距其实只是节点大小不一样。在内存中一样可以和Bt-ree这样做，我们只需要理解他们共同点都是利用树这种存储结构。

如果我们要在上图中搜索55这个值, 我们首先获取根磁盘块（图中的第一层节点）消耗一次IO,加载块数据到内存 。

由于关键码是递增排序的那么我们可以利用二分法进行查找然后找到第3指针(扇出), 定位到第3个叶子节点，消耗一次磁盘IO,加载块数据到内存 ,在内存中找到了55。

在此期间一共消耗了2次IO与2次内存查找操作。这是AVL树无法比拟的（试想一下一颗存有值1-100的AVL树中查找55的IO消耗无疑是巨大的）。

B-tree很好的解决了硬盘这种速度比较慢的设备查找数据太慢的问题，但是对于数据库系统文件系统的存储B-tree却不是非常合适。

因为B-tree在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。

B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

到这里，我们先总结一下，B-tree对比于二叉树，最大的差别就是B-tree一个节点存多个数据，这样减少了数据随机存储导致的大量IO操作，然后每个节点存储的数据集合是有序的，这样就可以通过二分法来快速定位数据。

5、B+树

由图得:

1.父叶的扇出与子叶的个数是一致的

2.叶子节点也保存非叶子的值。

3.所有非子叶节点都是只做索引

这样的好处在于上面所说遍历所有数据会变得非常简单, 且像数据库这样的系统, 范围查询是非常频繁，B+树使得范围查询效率更高了(只需要确定边界值, 通过非叶子查找即可)。

不过开销就是——存储一组数据B+tree也要比B-tree要耗费更多存储空间, 这里正好体现了空间换时间的思想。

6、mysql中的树结构

由图可知

1.叶子节点之间通过双向链表来关联, 它们在逻辑上是顺序(由于存储是基于磁盘在磁盘上存储未必是顺序, 所以要通过链表链接)
2.数据直接存在叶子节点里。

innodb就是这样来存储数据的，且一个innodb引擎一个页的大小为16K, 一般一个磁盘块大小为4K。

mysql为了能够一页存储更多的数据, 利用了磁盘预读原理（磁盘并不会按需读取, 而是读取到一页时候随便往后读取几页）, 从而把一页默认设置成为16K （当然我们可以通过手动设置为8K, 4K）。

假设我们一行数据的大小是1k（1K已经能存储很多数据了）, 且这是棵簇集索引树（就是节点里的内容存的是id）。bigint为8字节索引指针（也就是扇出）占6字节, 那么一共14个字节, 那么16k/14b = 1170 个索引指针。

那么根叶就大概有1170个索引指针, 又由B+树的父叶得扇出与子叶的个数是一致的性质可得, 二层的存储117016 = 18720条记录, 三层117016*16 = 21902400 条记录,。

即2000W的数据表的树的层数只有3, 即在一个2000W的数据表中通过id查找一条数据只要做2-3次I/O便可(而且根节点一般会常驻内存里, 即可少一次磁盘的I/O)。

由于叶子节点通过双向链表进行关联，这样也可以很方便的进行asc与desc的操作，范围查询也理所当然的被支持了，我们常常说的全表扫描说的也就是innodb从头到尾遍历了这个链表。

7、mysql中的索引

1.聚集索引

聚集索引在许多书上也称为聚簇索引, 它是说的就是上面B+树的数据组织方式, 它的节点是主键id, 由于主键只有一个，所以一张表只有一个聚集索引。

查询优化器倾向于采用聚集索引。因为聚集索引能直接在叶子节点找到数据, 且B+树定义了数据的逻辑顺序, 所以能够快速的进行对id的排序与范围查询。

2.普通索引

普通索引也叫辅助索引，也称非聚集索引, 叶子节点并不包含行记录的全部数据,只包含用做索引的值和一个书签，这个书签就是对应聚集索引的键。

如果使用了辅助索引，大致过程是，先在辅助索引找到书签也就是对应聚集索引的键。然后再用这个键在聚集索引中查找到对应的行数据。

若辅助索引的层数为3, 聚集索引的层数也为3, 那么理论上需要做6次IO才能得到对应的行数据。

上图展示了辅助索引的工作原理。

3.联合索引

联合索引是指对多个列进行索引。

alter table table_name add index group_index_name (field_name_1,field_name_2);

若添加数据多条数据

insert into table_name (field_name_1,field_name_2) values (1,'a'), (1,'b'), (1,'c'), (1,'d'), (2,'a'), (2,'b');

则通过这条语句生成的B+树结构如下：

它有如下特点：

1.一个节点包含当所有声明为索引的列的数据。

2.一个数据先从field_name_1优先开始排序, 再对field_name_2进行排序。

当一个查询条件只有 field_name_2做查询条件的时候, 是无法使用此索引的, where条件中必须有field_name_1才能使用, 这是联合索引的一个特性,由上图也看出每个节点的范围是由field_name_1来优先决定的。

对于这个索引只有2种情况会出现, field_name_1 用做查询条件, field_name_1 和 field_name_2同时做查询条件，这个特性也就是常常说最左匹配原则。

通过联合索引我们能更快的得到满足查询条件的主键, 通过行主键到集聚索引查找对应的行, 由于查询的行数更少, 所以无疑比聚集索引全表扫描的效率更高,。

且这样还有一个好处就是避免了很多的排序操作, 假设上面的field_name_1 是 用户的id , field_name_2是用户的某某值如:

select field_name_1, field_name_2 where table_name where field_name_1 = 1 and field_name_2 = 'a';

mysql在执行这种语句的时候, 首先触发了 field_name_1 和 field_name_2 的联合索引, 由于因为所需显示的字段值只有field_name_1 和 field_name_2 。

又由上图可以知道 辅助索引节点里保存这索引字段的值, 所以通过联合索引就可以得到我们所需的数据。mysql就不需要通过主键回表查询整一行, 直接用辅助索引里的值当作结果返回。这个过程我们称之为索引覆盖。

而且，如果你order by field_name_1 和 field_name_2 的话，由于本身数据是有序的，这样也可以减少排序的开销。

4.前缀索引

前缀索引一般用于给大本文的或者长字符串做索引，首先我先考虑下为什么不能给大字符串做索引？

通过上面的文章我们即可找到答案，大字符串做索引很快就会用完了节点里面的空间，这样扇出会很少，从而导致索引树的层数较高，这样找一条数据的花费的IO开销也会更高，所以解决的办法就是取前面几个字符串做索引。

大字符不能使用索引覆盖，所以会造成回表操作，所以我们大字符串的字段一般做拓展表，其目的就是为了不影响其他数据的查询。

8、总结

到这里，我们梳理完了索引的脉络，读完文章你也许感到思路还不是特别清晰，这时候你再带着疑问去了解如下几个问题：

1. 什么二分法
2. 什么是二叉树
3. 什么是多叉树
4. 硬盘怎么读取数据的
5. innodb如何进行数据的存储

相信聪明的你很快就可以理解它们。

到这里，全文完.....

不要错过这个系列：可以关注下公众号，里面还有例如mysql啊 数据结构等的技术文章