MySQL 索引下推：一次回表能省则省

目录

• 一个看似简单的查询
• 没有索引下推的执行流程
• 有了索引下推之后
• 怎么判断用没用索引下推
• 索引下推的前提条件
• 和覆盖索引、Using where 的区别
• 一个直观的对比实验
• 小结

---

一个看似简单的查询

先建一张表，加一个联合索引：

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_age(name, age)
) ENGINE=InnoDB;

索引 idx_name_age 包含两个列：name 和 age。

现在执行这条查询：

SELECT * FROM user WHERE name LIKE '张%' AND age = 25;

name LIKE '张%' 可以走索引范围扫描，这没问题。但 age = 25 呢？它在联合索引的第二个位置，前面的 name 是范围查询，按照最左前缀原则，age 没法用来定位索引，只能放弃。

这句话可能会让你产生一个疑问：既然 age 没法用来定位索引，那它是不是就没用了？

age 虽然不能帮我们"缩小搜索范围"，但它就在索引里，我们可以拿它来"过滤"。关键在于：这个过滤动作由谁来做？

没有索引下推的执行流程

MySQL 的架构分为两层：Server 层和存储引擎层。它们各管各的事：

Server 层：负责 SQL 解析、优化、结果返回
存储引擎层：负责数据的存储和读取（InnoDB、MyISAM 等）

在没有索引下推（ICP）的情况下，一条查询的执行流程是这样的：

问题出在第 2 步。假设 name LIKE '张%' 命中了 1000 条记录，其中只有 50 条满足 age = 25。但存储引擎不知道 age 的过滤条件，它老老实实地把 1000 条全部回表取出来，交给 Server 层去过滤。

950 次回表是白做的。

回表意味着什么？意味着通过二级索引拿到主键 id，再用 id 去聚簇索引里捞完整行数据。每一次回表都是一次随机 I/O。1000 次回表和 50 次回表，性能差距是巨大的。

有了索引下推之后

MySQL 5.6 引入了 Index Condition Pushdown（ICP），也就是索引下推。核心思想很简单：把原本在 Server 层做的过滤，下推到存储引擎层来做。

具体来说，存储引擎在遍历索引的时候，不急着回表，而是先看看索引里有没有其他列可以用来过滤。age 就在 idx_name_age 索引里，存储引擎读到一条索引记录后，可以直接检查 age = 25，不满足就跳过，满足了才回表。

同样是 1000 条索引记录命中 name LIKE '张%'，现在只有满足 age = 25 的 50 条才会回表。省掉了 950 次随机 I/O。

这就是"下推"的含义——把过滤条件从上层（Server）推到了下层（存储引擎）。

用伪代码来对比一下两种方式的区别：

// 没有 ICP
for 每条索引记录 where name LIKE '张%':
    回表取完整行数据                          ← 1000 次回表
    把数据发给 Server 层
    Server 层过滤 age = 25                   ← 过滤掉 950 条

// 有 ICP
for 每条索引记录 where name LIKE '张%':
    if age != 25:
        continue                            ← 直接跳过，不回表
    回表取完整行数据                          ← 只有 50 次回表
    把数据发给 Server 层

逻辑一样，结果一样，但回表次数差了一个数量级。

怎么判断用没用索引下推

用 EXPLAIN 看执行计划，关注 Extra 列：

EXPLAIN SELECT * FROM user WHERE name LIKE '张%' AND age = 25;

如果 Extra 里出现了 Using index condition，说明使用了索引下推。

+----+-------+------+---------------------+---------+---------+------+------+-----------------------+
| id | table | type | possible_keys       | key     | key_len | ref  | rows | Extra                 |
+----+-------+------+---------------------+---------+---------+------+------+-----------------------+
|  1 | user  | range| idx_name_age        |idx_name_age| ...  | NULL |  ... | Using index condition |
+----+-------+------+---------------------+---------+---------+------+------+-----------------------+

MySQL 默认开启索引下推，可以通过 optimizer_switch 查看和控制：

-- 查看当前状态
SHOW VARIABLES LIKE 'optimizer_switch';
-- 输出中会包含 index_condition_pushdown=on

-- 关闭索引下推（不建议，仅用于对比测试）
SET optimizer_switch = 'index_condition_pushdown=off';

索引下推的前提条件

不是所有查询都能享受索引下推，它有明确的适用范围：

1. 条件涉及的列必须在索引中

idx_name_age(name, age) 索引包含 name 和 age，所以 age = 25 可以下推。但如果条件是 city = '北京'，city 不在这个索引里，就没法下推，只能老老实实回表后在 Server 层过滤。

2. 适用于 range、ref、eq_ref、ref_or_null 等访问方法

索引下推并不是只在"最左前缀失效"的场景下才有用。即使索引定位条件完整，只要 WHERE 里还有索引列的额外条件，ICP 都可能介入。

3. 仅适用于 InnoDB 和 MyISAM

其他存储引擎不支持。

4. 聚簇索引（主键索引）上不适用

聚簇索引的叶子节点就是完整行数据，不存在"回表"这个动作，自然也就没有"减少回表"的意义。ICP 的收益来源于减少回表，没有回表就没有收益。

5. 子查询条件下推有条件限制

ICP 对子查询条件的下推支持有限，某些场景下不会生效。

和覆盖索引、Using where 的区别

EXPLAIN 的 Extra 列有三个容易混淆的值，它们代表完全不同的事情：

Extra 值	含义	是否回表	谁在过滤
Using index	覆盖索引，索引里已经有所需全部数据	不回表	存储引擎
Using index condition	索引下推，用索引列提前过滤减少回表	回表（但次数减少）	存储引擎
Using where	Server 层过滤	回表	Server 层

一句话区分：

• Using index：不用回表（覆盖索引）
• Using index condition：要回表，但存储引擎帮你先筛了一轮
• Using where：老老实实回表，拿到数据后 Server 层再过滤

这三个可以同时出现。比如 Using index condition; Using where 表示：一部分条件下推到了存储引擎层，但还有条件只能在 Server 层过滤（比如条件涉及的列不在索引里）。

一个直观的对比实验

我们可以用关闭 ICP 的方式，直观感受索引下推带来的差异。

先准备一些测试数据：

-- 创建存储过程批量插入数据
DELIMITER //
CREATE PROCEDURE insert_users(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        INSERT INTO user (id, name, age, city)
        VALUES (
            i,
            CONCAT('张', LPAD(FLOOR(RAND() * 1000), 4, '0')),
            FLOOR(RAND() * 50) + 20,
            ELT(FLOOR(RAND() * 4) + 1, '北京', '上海', '广州', '深圳')
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_users(100000);

然后分别在开启和关闭 ICP 的情况下执行同一条查询，观察回表次数的差异：

-- 开启 ICP（默认）
SET optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN SELECT * FROM user WHERE name LIKE '张%' AND age = 25;
-- Extra: Using index condition

-- 关闭 ICP
SET optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM user WHERE name LIKE '张%' AND age = 25;
-- Extra: Using where

两条查询的结果集完全一样，但执行过程中回表的次数完全不同。开启 ICP 时，存储引擎在索引层就过滤掉了大量不满足 age = 25 的记录；关闭后，这些记录全部要回表取完整数据，再由 Server 层过滤。

数据量越大、索引选择性越低（前缀列命中范围越宽），ICP 的收益越明显。

小结

索引下推的本质就一件事：把能在存储引擎层做完的过滤，不要拖到 Server 层再做。

索引里有的列，存储引擎直接就能看，没必要先回表取完整行数据再交给上层判断。每一次省掉的回表，都是一次随机 I/O 的节省。在大数据量、低选择性的场景下，这个优化的效果非常可观。

从架构的角度看，ICP 是对 MySQL 分层架构的一次"越层优化"。本来 Server 层和存储引擎层各管各的，ICP 打破了这个边界，让存储引擎多承担了一部分过滤职责。这种"越层"在软件工程中并不常见，但当某一层有天然的信息优势时（存储引擎手里有索引数据），把判断逻辑推到那一层去做，收益是实实在在的。