[!info] related notes

• 所属 MOC：数据库 MOC
• 前置：B+Tree 数据库索引、聚簇索引与二级索引
• 优化：索引选择性、基数与代价、EXPLAIN

联合索引、最左前缀与覆盖索引

[!abstract] 核心结论 联合索引 (A, B, C) 是一棵按复合 Key 字典序排列的 B+Tree，不是三棵单列索引。最左前缀、范围条件、排序和覆盖能力，都来自这一个有序结构。

字典序是根本模型

CREATE INDEX idx_orders_user_status_time
ON orders(user_id, status, created_at);

索引大致按以下顺序组织：

(user_id, status, created_at)

(1, paid,    09:00)
(1, paid,    10:00)
(1, pending, 08:00)
(2, paid,    07:00)

比较顺序：先 user_id，相同再比较 status，前两列相同再比较 created_at。

最左前缀不是死口诀

索引 (A, B, C) 最容易直接形成连续 Seek Range 的条件是：

A
A + B
A + B + C

例如：

WHERE user_id = 42
  AND status = 'paid'

可以定位一个连续的小区间。

只有 status = 'paid' 时，paid 分散在所有 user_id 组中，传统 Seek 很难直接缩小为单个连续范围。

[!note] 优化器可能使用 Skip Scan、Index Scan + Filter 或其他策略 因此“缺最左列就绝对不能使用索引”并不准确。更稳定的说法是：无法直接利用完整前缀形成高效连续 Seek Range，具体计划看数据库能力和成本。

Equality、Range 与后续列

对于 (A, B, C)：

WHERE A = ? AND B >= ? AND C = ?

• A = ? 确定一个大组；
• B >= ? 确定该组中的范围起点；
• C = ? 通常不能进一步把整个 B 范围缩成单个连续区间；
• 但 C 仍可能在 Index 层过滤，减少回表。

所以常说“范围条件后面的列不能继续用于定位”，但不等于后续列完全没有价值。

联合索引列顺序怎么设计

不要只按“选择性最高放最左”排序。依次考虑：

1. 高频 Query Pattern；
2. Equality 与 Range 条件；
3. ORDER BY / GROUP BY；
4. Tenant / Security Scope；
5. 覆盖需要；
6. 写入频率和索引宽度；
7. 数据分布与相关性。

例如多数查询都是：

WHERE tenant_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20

(tenant_id, status, created_at DESC) 可能比单纯把全局基数最高列放最左更匹配实际访问路径。

ORDER BY 如何利用索引

如果过滤后的索引顺序与排序要求一致，数据库可以直接按叶子顺序读取，避免额外 Sort。

WHERE user_id = 42
ORDER BY status, created_at

可能利用 (user_id, status, created_at)：user_id 已固定，剩余 Key 天然按 status, created_at 排序。

能否支持混合 ASC/DESC、NULL 排序和反向扫描，取决于数据库与索引定义，应以执行计划验证。

覆盖索引

当查询需要的过滤、排序和返回列都能从索引获得时，可以减少或避免访问表数据：

CREATE INDEX idx_orders_user_time_total
ON orders(user_id, created_at, total);

SELECT created_at, total
FROM orders
WHERE user_id = ?;

Included Columns

部分数据库支持把不参与排序的返回列作为 Included Column：

CREATE INDEX ... ON orders(user_id, created_at)
INCLUDE (total);

这样保持 Key 较窄，又为 Index-only Scan 提供返回列。具体语法与可见性规则按数据库实现。

覆盖不一定等于零表访问

• InnoDB Secondary Index 已包含 Primary Key；
• PostgreSQL Index-only Scan 还依赖 Visibility Map 判断是否需要访问 Heap；
• MVCC、锁定读和数据库实现都会影响是否真正避免表访问。

为什么 SELECT * 经常破坏覆盖机会

返回整行意味着索引必须包含大量列才可能覆盖：

• Index 变宽；
• Fan-out 下降；
• 写放大与存储增加；
• Cache 效率降低。

只查询真正需要的列，既减少网络和序列化，也增加覆盖机会。

联合索引不是越宽越好

每增加一列都会增加：

• Storage；
• Buffer Pool 占用；
• Insert / Update 成本；
• Page Split；
• Schema 变更和维护复杂度。

重复前缀索引也要审查：有了 (A, B) 后，是否还需要单独 (A)，取决于数据库、覆盖需求、宽度和其他 Query，而不是自动删除。

稳定分析模板

面对一条 SQL：

1. 列出 WHERE 的 equality / range / IN / null 条件
2. 列出 ORDER BY / GROUP BY / LIMIT
3. 列出实际返回列
4. 找出最常用的连续复合 Key 前缀
5. 估算匹配行、回表和排序成本
6. 检查索引宽度与写入代价
7. 用 EXPLAIN ANALYZE 验证 estimated vs actual

常见误区

• WHERE 里条件书写顺序必须和索引一致：优化器通常会重排条件，关键是复合 Key 结构。
• (A,B,C) 等于 (A)、(B)、(C)：错误，它只有一套字典序。
• 范围后面的列完全无用：可能无法继续 Seek，但仍可过滤/覆盖。
• 覆盖索引一定更快：索引过宽、低选择性或写入重时未必。
• 建完索引不用看计划：统计和数据分布可能让优化器选择其他路径。

掌握标准

• 能用字典序解释最左前缀。
• 能区分 Seek Range 与 Index-level Filter。
• 能设计同时服务过滤、排序和 LIMIT 的索引。
• 能解释 Covering / Included Column 和 Index-only 边界。
• 能平衡读收益与写入、存储、缓存成本。

Related notes

• 索引选择性、基数与代价
• 聚簇索引与二级索引
• EXPLAIN