《丁奇-MySQL45讲-05》之归纳总结

05 | 深入浅出索引（下）

• 覆盖索引：如果查找的列本身就在二级索引上，那么就不要回表了，这样子的索引叫做覆盖索引，通常用来减少回表的次数以提高性能。

• 联合索引：使用多个字段组合的索引，假设有联合索引（a，b），那么在索引树中它会先根据a的大小进行排序，如果有相同值的a在按照b的大小进行排序。

• 最左前缀：可以是联合索引的最左N个字段，比如说有联合索引（a，b，c），那么等值查询条件是a = 1的时候会用到联合索引中的部分索引a，条件是a = 1 and b = 2的时候会用到联合索引中的部分索引（a，b），同理条件是a = 1 and b = 2 and c = 3，而对于条件是a = 1 and c = 3的情况下会使用也只能用到联合索引中的部分索引a，而剩下的c = 3这个条件语句在MySQL5.6版本以后会使用索引下推技术来减少回表的次数（也就是说索引下推是会回表的，如果不需要回表的话就不会使用索引下推了），在5.6版本以前会先通过a = 1这个条件拿到主键，然后通过主键进行回表，拿到C的值，进而判断C是否等于3，而索引下推技术在这个过程中就不需要回表，直接在a = 1的对应联合索引上判断c的值，如果是3就是满足条件的，如果不是的话就遍历下一个联合索引来继续判断a = 1。对于没有包含a条件的SQL语句将不会使用到联合索引，也就是最终会全表进行扫描。这上面的内容说的都是等值查询，包含=、IN，对于范围查询（< 、>、 between、IS Null）后面的条件来说，它会直接停止匹配，也就是说条件是a = 1 and b > 2 and c = 3的时候会用到联合索引中的部分索引（a，b）这里为什么说是用到部分索引（a，b），而不是单独只有a呢，因为在使用explain select * from tuser where a = 1 and b = 2 / explain select * from tuser where a = 1 and b > 2 / explain select * from tuser where a = 1 and b > 2 and c = 3 这三种情况的结果key_len是一样，故而我认为三者都用到了部分索引（a，b），同时使用索引下推技术，至于有没有用到索引下推，可以使用explain看看Extra，如果出现Using index condition就表示使用了索引下推，这里的范围查询我没有加上like，是因为它比较特殊，like '张%'和like '%张'还是两种情况，对于第一种来说，它并不会停止匹配（有人认为会停止匹配，但我自己测试不会，所以我保留自己的观点），而对于第二种情况来说，它还要看要查询出来的列是否包含在二级索引中（覆盖索引），如果有的话那就会遍历二级索引，如果没有的话就是全表扫描了。

• 索引下推：MySQL5.6版本才有的技术，减少了回表次数，提高性能与效率。

• MRR（Multi-Range Read）：通常情况下使用二级索引去回表的时候都是先找到其中一个二级索引，然后回表查询，接着在继续拿下一个二级索引，来来回回，可能会以随机IO的方式读取，有了MRR的特性，它并不是一次一次地读取，而是先找出符合条件的二级索引，然后将其对应的主键缓存起来，在最后进行主键的排序，按照这个顺序去一次性回表，这样子读取磁盘的顺序就会变成顺序IO，可以提升性能，但由于MRR算法不是很成熟，所以默认情况下是关闭的。

• 场景：有二级索引c，有主键索引（a，b），InnoDB会把主键索引放到二级索引的后面，即联合索引（c，a，b），也会去重，也就是说如果联合索引是（c，a），最终的联合索引也会是（c，a，b），而当联合索引是（c，b）时，最终的联合索引是（c，b，a），所以在思考题中是不需要在创建（c，a）索引，因为已经有了联合索引（c，a，b），而需要在创建（c，b）索引。