一、联合索引基础

1.1 什么是联合索引

-- 单列索引
CREATE INDEX idx_a ON table(a);
CREATE INDEX idx_b ON table(b);

-- 联合索引（多列组合）
CREATE INDEX idx_a_b ON table(a, b);
CREATE INDEX idx_a_b_c ON table(a, b, c);

1.2 存储结构

联合索引 idx_a_b_c(a, b, c)的B+树：

    [(1,1,1), (1,1,2), (1,2,1), (2,1,1)]
            ↓ 先按a排序
    [(1,1,1), (1,1,2), (1,2,1)]  ← a相同
            ↓ 再按b排序
    [(1,1,1), (1,1,2)]  ← a、b相同
            ↓ 最后按c排序

二、最左前缀原则

2.1 核心规则

CREATE INDEX idx_a_b_c ON table(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
WHERE b = 2 AND c = 3;                -- 缺少a

2.2 原理

联合索引按从左到右的顺序排序：

  • a有序
  • a相同时，b有序
  • a、b相同时，c有序

缺少a：无法定位起始位置。

三、索引顺序选择

3.1 基本原则

-- 规则1：等值条件在前，范围条件在后
WHERE a = 1 AND b > 10 AND c = 3;

-- ❌ 差：(a, b, c)
CREATE INDEX idx_a_b_c ON table(a, b, c);
-- b是范围，c失效

-- ✅ 好：(a, c, b)
CREATE INDEX idx_a_c_b ON table(a, c, b);
-- 使用(a, c, b)

3.2 选择性原则

-- 规则2：选择性高的在前
SELECT
    COUNT(DISTINCT a) / COUNT(*) AS sel_a,  -- 0.9
    COUNT(DISTINCT b) / COUNT(*) AS sel_b,  -- 0.3
    COUNT(DISTINCT c) / COUNT(*) AS sel_c   -- 0.01
FROM table;

-- 选择性：a > b > c
-- ✅ 好：(a, b, c)
CREATE INDEX idx_a_b_c ON table(a, b, c);

3.3 查询频率原则

-- 规则3：频繁查询的组合
-- 如果经常查询：
WHERE a = ? AND b = ?;  -- 80%
WHERE a = ?;            -- 15%
WHERE b = ?;            -- 5%

-- ✅ 推荐：(a, b)
CREATE INDEX idx_a_b ON table(a, b);
-- 覆盖80%+15%=95%的查询

四、特殊情况

4.1 索引下推（ICP）

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

WHERE a = 1 AND b > 10 AND c = 3;
-- MySQL 5.6+：索引下推优化
-- 1. 使用(a, b)定位
-- 2. 在索引中过滤c=3（不回表）
-- 3. 回表获取完整数据

4.2 隐式最左前缀

CREATE INDEX idx_a_b ON table(a, b);

-- MySQL优化器自动调整顺序
WHERE b = 2 AND a = 1;  -- 等价于 WHERE a = 1 AND b = 2
-- 可以使用索引

五、联合索引 vs 多个单列索引

5.1 对比

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

-- 方案2：联合索引
CREATE INDEX idx_a_b ON table(a, b);

-- 查询
WHERE a = 1 AND b = 2;

方案1（多个单列索引）：

  • 选择一个索引使用（通常选择性高的）
  • 或index_merge（合并索引，性能一般）

方案2（联合索引）：

  • 直接使用联合索引
  • 性能更好

结论：联合索引优于多个单列索引。

六、冗余索引避免

6.1 冗余示例

-- ❌ 差：idx_a冗余
CREATE INDEX idx_a ON table(a);
CREATE INDEX idx_a_b ON table(a, b);

-- 解释：
-- WHERE a = 1;  → idx_a_b也能满足（只用前缀）
-- idx_a是冗余的

6.2 清理规则

-- 如果有(a, b, c)，以下都冗余：
(a)
(a, b)

-- 但以下不冗余：
(b)
(c)
(b, c)
(a, c)  -- 顺序不同

七、实战案例

案例1：订单查询优化

-- 查询场景
-- 1. 按用户查询：WHERE user_id = ?;  -- 70%
-- 2. 按用户+状态查询：WHERE user_id = ? AND status = ?;  -- 20%
-- 3. 按用户+时间范围：WHERE user_id = ? AND created_at BETWEEN ? AND ?;  -- 10%

-- 索引设计
CREATE INDEX idx_user_id ON orders(user_id);  -- ❌ 不够

-- ✅ 好：
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);
-- 覆盖所有查询场景

案例2：商品搜索优化

-- 查询：按分类、价格范围搜索
WHERE category = '手机' AND price BETWEEN 3000 AND 5000;

-- ✅ 索引设计
CREATE INDEX idx_category_price ON products(category, price);
-- category等值在前，price范围在后

案例3：复杂条件查询

-- 查询
WHERE a = 1 AND b = 2 AND c > 10 AND d = 4;

-- 分析：
-- a、b、d是等值 → 选择性：a(0.9) > d(0.7) > b(0.3)
-- c是范围

-- ✅ 最优索引
CREATE INDEX idx_a_d_b_c ON table(a, d, b, c);
-- 等值高选择性在前，范围在后

八、索引优化技巧

8.1 覆盖索引

-- 查询
SELECT a, b, c FROM table WHERE a = 1 AND b = 2;

-- 索引
CREATE INDEX idx_a_b_c ON table(a, b, c);
-- SELECT的列都在索引中 → 覆盖索引，不需要回表

8.2 索引排序

-- 查询
SELECT * FROM table WHERE a = 1 ORDER BY b, c;

-- 索引
CREATE INDEX idx_a_b_c ON table(a, b, c);
-- 利用索引有序性，避免额外排序

九、总结

核心要点

  1. 最左前缀原则：必须包含索引最左边的列
  2. 索引顺序：等值在前、范围在后、选择性高在前
  3. 避免冗余：(a,b)存在时，(a)是冗余的
  4. 联合优于单列：(a,b)优于(a)+(b)
  5. 覆盖索引：SELECT列都在索引中
  6. 索引下推：范围查询后的列仍可过滤

记忆口诀

联合索引最左前，缺少前导索引断，
等值条件排在前，范围条件放最后。
选择性高往前靠，查询频率要考虑，
避免冗余多检查，覆盖索引最高效。

本文字数：约2,400字 难度等级：⭐⭐⭐⭐（索引进阶）