11.Mysql之回表、最左匹配、索引下推

1.前言：

　　通过前面的两节，很容易简单地理解Mysql索引到底是干什么用的，不过这里说的回表、最左匹配、索引下推又是什么呢？

2.回表：

　　1.我们都知道Mysql索引对数据库的查询有很重要的位置，通过通过索引很快的查找到需要的数据，通过Mysql的索引分为两种：一种是主键索引，一种是辅助索引(辅助索引)，主键索引就是按照主键字段进行构成的索引组织表，通常我们在按照主键索引查询数据时，是直接就能返回所需要的记录的，因为主键索引的叶子节点上记录了该主键索引字段对应的行记录，但是如果我们要是通过辅助索引查找数据时，我们只能在它的叶子节点上先查找它对应的主键，然后再去主键索引上查找它的行记录，这种按照：辅助索引-->主键索引--->row记录的过程，我们就称之为回表操作。

　　2.从上面来看回表操作的过程，我们发现要经过两次的B+树搜素，这样相对与一次的搜素，它浪费了时间还消耗了磁盘的i/O

　　3.那么我们怎样可以避免回表操作呢？

• 能尽量用主键索引查询就能查询结果的的都用主键索引进行查询
• 不能直接用主键索引查询的，我们最好建立联合索引，其实联合索引只是减少了查询过程中回表的次数，但有时也能完全消除操作，具体还是要看select 后面所要返回的字段信息，
• 建立联合索引的其中的一个技术是用到了索引覆盖(索引覆盖其实也是减少查询过程中回表的次数)，通过我们在执行计划中的Extra字段中会有‘use index‘信息出现

3.最左匹配原则

　　1.最左匹配其实主要就是针对联合索引的，通过联合索引都是两个字段以上的组合，但通过不要超过三个字段，应该字段越多组成的联合索引后期的维护的成本也就越高

　　2.通过我们使用索引进行查询时，往往会发现执行计划中没有用到我们索建立的索引，这样就导致我们建立的索引失效，因此知道索引匹配原则，而对于Mysql而言，索引匹配原则用的是最左匹配原则，知道最左匹配原则，对我们建立索引是要很大帮助的。

　　3.这里关于最左匹配原则用如下一张表来表示:  其中(a,b,c)是构造的联合索引

4.索引下推

　4.1简介:

• 索引条件下推(index condition pushdown)，简称ICP.mysql5.6新添加的，用于优化数据查询
• 当你不使用ICP,通过使用费主键索引(普通索引or二级索引)进行查询，存储引擎通过索引检索数据，然后在返回mysql服务器，服务器再判断是否符合条件。
• 使用ICP, 当存在索引的列作为判断条件时，mysql服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合mysql服务器传递条件，只有当索引符合条件是才会将数据检索出来返回给mysql服务器　　　　

　 4.2.示例：

　　在开始之前先准备一张用户表(user)，其中主要有几个字段：id ,name age .address. 然后在建立联合索引(name,age)

　　要求：匹配姓名第一个字为陈，年龄为20岁的用户

SELECT * from user where  name like ‘陈%‘ and age=20

　　看看这条sql语句是如何执行的？

　　5.6版本之前：

• 执行过程：首先忽略age这个字段，直接通过name字段在联合索引(name,age)进行寻找，查到对应的结果id为1，2，然后再根据主键id进行回表操作，注意这里的回表是要进行两次的，意思是每查询到对应的id就立刻进行回表查询数据，然后再传到server层进行age字段的过滤，最后再将最终的结果集返回给客户端。

　　5.6版本以及之后的版本

• nnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。然后再传送到server层，最后再由server层将结果集传送给客户端。

　4.3 总结

• 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率
• 关闭索引下推可以使用如下命令，配置文件的修改不再讲述了，毕竟这么优秀的功能干嘛关闭呢：

set optimizer_switch=‘index_condition_pushdown=off‘;

11.Mysql之回表、最左匹配、索引下推

原文：https://www.cnblogs.com/zmc60/p/14938740.html