从原理、流程、场景、执行计划一次讲透看完再也不懵。一、先搞懂索引下推到底是什么全称Index Condition PushdownICPMySQL 5.6 开始支持默认开启。一句话核心把本来要在 Server 层做的索引列过滤下推到存储引擎层提前做减少回表次数。二、前置知识MySQL 两层结构MySQL 分为两层理解 ICP 必须先懂这个Server 层SQL 解析、优化、执行、最终过滤存储引擎层InnoDB负责实际读取索引、数据页、回表传统流程引擎只管按索引查 → 把主键丢给 Server → Server 回表 → Server 再过滤ICP 流程引擎在扫索引时顺便过滤→ 只把符合条件的主键给 Server → 减少回表三、最经典例子联合索引假设有表sqlCREATE TABLE user ( id INT PRIMARY KEY, age INT, city VARCHAR(20), name VARCHAR(20), KEY idx_age_city(age, city) );查询语句sqlSELECT * FROM user WHERE age 20 AND city LIKE 北% AND name 张三;索引idx_age_city(age, city)1. 没有索引下推旧模式引擎根据age20找到所有索引记录不管city条件直接把所有主键返回 ServerServer 拿着主键逐条回表查完整行Server 再过滤city like 北% and name张三问题回表次数 所有 age20 的行数大量无效 IO极慢。2. 开启索引下推ICPServer 发现city也在索引里把条件city LIKE 北%下推给引擎引擎在遍历索引时一边扫一边过滤 city只返回符合age20 AND city LIKE 北%的主键Server 只对这些行回表再判断 name结果回表次数大幅减少速度明显提升。四、索引下推的本质逻辑只针对二级索引联合索引只能下推索引中包含的列下推的是索引条件不是所有 WHERE 条件目的只有一个减少回表一句话总结能在索引上搞定的过滤绝不拖到回表后。五、索引下推 VS 最左前缀 VS 覆盖索引很多人混淆这三个一张表分清表格特性最左前缀匹配覆盖索引索引下推 (ICP)作用决定索引能不能用上避免回表减少回表次数核心索引能否定位数据查询列都在索引里引擎层提前过滤效果不走索引 → 走索引索引直接返回结果回表变少关系前提最优解次优优化简单理解能做覆盖索引→ 根本不需要 ICP不能覆盖、必须回表 → ICP 帮你少回表六、什么时候会触发索引下推满足这些条件ICP 自动生效使用InnoDB / MyISAM使用二级联合索引WHERE 中有索引后续列的条件非最左前缀完全匹配查询需要回表不是覆盖索引不是 range 查询的特殊边界情况如某些场景典型触发 SQLsql-- 索引 idx(a,b,c) SELECT * FROM t WHERE a1 AND b LIKE x% AND c3;七、什么时候索引下推没用主键索引查询聚簇索引直接就是数据不用回表覆盖索引不需要回表引擎直接返回ICP 无意义条件用了函数 / 运算where substring(city,1,1)北使用了OR逻辑查询使用,!,IS NOT NULL等无法索引过滤引擎已经能精确匹配无需额外过滤八、如何看执行计划确认 ICP 生效执行sqlEXPLAIN SELECT * FROM user WHERE age20 AND city LIKE 北%;看Extra字段出现Using index condition→索引下推已开启并生效如果是Using index→ 覆盖索引Using where→ Server 层过滤Using filesort/Using temporary→ 没优化好九、开关索引下推sql-- 查看状态 SHOW VARIABLES LIKE optimizer_switch; -- 关闭 SET optimizer_switch index_condition_pushdownoff; -- 开启默认 SET optimizer_switch index_condition_pushdownon;一般永远不要关关了会瞬间变慢。终极一句话总结索引下推就是让存储引擎在扫二级索引时顺便把能过滤的条件先过滤掉只把真正需要的行传回 Server 层回表从而大幅减少随机 IO提升查询速度。十一、9 条实战规则避免索引下推无效1、索引下推无效的根本原因一句话能下推的条件必须能用 “索引里的列” 直接判断一旦判断不了ICP 自动失效。无效场景 引擎无法在索引页完成过滤→ 只能全部回表 → ICP 白开1.1. 必须使用【联合索引】且条件列在索引里这是 ICP 生效的地基。错误示例sql索引 idx(age) WHERE age20 AND city北京city 不在索引里 → 无法下推 → ICP 无效正确sql索引 idx(age,city)条件列必须包含在索引中才能下推过滤。1.2. 不要在索引列上使用【函数 / 运算】一用函数索引失效ICP 跟着失效。错误sqlWHERE left(city,1)北 WHERE age120正确sqlWHERE city LIKE 北% WHERE age19规则索引列必须 “干净”不能计算、不能函数包裹。1.3. 不要使用 OR 连接条件OR 会让索引无法确定范围 → ICP 无法下推。错误sqlWHERE age20 OR city北京只要出现 ORICP 大概率无效。1.4. 避免使用 / / IS NOT NULL这些符号不能用索引有序性过滤ICP 无法下推。错误sqlWHERE city ! 北京1.5. 联合索引不要【跨列使用范围查询】范围查询 like between会中断索引后续字段使用。示例索引idx(a,b,c)sqlWHERE a1 AND b10 AND c3b 是范围 → c 无法用索引快速定位→但 c 仍然可以走索引下推注意范围查询不会让 ICP 完全失效只是无法用到索引最左匹配但仍能下推过滤。1.6. 必须是【二级索引】不能是主键索引主键索引聚簇索引直接就是数据行不需要回表→ICP 毫无意义不会触发。1.7. 不能是【覆盖索引】覆盖索引查询的列全部在索引里→ 不需要回表→ ICP 不触发没必要触发如果你看到 ExtraUsing index说明是覆盖索引ICP 不会显示。1.8. 不能使用 % 开头的模糊查询% xxx错误sqlWHERE city LIKE %京索引无法匹配无法下推。正确sqlWHERE city LIKE 北%1.9. 确保 MySQL 5.6 以上且 ICP 开启默认开启查看是否开启sqlshow variables like optimizer_switch;必须看到index_condition_pushdownon三、快速判断你的 ICP 是否有效执行EXPLAIN看Extra字段Using index condition索引下推生效 ✅没有 索引下推无效 ❌无效就对照上面 9 条找原因。四、最常见的 4 种索引下推无效场景90% 的人中招1. 索引列用函数 → 无效sqlwhere date(create_time) 2025-01-012. 条件列不在联合索引里 → 无效sql索引 idx(age,city) where age20 and name张三name 不在索引 → 无法下推3. 使用 OR → 无效sqlwhere age20 or city北京4. 左模糊 % → 无效sqlwhere city like %京五、终极总结如何保证索引下推有效给你一套万能口诀背会永远不踩坑联合索引要建好条件列都要包含索引列上别计算函数一用全完蛋最左前缀要遵守不要乱用 OR 连Like 不要 % 开头范围查询不中断不是主键覆盖索引擎回推才有效Extra 看见 using index condition就是生效了。总结索引下推无效 无法在索引上完成过滤9 条规则全部围绕让条件能在索引里直接判断最常见坑函数、OR、% 开头、索引不包含条件列看Extra: Using index condition即可确认是否生效