索引下推实战：复合条件下回表从万次降到几十次

问题背景：联合索引命中了，为什么仍然慢

很多 SQL 调优案例里，业务已经建立了联合索引，EXPLAIN 的 key 也明确显示索引被使用，但慢查询依旧存在。典型原因是：优化器只能利用索引的最左前缀定位记录，剩余条件如果不在存储引擎层提前判断，就需要先回到聚簇索引读取完整行，再交给 Server 层过滤。对于区分度较低的前缀条件，一次查询可能触发成千上万次回表，而最终符合全部复合条件的记录只有几十行。

MySQL 5.6 引入的 Index Condition Pushdown，简称 ICP 或索引下推，正是为了减少这种无效回表。它把能够使用索引字段判断的 WHERE 条件下推到存储引擎层，在遍历二级索引记录时先完成复合条件过滤，只有全部满足的索引记录才执行回表。

下面通过一个可直接运行的订单明细实验，观察 ICP 开启前后的执行差异，并用 EXPLAIN 与 Handler 状态指标验证回表次数变化。

原理对比：Server 层过滤与索引下推过滤

假设有联合索引 idx_shop_status_create(shop_id, status, create_time)，查询条件为：

WHERE shop_id = 10
  AND status IN ('PAID', 'DONE')
  AND create_time >= '2026-07-01'

如果三个字段都能参与索引访问，存储引擎可以在索引元组上直接判断 status 与 create_time，避免对不满足后缀条件的记录回表。需要注意，ICP 不是让索引跳过最左前缀规则，而是在已经确定的索引扫描范围内，尽量利用索引中的其他列提前过滤。

方案对比：覆盖索引、ICP 与改写查询

解决回表过多通常有三类方案。

第一是覆盖索引，把查询列也纳入索引，例如 (shop_id, status, create_time, id) 或根据实际查询扩展索引列。它能彻底避免回表，但会增加索引体积和写入成本，不适合 SELECT * 或查询列很多的场景。

第二是索引下推，在不改变查询列的情况下，让存储引擎利用联合索引中的字段提前过滤。它的改造成本最低，但要求条件列位于使用的索引中，并且条件本身可被存储引擎判断。

第三是 SQL 改写，例如先用覆盖索引子查询查出主键，再按主键 JOIN 原表。这种方式可控性强，但代码复杂，优化器也可能将子查询展开，未必总能保持预期计划。

因此，在线上调优时应优先确认 ICP 是否生效，再考虑扩展覆盖索引或强制改写，不应一看到慢查询就盲目增加索引。

实操步骤：构造复合条件过滤实验

1. 建表并写入低区分度数据

以下示例在 MySQL 5.7/8.0 均可运行。为了模拟真实业务，让同一个门店产生大量订单，但某个时间段、特定状态的记录较少。

DROP TABLE IF EXISTS orders_icp_demo;

CREATE TABLE orders_icp_demo (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT NOT NULL COMMENT '门店 ID',
  status VARCHAR(16) NOT NULL COMMENT '订单状态',
  create_time DATETIME NOT NULL COMMENT '创建时间',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '金额',
  remark VARCHAR(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (id),
  KEY idx_shop_status_create (shop_id, status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ICP 实验表';

写入 20000 行测试数据。其中 shop_id = 10 有 10000 行，大多数状态为 CREATED，只有少量 PAID 或 DONE 且位于目标时间范围。

-- 插入 10000 行 shop_id=10 的历史订单，绝大多数不符合状态条件
INSERT INTO orders_icp_demo(shop_id, status, create_time, amount, remark)
SELECT
  10 AS shop_id,
  IF(MOD(n, 100) = 0, 'PAID', 'CREATED') AS status,
  DATE_ADD('2026-01-01 08:00:00', INTERVAL n MINUTE) AS create_time,
  99.00 AS amount,
  'bulk data' AS remark
FROM (
  SELECT a.N + b.N * 10 + c.N * 100 + 1 AS n
  FROM
    (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
    (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
) t;

-- 插入 50 行 shop_id=10 且完全符合目标条件的数据
INSERT INTO orders_icp_demo(shop_id, status, create_time, amount, remark)
SELECT
  10,
  IF(MOD(n, 2) = 0, 'PAID', 'DONE'),
  DATE_ADD('2026-07-02 09:00:00', INTERVAL n MINUTE),
  199.00,
  'target rows'
FROM (
  SELECT a.N + b.N * 10 + 1 AS n
  FROM
    (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     UNION ALL SELECT 4) b
) t;

-- 插入其他门店数据，避免测试表过于单一
INSERT INTO orders_icp_demo(shop_id, status, create_time, amount, remark)
SELECT 20, 'PAID', DATE_ADD('2026-07-02 09:00:00', INTERVAL n MINUTE), 88.00, 'other shop'
FROM (
  SELECT a.N + b.N * 10 + 1 AS n
  FROM
    (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     UNION ALL SELECT 4) b
) t;

ANALYZE TABLE orders_icp_demo;

2. 观察默认开启 ICP 的执行计划

先确认优化器开关：

SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%' AS icp_enabled;

执行目标查询：

SELECT id, status, create_time, amount
FROM orders_icp_demo
WHERE shop_id = 10
  AND status IN ('PAID', 'DONE')
  AND create_time >= '2026-07-01 00:00:00'
ORDER BY create_time DESC
LIMIT 20;

查看执行计划：

EXPLAIN
SELECT id, status, create_time, amount
FROM orders_icp_demo
WHERE shop_id = 10
  AND status IN ('PAID', 'DONE')
  AND create_time >= '2026-07-01 00:00:00'
ORDER BY create_time DESC
LIMIT 20;

在 MySQL 5.7 中，Extra 常见结果为：

Using index condition; Using filesort

在 MySQL 8.0 中，执行计划展示更细化，可能看到：

Using index condition

或者在反向索引扫描满足 ORDER BY ... DESC 时不出现 filesort。不同小版本的成本选择可能不同，因此不要只背固定输出，关键是确认 key 使用了 idx_shop_status_create，并且 Extra 出现 Using index condition。

3. 用 Handler 指标量化回表压力

EXPLAIN 只能证明 ICP 可能被使用，不能直接告诉我们回表次数。可以在同一 Session 中用 FLUSH STATUS 和 SHOW SESSION STATUS 观察 Handler 计数。

FLUSH STATUS;

SELECT id, status, create_time, amount
FROM orders_icp_demo
WHERE shop_id = 10
  AND status IN ('PAID', 'DONE')
  AND create_time >= '2026-07-01 00:00:00'
ORDER BY create_time DESC
LIMIT 20;

SHOW SESSION STATUS LIKE 'Handler_read%';

重点关注：

• Handler_read_next：通过索引按顺序读取下一条记录的次数，可反映二级索引扫描量。
• Handler_read_rnd_next：全表扫描或 filesort 后随机读取完整行时可能增长。
• 多次实验时应使用相同 Buffer Pool 状态，并执行 SQL_NO_CACHE 无效，MySQL 8.0 已移除查询缓存，更可靠的方式是重复执行后比较相对计数。

如果只返回 20 行，而索引扫描前缀命中 10000 行，没有 ICP 时存储引擎可能需要把大量候选主键回表后再过滤；开启 ICP 后，status 与 create_time 条件在索引记录上先判断，实际回表数量会显著接近最终符合条件的行数。

4. 关闭 ICP 做 A/B 对比

在会话级别关闭索引下推：

SET optimizer_switch = 'index_condition_pushdown=off';

EXPLAIN
SELECT id, status, create_time, amount
FROM orders_icp_demo
WHERE shop_id = 10
  AND status IN ('PAID', 'DONE')
  AND create_time >= '2026-07-01 00:00:00'
ORDER BY create_time DESC
LIMIT 20;

FLUSH STATUS;

SELECT id, status, create_time, amount
FROM orders_icp_demo
WHERE shop_id = 10
  AND status IN ('PAID', 'DONE')
  AND create_time >= '2026-07-01 00:00:00'
ORDER BY create_time DESC
LIMIT 20;

SHOW SESSION STATUS LIKE 'Handler_read%';

此时 Extra 中的 Using index condition 通常会消失，可能只剩 Using where。它表示行被返回 Server 层后再进行 WHERE 过滤。对比时建议连续执行以下动作并记录结果：

-- 打开 ICP
SET optimizer_switch = 'index_condition_pushdown=on';
FLUSH STATUS;
-- 这里执行目标 SELECT
SHOW SESSION STATUS LIKE 'Handler_read%';

-- 关闭 ICP
SET optimizer_switch = 'index_condition_pushdown=off';
FLUSH STATUS;
-- 再次执行同样的目标 SELECT
SHOW SESSION STATUS LIKE 'Handler_read%';

-- 恢复默认设置
SET optimizer_switch = 'index_condition_pushdown=on';

在生产环境不要全局关闭 ICP，只应在测试实例或独立会话中验证。

为什么 EXPLAIN 有时看不到索引下推

看到 key = 联合索引 并不等于一定使用 ICP。ICP 的核心是“索引条件下推”，如果 WHERE 条件没有引用该索引中的其他列，就没有可下推的内容。例如索引是 (shop_id, status, create_time)，但查询写成：

SELECT id
FROM orders_icp_demo
WHERE shop_id = 10
  AND amount > 100;

amount 不在二级索引中，存储引擎无法在索引元组上判断该条件，只能根据 shop_id 找到候选记录后回表。此时应根据查询频率考虑建立 (shop_id, amount, id) 一类覆盖索引，而不是误以为 ICP 会自动处理所有 AND 条件。

另外，对索引列使用函数或表达式也会破坏条件的有效下推与范围访问：

-- 不推荐：对索引列使用函数
WHERE shop_id = 10
  AND DATE(create_time) >= '2026-07-01';

-- 推荐：保持裸列，范围可被索引利用
WHERE shop_id = 10
  AND create_time >= '2026-07-01 00:00:00';

避坑总结：ICP 调优的 4 个实战注意事项

1. 误把 Using where 当成 ICP。 常见现象是 EXPLAIN 显示使用了联合索引，但 Extra 只有 Using where，慢查询仍有大量随机读。修正方法是检查 WHERE 中的过滤列是否都在当前索引里，并重新设计联合索引列顺序；出现 Using index condition 才表示索引条件被下推。

2. 违反最左前缀却期待 ICP 救场。 例如缺少 shop_id 条件，直接按 create_time 范围查询，(shop_id,status,create_time) 通常无法高效定位。ICP 只能在索引扫描过程中过滤，不能把不可用的索引前缀凭空变成访问路径。应调整查询条件或新建以 create_time 开头的索引。

3. 在索引列上包函数导致复合条件过滤失效。 现象是 key 可能仍显示索引，但扫描行数很大，create_time 条件不能作为准确范围。修正方法是把 DATE(create_time) >= ? 改写为 create_time >= ? AND create_time < ?，让索引列保持裸列。

4. 只看 EXPLAIN 不看实际计数。 有些 SQL 受 LIMIT、filesort、统计信息影响，仅靠执行计划难以判断真实收益。应在测试库固定数据后，通过会话级开关做 A/B 对比，并结合 Handler_read%、慢日志 Rows_examined、InnoDB Buffer Pool 命中率与响应时间综合判断。

索引下推不是银弹，但在复合条件过滤、前缀选择性差、后缀条件区分度高的场景中，往往能用极低改造成本显著减少回表次数。真正可靠的调优方式，是让索引列顺序匹配过滤路径，让可下推条件留在索引中，再用 EXPLAIN 和 Handler 指标完成闭环验证。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。