跳到主要内容

联合索引

问题​

什么是联合索引（复合索引）？最左前缀原则是什么？索引下推（ICP）是怎么工作的？

答案​

联合索引的结构​

联合索引是在 多个列 上建立的索引。B+ 树按照列的声明顺序进行排序 —— 先按第一列排序，第一列相同则按第二列排序，以此类推。

CREATE INDEX idx_a_b_c ON users (a, b, c);

排列规律：

  • a 列全局有序：1, 1, 1, 2, 2, 3, 3, 3, 4
  • b 列在 a 相同时有序：a=1 时 b: 1, 2, 2；a=2 时 b: 1, 3
  • c 列在 a+b 相同时有序：a=1,b=2 时 c: 1, 5

最左前缀原则​

最左前缀原则：联合索引 (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 b = 2❌无缺少最左列 a
WHERE b = 2 AND c = 3❌无缺少最左列 a
WHERE a = 1 AND c = 3⚠️a（+ ICP）a 可用，c 跳过了 b
WHERE c = 3❌无缺少最左列 a
为什么要最左前缀？

回顾联合索引的排序规则：先按 a 排序，a 相同按 b 排序。

如果只查 WHERE b = 2：b 列的值在 B+ 树中不是全局有序的（只在同一个 a 值内有序），所以无法利用索引的二分查找。

范围查询对后续列的影响​

-- 索引 (a, b, c)

-- ✅ a 等值 + b 等值 + c 等值：全部命中
WHERE a = 1 AND b = 2 AND c = 3

-- ⚠️ a 等值 + b 范围：a, b 命中，c 不能用索引排序
WHERE a = 1 AND b > 2 AND c = 3
-- 因为 b 是范围查询，b 的范围内 c 不再有序

-- ⚠️ a 范围：只有 a 命中
WHERE a > 1 AND b = 2
-- a 是范围查询后，b 不再有序

规则总结：遇到范围查询（>、<、BETWEEN、LIKE 'xxx%'）后，后续列无法使用索引排序。

WHERE 中列的顺序不影响​

MySQL 优化器会自动调整 WHERE 条件的顺序，所以以下两个查询等价：

-- 这两个查询都能用到索引 (a, b, c)
WHERE a = 1 AND b = 2 AND c = 3
WHERE c = 3 AND b = 2 AND a = 1  -- 优化器会自动重排

索引下推（Index Condition Pushdown, ICP）​

MySQL 5.6 引入的优化，让存储引擎在索引层面就过滤掉不满足条件的记录，减少回表次数。

无 ICP（MySQL 5.6 之前）​

-- 索引 (a, b, c)
SELECT * FROM t WHERE a = 1 AND c = 3;

问题：a = 1 找到 100 行，全部回表后再过滤 c = 3，回表了很多无效行。

有 ICP（MySQL 5.6+）​

改进：虽然 c 不能用于索引定位（跳过了 b），但索引叶子节点中有 c 的值，可以在索引层面直接判断 c = 3，不满足就不回表。

判断 ICP：EXPLAIN 的 Extra 列显示 Using index condition。

EXPLAIN SELECT * FROM users 
WHERE name = '张三' AND age > 20;
-- Extra: Using index condition（如果索引是 (name, age)）

联合索引的设计原则​

ESR 原则​

设计联合索引时，列的顺序遵循 ESR 规则：

E = Equal（等值条件列放前面）
S = Sort（排序列放中间）
R = Range（范围条件列放后面）
-- 查询模式
SELECT * FROM orders 
WHERE status = 'paid'      -- E：等值
  AND user_id = 100         -- E：等值
ORDER BY created_at DESC    -- S：排序
LIMIT 10;

-- 最佳索引
CREATE INDEX idx_status_user_created 
ON orders (status, user_id, created_at);

选择性原则​

高选择性的列放前面。选择性 = 不重复值的数量 / 总行数。

-- 查看列的选择性
SELECT 
    COUNT(DISTINCT status) / COUNT(*) AS status_sel,     -- 0.001 低
    COUNT(DISTINCT user_id) / COUNT(*) AS user_sel,      -- 0.8   高
    COUNT(DISTINCT category) / COUNT(*) AS category_sel  -- 0.05  中
FROM orders;

-- 索引设计：高选择性在前
CREATE INDEX idx_user_category_status
ON orders (user_id, category, status);
ESR vs 选择性

两个原则可能冲突。ESR 原则优先，因为等值过滤允许后续列继续使用索引，而范围查询后索引会"断裂"。选择性是在 ESR 基础上的微调。

联合索引复用​

一个 (a, b, c) 联合索引等于同时拥有：

  • 索引 (a)
  • 索引 (a, b)
  • 索引 (a, b, c)

所以不需要额外创建 (a) 或 (a, b) 的单列/两列索引。

-- ❌ 冗余索引
CREATE INDEX idx_a ON t (a);
CREATE INDEX idx_a_b ON t (a, b);
CREATE INDEX idx_a_b_c ON t (a, b, c);

-- ✅ 一个索引即可
CREATE INDEX idx_a_b_c ON t (a, b, c);

索引合并（Index Merge）​

当一个查询可能用到多个单列索引时，MySQL 可能使用索引合并。

CREATE INDEX idx_a ON t (a);
CREATE INDEX idx_b ON t (b);

-- MySQL 可能使用 Index Merge
SELECT * FROM t WHERE a = 1 OR b = 2;
-- type: index_merge
-- Extra: Using union(idx_a, idx_b)

三种合并策略：

策略条件说明
UnionOR 查询对两个索引的结果集取并集
IntersectionAND 查询对两个索引的结果集取交集
Sort-UnionOR 范围查询排序后取并集
索引合并 vs 联合索引

索引合并是优化器的"兜底方案"，性能通常不如联合索引。如果经常 WHERE a = 1 AND b = 2，应该建 (a, b) 联合索引而不是 (a) 和 (b) 两个单列索引。

常见面试问题​

Q1: 联合索引 (a, b, c) 对以下查询哪些有效？​

答案：

-- ✅ 有效
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 ORDER BY b
WHERE a = 1 AND b > 2

-- ⚠️ 部分有效
WHERE a = 1 AND c = 3       -- a 走索引，c 走 ICP
WHERE a = 1 AND b > 2 AND c = 3  -- a, b 走索引，c 走 ICP

-- ❌ 无效（不能走索引）
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

Q2: 为什么范围查询后面的列不能用索引？​

答案：

联合索引 (a, b) 的排序是：先按 a 排序，a 相同时按 b 排序。当 a > 5 时，a 的值跨越了多个不同的值（6, 7, 8...），在这个范围内 b 的值不是全局有序的：

a=6, b=1
a=6, b=5  ← a=6 内 b 有序
a=7, b=2
a=7, b=3  ← a=7 内 b 有序
a=8, b=1

所以 WHERE a > 5 AND b = 3 中，b = 3 不能利用索引的有序性进行二分查找。

Q3: 如何设计一个好的联合索引？​

答案：

  1. 分析查询模式：列出所有高频查询的 WHERE、ORDER BY、SELECT 列
  2. ESR 原则：等值列在前 → 排序列在中 → 范围列在后
  3. 高选择性优先：在 ESR 基础上，选择性高的列靠前
  4. 考虑覆盖索引：把 SELECT 涉及的列也加入索引尾部
  5. 复用已有索引：(a, b, c) 能覆盖 (a) 和 (a, b) 的查询
  6. 控制索引宽度：列不宜超过 5 个，避免写入性能下降

Q4: 什么是索引下推？​

答案：

索引下推（ICP, Index Condition Pushdown）是 MySQL 5.6 引入的优化。在使用联合索引时，即使某些列不能用于索引定位（违反最左前缀），但这些列的值存在于索引叶子节点中，可以在存储引擎层直接判断，减少回表次数。

例如索引 (name, age)，查询 WHERE name LIKE '张%' AND age = 25：

  • name LIKE '张%' 可以用索引定位
  • age = 25 虽然在 LIKE 之后"断裂"了，但 age 值在索引中
  • 没有 ICP：全部回表后在 Server 层过滤 age
  • 有 ICP：在索引层就过滤 age，不满足的直接跳过不回表

Q5: ORDER BY 如何利用联合索引避免 filesort？​

答案：

-- 索引 (a, b, c)

-- ✅ 可以利用索引排序
WHERE a = 1 ORDER BY b           -- a 等值后 b 有序
WHERE a = 1 ORDER BY b, c        -- a 等值后 b, c 有序
WHERE a = 1 AND b = 2 ORDER BY c -- a, b 等值后 c 有序

-- ❌ 不能利用索引排序（需要 filesort）
WHERE a = 1 ORDER BY c           -- 跳过了 b
ORDER BY b                       -- 缺少 a 的过滤
WHERE a = 1 ORDER BY b DESC, c ASC  -- 排序方向不一致

关键规则：ORDER BY 的列必须紧接 WHERE 等值条件之后，且排序方向一致（MySQL 8.0+ 支持降序索引可放宽此限制）。

相关链接​