SQL 如何避免“索引下推”（Index Condition Pushdown）失效

ICP失效的根本原因是查询条件无法下推至存储引擎层，如对索引列使用函数、发生隐式类型转换或引用非索引列表达式。

为什么 ICP 会突然不生效

mysql 5.6+ 默认开启 index_condition_pushdown，但实际执行计划中常看到 using index condition 消失，退化为 Using where。根本原因不是开关被关了，而是查询条件无法下推到存储引擎层——比如用了函数、类型隐式转换、或引用了非索引列的表达式。

• WHERE YEAR(created_at) = 2024：对索引列加函数，ICP 失效（引擎无法用 B+ 树直接比较）
• WHERE user_id = '123'（user_id 是 int）：字符串与整数比较触发隐式转换，索引有序性被破坏，ICP 不参与
• WHERE status = 'active' AND CONCAT(first_name, last_name) LIKE '%john%'：后半部分无索引支撑，ICP 只能覆盖 status，但优化器可能整体弃用 ICP

EXPLaiN 里怎么看 ICP 是否启用

关键看 Extra 列是否出现 Using index condition。它和 Using where 同时存在是正常现象；前者表示部分条件已下推，后者是 Server 层对剩余条件的二次过滤。

• 只有 Using where → ICP 完全未启用
• 出现 Using index condition → ICP 生效，但需核对是否覆盖了你认为“应该下推”的条件
• key 列显示用了索引，但 rows 很大 + Extra 没有 Using index condition → 很可能因类型不匹配或函数导致 ICP 被跳过

示例：

EXPLAIN SELECT * FROM orders WHERE shop_id = 100 AND amount > 99.9 AND is_deleted = 0;

若 shop_id, amount, is_deleted 构成联合索引，且三者都参与过滤，则大概率看到 Using index condition；但如果把 amount > 99.9 改成 amount + 1 > 100.9，ICP 就会消失。

哪些写法明确禁用 ICP

ICP 仅支持存储引擎（如 InnoDB）能直接评估的简单条件。一旦条件涉及以下任一情况，MySQL 会放弃下推：

• 对索引列使用函数：UPPER(name)、date(updated_at)、COALESCE(status, 'unknown')
• 列与不同字符集/排序规则的字面量比较：name = '张三' COLLATE utf8mb4_0900_as_cs（而索引用的是默认 collation）
• 子查询结果参与比较：WHERE id IN (select ref_id FROM logs WHERE type = 'Error')
• 使用用户变量：WHERE @threshold := 100 AND value > @threshold
• 全文索引、空间索引、哈希索引（Memory 引擎）不支持 ICP

如何验证并修复 ICP 失效问题

不要只信 EXPLAIN 的静态分析，用 optimizer_trace 看真实决策过程：

SET optimizer_trace="enabled=on";
SELECT * FROM t WHERE a = 1 AND b > 2 AND UPPER(c) = 'X';
SELECT * FROM information_schema.OPTIMIZER_TRACE;

在 trace 输出中搜索 "icp_candidate" 和 "icp_usage" 字段，确认哪些条件被列为候选、哪些最终被下推。

• 修复方向优先级：先改写 SQL（去函数、对齐类型），再考虑调整索引（把高频过滤列前置），最后才动 optimizer_switch
• 临时关闭 ICP 测试影响：SET optimizer_switch='index_condition_pushdown=off';，但生产环境慎用
• 注意：即使 ICP 失效，只要 WHERE 条件能走索引范围扫描，性能未必差；ICP 的价值主要体现在大幅减少回表或全行读取的次数，尤其当索引覆盖不全、需要访问聚簇索引时

真正容易被忽略的是隐式类型转换——它不报错、不告警，EXPLAIN 看着也走了索引，但 ICP 就是不工作。查 SHOW WARNINGS 或开启 log_warnings = 2 才能看到转换提示。