从头开始给你讲清楚MySQL回表查询和索引覆盖是怎么回事

又到了金三银四的跳槽季，现在好多小伙伴都准备跳槽，手里面试题想必也是非常多，多份面试题，就多份面试成功的几率。前几天有个同学面试了几家公司，告诉我说，每次面试都会遇到MySQL回表查询和覆盖索引，我回答的都不是很完整。所以今天就大家从头到尾讲清楚回表查询和覆盖索引是怎么回事。

学习目录：

回表查询
覆盖索引
哪些场景可以利用索引覆盖来优化SQL
覆盖索引的优点
覆盖索引的注意事项
一、首先我们先来看一下什么是回表查询？

要说回表查询，先要从InnoDB的索引实现说起。InnoDB有两大类索引，一类是聚集索引(Clustered Index)，一类是普通索引(Secondary Index)。

InnoDB的聚集索引：InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有且只有一个聚集索引。

1.如果表定义了PK(Primary Key，主键)，那么PK就是聚集索引。

2.如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。

3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

这种机制使得基于PK的查询速度非常快，因为直接定位的行记录。

InnoDB的普通索引：InnoDB普通索引的叶子节点存储主键值(MyISAM则是存储的行记录头指针)。

回表查询：假设有个t表(id PK, name KEY, sex, flag)，这里的id是聚集索引，name则是普通索引。

表中有四条记录：

聚集索引的B+树索引(id是PK，叶子节点存储行记录)：

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
普通索引的B+树索引(name是KEY，叶子节点存储PK值，即id)：

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
普通索引因为无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的。

select * from t where name = ‘lisi’;

这里的执行过程是这样的：

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
粉红色的路径需要扫描两遍索引树，第一遍先通过普通索引定位到主键值id=5，然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些。

二、什么是覆盖索引

如果一个索引覆盖(包含)了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。

索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引(可以是单列索引，也可以一个索引语句定义所有要查询的列，即联合索引)，这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。

总之一句话：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。（这个不难理解吧）

三、哪些场景可以利用索引覆盖来优化SQL？

场景一：表结构

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
150多万的数据，就这么一个简单的语句：

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
慢查询日志里居然很多用了1秒的，Explain的结果是：

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
从Explain的结果可以看出，查询已经使用了索引，但为什么还这么慢?

分析：首先，该语句ORDER BY 使用了Using filesort文件排序，查询效率低;其次，查询字段不在索引上，没有使用覆盖索引，需要通过索引回表查询，也有数据分布的原因。知道了原因，那么问题就好解决了。

解决方案：由于只需查询uid字段，添加一个联合索引便可以避免回表和文件排序，利用覆盖索引提升查询速度，同时利用索引完成排序。

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

我们再Explain看一次：

程序员面试时遇到这个问题不知道怎么回答，从头到尾给你讲清楚
Extra信息已经有’Using Index’，表示已经使用了覆盖索引。经过索引优化之后，线上的查询基本不超过0.001秒。

案例总结：当一条查询语句符合覆盖索引条件时，sql只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

使用覆盖索引Innodb比MyISAM效果更好----InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

场景二：

分页查询

select id,name,sex … order by name limit 500,100;

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

InnoDB聚集索引普通索引，回表，索引覆盖，希望这1分钟大家有收获。

四、覆盖索引的优点有哪些？

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据。其有以下4个优点：

1、索引项通常比记录要小，所以MySQL访问更少的数据。

2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O。

3、大多数据引擎能更好地缓存索引，比如MyISAM只缓存索引。

4、覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

五、覆盖索引的注意事项

要注意的是，不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，索引MySQL只能使用B-Tree索引做覆盖索引。