wonder/cs-note

Fork 0

Files

T

wonder 30d312ac35 Init

2026-05-24 11:42:38 +08:00

14 KiB

Raw Permalink Blame History

tags, create time

联合索引与最左前缀

概述

联合索引（Composite Index）是将多个列放在同一个 B+ Tree 中组织的索引。它的核心法则是最左前缀匹配原则——理解这一点就能避开 80% 的索引设计失误。

从"字典查词"理解联合索引

翻开一本中文词典，里面的词条按照拼音 → 笔画 → 部首的顺序排列：

先按拼音排序（a, b, c...）
拼音相同时，按笔画排序
笔画也相同时，按部首排序

你想查"爱"字，先翻到拼音 ai 的区域（第一列），再找 10 画的（第二列），最后精确定位（第三列）——这就是最左前缀的直觉。

反过来，如果有人问"10 画的字在第几页？"——你没法回答，因为 10 画的字分散在每个拼音区里，没有连续的区域可以翻到。

[!QUESTION] 思考联合索引 idx(a, b, c) 就像这本字典。条件必须从第一列开始依次匹配，不能"跳着查"。下面我们来看看它的具体结构。

联合索引的结构

叶子节点按 a 排序，a 相同时按 b 排序，a 和 b 都相同时按 c 排序：

a	b	c	data
1	10	3	row1
1	10	7	row2
1	20	1	row3
1	20	9	row4
2	5	2	row5
2	15	4	row6
3	10	1	row7
3	10	8	row8
3	30	5	row9

[!QUESTION] 这意味着什么？联合索引的本质是多级排序。你可以把它想象成 SQL 的 ORDER BY a, b, c。索引中的数据已经按照这个顺序排好了。

最左前缀法则详解

flowchart TD
    IDX["联合索引 idx(a, b, c)"]

    IDX --> U1["✅ WHERE a=1"]
    IDX --> U2["✅ WHERE a=1 AND b=2"]
    IDX --> U3["✅ WHERE a=1 AND b=2 AND c=3"]
    IDX --> X1["❌ WHERE b=2"]
    IDX --> X2["❌ WHERE c=3"]
    IDX --> X3["❌ WHERE b=2 AND c=3"]
    IDX --> U4["⚠️ WHERE a=1 AND c=3"]

    U1 -. "用 1 列" .-> _u1
    U2 -. "用 2 列" .-> _u2
    U3 -. "用全部" .-> _u3
    X1 -. "无效" .-> _x1
    X2 -. "无效" .-> _x2
    X3 -. "无效" .-> _x3
    U4 -. "仅用 a" .-> _u4

    style U1 fill:#00D866,color:#fff
    style U2 fill:#00D866,color:#fff
    style U3 fill:#00D866,color:#fff
    style X1 fill:#EE5A24,color:#fff
    style X2 fill:#EE5A24,color:#fff
    style X3 fill:#EE5A24,color:#fff
    style U4 fill:#FF9F43,color:#000

为什么 b=2 单独查不了？

graph LR
    subgraph SQ1["✅ WHERE a=1 二分查找定位"]
        direction LR
        A1["a=1 区域连续"] --> A2["直接定位起始行"] --> A3["顺序扫描即可"]
    end

    subgraph SQ2["❌ WHERE b=2 需全表扫描"]
        direction LR
        B1["b=10 在 a=1 下"] --> B4["b=10 在 a=3 下"] --> B2["b=2 散落各处"] --> B3["无法定位起点"]
    end

    style SQ1 fill:#E8F8F5,stroke:#00D866,stroke-width:2px
    style SQ2 fill:#FDEDEC,stroke:#EE5A24,stroke-width:2px
    style A1 fill:#00D866,color:#fff
    style A2 fill:#00D866,color:#fff
    style A3 fill:#00D866,color:#fff
    style B1 fill:#EE5A24,color:#fff
    style B2 fill:#EE5A24,color:#fff
    style B3 fill:#EE5A24,color:#fff
    style B4 fill:#EE5A24,color:#fff

B+ Tree 中的数据先按 a 排序，只有 a 相同时 b 才有序。单独查 b=2 时，满足条件的行分散在不同的 a 值下面，没有连续的 b=2 区域可供二分查找——只能全树扫描。

[!QUESTION] 思考规律是什么？只有匹配了索引的第一列，才能继续利用第二列；匹配了前两列，才能利用第三列。条件必须从索引的最左列开始，依次匹配，不能跳过中间列。而对于 WHERE a=1 AND c=3，虽然 a 匹配了第一列，但跳过了 b，所以 c 无法走索引。不过别担心，MySQL 优化器足够聪明——它会自动调整 WHERE 条件的顺序来匹配索引，SQL 中条件的书写顺序不影响索引的使用。

范围查询后的断裂

联合索引遇到范围查询（>, <, BETWEEN, LIKE 'prefix%'）后，右侧列的索引失效。

-- 假设已有联合索引 idx(status, created_at, type)

-- ⚠️ 看起来像用上了 3 个列，实际上 type 不会走索引！
SELECT * FROM orders WHERE status = 'paid'
    AND created_at > '2026-01-01' AND type = 'online';

-- 逐列分析：
--   status = 'paid'    → ✅ 等值匹配，精确定位起始行
--   created_at > ...   → ✅ 范围扫描，划定区间终点
--   type = 'online'    → ❌ 区间内数据未按 type 排序 → 退化为内存过滤

[!QUESTION] 为什么范围查询会打断后续列？一个生活类比：联合索引就像一本按省份 → 城市 → 区县排序的通讯录。你可以先翻到"广东省"（等值），再在里面翻"深圳市"（等值），最后找"南山区"——很快。但如果你只知道"某个日期之后创建的订单"（范围），你得到的是一个连续但内部无序的数据段，就像拿到"2026 年之后的广东省所有城市"的名单——这个名单里的区县是乱序的，你没法再按区县快速定位。

简单说：范围扫描得到的是一个"连续但内部无序"的区间，后续列无法利用索引的有序性进行二分查找。

graph LR
    A["等值列 = 定位起点"] -->|"精确找到起始位置"| B
    B["范围列 = 确定终点"] -->|"划定扫描区间"| C
    C["右侧列 = 失效"] -->|"区间内无序"| D["退化为 WHERE 过滤"]

    style A fill:#00D866,color:#fff
    style B fill:#00B6BC,color:#fff
    style D fill:#EE5A24,color:#fff

实战：调整联合索引的顺序

-- 场景：订单表常用查询——按状态筛选 + 按时间范围 + 按类型过滤

-- ❌ 原始索引：范围列在中间，后续等值列无法使用
CREATE INDEX idx_sct ON orders (status, created_at, type);
-- 查询 A: WHERE status=? AND created_at>? AND type=?
-- → type 无法用索引（created_at 的范围扫描打断了后续列）

-- ✅ 优化方案 1：将等值列提前
CREATE INDEX idx_stc ON orders (status, type, created_at);
-- 查询 B: WHERE status=? AND type=? AND created_at>?
-- → 三个列全部用上！status 定位起点，type 进一步缩小，created_at 做范围
-- 💡 注意：即使 SQL 写的是 created_at 在前，优化器也会自动调整顺序匹配索引

-- ✅ 优化方案 2：如果两个查询频率差不多，拆成两个索引
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_status_time ON orders (status, created_at);
-- 让每个索引专注于它的典型查询模式

EXPLAIN 验证：从 range 到 ref

用 EXPLAIN 对比两种索引的效果：

-- ❌ 用 idx_sct(status, created_at, type)
EXPLAIN SELECT * FROM orders
WHERE status = 'paid' AND created_at > '2026-01-01' AND type = 'online';

+----+------+---------------+---------+-------+-----------------+------+----------+-------------+
| id | type | possible_keys | key     | ref   | key_len         | rows | filtered | Extra       |
+----+------+---------------+---------+-------+-----------------+------+----------+-------------+
|  1 | range| idx_sct       | idx_sct | NULL  | ...             | 1280 |    33.33 | Using where |
+----+------+---------------+---------+-------+-----------------+------+----------+-------------+
-- type=range：范围扫描后，type='online' 需要逐行过滤（filtered=33.33%）

-- ✅ 用 idx_stc(status, type, created_at)
EXPLAIN SELECT * FROM orders
WHERE status = 'paid' AND type = 'online' AND created_at > '2026-01-01';

+----+------+---------------+---------+------------------+---------+------+----------+-------+
| id | type | possible_keys | key     | ref              | key_len | rows | filtered | Extra |
+----+------+---------------+---------+------------------+---------+------+----------+-------+
|  1 | ref  | idx_stc       | idx_stc | const,const      | ...     |   42 |   100.00 | NULL  |
+----+------+---------------+---------+------------------+---------+------+----------+-------+
-- type=ref：等值列全部命中，范围扫描在最后一步，filtered=100%！

[!TIP] 联合索引列序黄金法则

等值优先于范围：等值列放在前面

选择性高的列靠前：区分度大的列（如 user_id）比区分度小的（如 gender）靠前

范围列放在最后：让前面的等值列尽量多命中，范围列最后扫描

索引失效的典型场景

以下 5 个场景都会导致索引失效，逐一排查可解决大部分问题。

[!WARNING] 场景 1：隐式类型转换
ALTER TABLE users ADD INDEX idx_phone (phone);  -- phone 是 VARCHAR 类型
SELECT * FROM users WHERE phone = 13800138000;  -- 传入 INT！
MySQL 会把 varchar 列隐式转换为 int 再比较——函数作用于列，索引失效。

解决：传入字符串 WHERE phone = '13800138000'。

[!WARNING] 场景 2：函数/表达式包裹索引列
SELECT * FROM users WHERE YEAR(created_at) = 2026;
索引列被函数包裹后，B+ Tree 无法直接定位。

解决：改为范围查询 WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'。

[!WARNING] 场景 3：LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%abc%';
前缀未知，无法利用索引的有序性定位。

解决：使用前缀匹配 LIKE 'abc%'，或考虑全文索引。

[!WARNING] 场景 4：OR 条件中有列没有索引
SELECT * FROM users WHERE email = 'a@x.com' OR phone = '138...';
如果 email 有索引但 phone 没有，整个查询不走索引。

解决：给 phone 加索引，或用 UNION ALL 拆分为两个独立查询。

[!WARNING] 场景 5：字符集不一致导致隐式转换
-- 表 charset=utf8mb4，客户端 charset=gbk → 自动转换
跨字符集连接时，MySQL 需要逐行转换再比较，索引失效。

解决：确保连接字符集一致 SET NAMES utf8mb4。

索引失效决策图

flowchart TD
    Q0{"查询条件是否包含索引最左列?"}
    Q1{"最左列是否被函数/表达式包裹?"}
    Q2{"列类型与传入值类型是否一致?"}
    Q3{"OR 条件中所有列是否都有索引?"}
    Q4{"字符集是否一致?"}
    OK["✅ 索引生效"]
    FAIL["❌ 索引失效，排查修复"]

    Q0 -->|是| Q1
    Q0 -->|否| FAIL
    Q1 -->|否| Q2
    Q1 -->|是| FAIL
    Q2 -->|是| Q3
    Q2 -->|否| FAIL
    Q3 -->|是| Q4
    Q3 -->|否| FAIL
    Q4 -->|是| OK
    Q4 -->|否| FAIL

    style OK fill:#00D866,color:#fff
    style FAIL fill:#EE5A24,color:#fff

最左前缀的灵活应用

一个设计良好的联合索引可以同时服务 WHERE、ORDER BY、GROUP BY 三种需求。

-- 建索引：idx(status, type, created_at)
CREATE INDEX idx_status_type_time ON orders (status, type, created_at);

-- ── 查询 1：等值 + 范围 ──
SELECT * FROM orders WHERE status = 'pending'
    AND created_at > '2026-01-01';
-- → status 等值定位起点，created_at 范围扫描。type 虽在中间但没用到，不影响前两列生效

-- ── 查询 2：利用 ORDER BY ──
SELECT * FROM orders WHERE status = 'pending'
ORDER BY type ASC;
-- → WHERE 条件锁定了 status='pending' 的子区间，该区间内数据天然按 type 排序
-- → 无需额外 filesort！

-- ── 查询 3：利用 GROUP BY ──
SELECT type, COUNT(*) FROM orders WHERE status = 'pending'
GROUP BY type;
-- → 同上，子区间内 type 已有序，分组可以直接跳过

Go 中利用索引的查询设计

在 Go 后端中，构建查询时应当有意识地按照索引列序组织条件：

// 按索引列序拼接查询条件，确保每个条件都能命中 idx_stc(status, type, created_at)
func buildOrderQuery(status string, orderType string, since time.Time) (string, []any) {
    conditions := []string{"1=1"}
    args := []any{}

    if status != "" {
        conditions = append(conditions, "status = ?")
        args = append(args, status)
    }
    if orderType != "" {
        conditions = append(conditions, "type = ?")
        args = append(args, orderType)
    }
    if !since.IsZero() {
        conditions = append(conditions, "created_at > ?") // 范围列放最后
        args = append(args, since)
    }
    query := "SELECT * FROM orders WHERE " + strings.Join(conditions, " AND ")
    return query, args
}

[!NOTE] 关键理解 ORDER BY / GROUP BY 能复用联合索引，靠的不是"巧合"，而是 B+ Tree 叶子节点本身有序这一物理特性。只要 WHERE 过滤条件匹配了联合索引的左侧列，剩余列就是有序的——优化器只是利用了已有的顺序，并没有多做一次排序。

[!TIP] 一索引多用一个好的联合索引可以同时服务 WHERE、ORDER BY、GROUP BY 三种需求。在设计索引时要考虑查询的整体模式，而不是单一查询。

联合索引列序决策表

查询模式	列序建议	示例索引	说明
等值 + 等值	高选择性列在前	`idx(user_id, status)`	两个等值条件都能走 ref
等值 + 范围	等值列在前，范围列最后	`idx(status, created_at)`	等值定位起点，范围最后扫描
等值 + 等值 + 范围	等值列全在前，范围列最后	`idx(status, type, created_at)`	让尽量多的等值列命中
等值 + ORDER BY	等值列 → 排序列	`idx(status, type)`	排序列可复用索引有序性
等值 + GROUP BY	同 ORDER BY	`idx(status, type)`	分组复用索引有序性
范围 + 范围	选择性高的范围列在前	`idx(created_at, amount)`	两个范围条件，只能一个走索引

[!QUESTION] 什么时候该拆索引？当两种查询模式的列序互相矛盾时（如一个需要 (a, b, c)，另一个需要 (b, a, c)），与其在同一个索引上做取舍，不如创建两个独立索引，让每个索引专注于它的典型查询模式。但要注意——索引越多，写入开销越大，需要在读写之间找到平衡。

关联笔记

hhs/MySQL/03-索引与查询优化/13-聚簇索引与二级索引 — 聚簇索引本身就是特殊的联合索引 (PK)
hhs/MySQL/03-索引与查询优化/15-EXPLAIN 完全指南 — 用 EXPLAIN 验证联合索引是否按预期生效
hhs/MySQL/03-索引与查询优化/16-慢查询日志分析 — 如何从 slow log 中识别索引未命中
hhs/MySQL/03-索引与查询优化/17-查询改写技巧 — 将失效的查询改写为可利用索引的形式

14 KiB Raw Permalink Blame History Unescape Escape