KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA

02 · 索引设计：从选择性到字段顺序 — keel 龙骨

这一章回答：联合索引的字段顺序怎么定，而不是"区分度高的放前面"一句话。

这一章回答：联合索引的字段顺序怎么定，而不是"区分度高的放前面"一句话。

先修课给了经验规则"区分度高的放前面，除非有排序/范围需求"。真实设计里有三类需求同时在竞争：等值过滤、范围过滤、排序。它们的优先级不同，顺序错了性能差一个数量级。

一、先量化：选择性怎么算

-- 单列的选择性 = 不同值个数 / 总行数，越接近 1 越好
SELECT
  COUNT(DISTINCT tenant_id) / COUNT(*) AS sel_tenant,
  COUNT(DISTINCT status)    / COUNT(*) AS sel_status,
  COUNT(DISTINCT created_at)/ COUNT(*) AS sel_created
FROM orders;

-- 联合选择性（判断两列组合是否更优）
SELECT COUNT(DISTINCT CONCAT(tenant_id,'-',status)) / COUNT(*) AS sel_tenant_status FROM orders;

经验阈值：

选择性	结论
> 0.2	单列就够，适合做联合索引的前导列
0.01 ~ 0.2	需要和别的列组合才有价值
< 0.01（如 `status`、`is_deleted`）	单独建索引几乎无效，只能作为联合索引的后继列

⚠️ 注意选择性是平均值。一个 status 列 90% 是 PAID、10% 是其它，平均选择性看着还行，但查 status='PAID' 时几乎等于全表——这正是需要直方图（第 01 章）或改写查询的场景。

二、三段排布法：等值 → 排序 → 范围

这是本节的核心。假设查询是：

SELECT * FROM orders
WHERE tenant_id = 42 AND status IN ('PAID','SHIPPED') AND created_at > '2026-01-01'
ORDER BY created_at DESC LIMIT 20;

索引应该这样排：

① 等值条件列：tenant_id        （IN 也算等值，可以放这）
② 排序列：    created_at       （让索引本身有序，避免 filesort）
③ 其余过滤列：status           （只能当"索引下推"的过滤项）

即 idx (tenant_id, created_at, status)。

为什么不是 (tenant_id, status, created_at)？ 因为 status 是等值但选择性极低，而 created_at 是范围。范围列一旦出现在索引中，它后面的列就无法再用于索引定位（只能用于索引下推过滤）。而如果范围列是排序所需的列，把它放在最后，索引的有序性可以直接服务 ORDER BY。

判断规则（按优先级）：

1. 等值条件列优先，按选择性从高到低排
2. 如果 ORDER BY / GROUP BY 的列在等值列之后 → 紧接等值列放排序列
3. 范围条件列放最后（它只能用来过滤，不能用来定位后续列）
4. 剩下的低选择性过滤列可放末尾，靠"索引下推"生效（Extra: Using index condition）

三、覆盖索引：收益与代价

-- 查询只需要三列，如果索引包含全部三列 → 不回表
CREATE INDEX idx_cover ON orders (tenant_id, status, created_at);
SELECT tenant_id, status, created_at FROM orders WHERE tenant_id = 42;
-- Extra: Using index

收益：省掉回表的随机 IO。对于扫描行数多的查询，这是最大的一笔优化（回表是随机读，比顺序扫描慢一个数量级）。

代价：索引变宽 → 占磁盘、占 buffer pool、每一个二级索引都要维护。判断标准：

场景	建议
高频、扫描行数多的查询	值得为它做覆盖索引（哪怕多加一列）
低频或返回行数很少（走 `ref` 只扫几行）	不值得，回表几行成本极低
想把大字段（`TEXT`/`JSON`）塞进索引	❌ 不行，索引长度有限制

一个反直觉的点：覆盖索引让你"多建了一个几乎重复的索引"。先检查是否可以通过调整现有索引的列顺序满足需求，而不是无脑新建。

四、前缀索引与函数索引

前缀索引（长字符串列）

-- 先看多长的前缀区分度足够
SELECT
  COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS p8,
  COUNT(DISTINCT LEFT(email, 12))/ COUNT(*) AS p12,
  COUNT(DISTINCT email)          / COUNT(*) AS full
FROM users;

CREATE INDEX idx_email ON users (email(12));

取舍：前缀越短越省空间，但区分度下降，且前缀索引不能用于覆盖索引与 ORDER BY。目标是取到"区分度接近全列"的最短长度。

函数索引（8.0+）与虚拟列

当查询条件不可避免地要套函数时（比如 JSON 字段、大小写不敏感匹配），与其让索引失效，不如给函数建索引：

-- 8.0 支持函数索引（本质是虚拟列上的索引）
CREATE INDEX idx_created_date ON orders ((DATE(created_at)));

-- JSON 字段：用生成的虚拟列
ALTER TABLE events
  ADD COLUMN event_type VARCHAR(32) AS (JSON_UNQUOTE(JSON_EXTRACT(payload,'$.type'))) STORED,
  ADD INDEX idx_event_type (event_type);

适用边界：函数索引只能匹配完全相同的表达式。DATE(created_at) 的索引帮不了 MONTH(created_at) 的查询。所以它的正确用法是：先把查询收敛到一个固定表达式，再为它建索引。

五、一份可复用的设计流程

1. 列出该接口所有高频 SQL（按调用量排序，只优化前 3~5 条）
2. 对每条 SQL 标出：等值列 / 范围列 / 排序列 / 返回列
3. 按"等值 → 排序 → 范围"排出候选索引
4. 检查能否与已有索引合并（左前缀兼容的可以合并）
5. 评估覆盖需求（是否值得为省回表加宽索引）
6. 在测试库造同量级数据，用 EXPLAIN + 压测验证
7. 记录：SQL、索引定义、前后 rows/耗时、写入耗时变化

动手：可观察结果

产出	判断标准
一张表的选择性测算表	每列的选择性数值 + 结论（能否单列 / 必须组合）
一次字段顺序对比实验	`(tenant,status,created_at)` vs `(tenant,created_at,status)`：两组 `key_len`、`rows`、耗时对比
一次覆盖索引验证	`Extra` 从空变成 `Using index`，并给出耗时与 buffer pool 占用的变化
一次前缀索引测算	不同前缀长度的区分度曲线，说明为什么选了该长度
一份索引设计记录	含 SQL、候选索引、取舍理由、验证数据

完成标志：给定一条带 WHERE + ORDER BY + LIMIT 的 SQL，你能直接写出最优索引定义，并说明每一列为什么在那个位置。

故障注入

注入方式	观察
把范围列放在联合索引中间	后面的列是否失效（`key_len` 停在范围列）
排序字段不在索引中	是否出现 `Using filesort`，数据量翻倍后耗时增长曲线
给低选择性列单独建索引	优化器是否根本不用它（看 `possible_keys` vs `key`）
覆盖索引改成 `SELECT *`	`Using index` 消失，耗时与 IO 变化
前缀索引长度取太短	区分度下降、回表行数上升
为覆盖索引把索引加到 5 列	写入耗时与索引体积的变化

自测题

"区分度高的字段放前面"在什么情况下会让性能更差？
索引 (a, b, c)，查询 WHERE a=1 AND c>10 ORDER BY b，索引能用到几列？排序能否避免 filesort？
覆盖索引值得建的判据是什么？什么情况下不值得？
前缀索引有哪些能力限制？
查询必须写成 WHERE DATE(created_at)=? 且不能改写，有哪些办法让索引生效？

进入 keel 阅读