前言
在上一篇文章中，我们理解了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] ← a=1, b=1, c=1 [1, 1, 3] ← a=1, b=1, c=3 [1, 2, 2] ← a=1, b=2, c=2 [1, 2, 5] ← a=1, b=2, c=5 [2, 1, 1] ← a=2, b=1, c=1 [2, 3, 1] ← a=2, b=3, c=1 [3, 1, 2] ← a=3, b=1, c=2 观察规律： - 第一列a全局有序：1-1-1-1-2-2-3 - 第二列b在第一列相同时局部有序：当a=1时b才有序(1,1,2,2) 当a=2时b有序(1,3) - 第三列c在前两列相同时局部有序：只有当a=1且b=1时c才有序(1,3)1.2 联合索引和多单列索引的区别
三个单列索引： idx_a: [1][2][3] idx_b: [1][2][3] idx_c: [1][2][3] → 一次查询只能用其中一个（大多数情况下） → 无法加速"a=1 AND b=2"这种组合查询（只能用一个索引先过滤，再对结果逐行比对） 一个联合索引： idx_a_b_c: [1,1,1][1,1,3][1,2,2]... → 一条索引覆盖所有三列的查询需求 → "a=1 AND b=2"可以直接在索引中定位，不需要回表后再比对但代价：联合索引比三个单列索引占更多磁盘空间——每条记录存三个值而不是一个值。且插入时要维护三个列的排序关系，写成本略高于单列索引。
二、最左前缀原则——底层原理
疑问：为什么联合索引(a,b,c)在查b和c时用不上？最左前缀原则到底在限制什么？
回答：最左前缀原则的底层原因是联合索引的排序规则——只有第一列全局有序，第二列在第一列给定时才局部有序。跳过第一列去查第二列，B+树无法定位。
2.1 能命中的场景
-- ✅ 命中：使用了最左列aSELECT*FROMuserWHEREa=1;-- ✅ 命中：使用了a和b，满足最左前缀SELECT*FROMuserWHEREa=1ANDb=2;-- ✅ 命中：使用了全部三列SELECT*FROMuserWHEREa=1ANDb=2ANDc=3;-- ✅ 命中：只要最左列a在，中间跳过b也能部分命中（只用到a）SELECT*FROMuserWHEREa=1ANDc=3;-- 索引定位到a=1的所有行，然后逐行比对c=32.2 不能命中或部分命中的场景
-- ❌ 完全不能命中：跳过了最左列aSELECT*FROMuserWHEREb=2ANDc=3;-- 为什么？-- 索引中的b只在a确定时才局部有序。-- 没有a，b在全局是无序的：-- a=1: [b=1, b=1, b=2, b=2]-- a=2: [b=1, b=3]-- a=3: [b=1]-- b=2的数据分散在不同a的区间中，无法用B+树结构直接定位-- 🟡 部分命中：范围查询中断了后续列的使用SELECT*FROMuserWHEREa>1ANDb=2;-- 只能用到a > 1来定位，b=2只能逐行比对-- 原因：a > 1是一个范围，在这个范围内b不再保证有序2.3 核心记忆法
联合索引像"省市区的层级关系"——你知道省才能查市，知道省市才能查区。跳过省直接查市，得全国地毯式搜索。
三、Explain——看懂执行计划
疑问：怎么知道一条SQL到底有没有用上索引？用了哪个索引？扫描了多少行？
回答：用Explain。它展示MySQL优化器选择的执行计划——包括命中了哪个索引、扫描了多少行、有没有回表、有没有文件排序。
3.1 Explain的核心输出项
EXPLAINSELECT*FROMuserWHEREa=1ANDb=2;| 输出项 | 含义 | 关键值 |
|---|---|---|
| type | 访问类型，越靠前越好 | ALL < index < range < ref < eq_ref < const |
| key | 实际使用的索引名 | NULL表示没走索引，有值表示命中了哪个索引 |
| key_len | 索引中使用的字节数 | 用来判断联合索引中用了几个列 |
| rows | 预估扫描的行数 | 越小越好，和实际返回行数的比值越低效率越高 |
| Extra | 额外信息 | Using index、Using filesort、Using temporary |
3.2 type值详解——从最差到最好
| type | 含义 | 举例 | 是否需要优化 |
|---|---|---|---|
| ALL | 全表扫描 | SELECT * FROM user WHERE name LIKE '%张%' | ✅ 必须优化 |
| index | 全索引扫描 | SELECT count(*) FROM user | 🟡 索引数据量小于表时优于ALL |
| range | 索引范围扫描 | 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的值可以判断用到了几个列WHEREa=1→ key_len=4（只用到了a）WHEREa=1ANDb=2→ key_len=8（用到了a和b）WHEREa=1ANDb=2ANDc=3→ key_len=12（用到了全部三列）WHEREa>1ANDb=2→ key_len=4（范围查询中断了b，只用到a）3.4 Extra——三条警告信号
| Extra值 | 含义 | 严重程度 |
|---|---|---|
| Using filesort | MySQL需要额外排序，无法用索引顺序直接返回 | 🟡 大数据量下影响显著 |
| Using temporary | 需要临时表（常见于GROUP BY和DISTINCT） | 🔴 通常需要优化 |
| Using index | 覆盖索引，不回表，最优情况 | 🟢 目标 |
| Using index condition | 使用了索引下推 | 🟢 比普通ref更进一步 |
3.5 一个实际的Explain解读
EXPLAINSELECT*FROMuserWHEREa=1ANDc=3ORDERBYcreate_timeDESC;| type | key | key_len | rows | Extra |
|---|---|---|---|---|
| ref | idx_a_b_c | 4 | 2000 | Using where; Using filesort |
解读：
type=ref：等值匹配，索引命中了key_len=4：联合索引只用到第一列a（c被跳过了，b也跳过了），不能形成最左前缀rows=2000：a=1条件下预估有2000行需要进一步过滤c=3Using filesort：需要额外对create_time做排序，索引无法覆盖排序
优化建议：如果这个查询频繁，考虑建(a, create_time)联合索引，或者(a, c, create_time)联合索引让查询和排序都走索引。
四、索引失效的常见场景
疑问：明明建了索引，Explain却不走，是什么原因？
回答：MySQL优化器判断走索引的代价高于不走索引时，会选择全表扫描。以下场景最容易导致索引失效。
4.1 索引列上做函数操作
-- ❌ 索引失效：对索引列做了函数运算SELECT*FROMuserWHEREDATE(create_time)='2024-01-01';-- ✅ 改写为范围查询，索引可用SELECT*FROMuserWHEREcreate_time>='2024-01-01 00:00:00'ANDcreate_time<'2024-01-02 00:00:00';4.2 隐式类型转换
-- ❌ 索引失效：phone是VARCHAR，但传入的是整数-- MySQL会自动把phone列转成数字，相当于在索引列上做了函数操作SELECT*FROMuserWHEREphone=13800138000;-- ✅ 传参类型匹配，索引可用SELECT*FROMuserWHEREphone='13800138000';4.3 LIKE前模糊
-- ❌ 索引失效：%在最前面，B+树无法定位SELECT*FROMuserWHEREnameLIKE'%张';-- ✅ 索引可用：%在后，可以用前缀定位SELECT*FROMuserWHEREnameLIKE'张%';4.4 OR条件中部分列没有索引
-- ❌ 索引失效：b没有索引，OR两边只要一边需要全表扫描，整个查询退化为全表SELECT*FROMuserWHEREa=1ORb=2;-- ✅ 两列各建索引，或者改写为UNIONSELECT*FROMuserWHEREa=1UNIONSELECT*FROMuserWHEREb=2;4.5 优化器认为全表扫描更快
-- 表里总共100行，MySQL觉得全表扫描比走索引还快SELECT*FROMsmall_tableWHEREstatus=1;-- Explain输出：type=ALL, key=NULL，但实际上可能全表扫描比定位B+树三页再回表更快五、索引下推（ICP）——MySQL 5.6的关键优化
疑问：什么是索引下推？为什么MySQL 5.6之后变快了？
回答：索引下推的关键变化——把部分WHERE过滤从Server层下推到存储引擎层，在叶子节点就过滤掉不匹配的行，减少回表次数。
5.1 没有ICP时（MySQL 5.5及以前）
SELECT*FROMuserWHEREa=1ANDb>2ANDc=3;-- 联合索引 (a, b, c)流程：1.存储引擎：通过索引找到a=1ANDb>2的所有行 → 全部返回给Server层2.Server层：对返回的每一行，检查c是否等于3→ 不匹配就丢弃 问题：b>2可能返回大量行，但c=3可能只匹配其中少部分。 不匹配的行白白做了"回表"操作，浪费大量磁盘IO。5.2 有ICP时（MySQL 5.6+）
-- 同样的SQL，开启ICP后流程：1.存储引擎：通过索引找到a=1ANDb>2的行 → 在索引内部直接检查c=3→ 只有c满足的行才回表取完整数据2.Server层：拿到已经过滤好的数据，直接返回5.3 ICP的局限性
哪些条件下推？哪些不行？ ✅ 能用ICP：WHERE条件中涉及索引中的列（c在联合索引中，可以下推） ❌ 不能用ICP：WHERE条件涉及索引外的列（如果c不在索引中，只能回表后过滤） ❌ 不能用ICP：范围查询使用了第一个列以外的列（a是第一个列，a的范围查询可以； b是第二个列，b的范围查询打断之后，c无法使用ICP）六、联合索引的设计方法
疑问：怎么根据业务SQL设计联合索引？
回答：遵循"等值在前，范围在后，排序在末尾"的黄金法则。
6.1 设计步骤
-- 假设业务中最频繁的查询是：SELECT*FROMorderWHEREuser_id=1ANDstatusIN(1,2,3)ANDcreate_time>'2024-01-01'ORDERBYcreate_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是判断索引命中的核心工具：
type=ALL和key=NULL是必须优化的信号，key_len可以判断联合索引中用了几个列，Using filesort在扫描行数大时需要优先治理 - 索引失效的常见原因：索引列上做函数、隐式类型转换、LIKE前模糊、OR有非索引列
- 索引下推在存储引擎层完成过滤，减少回表次数，Extra中标记为Using index condition
- 联合索引设计原则：等值在前、范围在后、排序在末尾
下一篇预告：MySQL索引原理（三）——覆盖索引与索引下推：让查询飞起来。深入拆解覆盖索引的设计方法、如何避免回表、以及索引下推和覆盖索引的协同优化。
