做MySQL性能优化这么久我最常被问到的不是“为什么全表扫描这么慢”反而是“我明明建了联合索引为什么执行计划还是扫了几十万行”。这类问题十有八九能聊到索引下推ICP头上。Index Condition Pushdown翻译过来就是“索引条件下推”MySQL 5.6开始引入作用是在存储引擎层提前过滤掉那些原本要回表后、到Server层才过滤的行核心收益就一个字省。这篇文章我不打算念说明书而是用一条真实能跑的SQL把ICP的原理、执行计划长什么样、什么场景有效、什么场景白搭一次讲透。不管你是刚入门正在看执行计划还是在准备面试又或者正在优化一条让人头疼的慢SQL应该都能从这里找到答案。我会先讲清楚“下推”到底推的是什么然后带你亲手做个实验再聊边界和限制最后按我自己的习惯总结一套排查和设计索引的思路。1. ICP是什么先搞懂“下推”这两个字1.1 没有ICP时一条索引扫描SQL是怎么执行的要理解“索引下推”得先知道没有它的时候MySQL是怎么干活的。以InnoDB为例一条查询大致会经过这样的流程Server层拿到SQL后优化器选一个索引然后把“去哪个索引、按什么范围扫”这个指令发给存储引擎存储引擎沿着索引的B树找到满足索引条件的记录把记录所在的整行数据通过聚簇索引回表查出来再返给Server层Server层拿到这些行之后再对WHERE子句里剩余的条件做最终过滤。这里面有个很微妙的地方存储引擎只能根据“能用来定位索引范围”的条件去扫数据。比如一个联合索引(a, b, c)WHERE里写了a 100 AND b 1优化器可能只把a 100用来做索引扫描b 1在5.6之前是等回表之后才由Server层判断的。换句话说存储引擎在索引扫描阶段只负责“把a大于100的行都捞出来”至于b是不是等于1它管不着也不该管。这个架构分工在数据量小的时候没什么问题可一旦a 100命中了十万行而b 1真正匹配的只有一百行那这一趟就白读了九万九千行还要为它们回表拿整行数据代价非常难看。我见过很多慢SQL问题就出在这里。索引看起来建了执行计划也显示range scan可扫描行数还是大得离谱。大部分人第一反应是“索引没生效”其实索引生效了只是过滤动作发生得太晚大量无效数据被无意义地搬运了一路。1.2 有了ICP之后谁在什么时候做过滤ICP做的事情简单说就是把那些“不能在索引扫描阶段用来定位范围、但字段本身就在索引里”的条件下推给存储引擎让存储引擎在扫到每条二级索引记录时就地检查一遍不满足的直接跳过根本不用回表。还是上面那个联合索引(a, b, c)WHERE a 100 AND b 1。开启ICP后InnoDB扫描二级索引时每扫到一条a 100的索引记录会顺手看一下这条记录里的b是不是1是1才回表拿完整行不是1就直接继续扫下一条。这样回表次数就从“所有a 100的行数”降到了“a 100且b 1的行数”效果立竿见影。我用仓库拣货来打个比方。没有ICP时仓库管理员拿着目录把可能相关的货箱全部搬到大门口再由门外的理货员一个个拆开检查有了ICP管理员在货架边上就先把不符合要求的货箱放回去只把确认合格的搬出去。搬运工作量一下子小了很多。这也是“下推”这个命名的由来过滤条件从高高在上的Server层被下推到了离数据更近的存储引擎层。要注意的是ICP不代表“Server层完全不检查了”。出于正确性和安全边界Server层通常还会对返回的行再做一次校验。所以执行计划里看到ICP生效指的是“过滤动作提前做了一部分”而不是“过滤全部由引擎完成”。这一点后面还会再提。1.3 为什么5.6之前做不到这件事从架构上看5.6之前存储引擎和Server层的边界比较“死板”。优化器告诉引擎“按这个索引范围扫”引擎就只负责按范围扫扫完把行原样返回。引擎并不了解也不负责WHERE条件里的其他列因为这是一个“职责清晰但极其浪费”的接口设计。5.6以后MySQL允许把一部分索引列上的过滤条件下推给引擎。为什么强调“索引列”呢因为引擎在扫描二级索引时只能访问索引页里的字段没法去读取那行完整数据里的其他字段否则还是要回表。所以ICP能下推的条件必须满足一个前提条件里用到的列都在当前使用的索引里。2. 动手验证ICP建表、造数、看执行计划2.1 建一张能说明问题的表和索引空谈原理容易记不牢我带你把实验完整跑一遍。先建一张用户表包含年龄、城市、姓名、分数几个字段。核心是外加一个联合索引(age, city, name)。CREATE TABLE t_user ( id INT NOT NULL AUTO_INCREMENT, age INT NOT NULL, city VARCHAR(32) NOT NULL, name VARCHAR(32) NOT NULL, score INT NOT NULL, PRIMARY KEY (id), KEY idx_age_city_name (age, city, name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;为什么选这个索引顺序因为我想模拟一个非常典型的场景查询条件是“年龄大于某个值且城市等于某个值且姓名以某个字开头”。在这个联合索引里age是第一个列city和name在age后面。由于age的条件是范围查询索引扫描时可以按age定位而city和name无法继续在这个范围之上做精确定位但它们本身就在索引里正好能被ICP拿来过滤。如果我把索引设计成(city, age, name)那city的等值条件会先精确锁定一片区域效果会更理想这个我们放到最佳实践里展开。现在先用这个结构把ICP的作用放大给你看。2.2 造1000行测试数据实验不用太大1000行足够看清执行计划的差异。MySQL 8.0可以用递归CTE一行INSERT搞定INSERT INTO t_user (age, city, name, score) WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM seq WHERE n 1000 ) SELECT FLOOR(18 RAND() * 50), -- 18~67岁 ELT(1 FLOOR(RAND() * 5), 北京, 上海, 成都, 深圳, 杭州), CONCAT(ELT(1 FLOOR(RAND() * 3), 张, 李, 王), LPAD(FLOOR(RAND() * 9999), 4, 0)), FLOOR(RAND() * 100) FROM seq;如果你用的是MySQL 5.7没有CTE那就写个存储过程循环插入效果一样DROP PROCEDURE IF EXISTS insert_t_user; DELIMITER $$ CREATE PROCEDURE insert_t_user(IN total INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i total DO SET i i 1; INSERT INTO t_user (age, city, name, score) VALUES ( FLOOR(18 RAND() * 50), ELT(1 FLOOR(RAND() * 5), 北京, 上海, 成都, 深圳, 杭州), CONCAT(ELT(1 FLOOR(RAND() * 3), 张, 李, 王), LPAD(FLOOR(RAND() * 9999), 4, 0)), FLOOR(RAND() * 100) ); END WHILE; END$$ DELIMITER ; CALL insert_t_user(1000);做完之后跑一下SELECT COUNT(*) FROM t_user;确认有1000行即可。2.3 一条SQL演示Using index condition现在我们用这条SQL来观察ICPEXPLAIN SELECT * FROM t_user WHERE age 20 AND city 成都 AND name LIKE 张%;在我本地MySQL 8.0上的执行计划大致是这样不同版本、不同统计信息下rows和filtered会有偏差idselect_typetabletypepossible_keyskeyrowsfilteredExtra1SIMPLEt_userrangeidx_age_city_nameidx_age_city_name9502.00Using index condition看到Extra里的Using index condition基本可以断定ICP生效了。这里age 20被用来做索引范围扫描city 成都和name LIKE 张%这两个条件没办法继续缩小索引扫描范围但被下推给了存储引擎让引擎在扫描索引记录时提前过滤掉不符合的行。你以为这个执行计划结束了吗还没我建议你亲手把ICP关掉看看对比效果。在会话级别执行SET optimizer_switch index_condition_pushdownoff; EXPLAIN SELECT * FROM t_user WHERE age 20 AND city 成都 AND name LIKE 张%; SET optimizer_switch index_condition_pushdownon;关闭ICP后执行计划的Extra列会从Using index condition变成Using where。搜索引擎还是用idx_age_city_name按age 20去扫但city和name这两个条件没法提前过滤只能等回表拿到整行数据后在Server层再做判断。数据只有1000行时你可能感觉不到差别但同样的逻辑放大到200万行age 20可能扫到180万条索引记录其中170万条城市或姓名不匹配。没有ICP这170万条不该回表的记录全都要回表有ICP它们会在索引扫描阶段直接被丢弃。这就是两个数量级的差距。如果你用的是MySQL 8.0.18以上版本还可以用EXPLAIN ANALYZE看真实执行时间、真实扫描行数对比开关ICP两个状态下的输出会比看EXPLAIN的估算更直观。当然实验数据量越大对比越明显。3. ICP的原理边界为什么它救不了所有SQL3.1 ICP到底减少了哪部分成本很多人以为ICP能减少索引扫描的行数这是个常见误解。看上面的执行计划rows是950这个值是优化器对索引范围扫描行数的估算跟开不开ICP关系不大。ICP改变的不是“索引扫描范围”而是“扫描过程中真正回表的行数”和“交给Server层的行数”。我习惯把成本拆成三块来理解。第一块是二级索引扫描成本这段路无论如何都要走因为你要通过索引定位数据第二块是回表成本拿着主键去聚簇索引里找完整行这是随机I/O的大头第三块是Server层过滤和网络传输成本从引擎接口取回的行越多整体开销越大。ICP能砍掉的主要是第二块和第三块的一部分但第一块依然存在。所以如果一条SQL本身用的是全表扫描ICP基本帮不上忙如果用的索引只能筛出很宽的范围ICP也只是把“回表后再淘汰”变成“回表前就淘汰”索引范围问题还是得靠索引设计本身去解决。3.2 与覆盖索引的区别我见过很多人把ICP和覆盖索引混为一谈这是面试里最容易被考官抓住的点。覆盖索引指的是“查询要返回的所有列都包含在索引中”于是存储引擎扫完索引就能直接返回结果连回表都省了ICP则是在“必须回表”的前提下尽量让回表发生在过滤之后。举两个查询你就明白了。假设索引还是(age, city, name)-- 查询列全部在索引中可能形成覆盖索引 SELECT id, age, city, name FROM t_user WHERE age 20 AND city 成都 AND name LIKE 张%; -- 查询列里有score索引覆盖不了必须回表 SELECT id, age, city, name, score FROM t_user WHERE age 20 AND city 成都 AND name LIKE 张%;第一条如果只读取索引里的列执行计划的Extra里可能出现Using index走的是覆盖索引路线回表被彻底消除第二条因为要读取score必须回表这时ICP才能发挥减少回表的优势。两者可能会同时出现但动机完全不同覆盖索引是“不需要回表”ICP是“减少回表的次数”。用个表格看更清晰对比项索引下推ICP覆盖索引核心目的减少回表次数消除回表必要条件WHERE条件列包含在索引中查询列全部包含在索引中是否一定不回表否通常仍要回表是EXPLAIN常见ExtraUsing index conditionUsing index典型适用场景大范围扫描多个等值/前缀过滤高频查询的宽索引设计3.3 什么样的条件能下推什么样不能ICP不是万能钥匙能不能下推要看两个层面一是条件里用到的列必须在当前索引中二是这个条件得是存储引擎能直接判断的简单形式。我把经验里的规则整理一下能下推的索引列上的等值比较如city 成都索引列上的范围比较如age 20索引列上的前缀匹配如name LIKE 张%。这些条件在索引扫描时可以直接对索引记录中的字段求值。不能下推的条件涉及非索引列的比如score 80引擎在二级索引页上根本看不到score只能回表后判断条件包含函数或表达式的比如age 1 20、LENGTH(name) 3这类写法通常连索引本身都难用到更别说下推了条件涉及子查询、存储函数等复杂表达式时也大概率无法下推。部分下推的情况一条WHERE里可能同时有能下推和不能下推的条件。比如WHERE age 20 AND city 成都 AND score 80age和city能下推score不能。MySQL会把能推的推下去不能推的留到Server层并不会因为一个条件不能推就连其他条件也放弃。记住这个原则ICP的判定边界是“当前索引的字段集合”。索引里有的字段才有机会提前过滤索引里没有的字段只能等回表。这个边界决定了你设计索引时绝对不能只想着“WHERE里的字段能用于定位”还要想着“WHERE里的字段在索引里是否足够丰富能支持下推过滤”。4. 版本、参数与真实限制4.1 版本与默认开关ICP从MySQL 5.6开始引入默认开启。到5.7、8.0依然是默认开启所以你一般不用做什么配置就能享受。它受optimizer_switch里的index_condition_pushdown这个开关控制可以用下面的SQL查看SELECT optimizer_switch\G输出里会有一大串开关状态找到index_condition_pushdownon就说明当前会话/实例开启了ICP。这个开关可以动态修改而且支持会话级别所以做实验非常方便SET optimizer_switch index_condition_pushdownoff; SET optimizer_switch index_condition_pushdownon;我建议你在自己的测试环境里开关这两个状态分别跑一遍EXPLAIN眼过千遍不如手过一遍。这里有个小提醒SET optimizer_switch index_condition_pushdownoff这种写法只会改变这个开关其他开关保持原值不用担心把整个优化器配置弄乱。4.2 只对二级索引有效ICP有个特别容易被忽略的限制它只对二级索引生效对聚簇索引InnoDB的主键索引无效。原因是InnoDB的主键索引叶子节点直接存的就是整行数据一旦通过主键定位整行已经拿到手了此时再把过滤条件下推给引擎和直接在Server层过滤相比省不了多少东西。二级索引则不同它的叶子节点只存索引列和主键值扫描完索引记录后绝大多数情况还要回表ICP能在回表前把不合格的记录扔掉收益非常明显。所以你在看执行计划时如果查询走的是主键索引即使Extra里有条件过滤也看不到Using index condition。这是正常的不是优化器不想推而是没有推的意义。4.3 其他限制与边界情况除了“只对二级索引”和“必须用到索引列”之外还有几个真实的边界值得记住。第一个是存储引擎支持范围ICP主要用于InnoDB和MyISAM这是最常遇到的两种情况。第二个是前面提过的简单条件限制带函数、表达式、隐式类型转换等情况的SQL下推概率会大打折扣。第三个是如果条件跨越了索引内字段和非索引字段引擎只会处理它能处理的那部分剩下的还是Server层兜底。第四个是ICP不会改变结果集的正确性因为Server层最后还会校验一遍这一点你可以放心它不会因为提前过滤而漏数据。另外在多表关联的场景里ICP同样可以发挥作用。当被驱动表通过二级索引连接时驱动表传过来的关联条件如果落在被驱动表的索引列上也可能被下推给存储引擎提前过滤。这是一个加分项很多人在解释ICP时只关注单表忽略了它在join里的价值。不过实际优化还是要看执行计划不能只看理论。5. 最佳实践怎么把ICP用到刀刃上5.1 联合索引设计顺序等值前、范围后、ICP兜底ICP再能打也只是“补救”不能替代索引设计。我在实际调优时有一条很实用的经验把等值条件列放在联合索引前面范围条件和模糊前缀放在后面。举个例子一条高频SQL是SELECT * FROM t_user WHERE city 成都 AND age 20 AND name LIKE 张%;如果索引是(city, age, name)那么city的等值条件可以先精确圈定“成都”这个范围age 20再在这个范围内做索引扫描name LIKE 张%则无法继续用于索引定位但能被ICP提前过滤。这比一开始用(age, city, name)要合理得多因为等值条件在前可以极大缩小索引扫描范围ICP的过滤压力也随之减小。如果业务上对name的前缀匹配特别高频而age范围又总是很大你甚至可以考虑(city, name, age)这样的顺序city等值定位name前缀匹配继续缩小范围age的条件交给Server层或ICP过滤。但要注意联合索引的列顺序没法同时满足所有SQL你得优先照顾最高频、最敏感的那条。这里我给一个自查清单先看等值条件能不能放到索引最前面再看范围条件是不是只有一个最后确认剩余条件是否都在索引里能不能被ICP兜住。5.2 慢SQL排查时怎么读EXPLAIN我排查慢SQL的顺序一般是先看type再看key然后看rows最后看Extra。type是不是ref、range还是ALL决定了有没有走索引rows是估算扫描行数如果特别大说明索引选择性可能不够Extra里出现Using index condition说明ICP已经生效但也要警惕ICP生效不代表索引设计完美它只是帮你把该省的回了表省了。如果Extra里出现的是Using where而不是Using index condition我会先检查两件事。第一WHERE条件里用到的列是不是都在当前使用的索引里比如某列不在索引中那就别指望ICP。第二条件里是不是有函数、表达式有的话先把SQL改写成简单比较形式。还有一种情况是优化器压根没选到你期望的索引这时候可以先ANALYZE TABLE刷新统计信息看一下是否有索引统计过期的问题。5.3 避免为ICP而ICPICP确实好用但它有一个副作用它变相鼓励你在索引里塞更多列好让引擎能提前过滤。这个诱惑要克制。每多一个索引列写入时就要多维护一份数据索引页也会更大内存和磁盘成本都在上升。我处理过一张高频写的表有人为了做查询过滤把五六个列全部塞进一个联合索引结果写入延迟肉眼可见地上升得不偿失。我的建议是分三步权衡第一步看能不能用覆盖索引直接消灭回表如果能比ICP更彻底第二步如果必须回表再看需要过滤的列是否已经在索引里没有且收益明显才考虑加入索引第三步对低基数列比如性别、状态这类加索引带来的过滤收益可能很小别为了ICP硬凑。总之ICP是优化工具箱里的一个零件不是银弹。6. 高频问题与面试回答参考6.1 常见问题速查表问题原因/解释处理方式EXPLAIN里没有Using index condition可能条件列不在索引里或用了函数/表达式或走的是主键索引检查WHERE列和索引字段改写SQL再看ICP和覆盖索引有什么区别ICP减少回表覆盖索引消除回表Extra分别为Using index condition和Using indexICP能减少索引扫描行数吗不能扫描范围由索引定位条件决定调整索引设计等值列往前放为什么主键索引用不上ICP聚簇索引叶子就是完整行无需提前减少回表属正常现象多表关联时ICP有用吗被驱动表扫描时也可能触发ICP结合EXPLAIN分析被驱动表的访问类型建了联合索引但过滤还是很慢ICP只是兜底索引列顺序可能不合理按等值、范围、模糊的条件顺序重新设计索引6.2 面试官追问怎么答面试里如果被问到ICP我建议不要只背定义用一个例子把闭环讲出来。你可以这样说MySQL 5.6之前存储引擎按索引范围扫描后所有候选行都要回表再由Server层过滤5.6引入ICP后只要WHERE条件里的字段在索引中存储引擎扫描二级索引时就可以先对这些条件求值不通过就不回表从而减少回表次数和Server层交互。比如联合索引(a, b, c)WHERE a 100 AND b 1a用来做范围扫描b被下推过滤执行计划Extra显示Using index condition。如果面试官继续追问“什么时候ICP没效果”你可以答条件列不在索引里、条件带函数表达式、走的是聚簇索引、或者SQL本身是全表扫描。然后补一句ICP和覆盖索引不是一回事覆盖索引是查询列都在索引里干脆不回表ICP是必须回表时尽量减少回表的行数。答到这里基本能把原理、场景、边界、对比都覆盖到这个考点就过关了。最后分享一个我自己的排查习惯遇到慢SQL我第一件事永远是先跑一遍EXPLAIN把Extra字段看清楚。是Using index condition说明引擎已经在努力帮你省回表了是Using where说明还有过滤条件被留在了Server层可能是索引列覆盖不够也可能是写法有问题。很多时候一条SQL从200毫秒优化到20毫秒差的不是某个神奇的高级特性而是搞清楚这些基础优化机制到底在哪一环帮你省了钱。希望这篇关于ICP的拆解能让你下次再看到Using index condition时心里有数。
