mysql覆盖索引与回表

select id,name where name='shenjian'
select id,name,sex* where name='shenjian'*

多查询了一个属性，为何检索过程完全不同？
什么是回表查询？
什么是索引覆盖？
如何实现索引覆盖？
哪些场景，可以利用索引覆盖来优化SQL？
这些，这是今天要分享的内容。
画外音：本文试验基于MySQL5.6-InnoDB。
一、什么是回表查询？
这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

• 聚集索引(clustered index)
• 普通索引(secondary index)

InnoDB聚集索引和普通索引有什么差异？
InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
画外音：所以PK查询非常快，直接定位行记录。
InnoDB普通索引的叶子节点存储主键值。
画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。
举个栗子，不妨设有表：
t(id PK, name KEY, sex, flag);
画外音：id是聚集索引，name是普通索引。
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

image
两个B+树索引分别如上图：
（1）id为PK，聚集索引，叶子节点存储行记录；
（2）name为KEY，普通索引，叶子节点存储PK值，即id；
既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？
通常情况下，需要扫码两遍索引树。
例如：
select * from t where name=’lisi’;
是如何执行的呢？

image
如粉红色路径，需要扫码两遍索引树：
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
二、什么是索引覆盖**(Covering index)**？
额，楼主并没有在MySQL的官网找到这个概念。
画外音：治学严谨吧？
借用一下SQL-Server官网的说法。

image
MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

image
不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
三、如何实现索引覆盖？
常见的方法是：将被查询的字段，建立到联合索引里去。
仍是《迅猛定位低效SQL？》中的例子：
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
第一个SQL语句：

image
select id,name from user where name=’shenjian’;
能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。
画外音，Extra：Using index。
第二个SQL语句：

image
select id,name,sex from user where name=’shenjian’;
能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。
画外音，Extra：Using index condition。
如果把(name)单列索引升级为联合索引(name, sex)就不同了。
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;

image
可以看到：
select id,name … where name=’shenjian’;
select id,name,sex … where name=’shenjian’;
都能够命中索引覆盖，无需回表。
画外音，Extra：Using index。
四、哪些场景可以利用索引覆盖来优化SQL？
场景1：全表count查询优化

image
原表为：
user(PK id, name, sex)；
直接：
select count(name) from user;
不能利用索引覆盖。
添加索引：
alter table user add key(name);
就能够利用索引覆盖提效。
场景2：列查询回表优化
select id,name,sex … where name=’shenjian’;
这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。
场景3：分页查询
select id,name,sex … order by name limit 500,100;
将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。
InnoDB聚集索引普通索引，回表，索引覆盖，希望这1分钟大家有收获。

88人点赞

数据库

“小礼物走一走，来简书关注我”
赞赏支持
共2人赞赏

Harri2012
总资产3 (约0.18元)共写了2.6W字获得94个赞共42个粉丝
关注

全部评论23只看作者

按时间倒序

按时间正序

会飞的蜗牛F
18楼 2020.11.24 15:37
可以写参考地址
赞 回复

b252d8021084
17楼 2020.11.08 01:20
讲得很棒
赞 回复

gengen0326
16楼 2020.10.17 18:03
感谢大佬，豁然开朗！！
赞 回复

chopinw
15楼 2020.07.15 11:00
一看‘画外音’就知道是抄的构架师之路，连个备注也没 恶心人
赞 回复

PythonAV
2020.07.15 14:54
啥叫超呢 这点我不同意 叫借鉴
回复

始不垂翅终能奋翼
2020.08.05 15:34
@PythonAV 你借鉴连个出处都不标注的？
回复

BugBigData
2020.08.25 13:54
抄了关你屁事，你可以不看~~！
回复
添加新评论

黯然记起
14楼 2020.06.18 18:35
能不能别一个劲的画外音
1 回复

Linrundong
13楼 2020.06.04 19:56
举的例子很清晰，一看就能懂
赞 回复

yi一方
12楼 2020.05.26 18:03
搜素的前几篇都是一样的内容 不知道谁抄谁的
赞 回复

IamHYN
2020.06.27 09:53
原作者是公众号“架构师之路”，用人家的文章也不标哥出处
回复
添加新评论

程序猿爱玩
11楼 2020.05.16 23:42
刚好接触这个概念，看您这个文章直接看懂了
赞 回复

肚导菠萝
10楼 2020.04.22 15:12
受教了，mark一下
赞 回复

upside_dow_e0f3
9楼 2020.04.12 20:39
写的非常棒 言简意赅 简单明了 不仅学习了知识 还学到了如何做一个好的分享 感谢
赞 回复

• 1
• 2

• 下一页

  被以下专题收入，发现更多相似内容

  

  推荐阅读更多精彩内容

• 马士兵到底靠不靠谱，马士兵的课程值不值得报？
  马士兵老师褒贬不一。但是据我说知：所有的身边朋友在学完马士兵老师的课之后，无一不竖起大拇指“马老师确实牛逼”，…
  阅读 341评论 1赞 0

• 为什么ThreadLocal用开放寻址法解决Hash冲突？
  问题来了什么是HASH?   这就是HASHhttps://www.jianshu.com/p/758f783d…
  [阅读 1,799评论 0赞 2
• 为什么索引能提高查询速度
  MySQL 的基本存储结构是页(记录都存在页里边)： 各个数据页可以组成一个双向链表https://www.ji…
  [阅读 533评论 0赞 6
  
• MySQL干货！21个写SQL的好习惯
  【导语】本文分SQL后悔药， SQL性能优化，SQL规范优雅三个方向，分享写SQL的21个好习惯~ 1. 写完SQ…
  阅读 171评论 0赞 4
  
• hashmap为什么用红黑二叉树而不用B+树
  通过查看我的mysql底层为什么用B+树做索引存储https://www.jianshu.com/p/99aabf…
  阅读 4,047评论 5赞 8

广告

Harri2012
关注
总资产3 (约0.18元)
美团配送资金安全治理之对账体系建设
阅读 267
Java中的四种引用类型
阅读 164

推荐阅读

MySQL优化
阅读 1,452
Mysql、索引
阅读 1,369
MySQL复习知识点总结(针对校招|持续更新中)
阅读 1,673
第五章 索引与算法 阅读总结
阅读 570
MySQL索引
阅读 55

评论23
赞88