MySQL的回表查询与索引覆盖查询详解

MySQL的回表查询与索引覆盖查询

回表查询

名词解释

上篇讲到，MySQL中分为聚集索引和非聚集索引。非聚集索引的btree叶子节点中存储的是当行数据的PK。所以为了取到具体数据，则需要通过PK回到聚集索引里去查询数据。着就叫回表查询。扫描了2次索引树。所以效率相对较低。

有图有真相

假设有个T表，如下图所示。id是聚集索引，name则是普通索引。

id	name	sex
1	小毛	男
2	小高	女
3	大雄	男
4	A梦	女

表中有4条数据

Id列的B+树索引图(聚集索引)

Name列的B+树索引图(非聚集索引)

执行的查询SQL

select name, sex from t where name = 'A梦';

其执行过程是这样的

粉红色的路径需要扫描两遍索引树，第一遍先通过非聚集索引定位到主键值id=4，然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些。

索引覆盖

什么是索引覆盖？

索引覆盖就是为了避免回表查询的一种解决方案。见名知意，就是查询的所有列均被所使用的索引的列给到覆盖(可以是单列索引也可以是联合索引)。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。

举例

还是以上面那张表与SQL为例子，Id是聚集索引，name是非聚集索引。
如果我们想要避免回表查询有两种方案。

• 在SQL中只查询name字段。这样name的索引就覆盖到了所有的查询列。
• 将name的索引修改为联合索引(name, sex)，这样也覆盖到了所有的查询列。

注意事项

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

• 当发起一个被索引覆盖的查询（索引覆盖查询）时，在explain（执行计划）的Extra列可以看到【Using Index】的信息。

索引覆盖的优点

• 索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。
• 索引是按照列值顺序存储的，对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
• 一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。