回表查询和索引覆盖

回表查询和索引覆盖

前言

作为 JAVA 开发的必备知识，了解回表查询和索引覆盖可以大大提升数据库查询的速度，也是优化数据库查询的必备知识。在此我将记录回表查询和索引覆盖的详细知识点（随时会更新补充），以便未来参考。如有错误请留言提出。

回表查询

回表查询顾名思义就是在数据查询过程中 MySQL 内部需要两次查询。即先定位查询数据所在表的主键值，再根据主键定位行记录。

要说回表查询，我们就要先从 InnoDB 的索引实现说起。InnoDB 索引分为两大类：聚集索引（ Clustered Index ）和普通索引（ Secondary Index ）。

InnoDB 聚集索引（ Clustered Index）

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

（1）如果表定义了 PK （Primary Key，主键），那么 PK 就是聚集索引；

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

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

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

InnoDB 普通索引（ Secondary Index ）

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

回表查询

例如：创建一个表 T ：

creat Table T(
	id int(11),
    `name` varchar(225),
    sex varchar(225),
    flag varchar(225),
    primary key(id),
    index (`name`)
)engine=innodb;

​ 其中 id 为聚集索引，name 为普通索引。

为表中添加四条记录：

insert into classes values(01,'shenjian','m','A'),(03,'zhangsan','m','A'),(05,'lisi','m','A'),(09,'wangwu','f',,'B');

1. 01，shenjian，m，A
2. 03，zhangsan，m，A
3. 05，lisi，m，A
4. 09，wangwu，f，B

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

普通索引的B+树索引（name是KEY，叶子节点存储PK值，即id）：

​

普通索引因为无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的。

select * from t where name = 'lisi';

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

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

怎么判断是否出现了回表

1. 在 explain（执行计划）Extra 中看到了 Using Where，代表是按照 where 条件进行了过滤，和是否走索引、回表无必然的关系。
2. 在 explain（执行计划）Extra 中是 Using Index & Using Where，表示 select 的数据在索引中能找到，但需要根据 where 条件过滤，这种情况也不会用到回表。
3. 只有在使用了索引，且在 explain（执行计划）Extra 中看到了 Using Where 的情况下，才代表回表查询数据。

索引覆盖

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

定义

如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。

怎么判断索引覆盖查询

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

可以利用索引覆盖来优化 SQL 的场景

场景1：全表count查询优化

原表为：

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)，也可以避免回表。

参考：

https://www.jianshu.com/p/8991cbca3854 ——mysql覆盖索引与回表

https://www.cnblogs.com/yanggb/p/11252966.html ——mysql中的回表查询与索引覆盖