【MySQL】索引下推减少回表次数-EW帮帮网

一、简述索引下推

“索引下推”是数据库领域的一个术语，主要出现在MySQL（尤其是InnoDB存储引擎）中，英文名叫 Index Condition Pushdown，简称 ICP。就是过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。ICP是在MySQL5.6之后完善的功能

二、举例

假设有个user_consum表，有主键索引id和联合索引(name, phone)，id是用户的消费记录，一个用户有很多条消费记录且name是用户昵称，可能撞车：

CREATE TABLE `user_consum` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `user_role` int DEFAULT 0,
  PRIMARY KEY (`id`)，
  INDEX IDX_NAME_PHONE (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设查询语句为：

SELECT * FROM user_innodb WHERE name = "luna" AND phone LIKE "%8848";

注意like是模糊匹配，无法用到索引。
此时假如在二级索引树中找到了几千个id即该用户有几千条消费记录，由于用户昵称可能撞车，这里不一定都是目标用户的，还需要手机尾号符合，那这里：

1，在二级索引树查到符合name的id后，回表到聚簇索引树（主键索引树），回表查询几千次，这些id可能还是离散不连续的，即几千次随机磁盘IO；
2，在存储引擎层查到几千行数据后，再将这些数据返到服务层根据过滤条件再筛选出尾号为8848的数据，发现其实只有10条数据符合

如果用上索引下推。
就是过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理：
也就是在联合索引的查找中，即查找符合name = "luna"的数据，也直接在联合索引的叶子节点中去逐个判断phone的尾号是否为8848，然后找到那10条记录，再回表，从而将回表次数降低至10次！
在主键索引树中找到符合条件的10行数据后，再返给服务层10行，减少了存储引擎层到服务层的拷贝开销，也减轻了服务层的过滤处理压力！

三、如何查看是否用到了索引下推

执行：

EXPLAIN SELECT * FROM user_innodb WHERE name = "luna" AND phone LIKE "%8848";

如果 Extra 字段出现 Using index condition，说明用了索引下推（ICP）。

四、ICP的意义

减少回表次数，提升查询效率，尤其是对于大表和回表代价高的InnoDB表。
减少存储层到服务层的拷贝开销，减轻服务层的处理压力
只适用于InnoDB和MyISAM存储引擎（MySQL 5.6及以上版本）。

五、总结

索引下推（Index Condition Pushdown, ICP）是MySQL的一项索引优化技术，通过在索引遍历阶段尽量多地应用WHERE条件，减少回表次数，提高查询效率。

前置知识复习：

联合索引、最左匹配，回表、索引覆盖，

（1）联合索引：

(a,b)字段建立联合索引的话
按照a来进行排序，在a相等的情况下，才按b来排序

联合索引与最左匹配博客：
https://www.cnblogs.com/rjzheng/p/12557314.html

（2）最左匹配：

所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like前缀匹配)就会到此为止停止匹配（含当前这个）。
如有索引 KEY idx_abcd(a, b, c, d)
查询 WHERE a = 1 AND b = 2 AND c > 3 AND d = 4
a = 1 —— 用到了索引的第1列
b = 2 —— 用到了索引的第2列
c > 3 —— 范围查询（>），用到了索引的第3列
d = 4 —— 用不到索引，因为c是范围查询，后面的索引列就失效了，如果满足a = 1 AND b = 2 AND c > 3的行很多，那就要做很多匹配
所以sql应该这样写：WHERE a = 1 AND b = 2 AND d = 4 AND c > 3，这样都能用上索引
注意范围查询中like的情况

如果是前缀匹配（比如LIKE ‘abc%’），能用到索引。
如果是模糊匹配（比如LIKE ‘%6606’），无法走索引，因为无法确定起始位置，只能全表/全索引扫描

（3）回表

因为像联合索引还是单列普通索引这种非主键索引，都是查二级索引树，其value是主键id，所以如果要查一些在二级索引树没有的信息，就需要拿着主键id到聚簇索引（主键索引）树去查，这个过程就是回表。
回表的问题主要是，回表是一行数据回表一次，就要查一次聚簇索引！所以要想方法减少回表次数
比如假如查 select x from user where a=1 and c > 3; 其中x没有索引，a和c有索引，但是符合这个条件的有1000个数据，那回表会产生1000次查找，因为MySQL 只能从二级索引（idx_ac）中，依次取出每个满足条件的主键ID，每拿到一个主键值，就去聚簇索引树读取对应的整行数据，然后取出 x 字段。
因为二级索引返回的主键ID通常是离散的，这些主键对应的数据行物理上可能散布在磁盘不同位置，所以每次回表可能都需要一次磁盘I/O（如果不在缓冲池内），这就是“随机I/O”，效率低下（如果不离散的话倒是能直接在叶子节点顺序找到）

InnoDB接口设计：MySQL的存储引擎接口本身是逐行取主键再查主表，没有批量取一批主键一次返回多行的API（虽然理论上可以优化，但InnoDB的实现就是这样）。
聚簇索引结构的限制：主表的行分散在磁盘不同位置，没办法保证物理上顺序访问，还是会有大量随机I/O。

也可以打开BKA批量回表机制（Batched Key Access ），比如每批次为500，那么对这500个主键id先排序，这样对于一些连续id就可以一次查找，减少随机I/O。注意：不是所有场景都能用BKA，主要在Join和二级索引回表时生效。
如果这种情况多，那最好就是给x也放到联合索引里，(a, c, x) — — 索引覆盖

（4）索引覆盖

如上user表里，如果

SELECT id, name,  phone FROM user_info WHERE name = "userxxxx";

因为name和phone是联合索引，而id是主键，所以直接在一次就能在联合索引树上查到所有信息，所以不会有回表，这就是索引覆盖

（5）SQL执行过程简述

大部分情况下，MySQL会先从存储层获取主键ID对应的完整行数据，然后在服务层根据过滤条件进行过滤

SQL解析&优化（服务层）SQL语句先在服务层被解析、生成执行计划，决定使用哪个索引、如何访问数据。
存储引擎访问数据（存储层）执行计划下发到存储层（如InnoDB），存储层根据执行计划，从磁盘或缓存（Buffer Pool）中读取行数据。
返回数据到服务层过滤存储层把读到的数据行返回到服务层，服务层按照WHERE条件、HAVING条件等进行筛选、聚合等操作。

【MySQL】索引下推减少回表次数