一、最左前缀原则第一步联合索引在 B 树里到底存了什么假设你有一张表CREATE TABLE tb_user ( id INT PRIMARY KEY, a INT, b INT, c INT, INDEX idx_abc (a, b, c) -- 联合索引 );你建了一个联合索引(a, b, c)。MySQL 不会建三棵独立的树而是只建一棵 B 树。但这棵树的叶子节点存的键值不是单独的a、b或c而是把三列的值拼在一起当成一个整体键值索引键值格式(a, b, c) 叶子节点实际存储的是 (1, 2, 3) (1, 2, 5) (1, 3, 1) (2, 1, 4) (2, 2, 6) (3, 1, 2) ...排序规则是先按 a 排a 相同再按 b 排b 相同再按 c 排。这和字典序排序一模一样先比较第一个字母第一个相同比较第二个第二个相同比较第三个第二步B 树的有序性决定了查找方式B 树的核心特性是节点内的所有键值是有序的查找时必须利用这个有序性做二分查找或顺序扫描。当你执行SELECT * FROM tb_user WHERE a 1;MySQL 去idx_abc这棵 B 树里找(1, ?, ?)。因为所有键值是先按a排序的所以a 1的记录一定连续地排在一起。B 树能快速定位到第一个(1, ...)的位置然后顺着链表往后读直到a不等于 1 为止。这个过程能走索引因为查询条件用到了排序的第一维。第三步为什么WHERE b 2走不了索引现在看这条 SQLSELECT * FROM tb_user WHERE b 2;MySQL 拿着b 2去idx_abc这棵 B 树里找。问题出现了在这棵树上(a, b, c)是按a为第一优先级排序的。b的值是散在整个树里的(1, 2, 3) -- b2 (1, 2, 5) -- b2 (1, 3, 1) -- b3 (2, 1, 4) -- b1 (2, 2, 6) -- b2 (3, 1, 2) -- b1b 2的记录出现在(1, 2, 3)、(1, 2, 5)、(2, 2, 6)这几个位置它们在 B 树的叶子链表上不是连续的。B 树没有办法直接跳到所有b 2的位置因为它只能按完整的(a, b, c)键值排序查找。要找到所有b 2的记录只能遍历整棵树逐个检查每个节点的b是不是 2。遍历整棵树 全表扫描索引扫描版代价和全表扫描一样高所以优化器会放弃这个索引直接走全表扫描。四为什么WHERE a 1 AND c 3只能用到 aSELECT * FROM tb_user WHERE a 1 AND c 3;这个查询能用到索引但只用到了a这一列c用不上。原因先通过a 1定位到 B 树上a 1的连续区间(1, 2, 3) (1, 2, 5) (1, 3, 1)在这个区间内b的值是2, 2, 3是有序的。但c的值是3, 5, 1因为b不同所以c在这个区间内不是有序的。现在你要找c 3但c在a 1这个范围内是散落的3, 5, 1无法二分查找只能在a 1的所有记录里逐个检查c。所以索引只帮你在第一步过滤了a 1第二步的c 3还是要回表后逐行判断。第五步WHERE a 1 AND b 2 AND c 3为什么能全走索引SELECT * FROM tb_user WHERE a 1 AND b 2 AND c 3;B 树里的键值是(a, b, c)排序顺序是(1, 2, 3) (1, 2, 5) (1, 3, 1) (2, 1, 4) ...先找a 1定位到(1, ...)开头的连续区间在这个区间内b是有序的再找b 2定位到(1, 2, ...)的子区间在这个子区间内c是有序的再找c 3精确命中(1, 2, 3)每一层都利用了 B 树的有序性每一层都能二分查找所以三列都用上了索引。第六步范围查询为什么断尾SELECT * FROM tb_user WHERE a 1 AND b 2 AND c 3;这个查询能用索引但只用到a和bc用不上。原因a 1定位到a 1的区间b 2在a 1的区间内b是有序的可以找到第一个b 2的位置然后顺序往后读此时读取到的键值可能是(1, 3, 1) -- b3, c1 (1, 3, 5) -- b3, c5 (1, 4, 2) -- b4, c2现在你要找c 3但在这个b 2的范围内c的值是1, 5, 2不是有序的。因为b已经是一个范围 2b的值在变化3, 3, 4...导致c的值无法保证有序。一旦某一列用了范围查询它右边的列就无法再走索引的有序性了。第七步顺序无关——优化器会自动调整WHERE b 2 AND a 1 AND c 3这个 SQL 虽然写的顺序是b, a, c但优化器会自动重排为a, b, c然后走索引。注意这是等值条件的顺序重排不是列的使用顺序。如果你写的是WHERE b 2 AND c 3优化器不会凭空给你补一个a依然走不了索引。总结最左前缀原则的本质规则原因必须从最左列开始B 树按(a, b, c)整体排序最左列是第一排序键中间不能断断了左边右边列在树中不连续无法二分范围查询断尾范围条件导致后续列在局部区间内无序顺序可重排优化器会重排等值条件的顺序但不会补缺失的列一句话记忆联合索引(a, b, c)就是一棵按a → b → c优先级排序的 B 树。查询条件必须能按这个优先级一层层定位才能利用索引的有序性。跳过了a树就不知道从哪开始找跳过了bc在a的范围内就是乱的。二、索引下推ICPMySQL 5.6后在二级索引遍历时就过滤条件减少回表次数。第一步没有 ICP 时MySQL 的查询流程是什么假设你有一张表CREATE TABLE tb_user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_name_age (name, age) -- 联合索引 );执行这条 SQLSELECT * FROM tb_user WHERE name LIKE 张% AND age 20;根据最左前缀原则name LIKE 张%可以用到idx_name_age索引但age 20是断尾的因为name是范围条件所以age本身无法利用 B 树的有序性来快速定位。没有 ICP 时的执行流程存储引擎去idx_name_age索引树找到所有name LIKE 张%的索引记录对每一条找到的索引记录不管age是多少都拿着主键id去回表回表拿到完整的行数据后交给MySQL Server 层Server 层再检查age 20把不符合条件的过滤掉弊端暴露如果name LIKE 张%匹配了 1000 条记录但这 1000 条里只有 10 条的age 20那么发生了1000 次回表其中990 次回表是白做的——回表后发现age ! 20被 Server 层丢弃回表需要查主键索引树是磁盘 IO 操作。990 次无效回表 990 次无效磁盘 IO。这就是没有 ICP 的核心问题Server 层和存储引擎层之间职责划分太死板。存储引擎只负责用索引找到记录找到就回表把完整行交给 ServerServer 层负责过滤条件。但存储引擎在遍历索引的时候明明已经看到了age的值因为idx_name_age的索引键是(name, age)它却不判断非要等回表后再让 Server 层判断。第二步ICP 的设计思路——把过滤条件下推MySQL 5.6 引入 ICPIndex Condition Pushdown设计思路是在存储引擎遍历二级索引的过程中就把能在索引层面判断的条件提前过滤掉只有满足条件的记录才回表。为什么能这样做因为idx_name_age这棵索引树的叶子节点存储的是(name, age, id)。对于每一个索引条目存储引擎在读取它的时候已经同时拿到了name和age的值。既然age的值就在索引条目里为什么非要回表后再判断直接在索引层判断age 20不满足的条目直接丢弃不回表。第三步有 ICP 时的执行流程对比同样的 SQLSELECT * FROM tb_user WHERE name LIKE 张% AND age 20;有 ICP 时的执行流程存储引擎去idx_name_age索引树找到第一条name LIKE 张%的索引记录ICP 生效存储引擎检查这条索引记录里的age字段如果age ! 20直接丢弃不回表如果age 20拿着主键id回表查完整行数据返回给 Server 层顺着索引链表继续找下一条name LIKE 张%的记录重复步骤 2结果对比阶段没有 ICP有 ICPname LIKE 张%匹配 1000 条1000 次回表只回表age 20的那 10 条age ! 20的 990 条回表后交给 Server 层丢弃在索引层直接丢弃零回表磁盘 IO1000 次回表 IO10 次回表 IO第四步在代码和 EXPLAIN 中怎么看 ICPEXPLAIN 中的标志EXPLAIN SELECT * FROM tb_user WHERE name LIKE 张% AND age 20;如果 ICP 生效在Extra列会看到Using index condition注意区分Extra 值含义Using index覆盖索引不需要回表Using index condition使用了 ICP需要回表但回表前在索引层做了过滤Using where没有 ICP回表后在 Server 层过滤关闭 ICP 做对比测试-- 关闭 ICP默认是开启的 SET optimizer_switch index_condition_pushdownoff; EXPLAIN SELECT * FROM tb_user WHERE name LIKE 张% AND age 20; -- Extra 显示Using where表示回表后 Server 层过滤 -- 开启 ICP SET optimizer_switch index_condition_pushdownon; EXPLAIN SELECT * FROM tb_user WHERE name LIKE 张% AND age 20; -- Extra 显示Using index condition第五步ICP 的生效条件ICP 不是万能的它有以下限制1. 只对二级索引生效主键索引聚簇索引的叶子节点本身就是完整数据不存在回表这个概念所以不需要 ICP。2. 条件必须能在索引层判断-- 能下推age 在 idx_name_age 索引里 WHERE name LIKE 张% AND age 20 -- 不能下推address 不在 idx_name_age 索引里 WHERE name LIKE 张% AND address 北京address不在索引中存储引擎在遍历idx_name_age时看不到address的值所以address 北京无法下推只能回表后由 Server 层判断。3. 不能用于存储函数WHERE name LIKE 张% AND YEAR(created_at) 2024如果created_at在索引中但条件里包函数YEAR()ICP 通常不会下推因为存储引擎不一定能直接计算函数结果。总结逻辑链阶段问题/弊端解决方案没有 ICP存储引擎只负责索引定位所有匹配记录都回表Server 层再过滤职责划分不合理回表次数过多ICP 设计索引条目里明明有age的值却非要回表后再判断把过滤条件下推到存储引擎层有 ICP 后存储引擎遍历索引时先检查索引中的列条件不满足直接丢弃大幅减少无效回表降低磁盘 IO限制只对二级索引生效条件列必须在索引中主键索引不需要非索引列无法下推
