为了优化一个慢查询，给订单表加了三个索引。查询从 800 毫秒降到 40 毫秒，然后没人再提起这件事——直到半年后批量导入任务从四十分钟变成两小时。翻出来看，这张表上有十一个索引，其中四个从上线起就没有任何查询用过。
索引不是免费的加速器，它是用写入换读取的一次交易。这笔交易在加索引的那一刻就成立了，但账单不在那一刻结算：它分散在之后每一次写入、每一次批量导入、每一份存储里，因此长期不被看见。
1. 一个索引的三笔代价
每建一个索引，代价分三处结算，只有第一处会被立刻感知。
| 代价 | 结算时机 | 是否被看见 |
|---|---|---|
| 写入放大：每次 INSERT/UPDATE/DELETE 要多维护一棵 B+ 树 | 每次写入 | 单条操作看不出，批量写时才显形 |
| 写放大还有个二阶效应：索引越多，单次事务产生的 redo 与 undo 越多，缓冲池脏页回刷的压力越大。表现出来往往不是"写变慢"，而是主从延迟变大、检查点更频繁——这两个症状很少被追溯到索引上。 |
| 存储放大：索引本身占空间，通常接近被索引列的规模 | 建索引时一次性 | 只有看磁盘账单时可见 |
| 优化器选择变差：可选的执行路径变多，选错的概率上升 | 查询时随机 | 表现为"偶发的慢查询"，最难归因 |
写入放大是最容易被低估的一项。单条 INSERT 多维护三个索引，耗时可能只多几十微秒；但一次 5 万行的批量导入，放大倍数会接近线性叠乘，而且它同时把 redo 日志、缓冲池脏页、主从延迟一起推高。批量导入从四十分钟变成两小时，不是某个索引坏了，而是十一个索引的正常成本。
一次批量导入前后各测一次，比任何估算都准：
// 导入前记录一次基准，导入后再测一次，两次之间只允许索引数变化
time mysql -e "LOAD DATA LOCAL INFILE '/tmp/orders.csv' INTO TABLE orders
FIELDS TERMINATED BY ',' IGNORE 1 LINES"
// 同时看两个容易被忽略的数
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted'"
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Seconds_Behind|Running"
// 耗时上升而 inserted 行数不变，才是索引带来的净成本
// 把"写入代价"从直觉变成数：单表索引数与批量导入耗时的对照
SELECT t.table_name,
COUNT(s.index_name) AS idx_cnt,
ROUND(SUM(s.stat_value) / 1024 / 1024, 1) AS idx_mb
FROM information_schema.tables t
JOIN information_schema.statistics s
ON s.table_schema = t.table_schema AND s.table_name = t.table_name
WHERE t.table_schema = 'app'
GROUP BY t.table_name
ORDER BY idx_cnt DESC
LIMIT 20;
// idx_cnt > 8 且 idx_mb 接近表本身时，先按 §2 找冗余，再谈加索引
2. 复合索引的列顺序与冗余索引
复合索引 (a, b, c) 能被 a、(a,b)、(a,b,c) 三种条件复用，但不能被 b、(b,c) 复用。这条规则人人知道，但落到评审现场，真正的浪费来自另一个方向：前缀重复。
| 已有索引 | 新提出的索引 | 是否冗余 | 原因 |
|---|---|---|---|
(user_id, status) |
(user_id) |
冗余 | 前者是最左前缀的超集 |
(user_id, status) |
(status) |
不冗余 | 状态列不是最左前缀 |
(a, b) |
(b, a) |
不冗余但低效 | 两条都能用，通常留一条即可 |
(a, b, c) |
(a, c) |
冗余 | a 后跳过 b 无法命中，但 (a,c) 的需求多由 (a,b,c) 覆盖大部分场景 |
(a) |
(a) WHERE deleted = 0 |
视场景 | 部分索引体积小得多，可替代全量索引 |
冗余索引在评审时极难看出来，因为它需要把新索引和全表已有索引逐个比对，而评审者通常只看到这次改动涉及的几个文件。可行的做法是把比对交给工具：读 information_schema.statistics 或 SHOW INDEX，按最左前缀做包含判断。
// 冗余索引粗筛：对每张表列出"被其他索引以最左前缀包含"的索引
mysql -N -e "
SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) cols
FROM information_schema.statistics
WHERE table_schema='app'
GROUP BY table_name, index_name" \
| awk '{k=$1; c[k]=c[k] " " $3; n[k]=n[k] " " $2}
END {for (t in c) {split(c[t],a," "); for (i in a) for (j in a)
if (i!=j && index(a[j],a[i])==1) print t, "可能冗余:", a[i], "被", a[j], "包含"}}'
// 命中项逐条人工确认，部分索引与函数索引不适用此判据
部分索引是压缩索引成本的另一个方向。它把索引体积按条件比例缩减，代价是查询条件必须与索引条件一致才会命中：
// 只为未删除行建索引，体积通常只有全量索引的一个零头
CREATE INDEX idx_orders_open ON orders (status, created_at)
WHERE deleted = 0;
// 能命中：条件里带 deleted = 0
SELECT id FROM orders WHERE deleted = 0 AND status = 'PAID' ORDER BY created_at DESC LIMIT 50;
// 命中不了：查询没写 deleted = 0，优化器无法证明与索引条件相容
SELECT id FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 50;
列顺序的选择还有个常被忽略的约束：等值列在前、范围列在后。(status, created_at) 能让"状态等值 + 时间范围"一次命中；反过来 (created_at, status) 在时间范围跨多天时几乎退化成只用第一列。
3. 膨胀：删除不回收，更新会分裂
B+ 树的页在删除后不会立刻归还。删掉一半数据，表文件大小可能几乎不变，页利用率降到三四成，扫描同样的行数要多读一倍的页。
| 现象 | 成因 | 重建能否解决 |
|---|---|---|
| 表很大但行数不多 | 大量删除后页未回收 | 能 |
| 扫描行数不多却读了很多页 | 页利用率低、页内碎片 | 能 |
| 查询计划突然变差 | 统计信息陈旧，不是膨胀 | 不需要重建，更新统计即可 |
| 写入集中在少数页 | 单调递增主键之外的随机插入 | 部分能，根本解法是调整主键或分区 |
这里有个经典的归因错误：重建索引之后查询变快，就认定是"索引碎片被清理了"。实际上重建会把数据重新读进缓冲池，第一次查询快很可能只是缓存效应。要分离两个因素，得在重建后把缓冲池清掉再测一次，或者对比重建前后的页利用率与逻辑读次数，而不是对比耗时。
4. 删索引的风险其实低于直觉
团队不敢删索引，通常的理由是"万一有查询在用"。但两件事值得分开看：
| 顾虑 | 真实风险 | 应对 |
|---|---|---|
| 删了之后某查询变慢 | 中，且可观测 | 先设 invisible（MySQL 8.0 支持），观察一个周期再真删 |
| 删了之后写入变快但没人注意 | 无风险，是收益 | 记录删前删后的批量导入耗时 |
| 删错导致事故 | 低，回滚就是重建 | 重建是可控操作，代价是可预期的时间 |
| 不知道有没有查询在用 | 高，但这是观测缺失不是删除风险 | 先补索引使用统计，再谈删除 |
真正的问题不是"删索引危险"，而是没人知道哪个索引在用。多数数据库都能给出索引使用计数（MySQL 的 performance_schema.table_io_waits_summary_by_index_usage、PostgreSQL 的 pg_stat_user_indexes），但这些数据默认不采集或不看。补上这个观测之后，"哪些索引可以删"会从争论变成看数。
// MySQL 8.0：从未被读过的索引（自实例启动以来）
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema NOT IN ('mysql','performance_schema','sys')
ORDER BY object_name;
// 注意 count_star 自实例启动累计，重启会清零；至少观察一个完整业务周期
5. 排错：五种"加了索引反而更慢"
| 现象 | 成因 | 处理方向 |
|---|---|---|
| 批量导入明显变慢 | 索引数随表增长，写入放大叠乘 | 导入前临时关闭非唯一索引，导入后重建 |
| 某查询偶发变慢，重跑又正常 | 优化器在多索引间选错执行计划 | 用 FORCE INDEX 定位，再看统计信息是否陈旧 |
| 表文件持续增长但行数平稳 | 删除不回收 + 更新导致页分裂 | 评估重建，同时检查是否有高频更新的大字段 |
| 加索引后另一条查询变慢 | 新索引改变了优化器成本估算 | 对比两条查询的 EXPLAIN，必要时用索引提示 |
| 建索引期间业务卡顿 | 在线 DDL 仍占用 IO 与元数据锁 | 放到低峰，控制单次 DDL 规模，观察主从延迟 |
补充一条判据：排错时先问"这个慢是新出现的，还是一直如此"。一直如此的慢属于成本结构，改法是调整索引设计；新出现的慢才可能是索引坏了或被优化器绕过了。这两类问题的排查方向完全相反。
第一条最常见的误解是"导入慢是索引坏了"。索引没坏，是它的正常成本在批量场景被放大。临时关闭非唯一索引再重建，比逐条排查索引有效性更直接——但要注意这一步只在可停机或可接受期间不可检索的场景适用。
6. 边界：什么情况下多索引的代价可以忽略
| 场景 | 为什么代价低 | 做法 |
|---|---|---|
| 读多写极少的表（配置、字典） | 写入放大几乎不发生 | 按查询需要加，不必严格评审 |
| 数据量小到全表扫描更快 | 索引的价值不存在 | 连索引都不必加，先确认数据量级 |
| 写入完全离线（每日批量重建的报表表） | 写入成本在非高峰结算 | 索引可以多，但重建顺序要排 |
| 索引只服务于一条关键查询 | 收益明确、可验证 | 保留并标注归属查询，避免被误删 |
| 分区分表的单分区 | 单表规模被切小，页利用率稳定 | 膨胀问题转移成跨分区查询问题 |
判断口径可以压成一句：这个索引是否有一条明确归属的查询，且它的读取收益大于它对写入的放大。两条都答不上来时就该删，而不是"留着看看"。
7. 自测
- 一个索引的三笔代价分别在哪三个时刻结算？哪一笔最难归因？
- 复合索引
(a, b, c)能被哪些条件复用？(a, c)为什么不算在内？ - 重建索引后查询变快，如何排除缓存效应？
- MySQL 8.0 的
invisible index在删除决策中解决的是哪个具体顾虑？ - 批量导入前临时关闭非唯一索引，这个操作的适用前提是什么？
- 为什么"数据量小到全表扫描更快"时，加索引连收益都没有？