什么是回表查询
小伙伴们可以先看这篇文章了解下什么是聚集索引和辅助索引：Are You OK？主键、聚集索引、辅助索引，简单回顾下，聚集索引的叶子节点包含完整的行数据，而非聚集索引的叶子节点存储的是每行数据的辅助索引键 + 该行数据对应的聚集索引键（主键值）。
假设有张 user 表，包含 id（主键），name，age（普通索引）三列，有如下数据：
id name age
1 Jack 18
7 Alice 28
10 Bob 38
20 Carry 48
画一个比较简单比较容易懂的图来看下聚集索引和辅助索引：
- 聚集索引：
- 辅助索引（age）：
如果查询条件为主键，则只需扫描一次聚集索引的 B+ 树即可定位到要查找的行记录。举个例子：
select * from user where id = 7;
查找过程如图中绿色所示：
如果查询条件为普通索引（辅助索引） age，则需要先查一遍辅助索引 B+ 树，根据辅助索引键得到对应的聚集索引键，然后再去聚集索引 B+ 树中查找到对应的行记录。举个例子：
select * from user where age = 28;
上述 select * 等同于 select id, age, name 对吧，id 是主键索引，age 是普通索引，而 name 并不存在于 age 索引的 B+ 树上，所以通过 age 索引查询到 id 和 age 的值之后，还需要去聚集索引上才能查到 name 的值。
如图所示，第一步，查 age 辅助索引：
第二步，查聚集索引：
这就是所谓的 回表查询，因为需要 扫描两次索引 B+ 树，所以很显然它的性能较扫一遍索引树更低。
什么是覆盖索引
覆盖索引的目的就是避免发生回表查询，也就是说，通过覆盖索引，只需要扫描一次 B+ 树即可获得所需的行记录。
如何实现覆盖索引
上文解释过，下面这个 SQL 语句需要查询两次 B+ 树：
select * from user where age = 28;
我们将其稍作修改，使其只需要查询一次 B+ 树：
select id, age from user where age = 28;
之前我们的返回结果是整个行记录，现在我们的返回结果只需要 id 和 age。
id 是什么？主键索引（聚集索引），age 是什么？普通索引（辅助索引），age 索引的 B+ 树的叶子节点存储的是什么？辅助索引键 + 对应的聚集索引键
所以这条 SQL 语句只需要扫描一次 age 索引的 B+ 树就行了
这样，结合这个例子，不知道各位有没有受到启发，如何实现覆盖索引拒绝回表查询呢？
答： 联合索引。
我们把 age,name 设置为联合索引：
create index idx_age_name on user(age,name);
此时 age 和 name 作为辅助索引键都在同一棵辅助索引的 B+ 树上，所以只需扫描一次这个组合索引的 B+ 树即可获取到 id、age 和 name，这就是实现了索引覆盖
覆盖索引的常见使用场景
在下面三个场景中，可以使用覆盖索引来进行优化 SQL 语句：
1） 列查询回表优化（如上面讲的例子，将单列索引 age 升级为联合索引（age, name））
2） 全表 count 查询
举个例子，假设 user 表中现在只有一个索引即主键 id：
select count(age) from user;
可以用 explain 分析下这条语句，如果 Extra 字段为 Using index 时，就表示触发索引覆盖：
显然现在是没有触发覆盖索引的，我们来优化下：将 age 列设置为索引 create index idx_age on user(age)，这样只需要查一遍 age 索引的 B+ 树即可得到结果：
3） 分页查询
select id, age, name from user order by username limit 500, 100;
对于这条 SQL，因为 name 字段不是索引，所以在分页查询需要进行回表查询。
Using filesort 表示没有使用索引的排序，或者说表示在索引之外，需要额外进行外部的排序动作。看到这个字段就应该意识到你需要对这条 SQL 进行优化了。
使用索引覆盖优化：将 (age, name) 设置为联合索引，这样只需要查一遍 (age, name) 联合索引的 B+ 树即可得到结果。
我是小牛肉，长风破浪会有时，小伙伴们下篇文章再见 👋
🎉 关注公众号 | 飞天小牛肉，即时获取更新
- 博主东南大学硕士在读，携程 Java 后台开发暑期实习生，利用课余时间运营一个公众号『 飞天小牛肉 』，2020/12/29 日开通，专注分享计算机基础（数据结构 + 算法 + 计算机网络 + 数据库 + 操作系统 + Linux）、Java 技术栈等相关原创技术好文。本公众号的目的就是 让大家可以快速掌握重点知识，有的放矢。关注公众号第一时间获取文章更新，成长的路上我们一起进步
- 并推荐个人维护的开源教程类项目： CS-Wiki（Gitee 推荐项目，现已累计 1.8k+ star）, 致力打造完善的后端知识体系，在技术的路上少走弯路，欢迎各位小伙伴前来交流学习 ~ 😊
- 如果各位小伙伴春招秋招没有拿得出手的项目的话，可以参考我写的一个项目「开源社区系统 Echo」Gitee 官方推荐项目，目前已累计 900+ star，基于 SpringBoot + MyBatis + MySQL + Redis + Kafka + Elasticsearch + Spring Security + … 并提供详细的开发文档和配套教程。公众号后台回复 Echo 可以获取配套教程，目前尚在更新中。
Original: https://www.cnblogs.com/cswiki/p/15236876.html
Author: 飞天小牛肉
Title: 你的 SQL 还在回表查询吗？快给它安排覆盖索引
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/714861/
转载文章受原作者版权保护。转载请注明原作者出处！