前言在上一篇文章中我们理解了B树的底层结构和InnoDB索引的组织方式。但面试中面试官不会只问你B树是什么他会追着问“你建了一个联合索引(a,b,c)查询条件只有b和c这个索引能用上吗”这个问题考察的就是联合索引和最左前缀原则。而回答它的关键工具就是Explain。本文带你从联合索引的存储结构出发理解最左前缀的底层原理最后用Explain分析SQL的执行计划真正看懂一个查询有没有命中索引。本文核心问题联合索引的存储结构是什么样的按什么规则排序为什么联合索引(a,b,c)在WHERE b? AND c?时用不上最左前缀原则是什么它的底层原理是什么Explain的输出项怎么看type、key、key_len、Extra分别代表什么索引失效的常见场景有哪些为什么函数操作让索引失效什么是索引下推MySQL 5.6前后有什么区别如何根据业务场景设计合理的联合索引读完本文你将能通过Explain独立分析SQL的执行计划判断索引是否命中以及如何优化。一、联合索引的存储结构——按什么排序疑问联合索引(a,b,c)和三个单列索引有什么区别它内部是怎么组织的回答联合索引按列的顺序分层排序——先按a排序a相同再按b排序b相同再按c排序。这个排序规则决定了你能怎么查不能怎么查。1.1 联合索引的B树结构CREATEINDEXidx_a_b_cONuser(a,b,c);联合索引 (a, b, c) 的B树叶子节点 [1, 1, 1] ← a1, b1, c1 [1, 1, 3] ← a1, b1, c3 [1, 2, 2] ← a1, b2, c2 [1, 2, 5] ← a1, b2, c5 [2, 1, 1] ← a2, b1, c1 [2, 3, 1] ← a2, b3, c1 [3, 1, 2] ← a3, b1, c2 观察规律 - 第一列a全局有序1-1-1-1-2-2-3 - 第二列b在第一列相同时局部有序当a1时b才有序(1,1,2,2) 当a2时b有序(1,3) - 第三列c在前两列相同时局部有序只有当a1且b1时c才有序(1,3)1.2 联合索引和多单列索引的区别三个单列索引 idx_a: [1][2][3] idx_b: [1][2][3] idx_c: [1][2][3] → 一次查询只能用其中一个大多数情况下 → 无法加速a1 AND b2这种组合查询只能用一个索引先过滤再对结果逐行比对 一个联合索引 idx_a_b_c: [1,1,1][1,1,3][1,2,2]... → 一条索引覆盖所有三列的查询需求 → a1 AND b2可以直接在索引中定位不需要回表后再比对但代价联合索引比三个单列索引占更多磁盘空间——每条记录存三个值而不是一个值。且插入时要维护三个列的排序关系写成本略高于单列索引。二、最左前缀原则——底层原理疑问为什么联合索引(a,b,c)在查b和c时用不上最左前缀原则到底在限制什么回答最左前缀原则的底层原因是联合索引的排序规则——只有第一列全局有序第二列在第一列给定时才局部有序。跳过第一列去查第二列B树无法定位。2.1 能命中的场景-- ✅ 命中使用了最左列aSELECT*FROMuserWHEREa1;-- ✅ 命中使用了a和b满足最左前缀SELECT*FROMuserWHEREa1ANDb2;-- ✅ 命中使用了全部三列SELECT*FROMuserWHEREa1ANDb2ANDc3;-- ✅ 命中只要最左列a在中间跳过b也能部分命中只用到aSELECT*FROMuserWHEREa1ANDc3;-- 索引定位到a1的所有行然后逐行比对c32.2 不能命中或部分命中的场景-- ❌ 完全不能命中跳过了最左列aSELECT*FROMuserWHEREb2ANDc3;-- 为什么-- 索引中的b只在a确定时才局部有序。-- 没有ab在全局是无序的-- a1: [b1, b1, b2, b2]-- a2: [b1, b3]-- a3: [b1]-- b2的数据分散在不同a的区间中无法用B树结构直接定位-- 部分命中范围查询中断了后续列的使用SELECT*FROMuserWHEREa1ANDb2;-- 只能用到a 1来定位b2只能逐行比对-- 原因a 1是一个范围在这个范围内b不再保证有序2.3 核心记忆法联合索引像省市区的层级关系——你知道省才能查市知道省市才能查区。跳过省直接查市得全国地毯式搜索。三、Explain——看懂执行计划疑问怎么知道一条SQL到底有没有用上索引用了哪个索引扫描了多少行回答用Explain。它展示MySQL优化器选择的执行计划——包括命中了哪个索引、扫描了多少行、有没有回表、有没有文件排序。3.1 Explain的核心输出项EXPLAINSELECT*FROMuserWHEREa1ANDb2;输出项含义关键值type访问类型越靠前越好ALL index range ref eq_ref constkey实际使用的索引名NULL表示没走索引有值表示命中了哪个索引key_len索引中使用的字节数用来判断联合索引中用了几个列rows预估扫描的行数越小越好和实际返回行数的比值越低效率越高Extra额外信息Using index、Using filesort、Using temporary3.2 type值详解——从最差到最好type含义举例是否需要优化ALL全表扫描SELECT * FROM user WHERE name LIKE %张%✅ 必须优化index全索引扫描SELECT count(*) FROM user 索引数据量小于表时优于ALLrange索引范围扫描WHERE a 1、WHERE a BETWEEN 1 AND 10 范围合理时可以接受ref非唯一索引等值匹配WHERE a 1 正常索引命中eq_ref唯一索引等值匹配JOIN时用主键关联 最优const主键等值匹配WHERE id 1 最优3.3 key_len——看出联合索引用了几个列-- 联合索引 (a INT, b INT, c INT)每列4字节-- key_len的值可以判断用到了几个列WHEREa1→ key_len4只用到了aWHEREa1ANDb2→ key_len8用到了a和bWHEREa1ANDb2ANDc3→ key_len12用到了全部三列WHEREa1ANDb2→ key_len4范围查询中断了b只用到a3.4 Extra——三条警告信号Extra值含义严重程度Using filesortMySQL需要额外排序无法用索引顺序直接返回 大数据量下影响显著Using temporary需要临时表常见于GROUP BY和DISTINCT 通常需要优化Using index覆盖索引不回表最优情况 目标Using index condition使用了索引下推 比普通ref更进一步3.5 一个实际的Explain解读EXPLAINSELECT*FROMuserWHEREa1ANDc3ORDERBYcreate_timeDESC;typekeykey_lenrowsExtrarefidx_a_b_c42000Using where; Using filesort解读typeref等值匹配索引命中了key_len4联合索引只用到第一列ac被跳过了b也跳过了不能形成最左前缀rows2000a1条件下预估有2000行需要进一步过滤c3Using filesort需要额外对create_time做排序索引无法覆盖排序优化建议如果这个查询频繁考虑建(a, create_time)联合索引或者(a, c, create_time)联合索引让查询和排序都走索引。四、索引失效的常见场景疑问明明建了索引Explain却不走是什么原因回答MySQL优化器判断走索引的代价高于不走索引时会选择全表扫描。以下场景最容易导致索引失效。4.1 索引列上做函数操作-- ❌ 索引失效对索引列做了函数运算SELECT*FROMuserWHEREDATE(create_time)2024-01-01;-- ✅ 改写为范围查询索引可用SELECT*FROMuserWHEREcreate_time2024-01-01 00:00:00ANDcreate_time2024-01-02 00:00:00;4.2 隐式类型转换-- ❌ 索引失效phone是VARCHAR但传入的是整数-- MySQL会自动把phone列转成数字相当于在索引列上做了函数操作SELECT*FROMuserWHEREphone13800138000;-- ✅ 传参类型匹配索引可用SELECT*FROMuserWHEREphone13800138000;4.3 LIKE前模糊-- ❌ 索引失效%在最前面B树无法定位SELECT*FROMuserWHEREnameLIKE%张;-- ✅ 索引可用%在后可以用前缀定位SELECT*FROMuserWHEREnameLIKE张%;4.4 OR条件中部分列没有索引-- ❌ 索引失效b没有索引OR两边只要一边需要全表扫描整个查询退化为全表SELECT*FROMuserWHEREa1ORb2;-- ✅ 两列各建索引或者改写为UNIONSELECT*FROMuserWHEREa1UNIONSELECT*FROMuserWHEREb2;4.5 优化器认为全表扫描更快-- 表里总共100行MySQL觉得全表扫描比走索引还快SELECT*FROMsmall_tableWHEREstatus1;-- Explain输出typeALL, keyNULL但实际上可能全表扫描比定位B树三页再回表更快五、索引下推ICP——MySQL 5.6的关键优化疑问什么是索引下推为什么MySQL 5.6之后变快了回答索引下推的关键变化——把部分WHERE过滤从Server层下推到存储引擎层在叶子节点就过滤掉不匹配的行减少回表次数。5.1 没有ICP时MySQL 5.5及以前SELECT*FROMuserWHEREa1ANDb2ANDc3;-- 联合索引 (a, b, c)流程1.存储引擎通过索引找到a1ANDb2的所有行 → 全部返回给Server层2.Server层对返回的每一行检查c是否等于3→ 不匹配就丢弃 问题b2可能返回大量行但c3可能只匹配其中少部分。 不匹配的行白白做了回表操作浪费大量磁盘IO。5.2 有ICP时MySQL 5.6-- 同样的SQL开启ICP后流程1.存储引擎通过索引找到a1ANDb2的行 → 在索引内部直接检查c3→ 只有c满足的行才回表取完整数据2.Server层拿到已经过滤好的数据直接返回5.3 ICP的局限性哪些条件下推哪些不行 ✅ 能用ICPWHERE条件中涉及索引中的列c在联合索引中可以下推 ❌ 不能用ICPWHERE条件涉及索引外的列如果c不在索引中只能回表后过滤 ❌ 不能用ICP范围查询使用了第一个列以外的列a是第一个列a的范围查询可以 b是第二个列b的范围查询打断之后c无法使用ICP六、联合索引的设计方法疑问怎么根据业务SQL设计联合索引回答遵循等值在前范围在后排序在末尾的黄金法则。6.1 设计步骤-- 假设业务中最频繁的查询是SELECT*FROMorderWHEREuser_id1ANDstatusIN(1,2,3)ANDcreate_time2024-01-01ORDERBYcreate_timeDESCLIMIT20;第一步找出等值条件——user_id和status是等值匹配IN本质是多值等同。两列都在WHERE中作为固定值查询都可以放在联合索引的前面。第二步找出范围条件——create_time是范围查询。范围查询会中断索引的继续使用所以放在等值列的后面。第三步考虑排序——ORDER BY create_time。如果排序列已经在索引中作为范围列create_time可以直接用索引顺序不需要额外排序。但如果范围查询的值跨度太大索引顺序可能失效——这是可以用Explain验证的具体问题。-- 最终设计CREATEINDEXidx_user_status_timeONorder(user_id,status,create_time);6.2 设计口诀“等值查什么索引列就放什么范围查询放最后排序避免filesort。”总结联合索引按列顺序分层排序——第一列全局有序后续列在前一列给定时局部有序最左前缀原则的底层原因跳过最左列后后续列在全局中无序B树无法定位Explain是判断索引命中的核心工具typeALL和keyNULL是必须优化的信号key_len可以判断联合索引中用了几个列Using filesort在扫描行数大时需要优先治理索引失效的常见原因索引列上做函数、隐式类型转换、LIKE前模糊、OR有非索引列索引下推在存储引擎层完成过滤减少回表次数Extra中标记为Using index condition联合索引设计原则等值在前、范围在后、排序在末尾下一篇预告MySQL索引原理三——覆盖索引与索引下推让查询飞起来。深入拆解覆盖索引的设计方法、如何避免回表、以及索引下推和覆盖索引的协同优化。