vlambda博客
学习文章列表

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

NOT NULL PRIMARY KEY AUTO_INCREMENT

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查询语句的执行流程：

1. 在 age 索引树上找到 age=28 的记录，取得 ID=50；

2. 再到 ID 索引树查到 ID=50 对应的行；

3. 在 age  索引树下取一个值 age=32，取得 ID=50；

4. 再回到 ID 索引树查到 ID=15 对应的行；

5. 在 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官方文档：

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.

简单来说，最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此索引就可以被用到。

为了直观地说明这个概念，我们用（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 之后新增的索引下推，通过减少回表次数提高效率。