《迅猛定位低效SQL?》留尾：selectid,namewherename='shenjian'selectid,name,sexwherename='shenjian'多查询了一个属性，为什么检索过程完全不同？什么是回表查询？什么是索引覆盖率？如何实现索引覆盖？在哪些场景下可以使用索引覆盖率来优化SQL？这些就是今天要分享的内容。画外音：本文实验基于MySQL5.6-InnoDB。1.什么是查询回表？让我们从InnoDB的索引实现开始。InnoDB有两种索引：聚集索引（clusteredindex）普通索引（secondaryindex）InnoDB聚集索引和普通索引有什么区别？InnoDB聚集索引的叶子节点存储行记录，因此，InnoDB必须有且只有一个聚集索引：如果表定义了PK，那么PK就是聚集索引；如果表没有定义PK，那么第一个非NULL唯一列是聚集索引；否则，InnoDB将创建一个隐藏的row-id作为聚集索引；画外音：所以PK查询很快，直接定位到行记录。InnoDB普通索引的叶子节点存储主键值。画外音：请注意，MyISAM索引叶节点存储记录指针，而不是存储行记录头指针。比如你不妨建一个表：t(idPK,nameKEY,sex,flag);画外音：id是聚簇索引，name是普通索引。表中有4条记录：1,shenjian,m,A3,zhangsan,m,A5,lisi,m,A9,wangwu,f,B两个B+树索引如上图：id是PK，聚簇索引，Leaf节点存储行记录；name为KEY，普通索引，叶子节点存放PK值，即id；既然普通索引不能直接定位到行记录，那么普通索引的查询过程是怎样的呢？通常，需要对索引树进行两次扫描编码。例如：select*fromtwherename='lisi';它是如何执行的？比如粉色路径需要对索引树进行两次扫描：首先通过普通索引定位到主键值id=5；然后通过聚集索引定位行记录；这就是所谓的回表查询，先定位主键值，再定位行记录，其性能低于扫描索引树。2.什么是索引覆盖率（Coveringindex）？嗯，楼主在MySQL的官网上没有找到这个概念。画外音：你学习严谨吗？借用SQL-Server官网的说法。在MySQL官网上，在explainqueryplanoptimization章节中出现了类似的说法，即explain输出结果的Extra字段为Usingindex时，可以触发索引覆盖。无论是SQL-Server的官网还是MySQL的官网，都表示SQL需要的所有列数据都可以在一个索引树上获取，不需要回表，速度更快。3、如何实现索引覆盖？一种常见的方法是将查询的字段构建成联合索引。还是《迅猛定位低效SQL?》中的例子：createtableuser(idintprimarykey,namevarchar(20),sexvarchar(5),index(name))engine=innodb;第一条SQL语句：selectid,namefromuserwherename='shenjian';可以打name索引，索引叶子节点存放主键id，id和name可以通过name的索引树获取，不需要回表，符合索引覆盖，效率更高。画外音，额外：使用索引。第二条SQL语句：selectid,name,sexfromuserwherename='shenjian';可以命中name索引，索引叶子节点存储主键id，但是sex字段必须回表获取，不满足索引覆盖，需要重新传id值扫描聚簇索引获取性别字段会降低效率。画外音，额外：使用索引条件。如果(name)单列索引升级为联合索引(name,sex)就不一样了。createtableuser(idintprimarykey,namevarchar(20),sexvarchar(5),index(name,sex))engine=innodb;可以看到：selectid,name...wherename='shenjian';selectid,name,sex...wherename='shenjian';可以在不返回表的情况下命中索引覆盖率。画外音，额外：使用索引。4、哪些场景可以使用索引覆盖来优化SQL？场景一：全表统计查询优化原表为：user(PKid,name,sex)；直接：从用户中选择计数（名称）；不能使用索引覆盖率。添加索引：altertableuseraddkey(name);您可以使用索引覆盖率来提高效率。场景二：列查询回表优化selectid,name,sex...wherename='shenjian';本例不再赘述，可以将单列索引（name）升级为联合索引（name，sex），避免返表。场景三：分页查询selectid,name,sex...orderbynamelimit500,100;将单列索引（name）升级为联合索引（name，sex）也可以避免返表。InnoDB聚簇索引普通索引、回表、索引覆盖，希望你在这1分钟里有所收获。提示，如果不明白explain结果的Extra字段使用索引的含义，请阅读前言文章：《如何利用工具，迅猛定位低效SQL?》【本文为专栏作者“58神剑”原创稿件，转载请联系原作者转载】点此查看作者更多好文
