[MySQL] 不会 B-Tree 索引，耶稣都留不住你，Hello 大家周四好呀。我是野区 JC。 MySQL 目前是

毕竟留不留得住你，是面试官说了算！

❤️ 前言

Hello 大家周四好呀。我是野区 JC。

MySQL 目前是很多公司在数据库技术选型方面的首选，而在存储引擎方面，因为 InnoDB 具备其他存储引擎所不具备的优秀特性，所以在技术选择上被使用的概率是非常高的，同时也非常值得我们去了解其内部的各种机制。

大家都明白，数据库本质上就是++存储数据++，最初的目的是为了对数据的增加/删除/修改/持久化等等操作。但是这几个本质功能操作，随着业务的复杂度提升，会延伸出非常多的问题。例如说，我们想在百万乃至千万级别数据中快速找到自己想要的数据，肯定不能通过循环的方式来检索到合适的数据，不管是软件和硬件耗费都太高了，所以我们不仅仅要做到的是++存储数据++这么简单，我们还需要考虑硬件软件的协调结合，包括数据结构与算法的优化等等。

如果你想加快检索速度，不仅仅需要知道索引怎么用，还要了解其内部的索引机制。

下面开始吧！

❤️ 介绍索引

索引是什么？

其实网上很多文章在介绍索引的时候，都会将其比喻成为“一本书的目录”，把每一页当作“数据库表中具体每一条数据”。

你可以通过目录“==定值查询==”，例如我要找内容是讲索引的那一页，那你可以每一页的小标题，迅速定位找到相应的页码；

你也可以进行“==范围查找==”，你说你要第七章 AND 第八章的所有内容，你能通过目录找到章节，通过简单的计算就可以得到相应的页码范围；

甚至你同事跟你说，它要进行“==模糊查询==”，要所有以“第七”开头的章节，这对于你来说很简单啊，仅仅通过对全书的所有章节的名称进行比对，就可以迅速定位到了数据对吧？

可能你会说，“++道理我们都懂，但是还是很茫然。你讲这个书的目录吧，我还是不懂究竟索引是怎么实现的？毕竟人脑可以通过动作和经验迅速掌握这个技巧，但是机器是通过怎么样才能实现这个索引的呢？++”

这个问题问得好，问得出这个问题说明你对索引的深层原理有了最原始，了解的欲望了。

❤️ 操作索引

创建索引的两种方式

通过用 CREATE INDEX 或 ALTER TABLE 来给表增加索引。

Alter Tbale

    ALTER TABLE tbl_name 
    | ADD {INDEX|KEY} {index_nmae}
    {index_type} {index_col_name, ...} {index_option}
    
    ALTER TABLE tbl_name
    DROP PRIMARY KEY
    | DROP {INDEX|KEY} index_name

例如我在用户表 Account 加上一个 username 索引。

    alter table sys_account add index index_username (username); // 普通索引
    alter table sys_account add unique (username);              //   UNIQUE 索引
    alter table sys_account add primary key (username);         //   主键索引

CREATE DROP INDEX

    CREATE {UNIOUE} INDEX index_name
    {index_type}
    ON tbl_name {index_col_name, ...}
    
    DROP INDEX index_name ON tbl_name

删除索引

通过使用 ALTER TABLE 或 DROP INDEX 语句。

例如我在用户表 Account 删除一个 username 索引。

drop index index_username on sys_account ;
alter table sys_account drop index index_username ;
alter table sys_account drop primary key ;

❤️ InnoDB 支持的几种常见的索引类型

B-Tree 索引
全文索引

而索引在检索数据的方面最主要做到的三个点来优化检索速度

索引减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机 IO 转为顺序 IO

B-Tree是最为常用和最有效的索引，因为它具备了比较齐全的关系型数据库所需的特性，而且性能也不差。（但是在某些特定情况下，哈希索引的检索速度比其快好几倍，所以具体情况具体分析）

这篇文章我会先介绍 B+ 树索引的相关知识。

❤️ 从数据模型(结构)与算法出发

二分查找法

二分查找法也称折半查找法，其时间复杂度为 O(log2n)。它的特点是，++查找的线性表必须是顺序存储结构++，++而且必须按关键字大小有序排序++。

假设我们的目标是从一组数据 A

5、7、9、11、15、19、21

假设我们检索的元素是 5，然后首先折半找到一个元素 A[3]，然后进行与 11 进行比较：如果相等就中止；如果不等，当 A[3] < 19，我们想右继续查找，当 A[3] > 19，我们向左边继续查找。周而复始，直到我们找到对应的元素。

可以看出，二分查找法数据不断地减少其搜索范围，跳跃式的检索因素。如果是顺序查找的话，至多要 O(n) 次才能找到该因素。

二叉查找树和平衡二叉树

二叉排序树（Binary Sort Tree），又称二叉查找树（Binary Search Tree），亦称二叉查找树。下面是它的一些特点

若左子树不为空，则左子树上的所有元素都小于其根节点
若右子树不为空，则右子树上的所有元素都大于其根节点
左、右均为二叉排序树（也就是递归下去，无论是子树还是子子树都具备二叉排序树的所有特点）
没有相同的节点

下面是一棵正常二叉排序树，接下来我会讲解++命中++的检索顺序。

假设我们需要找到 11这个元素，我们从顶根节点 8 开始。
因为 8 < 11，根据特点 ②，我们可以直接去检索右子树；
来到了节点 19，根据特点 ①，因为节点 19 > 11，所以我们继续检索左子树；
来到了节点 13，因为节点 13 = 13，所以最后命中结果！

对于这棵有 7 个元素的二叉树，我们只需要比较 2 次。若是非有序状态，我们则需要重头到尾检索一次，可见会造成多大的性能和算力的浪费。

但二叉排序树并非完美，在非正常的状态下，它可能是这样的

形成这种树的原因是树本身不能实现自平衡，导致单边挂在元素过多，渐渐演变为链表式单支树，最坏情况下效率可以与顺序查找等同，这样二叉树的检索优势将不复存在。

为了解决这个问题，算法大神们给出了几种解决方案

AVL 树（平衡二叉树）
红黑树
Treap(Tree + Heap)

这些解决方案都有自动维护，调节功能，避免二叉树变成单支树。下面我介绍一下 AVL 树。

其特点为

其本身就是棵二叉搜索树
任意一结点的两个左右子树的高度之差的绝对值最多为 1。

可以看出，AVL 树比二叉搜索树多了一个具备==自平衡==的特点，属于二叉搜索树的优化树。

B+Tree 树

B-Tree 树也是一棵具备自适应，但是比二叉搜索树具备高效率的树。

这是一颗具备两层节点的 B+Tree。

我们可以观察到，它并不像 AVL 或者其他二叉树那样仅仅只有两个节点，它可以拥有很多个节点。

我们从 AVL 等自平衡的二叉树可树可以知道，其实它们的平衡过程是不断地左旋转，右旋转，然后向上抽取的过程。

然而 B+Tree 不仅仅具备节点旋转的功能，下面来看下怎么保持平衡的。

插入

下面是 B+Tree 的一些操作。

序号	Leaf Page 满	Index Page 满	操作
①	NO	YES	直接将记录插入到叶子节点
②	YES	NO	1. 拆分 Leaf Page 2. 将中间元素抽取放到 Index Page 3. 小于中间节点的记录放在左边 4. 大于或等于中间节点的记录放右边
③	YSE	YES	1. 拆分 Leaf Page 2. 小于中间节点的记录放左边 3. 大于或等于中间节点的记录放右边 4. 拆分 Index Page 5. 小于中间节点的记录放左边 6. 大于或中间节点记录放右边 7. 中间节点放入上一层 Index Page

假设我们插入一个目标元素 28。首先第一层，由于目标元素大于 25 小于 50。所以定位在叶节点 2。根据规则 ①，叶节点没满，也就是 Leaf Page 没满，那可以直接插入了。

然后这时候又来了一个目标元素 70。老规矩，由于目标元素大于 50 小于 75，所以迅速定位到叶节点 3，但是叶节点已经满了。所以根据规则 ②，我们先将目标元素加入到节点 3 的所有元素，得到了一个数据列：

50 55 60 65 70

根据数据列中间位置的进行对半分，也就是 60。接下来我们是将这个数据列拆分成两个 Leaf Page。 ++小于 60 放在左 Leaf Page，等于或大于 60 的放在右 Leaf Page，并将 60 抽到上一层(Index Page)++。

目前Leaf Page3 和Leaf Page4 以及 Leaf Page 都满了。可以测试规则③。假设这时候插入一个目标元素 70。老规矩，我们可以定位到Leaf Page4。这时候，Leaf Page 满了，Index Page 也满了，我们需要考虑继续往上抽取多一层了。效果图如下

B+Tree 是适用于存储在磁盘上面，那就意味着 B-Tree 页分裂操作次数越大，也就是磁盘会进行频繁的数据迁移，IO 等耗费性能的操作。所以我们需要考虑怎么尽量减少页分裂的情况出现。

我们在平时建表的时候可以设一个非业务性质，唯一且自增的主键标识。当主键标识作为 B+Tree 的索引字段，索引值会按照顺序维护，而不会频繁的分裂调整，会有效的提升插入数据的效率，这样可以减少页分裂次数。

当然并不是每次像规则 ③ 那样的增加操作都会进

删除

在 B-Tree 在删除方面其实是差不多的。只不过删除的时候会使用 fill factor 来控制树的变化。假设元素少于一个页总容量的 50%，则进行页合并操作。下面是一些删除规则供参考，思考一下。

序号	叶子节点小于填充因子	中间节点小于填充因子	操作
①	NO	NO	直接从叶子节点删除。如果该节点还是 index Page 节点，则使用该节点的右节点代替
②	YES	NO	合并叶子节点和它的兄弟节点，同时更新 Index Page
③	YSE	YES	1. 合并叶子节点和它的兄弟节点 2. 更新 Index Page 3. 合并 Index Page 和它的兄弟节点

❤️ B-Tree 索引

所谓的 B-Tree 索引其实就是使用数据库使用 B-Tree 实现了索引。我们前面铺垫了很多数据结构和算法的信息，其实是一步一步地完善了 InnoDB 实现 B-Tree 所需的知识。

那为什么会使用 B-Tree 来实现索引？++根本的原因是因为出于对计算机内存+机械硬盘两层结构的考虑++。我们都知道，内存的执行速度比机械硬盘快不知道多少倍，但是内存是有限的且昂贵的，而机械硬盘相对是便宜的。所以在想使用 B-Tree 的特性来将内存和机械硬盘相结合，但是很可能检索一条数据会造成几次磁盘 IO 才能完成。

但是 InnoDB 中的 B-Tree 的实现做了许多优秀的优化。不知道你们是否留意到，如果一棵 B-Tree 在 Leaf Page 和 Index Page 不断地被装满的情况下，那么 B-Tree 的树层会不会不断地增加呢？如果想要了解清楚，这背后又是一个对 InnoDB 更加深层次的了解。

具体实现优化不讲，但是理论上 InnoDB 实现的 B-Tree 的层数能控制在 2~4 层，这也是说查找某一键值的记录时候，最多 2 ~ 4 次的 IO，也就是读取硬盘数据的次数。一般的机械硬盘每秒至少做 100 次 IO，所以查找的 IO 花费对于磁盘的读取能力还是很容易驾驭。在现实中，一棵 B-Tree 可能有十几万个页节点，每个页节点有一千条数据，如果没有 B-Tree 索引的话，估计每一次都要进行一次 IO，这得多耗费性能？

所以可想而知在 B-Tree 索引对提升检索数据的速度有多快。

`InnoDB` 在 `B-Tree` 上检索数据的过程

现在我们以实例进行讲解在 B-Tree 索引上检索信息的过程

假设我们要找目标元素 17。
首先会第一层上面的数据页加载进内存，然后根据二分查找法找到了位于 14 和 66 之间的数据页；
然后根据引用将相应的数据页加载进内存当中，根据二分查找法，找到了位于 14 至 20 之间的数据页；
最后，将该数据页加载进内存，再根据二分查找法找到了相应的数据列。

从整个加载过程，我们可以作出以下结论

每次查询，我们需要将一个数据页加载进内存而无须所有数据加载
我们可以通过减少主键的长度，这样子可以使加载的数据更多，而且在查找比对算法中速度更快
同时，使用间断的索引值(例如自增唯一的主键)，可以减少辅助索引的大小(后面介绍辅助索引会说明)。
发现没有，最后一层的 B+Tree 下面直接与一个 Data 挂钩，但是其他层却没有与 Data 挂钩，说明只要能定位到最后一层，我们就相当于找到了对应的数据行了。但是为什么 InnoDB 在设计的时候会这么设计呢？下面就讲到了我 InnoDB 的索引类型之一，聚簇索引。

聚簇索引(聚集索引/主键索引)

什么是聚簇索引？其实我们依旧可以用开头“书”的那个例子来比喻。我们一本书中每一章节的内容是由由一页或一页以上组成的。当我们翻开每一页的时候，书的顶部都会有第几章的字样。在这个例子中，每一章就是一个聚簇索引，而每一页都是一条一条数据。聚簇索引就好像一个有专属标示的队列，将所有相关的数据都放在队列中。当你想找到指定聚簇索引的数据时，可以在迅速找到其所有的数据。

再举一个例子，假设有一个用于记录用户浏览商品记录表，其中字段 username 是一个聚簇索引。所以在 B+Tree 索引上，是这样体现的。

估计看图你明白了，username=张三 作为索引，可以让 CPU 非常有精准性的这一个页数据一下子加载进内存，范围一下子缩小了，这样查找是不是很快？

聚簇索引是一种数据存储方式。具体的细节依赖于其实现方式。在 InnoDB 中聚簇索引实际上在同一个结构保存了 B-Tree 索引和数据列。当表有聚簇索引时，它的数据行实际上存放索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为一个一条数据行只能放在一个索引之下，所以一个表只能有一个聚簇索引。

关于聚簇索引的建立，会分为以下三种情况

如果你的表设了主键，那么 InnoDB 会将其用于聚簇索引。但是如果没有逻辑惟一的非空列或一组列，则添加一个新的自动递增列，其值将自动填充。
如果没有为表定义主键，MySQL将定位第一个惟一索引，其中所有键列都不是NULL, InnoDB将其用作集群索引。
如果表没有主键或合适的惟一索引，InnoDB 内部会在包含行 ID 值的合成列上生成一个名为 GEN_CLUST_INDEX 的隐藏集群索引。这些行按照 InnoDB 分配给此类表中的行的 ID 排序。行ID是一个6字节的字段，它随着插入新行而单调增加。因此，按行 ID 排序的行实际上是按插入顺序排列的

图上展现了聚簇索引的存放方式。红字“页节点”仅仅存储了索引值，而红字“叶子页”不仅存储了索引值，还有整行数据行。

关于聚簇索引的优点

可以把相关的数据保存在一起。
数据访问快。因为聚簇索引包含了数据行的数据；而且在物理上，是整块读取的，速度当然快。
使用覆盖索引扫描查询可以直接食用页节点中的主键值。
多数情况下，查询优化器倾向于使用聚簇索引，因为聚簇索引能够在 B+ 树索引的叶子节点上直接找数据。
因为定义了数据的逻辑性，聚簇索引 能够特别快地针对范围值的查询。
对于主键排序查找和范围查找速度非常快。

关于聚簇索引的缺点

本质上，聚簇索引是为了提高对 IO 密集型应用的性能。能够在有限的内存中，按需快速找到想要的数据，仅加载相应的数据块进入内存。如果内存都足够大了，整棵树都可以加载进内存，那么聚簇索引的优势不复存在。
插入速度依赖插入顺序。因为一旦没有按照乱序插入，对于 B+ 树的数据移位是非常耗性能的。
更新聚簇索引的代价是非常高的。你想象，一旦更新了，那么这条被更新的数据要重新去找到对应的位置。快的话可能就在隔壁，在大数据量的情况下，还是非常浪费性能的。
当一个页被被装满的时候，这时候就会面临着 Page Split (页分裂)的问题，也就是使用两个页来存储数据。这样会出现两个问题：1. 需要进行一次页分裂操作 2. 占用更多空间
聚簇索引可能会导致表扫描变慢。例如每一个聚簇索引仅仅有一条数据（也就是一个页仅有一条数据），显得行比较稀疏；或者因为页分裂了，平均每个页仅仅含有此聚簇索引 50% 的数据，那样子在存储上是不连续的。
二级索引（非聚簇索引）可能比想象得要大，因为二级索引的叶子节点存储的是主键值；而且二级索引是两次查找，需要额外的一次“回表”。

对于聚簇索引，关于 InnoDB 和 MyISAM 对比

在 MyISAM 依据数据插入的顺序存储在磁盘上的，主键索引和二级索引在结构上一致，主键索引就是一个名为 PRIMARY 的唯一非空索引。

MyISAM 是数据文件和索引文件分离的；InnoDB 是索引和数据在一起的。
MyISAM 是按照数据插入的顺序存储在磁盘上，它的 B-Tree 是通过引用了其对应的数据行的指针，所以即使在磁盘上是乱序的，但是在索引文件是有序的；而 InnoDB 是将数据文件和索引文件合在一起的。
也是因为数据的分布问题，MyISAM 实现聚簇索引的方式和 InnoDB 实现聚簇索引不一样。
通过上图可以知道，InnoDB 实现二级索引的方式和 MyISAM 实现的方式也不同。InnoDB 的叶子节点存的是主键值，这样的好处是减少了当出现行移动或者数据页分裂时二级索引的维护工作，也就是移动行时无须更新二级索引中的这个“指针”。相反使用主键值当作指针会让二级索引占用更多的空间。

辅助索引(非聚集索引/二级索引)

辅助索引也称非聚集索引/二级索引。我们在聚集索引的图上会看到一个二级索引的图。我们会发现在辅助索引最后一层，挂载的并不直接是数据行，而是聚集索引。因为辅助索引的检索顺序是，++首先会根据辅助索引的顺序，筛选到相应的数据后，也就是拿到各个确定的节点后（因为节点存储了主键标示），再回聚集索引的 B+ 树上，检索出相应的数据，这个回到聚集索引的动作叫做“回表”++。

联合索引

在 InnoDB 可以创建以单个字段作为索引，也可以使用多个字段联合组建索引，这种联合性组建索引被称为联合索引。但是无论你是单个字段索引还是多个字段联合索引，InnoDB 都会为每个索引建立一个 InnoDB 索引。(当然你是在使用 B-Tree 的前提下)。

这个的好处，联合索引可以把一个索引当作多个索引。假设我们使用一个表的三个字段: a/b/c 组建了索引 (a,b,c)。这个索引等同于 (a)/(a，b)/(a，b，c)。

但是使用联合索引的时候，需要注意它的生效方式。

覆盖索引

根据官方文档介绍，InnoDB ++存储引擎支持覆盖索引。而所谓的覆盖索引，指的是从辅助索引中即可查询到记录，不需要回表操作，再次到聚集索引查询相关记录。++

其实我看到第一眼看到这个，有点懵。这跟我们上面学的辅助索引有啥区别？不需要回表操作怎么做到？后来一想，恍然大悟，原来是利用索引返回 SELECT 列表中的字段，也就是索引已经存储列的值了。

无需回表操作我们就可以节省了非常多次因回表而产生的 IO 操作，同时包括在内存上面的排序等等操作；索引的大小往往比数据行要小得多，所以我们无需想聚集索引那样每次访问整行数据，在覆盖索引就可以拿到相关的值；索引值是具备顺序性的，对于范围式查询也能很快执行。

使用 B-Tree 索引的高效策略？

根据上面我们了解 B-Tree 结构后，结合数据结构和上面几个索引的介绍，或许你能思考怎么使用索引最高效了。B-Tree 适合全键值对，键值范围查找或键前缀查找。

我们来看下面的例子。

假设我们有个表，表中两个字段 name 以及 age。

create table People (
    name varchar(50) not null,
    age  varchar(10) not null,
    key(name, age)
)

全值匹配
```
    SELECT * FROM student WHERE name = 'Air'；
```
因为有有关 name 的索引，会匹配第一列，所以索引生效。
匹配最左前戳
```
    SELECT * FROM student WHERE name = 'Air'；
```
是指检索条件必须要有索引的第一列。如果 SQL 是这样的
```
    SELECT * FROM student WHERE age = '15'；
```
这样的索引是无法生效的。
匹配列前戳
```
    SELECT * FROM student WHERE name like 'Ai%'；
```
所谓的列前戳指的是以 Air 开头的人名。为什么是最左前戳呢？因为关于最左的关键字，InnoDB 可以转换为集合 ['Air', 'Air*']。（集合里面的 * 是指通配符）在有序集合下可以直接匹配就完事了。如果换做最右后戳，InnoDBpage 需要一个一个进行比较才行。
匹配范围值
```
    select * from student where name > 'apple' AND name < 'dog';
```
这样可以通过比较找到名字为 apple 与 dog 之间的人。其实这个也很好理解，因为 B+ 树是有序的，模糊匹配相关范围的块，就可以一起加载返回客户端了。
精确匹配某一列并范围匹配另外一列
```
    select * from student where name = 'apple' AND age < 15
```
这个是指 name 使用索引的第一列进行精确匹配，找到对应的数据，然后 age 在这段数据中进行筛选。
只访问索引的查询
B+ 树通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。后面的“覆盖索引”我会将将相应的优化。

❤️ 结语

这篇文章刚开始写的时候是想从概念入手，然后通过数据结构与算法循序渐进的说明，侧重出其重要性，最后介绍 B-Tree 的实现是如何和索引结合，在计算机硬件与软件两者之间进行权衡考虑，作出最优的表现，最后再介绍一些索引优化策略。

我觉得在学习数据库的，特别是索引，如果深入下去，写出来的文章不仅仅是几百几千字。所以，如果对数据库有兴趣的人，一定深入去要好好去体验一番。

[MySQL] 不会 B-Tree 索引，耶稣都留不住你，