MySQL-索引的那点事（MySQL B+Tree索引实现原理解析）

侯晓阳丶

已于 2024-08-20 16:21:08 修改

阅读量535

点赞数 17

分类专栏： MySQL 文章标签： mysql 数据库 dba

于 2024-08-20 14:34:12 首次发布

本文链接：https://blog.csdn.net/h_3369/article/details/141357887

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL innodb存储引擎使用的索引为B+tree索引，B+tree索引又是从B-Tree索引优化而来。而B-tree索引，又是在平衡二叉树索引的基础上得到的优化。而平衡二叉树索引从二叉树优化而来。所以要想搞懂MySQL的B+树索引概念，需要从二叉树开始。

什么是二叉树？

二叉树主要用于实现二叉查找法，二叉树分为左子树和右子树，每个节点最多拥有两个子树，每个节点都包含一个元素和多个子树。二叉树的左子树的值一定要小于父节点的值，而右子树的值一定要大于父节点。根据数据的插入顺序，如果是按照顺序插入35、27、48、12、29、38、55，形成的二叉树，如下图：

如上图所示：二叉树的情况就会出现一种问题，那就如果说插入顺序变了，都是比父节点小的，或者比父节点大的。那就会形成一个链表，链表再去获取数据，就需要从最初的节点一直遍历到想要获取数据的节点。那么这种情况就很消耗资源，并且大大降低了查询的效率，

插入顺序12、27、29、35、38、48、55，如下图：

什么是平衡二叉树？

根据上方的问题，就出现了一种平衡二叉树的算法，平衡二叉树，是一种特殊的二叉树，二叉树特性他都有，但是他的区别是，他的左右两个子树的高度差不会超过1，并且左右两个子树都是一颗平衡的二叉树。插入数据的顺序为：12、27、29、35、38、48、55 ，如下图：

通过图中可以看出，平衡二叉树与二叉树的不同点，当子树出现了偏差，左右两个子树高度的绝对值，超过1时，将会替换原来的父节点。这样做到一个平衡的二叉树。这样我们查询数据时就和二叉树的查找效率是一样的了。但是这里面涉及到一个概念。一棵平衡二叉树能容纳多少的结点呢？这跟树的高度是有关系的，假设树的高度为 h，那每一层最多容纳的结点数量为 2^(n-1)，整棵树最多容纳节点数为 2⁰⁺²1+2^2+…+2(h-1)。那么如果是100w数据，就需要遍历20次才能查到，这样的查找方式在数据量级比较小的情况下，还不是什么问题。但是如果是1000w，1个亿的数据呢？那这个查找的开销服务器的硬件可承受不住啊。

什么是B-tree？

B-tree索引算法其实是在平衡二叉树，这棵树上压缩了一下，让每一层能够容纳更多的节点，而非一个元素，只能有两个节点。

B-tree的特性：

每个节点最多可以是m个子节点
除了根节点和叶子节点外，每个节点最少有m/2个子节点。
如果根节点不是叶子节点，那么根节点至少是包含两个子节点。
所有的叶子节点都位于同一层
所有的节点都包含k个元素。，m/2<=k
每个节点中的元素从小到大进行排列
每个元素左节点的值，都小于或等于该元素，右节点的值都大于该元素（类似二叉树的概念）

补充点：M为磁盘页的大小，如过磁盘页的大小只能存储10个子节点，那么除了根节点和叶子节点以外的支节点就是至少有10/2=5个子节点。

查询数据的方式：

接下来我们再看一张图，从前面看到的是，我们存储的都是123这样的数字，而在数据库中，我们实际存储的是各种各样的数据，而像1、2、3这样的数字，就被称为key,也被称为元素，在B-tree索引算法中，是一个元素一个数据。

什么是B+tree？

B+tree是从B-tree索引优化而来，B+tree也是MySQL主要用到的索引。他与B-tree最大的不同时。B+tree所有的数据存在叶子节点，而支节点和根节点，只存储元素。

B+tree特性：

所有的非叶子节点只存储关键字信息。
所有具体数据都存在叶子结点中。
所有的叶子结点中包含了全部元素的信息。
所有叶子节点之间都有一个链接指针

B-tree和B+tree的优缺点

B-tree非叶子节点也会保存数据，所以在查询数据的时候，获取到关键字即可获取到数据。而B+tree无论如何都需要到达叶子节点，才可以获取到数据，所以在相同索引高度的条件下，B-tree可能会比B+tree查询效率要高。
B+tree因为叶子节点之间存在链表，所以再查找到数据的时候，还需要再查找相近的较小值或较大值的时候，只需要根据链表进行遍历即可，而B-tree因为节点之间没有链表，每次查询一定都需要从根节点进行遍历。这一点尤为重要，毕竟数据库中有个概念，当我们要查询一条数据时，往常这条数据上下的数据都会被获取，例如范围查询。
由于B-tree每个节点都存储数据和元素，那么相当于每个数据页，即存储元素，也存储数据，所以相比较B+tree，非叶子节点只存储元素的情况来看，B-tree每个数据页，能存储的数据更少。这样的话，同样数据量，B-tree查询的深度可能更大，也就更消耗磁盘的I/O。

MySQL Innodb存储引擎与B+tree的那些事

在MySQL Innodb存储引擎的概念中，也是有页（page）的存在，只不过与系统不同的是，这个页默认是16k。也就是4个系统的数据页。相当于一个innodb存储引擎的数据页。
而innodb插入数据的方式为：当一个page1满了，再有新的数据进行插入，会出现一种操作，创建page2，将page1的数据复制到page2，并创建page3，写入新数据。这样page1，只保存索引信息。

那么问题来了，为什么要这么操作，要多复制一下呢？这不是增加io开销吗？其实啊，主要目的是让根目录在内存和磁盘当中的物理位置不发生改变，如果page1不复制，而是变更根目录的物理地址，可能产生一些不必要的麻烦把。

问题点：如果根据innodb这样的操作，原来 Page1 有 10 条数据，在插入第 11 条数据的时候进行裂变，根据前面对 B-Tree、B+Tree 特性的了解，那这至少是一棵 11 阶的树，裂变之后每个结点的元素至少为 11/2=5 个。那是不是应该页裂变之后主键 1-5 的数据还是在原来的页，主键 6-11 的数据会放到新的页，根结点存放主键 6？那如果这样的话，就可能因为频繁的产业页的裂变，导致页的利用率只有50%左右。简单理解就是，如果按照这种方式让B+tree分裂下去，即浪费资源，又消耗磁盘I/O。

其实根据上方问题，Innodb是做了优化的，就是不移动页，写满一个页再新创建一个页写入数据，但是这里有个隐藏条件：必须是主键自增的方式。主键自增写入时，新插入的数据不会影响到原有的页，插入效率高！且页的利用率高！如果主键是无序的或者是随机的，那么可能就会影响让页频繁的分裂。如果表没有主键，innodb也是会默认创建隐藏主键的。

Innodb是如何通过主键自增，实现让页不分裂呢？其实很简单，就是连续性的主键，不需要进行分裂了。一直可以顺序写下去。如果说不是顺序的，此时有page1-page4四个页，那么此时有点数据过来了。他的顺序刚好是，page1-page4之间，而他们之间又都没有剩余空间了，那么此时想要写进来，就需要将页进行分裂。然后创建新的页，再将原有的页进行重新排序。

MySQL中，其实有种方法可以我们人为干涉，页的合并和分裂。那就是使用，MERGE_THRESHOLD 参数，此参数在创建表或者创建索引时使用，默认为最大值50即页大小剩余一半，就触发合并。具体操作手法参考下方链接

https://opensource.actionsky.com/20201014-mysql/

Innodb查找数据的方式：

单项链表查询方法:

聚簇索引与辅助索引的查找方式

其实从上图可以看出，辅助索引与聚集索引不同的是，查找的时候，聚集索引叶子节点存放的就是数据页。直接可以获取到数据，而辅助索引，建立后，最终的叶子节点存放的是聚集索引的主键key，也就是自增id或者主键id，随后拿到主键id后，再从聚集索引进行查询，查到叶子节点，随后返回数据。拿到主键id再次去查询，也称之为回表查询。

Innodb与MyISAM引擎对B+tree索引的不同

聚集索引只有innodb支持，上方为MyISAM的非聚集索引，通过B+tree算法实现，从图中可以看出，MyISAM最后叶子节点存储的也不是数据，而是实际磁盘的物理地址，再通过这些地址指针获取到实际的数据。而通过图片还可以看出，MyISAM在页中的顺序和innodb不同，不是像聚集索引一样，按照自增id或者主键id进行排序，而是根据物理地址排序，也就是按照插入顺序进行排序的。另外一点可以看出，为什么MyISAM更适合读多写少的业务，因为MyISAM的非聚簇索引，与innodb的聚簇索引类似，不需要回表查询。

索引为什么不是越多越好：

索引的原理是拿额外的存储空间换取查询时间，增加了写入数据的开销，但使读取数据的时间复杂度一般从O(n)降低到O(logn)甚至O(1)。
创建索引会占用磁盘空间，尤其是表很大，且创建索引的字段值比较多，内容比较长的话，更是如此。
索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。
优化器在选择索引时，会根据统计信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

小知识：真正有问题是那些低效的索引。MySQL 的优化器不会去选择使用这些低效的索引。这些低效的索引既占用了空间，又影响了插入或更新的性能，最关键的是还不给查询带来任何速度上的收益。

覆盖索引

覆盖索引，一句话可以理解，就是普通的非聚集索引的辅助索引，不需要回表查询，只从辅助索引的索引树就可以获取到数据，能够更加降低查询语句的开销。在执行计划中为：Using index表示用到了覆盖索引。Select * 无法使用到覆盖索引，很简单因为，select * 获取的是数据行的全部数据，如果索引是id，sid，那只有使用select id，sid或者是select id和select sid，才能使用到覆盖索引。

# 创建一个示例索引
SQL > alter table t1 add index 'id_sid_idx' (id,sid);

#以下语句都可以用到覆盖索引
SQL > select id,sid from t1 where id=xxx and sid =xxx;
SQL > select id from t1 where id=xxx and sid=xxx;
SQL > select sid from t1 where id=xxx and sid=xxx;
----+-------------+-------+------------+-------+---------------+-------------+---------+-------------------+------+----------+--------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | id_sid_idx    | id_sid_idx  | 9       | const,const       |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------------------+------+----------+-------------+

全文索引

全文索引，最开始是只有MyISAM存储引擎支持，后面5.6版本以后，Innodb存储引擎也支持了。全文索引其实就是文本索引，将文本进行分词，并存放在系统表ft-min-word-len这张表中。全文索引实际上也是B+ Tree结构，不过比较特殊，它一共有两层，第一层是所有的关键字，第二层则是每个关键字的一组指文档针，例如 “X” ->行1，行2，行3…。通俗解释全文索引结构就是：它是以关键字去找文档（行），而不是像其他一些索引以行主键来找其他列的内容。

全文索引的缺点：

占有存储空间更大，如果内存一次装不下全部索引，性能会非常差。
维护代价高，全文索引如果对文档修改10次，索引就需要维护10次，而普通的索引可能就只需要维护1次。
如果一个列上有全文索引则一定会用上，即使有性能更好的其他索引也不会用上。由于只是存储文档指针，也就没法用上索引覆盖。

ICP索引下推

索引下推，称为 Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。它的作用如下：

减少完整记录（一条完整元组）读取的个数；
对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚簇索引有效

ICP是否开启取决于index_condition_pushdown这个参数控制，而执行计划中，Using index condition表示使用到了ICP索引下推。

举例：
当没有开启ICP索引下推的情况下，MySQL查询数据的方式：

server执行一条DQL查询语句
存储引擎层获取到本次查询所需要的索引记录
存储引擎曾根据主键的值，去查询相关数据完整行记录
拿着查到的记录去给到server层与where条件进行比对。

开启ICP的情况下，MySQL查询数据的方式：

server执行一条DQL查询语句
存储引擎层去读取此条语句的相关索引记录（不是完整的行记录）
判断where条件中的部分条件，是否能用到相关索引的某一列进行检查，如果条件不满足就再处理一下条索引记录
如果条件满足的情况，使用索引中的主键去定位回表查询，读取完整的行记录
最后存储引擎再把查到的记录交给server层，server层检测此条记录是否满足条件的其余部分

# 创建一个示例索引
SQL > alter table t1 add index 'name_age_idx' ('name', 'age');

# 执行以下语句
SQL > select * from t1 where name like '张%' and age=10;

比如上方语句，name条件的结果为2，而sid条件的结果为1

在没有开启ICP的情况下，存储引擎层通过联合索引name_age_idx，找到name like ‘张%’ 条件的主键为id（1、4），然后通过1和4这两个主键id，一个一个的回表到聚簇索引找到完整的这两行数据的完整记录，（回表两次）返回给server层后，server层拿着记录，去通过age=10的条件去筛选。

如果是在开启了ICP索引的情况下，存储引擎层，根据name_sid_idx联合索引中的name和age列，存储引擎层直接通过联合索引，在索引中过滤一次age=10，过滤出一条数据，这时候是这一行的部分数据，如果只需要获取name和age列，比如select name,age。那就返回数据了，属于覆盖索引，但是我们此时是select * 需要拿到主键id，然后按照过滤后的数据再进行回表扫描。（回表1次）。