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 才能看到转换提示。
