MySQL之索引深度探究（下）

覆盖索引

假设需要执行一条语句：

select * from T where k between 3 and 5;

最初对列k建立了索引，表中的数据呈现如下状态：

其执行过程为：

• 在k索引树上找到k值为3的记录，获取到ID=300；
• 前往ID索引树中查找ID=300对应的记录。
• 在k索引树上找到下一个符合k在3到5之间的记录，得到ID=500；
• 再到ID索引树里查找ID=500对应的记录。
• 在k索引树上找到下一个值为6的记录，发现不满足既定条件。

此过程中发生了回表操作。倘若建立的索引能够“覆盖”我们的查询需求，那就无需进行回表，这样的索引被称作覆盖索引。比如在上述查询语句中，若查询的是列k而非全部字段*，便是一个覆盖索引的情形。

需要注意的是，在引擎内部运用覆盖索引时，在索引k上实际能读取到R3 - R5这三个记录，但由于Server层只会从引擎处获取两条记录，所以MySQL将扫描行数判定为2。

最左前缀原则

B+树这种索引结构，能够凭借索引的最左前缀来定位记录。

例如建立了(name,age)的联合索引：

当要查询name="张三"时，能够快速定位到ID4，然后往后进行遍历。

当要查询where name like "张%"时，也能够利用该索引，定位到ID3，接着往后遍历。

所以，索引的最左前缀可以是联合索引的最左边的N个字段，也可以是字符串索引的最左边的M个字符。

索引下推

还是假设建立了(name,age)的联合索引，现在有一条SQL语句如下：

select * from tuser where name like '张%' and age=10 and ismale=1;

依据最左前缀原则，这条语句能够利用name的索引。

对于其他条件的判断：

• 在MySQL 5.6之前，只能从找到的第一个记录ID3开始逐个进行回表操作，到主键索引中找出数据行，再对比后面的两个字段。
• MySQL 5.6引入了索引下推优化，能够在索引遍历过程中，先对索引中包含的字段进行判断，直接过滤掉不满足条件的记录，以此减少回表次数。

两者的对比情况如下，第一张图是没有索引下推的状况，第二张图是有索引下推的状况，箭头表示回表的走向：