联合索引最左匹配与索引下推的奥秘 ⬅️

一、开篇故事：图书馆的三级分类 📚

想象图书馆的书籍按三级分类排列：

传统图书馆（单列索引）

按主题分类：
  科学 → 一堆书
  文学 → 一堆书
  历史 → 一堆书
  
找"科学 + 作者张三 + 2020年"的书：
  1. 找到科学区
  2. 在科学区翻找张三的书（慢！）
  3. 再找2020年的（更慢！）

现代图书馆（联合索引）

按"主题 → 作者 → 年份"三级分类：
  科学 → 
    张三 → 
      2018年 → 书1
      2020年 → 书2 ✅ 直接定位！
      2022年 → 书3
    李四 → ...
  文学 → ...
  
找"科学 + 张三 + 2020年"的书：
  → 沿着三级分类直接找到！

问题来了

读者1："我要张三的书"
管理员："你是哪个主题的？"
读者："不知道，就要张三的"
管理员："那我只能全馆找了..."
→ 跳过了第一级分类（主题）
→ 无法使用三级索引！
→ 这就是"最左匹配原则"！

---

二、最左匹配原则 ⬅️

2.1 原理详解

联合索引的存储结构

-- 建立联合索引
CREATE INDEX idx_abc ON users(a, b, c);

-- 索引的物理存储（B+树）：
-- 先按a排序，a相同按b排序，b相同按c排序

+---+---+---+----------+
| a | b | c | 主键ID    |
+---+---+---+----------+
| 1 | 1 | 1 |   5      |
| 1 | 1 | 2 |   8      |  ← a=1, b=1 的范围内，c是有序的
| 1 | 2 | 1 |   3      |
| 1 | 2 | 2 |   9      |  ← a=1, b=2 的范围内，c是有序的
| 2 | 1 | 1 |   2      |  ← a=2, b=1 的范围内，c是有序的
| 2 | 1 | 2 |   7      |
| 2 | 2 | 1 |   4      |
| 2 | 2 | 2 |   6      |
+---+---+---+----------+

观察：
  - 整体看，a是有序的 ✅
  - a相同的范围内，b是有序的 ✅
  - a、b相同的范围内，c是有序的 ✅
  - 但整体看，b不是有序的 ❌（1,1,2,2,1,1,2,2）
  - 整体看，c也不是有序的 ❌（1,2,1,2,1,2,1,2）

---

2.2 使用规则

规则1：必须从最左列开始

-- 联合索引：idx_abc(a, b, c)

-- ✅ 可以使用索引的查询
WHERE a = 1                      -- 用到a
WHERE a = 1 AND b = 2            -- 用到a, b
WHERE a = 1 AND b = 2 AND c = 3  -- 用到a, b, c
WHERE a = 1 AND c = 3            -- 用到a（c用不到，后面解释）

-- ❌ 无法使用索引的查询
WHERE b = 2                      -- 跳过a，索引失效
WHERE c = 3                      -- 跳过a和b，索引失效
WHERE b = 2 AND c = 3            -- 跳过a，索引失效

规则2：不能跳过中间列

-- 联合索引：idx_abc(a, b, c)

WHERE a = 1 AND c = 3
-- 分析：
--   a = 1    ✅ 使用索引
--   c = 3    ❌ 不使用索引（跳过了b）
-- 结果：只用到索引的a列，c需要在结果中遍历过滤

为什么？

索引结构（a, b, c）:
(1, 1, 1)
(1, 1, 2)
(1, 2, 1)  ← a=1的范围内，c不是有序的！
(1, 2, 2)
(1, 3, 1)
(1, 3, 3)  ← 找c=3要遍历整个a=1的范围

WHERE a = 1:
  → 可以定位到(1, *, *)的起始位置
  
WHERE a = 1 AND c = 3:
  → 可以定位到a=1的范围
  → 但c在这个范围内不是有序的
  → 需要扫描所有a=1的行，过滤c=3

规则3：范围查询后的列无法使用索引

-- 联合索引：idx_abc(a, b, c)

WHERE a = 1 AND b > 10 AND c = 3
-- 分析：
--   a = 1    ✅ 使用索引（精确匹配）
--   b > 10   ✅ 使用索引（范围查询）
--   c = 3    ❌ 无法使用索引（范围查询后）

为什么？

索引结构：
(1, 5,  1)
(1, 5,  2)
(1, 11, 1)  ← b>10从这里开始
(1, 11, 3)
(1, 15, 1)  ← 注意：b>10的范围内，c不是有序的！
(1, 15, 3)
(1, 20, 2)

WHERE a = 1 AND b > 10:
  → 定位到b>10的起始位置
  
WHERE a = 1 AND b > 10 AND c = 3:
  → b>10是一个范围，不是精确值
  → 这个范围内，c不是有序的
  → 无法使用索引定位c=3
  → 需要扫描b>10的所有行，过滤c=3

---

2.3 WHERE条件顺序无关

-- 以下查询完全等价，MySQL优化器会自动调整

WHERE a = 1 AND b = 2 AND c = 3
WHERE b = 2 AND a = 1 AND c = 3
WHERE c = 3 AND b = 2 AND a = 1

-- MySQL优化器会自动重排为：
WHERE a = 1 AND b = 2 AND c = 3

-- 所以，WHERE条件的顺序不影响索引使用 ✅

---

2.4 实战案例

案例1：电商订单查询

-- 表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status TINYINT,      -- 0待付款 1已付款 2已发货 3已完成 4已取消
    create_time DATETIME,
    amount DECIMAL(10,2),
    INDEX idx_user_status_time (user_id, status, create_time)
);

-- 查询1：用户的所有订单
SELECT * FROM orders WHERE user_id = 123;
-- ✅ 使用索引：user_id

-- 查询2：用户的待付款订单
SELECT * FROM orders WHERE user_id = 123 AND status = 0;
-- ✅ 使用索引：user_id, status

-- 查询3：用户在某时间段的订单
SELECT * FROM orders 
WHERE user_id = 123 
  AND create_time >= '2024-01-01' 
  AND create_time < '2024-02-01';
-- ⚠️ 使用索引：user_id
-- ❌ create_time用不到（跳过了status）

-- 查询4：用户某状态某时间段的订单
SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 1
  AND create_time >= '2024-01-01' 
  AND create_time < '2024-02-01';
-- ✅ 使用索引：user_id, status, create_time（全部使用）

案例2：索引优化

-- 原始查询（慢）
SELECT * FROM orders WHERE status = 1;
-- ❌ 无法使用idx_user_status_time（跳过了user_id）

-- 解决方案1：单独建索引
CREATE INDEX idx_status ON orders(status);
SELECT * FROM orders WHERE status = 1;
-- ✅ 使用idx_status

-- 解决方案2：调整联合索引顺序（如果常查status）
DROP INDEX idx_user_status_time;
CREATE INDEX idx_status_user_time (status, user_id, create_time);
-- ✅ 现在WHERE status = 1可以使用索引了

---

三、索引下推（ICP）🚀

3.1 什么是索引下推？

MySQL 5.6引入的优化技术，将WHERE条件的过滤下推到存储引擎层。

没有ICP时（MySQL 5.5及之前）

SELECT * FROM users 
WHERE age > 20 AND name LIKE '张%';

-- 索引：idx_age_name(age, name)

执行流程（无ICP）:
  1. 存储引擎：在索引中找到age > 20的所有记录
  2. 存储引擎：回表查询完整数据（1000次回表）
  3. Server层：过滤name LIKE '张%'（在Server层过滤）
  4. 返回结果（10条）

问题：
  → 回表1000次，但只有10条符合条件
  → 990次回表是浪费！

有ICP时（MySQL 5.6+）

SELECT * FROM users 
WHERE age > 20 AND name LIKE '张%';

-- 索引：idx_age_name(age, name)

执行流程（有ICP）:
  1. 存储引擎：在索引中找到age > 20的记录
  2. 存储引擎：在索引中直接过滤name LIKE '张%'（索引下推）
  3. 存储引擎：只对符合条件的记录回表（10次回表）
  4. 返回结果（10条）

优化：
  → 只回表10次
  → 减少了990次回表！

---

3.2 ICP的原理

传统方式（无ICP）

Server层（MySQL）
  ↑ 返回完整数据（1000行）
  ↓ 发送查询
存储引擎层（InnoDB）
  ↓ 索引查找 age > 20（1000行）
  ↓ 回表1000次
  ↑ 返回1000行完整数据
  
Server层
  ↓ 过滤 name LIKE '张%'（留下10行）
  ↑ 返回10行

缺点：回表太多次！

索引下推（ICP）

Server层（MySQL）
  ↑ 返回完整数据（10行）
  ↓ 发送查询（包含所有WHERE条件）
存储引擎层（InnoDB）
  ↓ 索引查找 age > 20
  ↓ 在索引中过滤 name LIKE '张%'（索引下推）
  ↓ 只对10行回表
  ↑ 返回10行完整数据

优点：回表次数大幅减少！

---

3.3 ICP适用场景

适用条件

1. 使用了联合索引
2. WHERE条件中有索引中的列，但无法完全使用索引
3. MySQL 5.6+版本
4. InnoDB或MyISAM存储引擎

示例

-- 联合索引：idx_abc(a, b, c)

-- ✅ 可以使用ICP
WHERE a = 1 AND b > 10 AND c = 3
-- a和b使用索引定位
-- c在索引中但无法用于定位，ICP在索引中过滤c=3

WHERE a = 1 AND c = 3
-- a使用索引定位
-- c在索引中但无法用于定位，ICP在索引中过滤c=3

WHERE a > 10 AND b = 2
-- a使用索引定位（范围）
-- b在索引中但无法用于定位，ICP在索引中过滤b=2

-- ❌ 不能使用ICP
WHERE a = 1 AND phone = '138xxx'
-- phone不在索引中，无法下推

---

3.4 如何验证ICP？

使用EXPLAIN

EXPLAIN SELECT * FROM users 
WHERE age > 20 AND name LIKE '张%';

+----+-------------+-------+-------+---------------+--------------+---------+------+------+------------------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                              |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | users | range | idx_age_name  | idx_age_name | 5       | NULL | 1000 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+------------------------------------+

关键字段：
  Extra: Using index condition
  ↑ 表示使用了索引下推（ICP）

性能对比

-- 测试数据：100万行
-- age > 20: 50万行
-- age > 20 AND name LIKE '张%': 1000行

-- 无ICP（MySQL 5.5）
执行时间：2000ms
  → 索引扫描：100ms
  → 回表50万次：1900ms

-- 有ICP（MySQL 5.6+）
执行时间：150ms
  → 索引扫描+ICP过滤：100ms
  → 回表1000次：50ms

性能提升：13倍！

---

3.5 ICP的限制

❌ 不能下推的条件：

1. 子查询
   WHERE a = 1 AND b IN (SELECT ...)

2. 存储函数
   WHERE a = 1 AND my_func(b) = 1

3. 触发器条件
   
4. 全文索引
   WHERE MATCH(name) AGAINST('张三')

---

四、最佳实践 💡

4.1 索引列顺序选择

原则1：区分度高的列放前面

-- 表数据分布：
--   gender: 2个值（男、女）→ 区分度低
--   city: 100个值 → 区分度中等
--   user_id: 100万个值 → 区分度高

-- ❌ 不好的索引
CREATE INDEX idx_gender_city_user (gender, city, user_id);
-- 第一级只能分成2类，区分度太低

-- ✅ 好的索引
CREATE INDEX idx_user_city_gender (user_id, city, gender);
-- 第一级就能精确定位用户，区分度高

原则2：常用条件放前面

-- 查询频率：
--   WHERE user_id = ? → 80%的查询
--   WHERE city = ?    → 15%的查询
--   WHERE gender = ?  → 5%的查询

-- ✅ 按查询频率排序
CREATE INDEX idx_user_city_gender (user_id, city, gender);

原则3：等值查询放前面，范围查询放后面

-- 查询：
WHERE user_id = 123      -- 等值
  AND create_time > xxx  -- 范围
  AND status = 1         -- 等值

-- ✅ 好的索引（等值在前，范围在后）
CREATE INDEX idx_user_status_time (user_id, status, create_time);

-- ❌ 不好的索引（范围在中间）
CREATE INDEX idx_user_time_status (user_id, create_time, status);
-- create_time是范围查询，后面的status用不到索引

---

4.2 索引覆盖优化

-- 联合索引：idx_abc(a, b, c)

-- ✅ 覆盖索引（不需要回表）
SELECT a, b, c FROM t WHERE a = 1 AND b = 2;
-- 索引中已经包含了a, b, c，不需要回表

-- ❌ 需要回表
SELECT a, b, c, d FROM t WHERE a = 1 AND b = 2;
-- 索引中没有d，需要回表获取d

-- 优化方案：
CREATE INDEX idx_abcd (a, b, c, d);
SELECT a, b, c, d FROM t WHERE a = 1 AND b = 2;
-- 现在是覆盖索引，不需要回表了 ✅

---

4.3 多个单列索引 vs 联合索引

-- 方案1：多个单列索引
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_b ON t(b);
CREATE INDEX idx_c ON t(c);

SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
-- MySQL会选择其中一个索引（通常是区分度最高的）
-- 或者使用index_merge（合并多个索引的结果）

-- 方案2：联合索引 ✅（推荐）
CREATE INDEX idx_abc ON t(a, b, c);

SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
-- 完全使用联合索引，性能最好

对比：

特性	多个单列索引	联合索引
空间占用	大（每个索引都存储）	小（只存储一份）
查询性能	较差（可能只用一个索引）	好（充分利用）
维护成本	高（更新3个索引）	低（更新1个索引）
灵活性	高（每个列都能单独查）	低（必须遵循最左匹配）

建议：

• 如果查询总是组合条件，用联合索引 ✅
• 如果列经常单独查询，可以考虑单列索引

---

五、实战案例：订单表索引设计 💼

需求分析

-- 订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(50),
    user_id BIGINT,
    status TINYINT,      -- 0待付款 1已付款 2已发货 3已完成 4已取消
    pay_type TINYINT,    -- 1微信 2支付宝 3银行卡
    amount DECIMAL(10,2),
    create_time DATETIME,
    update_time DATETIME
);

-- 常见查询：
-- Q1: 查询用户的订单（user_id）
SELECT * FROM orders WHERE user_id = ?;

-- Q2: 查询用户某状态的订单（user_id + status）
SELECT * FROM orders WHERE user_id = ? AND status = ?;

-- Q3: 查询用户某时间段的订单（user_id + create_time）
SELECT * FROM orders 
WHERE user_id = ? 
  AND create_time >= ? 
  AND create_time < ?;

-- Q4: 查询某状态的订单（status）
SELECT * FROM orders WHERE status = ?;

-- Q5: 按订单号查询（order_no）
SELECT * FROM orders WHERE order_no = ?;

索引设计

-- 索引1：主查询（user_id + status + create_time）
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- 覆盖Q1, Q2, Q3

-- 索引2：订单号唯一索引
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);
-- 覆盖Q5

-- 索引3：状态索引（如果Q4查询频繁）
CREATE INDEX idx_status_time ON orders(status, create_time);
-- 覆盖Q4

查询分析

-- Q1: WHERE user_id = ?
-- 使用idx_user_status_time，用到user_id ✅

-- Q2: WHERE user_id = ? AND status = ?
-- 使用idx_user_status_time，用到user_id, status ✅

-- Q3: WHERE user_id = ? AND create_time >= ? AND create_time < ?
-- 使用idx_user_status_time，用到user_id（create_time因跳过status用不到）⚠️
-- 可以改写：WHERE user_id = ? AND status IN (0,1,2,3,4) AND create_time >= ?
-- 这样可以用到user_id, status, create_time ✅

-- Q4: WHERE status = ?
-- 使用idx_status_time，用到status ✅

-- Q5: WHERE order_no = ?
-- 使用uk_order_no，精确定位 ✅

---

六、面试高频问题 🎤

Q1: 什么是联合索引的最左匹配原则？

答： 联合索引是按照索引列的顺序依次排序的。查询时必须从最左列开始，不能跳过中间列。例如索引(a,b,c)：

• WHERE a=1 ✅ 用到a
• WHERE a=1 AND b=2 ✅ 用到a,b
• WHERE b=2 ❌ 跳过a，索引失效
• WHERE a=1 AND c=3 ⚠️ 只用到a，c用不到

Q2: 为什么范围查询后的列无法使用索引？

答： 因为范围查询后，后面的列在查询范围内不是有序的。例如索引(a,b,c)，查询WHERE a=1 AND b>10 AND c=3：

• a=1：可以定位
• b>10：可以定位范围
• c=3：在b>10的范围内，c不是有序的，无法使用索引定位，只能扫描过滤

Q3: 什么是索引下推（ICP）？

答： MySQL 5.6引入的优化技术，将WHERE条件的过滤下推到存储引擎层执行。例如WHERE a>10 AND b LIKE '张%'，在索引中过滤b条件，减少回表次数，提升性能。EXPLAIN中显示"Using index condition"。

Q4: 联合索引(a,b,c)和三个单列索引(a),(b),(c)有什么区别？

答：

• 联合索引：a、b、c组合查询时性能最好，但必须遵循最左匹配，灵活性较差
• 单列索引：每个列都能单独使用，灵活性好，但组合查询时只能用一个索引（或index_merge），性能较差
• 存储：联合索引占用空间更小，维护成本更低

Q5: 如何选择联合索引的列顺序？

答：

1. 区分度高的列放前面（user_id > city > gender）
2. 常用条件放前面（根据查询频率）
3. 等值查询放前面，范围查询放后面
4. 考虑覆盖索引（常查询的列放入索引）

---

七、总结口诀 📝

联合索引有学问，
最左匹配要记牢。
从左到右不能跳，
中间断了后面抛。

等值查询放前头，
范围查询放最后。
区分度高优先用，
常用条件不能丢。

索引下推ICP强，
存储引擎早过滤。
减少回表次数多，
性能提升真不少。

WHERE顺序随便写，
优化器自动调。
EXPLAIN常分析，
索引使用一目了！

---

参考资料 📚

• MySQL官方文档 - Index Condition Pushdown
• 《高性能MySQL》第5章 - 索引策略
• MySQL索引原理及慢查询优化

---

下期预告： 149-MySQL的死锁检测和处理机制 🔒

---

编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0

愿你的索引最左匹配，查询飞速如电！ ⚡️✨