对于Mysql索引的理解（二）

对于Mysql的索引有很多比较专业的词汇，这次就来讲讲这些回表查询、索引覆盖、最佳左前缀、索引下推。

1. 回表查询

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。
我们在往表里面插入数据的时候，必须要有一个key值。若这张表中有主键，就取主键当做key值，如果没有主键，就取一个唯一键来当做key值，如果也没有唯一键，那么就生成一个6字节的row_id来当做key值，以下就以主键为key值为例。

Mysql回表查询.png

如图所示，如果我们以主键为条件去查询的话，就会直接走主键索引的B+树，这棵B+树中的叶子节点存储的就是整个对应数据行的数据，我们只需要扫描着一次B+树即可。但如果我们以name字段为条件的话，就不能直接走主键索引的这棵B+树了，而是要先去name字段的这棵B+树中找，可以找到对应的主键，再去扫描主键索引的B+树，即可获得数据，这就是回表查询，需要扫描两次索引B+树。因此我们要尽量的避免回表查询。

2. 索引覆盖（covering index）

指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。
我们还是以上面的这张图为例，先写两个SQL：

1. select * from user where name = "张三"
2. select id from user where name = "张三"
   这两条语句就很好的体现了这个索引覆盖。第一条语句，就必须像刚才说的那样，扫描两次B+树，进行回表查询（这里图画的简略了点，实际上这个表还有很多其他的字段）。而第二条语句呢，它也会先去扫描name索引的B+树，扫描完，就能得到id字段值了，那就没有回表的必要了。直接从索引中就获得了需要的数据。

3. 最佳左前缀

即最左优先，在检索数据时从联合索引的最左边开始匹配
我们先假设建立一个name，age的组合索引
现在有四条SQL语句

1. select * from user where name = "xxx" and age = xx;
2. select * from user where name = "xxx";
3. select * from user where age = xx ;
4. select * from user where age = xx and name = "xxx";
   请问哪条语句的索引会失效？
   答案是只有第三条，这就是最佳左前缀。可能有人会问，为什么第四条可以？因为Mysql中有一个优化器，它会帮我们把顺序换过来。

4. 索引下推（Index Condition Pushdown）

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
我们还是假设建立一个联合索引（name，age）
在5.6以前：Mysql会忽略age这个字段，直接通过name进行查询，假设在(name,age)这课树上查找到了两个结果，id分别为1,2，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。
在5.6以后：InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。这样减少了回表次数，也就减少了IO。