谈到 MySQL 索引，服务端的同学应该是再熟悉不过，很多人会通过索引联想到字典中的目录，这样的类比是很形象的。但是如果再往深处说，恐怕很多小伙伴就有点张口结舌了。
比如 MySQL 索引都有哪些类型呢？索引结构是什么样的呢？有了索引是如何检索数据的呢？
本文就围绕这些问题来探讨一下MySQL 索引的实现原理。
本文篇幅稍微有点长，希望你能耐心看下去，并有所收获。
索引结构
我们之前提到过，索引本质是提升查询效率的一种数据结构。
在数据库中，索引是分很多种类的（千万不要狭隘的认为索引只有 B+ 树，那是因为我们平时使用的基本都是 MySQL）。而不同的种类很显然是为了应付不同的场合，那索引到底有那些种类呢？
在目前的 MySQL 8.0 版本中，InnoDB 存储引擎支持的索引有：B+ 树索引、全文索引、R 树索引。
本文我们就先关注使用最为广泛的 B+ 树索引。
B+ 树
B+ 树索引是数据库系统中最为常见的一种索引数据结构，几乎所有的关系型数据库都支持它。
B+ 树是如何成为关系型数据库索引的主流数据结构的？
B+ 树的特点：
- 在 B+ 树中，所有数据记录节点都是按照键值的大小存放在同一层的叶子节点上，而非叶子节点只存储 key 的信息，这样可以大大减少每个节点的存储的 key 的数量，降低 B+ 树的高度；
- B+ 树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针；
- B+ 树的层级更少：相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快；
- B+ 树查询速度更稳定：B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定；
- B+ 树天然具备排序功能：B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高；
- B+ 树全节点遍历更快：B+ 树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。
总的来说，B+ 树是目前为止排序最有效率的数据结构。像二叉树，哈希索引、红黑树、SkipList，在海量数据基于磁盘存储效率方面远不如 B+ 树索引高效。
感兴趣的同学可以看看我的这篇文章：MySQL 为什么用 B+ 树实现索引
B+ 树索引结构
B+ 树索引由根节点（root node）、中间节点（non leaf node）、叶子节点（leaf node）组成，其中叶子节点存放所有排序后的数据。
当然也存在一种比较特殊的情况，比如高度为 1 的B+ 树索引。B+ 树的高度为1，只有一个页，该页即是根节点也是叶子节点。
MySQL中磁盘和内存交互的基本单位是页，一个页的大小一般是16KB，也就是16384字节。
如下表：
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`age` int DEFAULT NULL,
`user_id` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。
大家要牢记，索引是对记录进行排序， 高度为 1 的 B+ 树索引中，存放的记录都已经排序好了，若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。
随着插入 B+ 树索引的记录变多，1个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2。当 B+ 树的高度 >= 2 时，根节点和中间节点存放的是索引键对，由（索引键、指针）组成。
索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。
下图显示了 B+ 树高度为 2 时，B+ 树索引的样子：
在上面的 B+ 树索引中，若要查询索引键值为 4 的记录：
- 首先查找根节点，查到键值对（20，地址），这表示小于 20 的记录在地址指向的下一层叶子节点中。
- 接着根据下一层地址就可以找到最左边的叶子节点，在叶子节点中根据二叉查找就能找到索引键值为 4 的记录。
那一个高度为 2 的 B+ 树索引，理论上最多能存放多少行记录呢？
在 MySQL InnoDB 存储引擎中，一个页的大小为 16K，在上面的表 User 中，键值 userId 是BIGINT 类型，则：
根节点能最多存放以下多个键值对 = 16K / 键值对大小(8+6) ≈ 1100
假设表 User 中，每条记录的大小为 500 字节，则：
叶子节点能存放的最多记录为 = 16K / 每条记录大小 ≈ 32
综上所述，树高度为 2 的 B+ 树索引，最多能存放的记录数为：
总记录数 = 1100 * 32 = 35200
也就是说，35200 条记录排序后，生成的 B+ 树索引高度为 2。在 35200 条记录中根据索引键查询一条记录只需要查询 2 个页，一个根节点，一个叶子节点，就能定位到记录所在的页。
同理，树高度为 3 的 B+ 树索引，最多能存放的记录数为：
总记录数 = 1100（根节点） * 1100（中间节点） * 32 = 38720000
高度为 3 的 B+ 树索引竟然能存放 3800W 条记录。在 3800W 条记录中定位一条记录，只需要查询 3 个页。
一般根节点是常驻内存的，所以一般我们查找3800W数据，只需要2次磁盘IO。
那么 B+ 树索引的优势是否逐步体现出来了呢？
讲到这，我们了解了 B+ 树索引的组织形式，以及为什么选择 B+ 树作为索引的底层实现。
但 B+ 树的查询高效是要付出代价的，就是我们前面说的插入性能问题，接下去咱们就来分析一下。
索引维护
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。
以下图为例，如果要插入新的 行ID 为700，则只需要在 R5 的记录后面插入一个新记录。
如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页，整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。
合并的过程，可以认为是分裂过程的逆过程。
基于上面的索引维护过程说明，我们来讨论一个案例：
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的：
NOT NULL PRIMARY KEY AUTO_INCREMENT
插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
除了考虑性能外，我们还可以从存储空间的角度来看。
假设表中有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
由于每个非主键索引的叶子节点上都是主键的值。
如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。
不用业务字段做主键的原因：
- 业务字段不一定是递增的，有可能会造成主键索引的页分裂，导致性能不稳定。
- 二级索引存储的值是主键，如果使用业务字段占用大小不好控制，如果业务字段过长可能会导致二级索引占用空间过大，利用率不高。
有没有什么场景适合用业务字段直接做主键的呢？
还是有的。比如，有些业务的场景需求是这样的：
- 只有一个索引
- 该索引必须是唯一索引。
这是典型的 KV 场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
这时候我们就要优先考虑上一段提到的「尽量使用主键查询」原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
总结：
- 自增主键防止页分裂，逻辑删除并非物理删除防止页合并。
- 在表结构设计时，主键的设计一定要尽可能地使用顺序值，这样才能保证在海量并发业务场景下的性能。
了解了 B+ 树索引的结构，以及 MySQL 中对 B+ 树索引基本的管理，下面我们聊一聊 MySQL InnoDB 存储引擎的索引模型。
InnoDB 的索引模型
InnoDB 存储引擎是 MySQL 数据库中使用最为广泛的引擎，在海量大并发的 OLTP 业务中，InnoDB 必选。它在数据存储方面有一个非常大的特点：索引组织表（Index Organized Table，IOT）。
索引组织表
数据存储有堆表和索引组织表两种方式。
堆表中的数据无序存放， 数据的排序完全依赖于索引。
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
前面我们提到，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
每一个索引在 InnoDB 里面对应一棵 B+ 树。在 InnoDB 中，每一张表其实就是多个 B+ 树，即一个主键索引树和多个非主键索引树。
假设，我们有一个主键列为 ID 的表，表中有字段 age，并且在 age上有索引。
建表语句：
CREATE TABLE `user` (
`id` bigint NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`age` int(10) NOT NULL,
KEY `idx_age` (`age`)
) ENGINE=InnoDB
初始化语句：
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('15', 'Bob', '32' );
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('18', 'Alice', '25');
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('20', 'Jim', '37');
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('30', 'Eric', '26');
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('49', 'Tom', '24');
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('50', 'Rose', '28');
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('56', 'Jack', '26');
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('77', 'Mark', '38');
主键索引树和非主键索引树的示意图如下：
从上图我们看到，根据叶子节点的内容，索引类型分为主键索引和非主键索引。
- 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引(clustered index)。
- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引(secondary index)。
基于主键索引和普通索引的查询有什么区别？
- 如果语句是
select * from user where id = 15，即主键查询方式，则只需要搜索 ID 这棵 B+ 树； - 如果语句是
select * from user where age = 32，即普通索引查询方式，则需要先搜索 age 索引树，得到 ID 的值为 15，再到 ID 索引树搜索一次。这个过程称为回表。
回表
我们通过一个例子分析回表。
如下 SQL：
select * from user where age between 28 and 35;
需要执行几次树的搜索操作，会扫描多少行？
我们分析一下这条SQL查询语句的执行流程：
- 在 age 索引树上找到
age=28的记录，取得ID=50； - 再到 ID 索引树查到
ID=50对应的行； - 在 age 索引树下取一个值
age=32，取得ID=15； - 再回到 ID 索引树查到
ID=15对应的行； - 在 age 索引树下取一个值
age=37，不满足条件，循环结束。
在这个过程中，回到主键索引树搜索的过程，我们称为回表。
可以看到，这个查询过程读了 age 索引树的 3 条记录(步骤1、3和5)，回表了两次(步骤2和4)。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？
当然有，覆盖索引。
覆盖索引
如果执行的语句是：
select id from user where age between 28 and 35;
这时只需要查 ID 的值，而 ID 的值已经在 age 索引树上了，因此可以直接提供查询结果，不需要回表。
也就是说，在这个查询里面，索引 age 已经「覆盖了」我们的查询需求，我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
需要注意的是，在引擎内部使用覆盖索引在索引 age 上其实读了三个记录，也就是id_50、id_15、id_20（对应的索引 age 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 任务扫描行数是 2。
我们上面举的例子都是基于一个列进行索引排序和使用，比较简单。在实际业务中，我们会遇到很多复杂的场景，比如对多个列进行查询。
这时，就需要联合索引。
联合索引
联合索引又叫复合索引，是指由多个列所组合而成的 B+ 树索引，这和我们之前介绍的 B+ 树索引的原理完全一样，只是之前是对一个列排序，现在是对多个列排序。
从上图可以看到，组合索引只是排序的键值从 1 个变成了多个，本质还是一颗 B+ 树索引。
基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
假设这个市民表的定义是这样的：
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。
而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。
它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
给 id_card 和 name 建立联合索引后，name 的值也会被保存在 id_card 索引树的节点上，这样根据给定 id_card 的值找到的对应行时，就可以直接获取到 name 了，而不需要拿着对应的主键再进行回表操作。
排序时会优先比较第一个字段，如果值相同，再比较第二个字段的值，以此类推。
当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。
看到大家可能有个疑问，如果为每一种查询都设计一个索引，索引是不是太多了。
如果现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？
反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？
B+ 树这种索引结构，可以利用索引的「最左前缀」，来定位记录。
最左前缀原则
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.
MySQL 的联合索引可以用于包含索引中所有列的查询语句的查询，或者是仅包含第一列，前两列，前三列，等等。如果你在索引定义中以正确的顺序指定列，那么联合索引就可以加快对同一张表的多种不同类型的查询。（详见：官方文档）
简单来说，最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此索引就可以被用到。
为了直观地说明这个概念，我们用（name，age）这个联合索引来分析。
可以看到，索引项是按照索引定义里面出现的字段顺序排序的。联合索引先根据第一个字段排序，如果第一个字段有相同的，就按照第二个字段排序。
注意，这里仅仅有相同的第一个字段情况下，才会根据第二个字段排序。
当业务需求是查到所有名字是「张三」的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。
如果要查的是所有名字第一个字是「张」的人，对应 SQL 语句的条件是：
where name like '张 %'
这时，也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。
这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
基于上面对最左前缀索引的说明，我们来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。
如何安排索引内的字段顺序
这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。
因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
所以现在知道了，这段开头的问题里，我们要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持「根据身份证号查询地址」的需求。
那么，如果既有联合查询，又有基于 a、b 各自的查询呢？
查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候不得不维护另外一个索引，也就是说需要同时维护 (a,b)、(b) 这两个索引。
这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那就可以创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。
前面我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。那些不符合最左前缀的部分，会怎么样呢？
索引下推
索引下推（Index Condition Pushdown），简称 ICP。 是 MySQL5.6 之后新增的功能，旨在 在「仅能利用最左前缀索的场景」下（而不是能利用全部联合索引），对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤(where条件里的匹配)。
过滤会减少遍历索引查出的主键条数，从而减少回表次数，提示整体性能。
我们还是以市民表的联合索引（name, age）为例。
比如，我们要检索出表中「名字第一个字是张，而且年龄是 10 岁的所有男孩」。
那么，SQL 语句是这么写的：
select * from tuser where name like '张%' and age=10 and ismale=1;
根据前缀索引规则，所以这个语句在搜索索引树的时候，只能用 「张」，找到第一个满足条件的记录 ID3。
可能有的同学会问 age 也是索引中的字段，为什么不直接用完整的索引字段进行比较？
因为 name 列使用了 like ，导致后面的索引无法再以组合索引生效。
MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。
即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
然后判断其他条件是否满足。
在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
而 MySQL 5.6 引入的索引下推优化， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
如下两图是这两个过程的执行流程图。
这上面两个图里面，每一个虚线箭头表示回表一次。
第一个图中，在 (name,age) 索引里面我去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把「name 第一个字是’张’」的记录一条条取出来回表。因此，需要回表 4 次。
第二个图跟第一个图的区别是，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。
在我们这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。
总结
本文，我们分析了 B+ 树的特点，以及 B+ 作为索引底层实现的优势。了解了 B+ 树索引的结构，以及 MySQL 中对 B+ 树索引基本的管理：页分裂和页合并，这里建议在设计表结构时尽量选择自增主键。
我们重点分析了 InnoDB 的索引模型，它核心的概念：索引组织表。
我们还了解到，索引类型分为主键索引（聚簇索引）和非主键索引（二级索引）。
聚簇索引和二级索引的区别：
- 聚簇索引的叶子节点存放的是数据行（主键值也是行内数据），支持覆盖索引；而二级索引的叶子节点存放的是主键值或指向数据行的指针。
- 由于叶子节点(数据页)只能按照一棵B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引。
我们还聊到了回表问题以及通过覆盖索引进行性能优化。
在实际业务中，我们会遇到很多复杂的场景，比如对多个列进行查询。这时我们可以用到联合索引。
如果使用了联合索引但是没有遵循最左前缀原则，会造成索引失效的。
最后，我们分析了 MySQL5.6 之后新增的索引下推，通过减少回表次数提高效率。
如果你还想看更多优质原创文章，欢迎关注我的公众号「ShawnBlog」。
308
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
