如何避免回表

01 回表

在使用非聚簇索引查询数据时，根据主键ID到聚簇索引上查询数据的过程称为回表。有关聚簇与非聚簇索引以及回表的过程可以点击下图查看相关文章

---

 

02 覆盖索引

先来看看MySQL官方对EXPLIAN工具的输出字段Extra infomation中有关覆盖索引的描述

 链接地址：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

切换一下语言，核心观点为：

• Using index

  无需回表，查询需要返回的列信息直接从索引树获得，不再访问物理行数据

• Using index condition

  需要回表，存储引擎层根据索引尽可能的过滤数据，然后在返回给服务器层根据WHERE其他条件进行过滤（索引下推）

• 覆盖索引必定是非聚簇索引，事实上聚簇索引不需要回表

 

一个例子

CREATE TABLE t(

  id INT PRIMARY KEY,

  c1 VARCHAR(32),

  c2 VARCHAR(8),

  INDEX(c1)

)ENGINE=innodb;

 

第一个SQL

能命中索引c1，并从c1的索引树中直接获得id、c1，无需回表 

第二个SQL
能命中索引c1，但c1的索引树中不存在c2列，需要回表

 

将单列索引index(c1)更改为联合索引index(c1，c2)，从下图的执行计划看到，已经都不需要回表了。

ALTER TABLE t DROP INDEX c1;
ALTER TABLE t ADD INDEX c1_c2(c1,c2);

---

 

03 索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

 

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层执行数据的存储和检索。索引下推是指将部分上层（服务层）负责的事情（如WHERE查询），交给了下层（引擎层）去处理。

无ICP查询过程

• 存储引擎读取索引；
• 根据索引中的主键值，定位并读取完整的行记录（回表）；
• 存储引擎把记录交给服务层去检测该记录是否满足WHERE条件。

 

ICP查询过程

• 存储引擎读取索引；
• 判断WHERE条件部分能否用索引中的列来做检查

  1. 条件不满足，处理下一行记录（丢弃，减少回表次数）；

  2. 条件满足，用索引中的主键去定位并读取完整的行记录（回表）；

• 存储引擎把记录交给服务层，服务层检测该记录是否满足WHERE条件的其余部分。

  ---

04 索引下推实践

 

CREATE TABLE t(

  id INT PRIMARY KEY,

  name VARCHAR(64),

  age INT,

  sex INT,

  INDEX(name,age)

)ENGINE=innodb;

创建一张表t，建立联合索引（name, age），并写入下面数据：

 

 

 

需求：查询所有name以'萧'开头且age等9的记录

SELECT * FROM t WHERE name LIKE '萧%' AND age=9;

无ICP查询过程

由于索引的左匹配原则，只能使用name，无法使用age。通过name分别找到匹配的主键1、2，然后回表查询取出记录，最后交由服务层进行WHERE判断，回表2次。

 

ICP查询过程

可以看到在引擎层筛选掉了不符合age条件的记录，只需要回表一次。

 

ICP开启状态查询

SHOW VARIABLES LIKE '%optimizer_switch%';

 

状态开启与关闭

SET optimizer_switch="index_condition_pushdown=off/on";