S13-A21 · 索引的写入成本：加索引之前先算什么

一张写入量很大的表上，三天里加了五个索引，每个都是为了优化某一条慢查询。读变快了，写开始排队——批量导入从 8 分钟变成 25 分钟，而"是谁变慢了"花了半天才定位到索引。

索引不是免费的：每一次写入都要维护所有相关索引，包括 B+ 树的页分裂、二级索引的回表更新、唯一性检查。这个权衡有一个不对称之处：读的收益立刻看得见（某条查询变快），写的代价是全局的（所有写入都变慢）。所以按"某条慢查询"加索引时，受益者与受罚者不是同一批人，问题因此难被及时提出。

所以"加索引"是一次读写权衡，不是单纯的优化。

1. 写入成本的三个来源

来源	说明
索引条目维护	每索引一次额外的树操作
页分裂与随机 IO	索引键无序时更严重
唯一性检查	唯一索引需要读一次确认

第二项与键的选择强相关：用自增主键插入是顺序写，用 UUID 是随机写，后者的页分裂与缓存命中率都差得多。

还有一项隐性成本：索引会拖慢备份与恢复。物理备份要拷更多页，逻辑备份要扫更多索引，恢复时重建索引也计时——这些都不在"加索引"的评估里，但会在某次恢复演练中暴露。

第三项只在唯一索引上出现，但代价不小：每次插入要额外一次索引查找，在高并发插入时是明显的额外读。

写侧的成本还要看"写入形态"：单行插入的成本最高（每次提交都维护索引），批量插入可以摊薄。所以同一张表，在线业务写与批量导入写的"索引代价"不在一个量级，评估时不能混算。

// 实测索引数量的影响：同一台机器，2 索引 vs 7 索引
for n in 2 7; do
  mysql -e "CREATE TABLE bench_$n (id BIGINT AUTO_INCREMENT PRIMARY KEY, a INT, b INT,     c INT, d INT, e INT, f INT, KEY(a), KEY(b)$( [ $n -eq 7 ] && echo ', KEY(c),KEY(d),KEY(e),KEY(f)' ))"
  /usr/bin/time -f "idx=$n %e s" mysqlimport --local shop /tmp/rows.csv
done
// 差异通常在 1.8–3.5 倍之间；把这个数代入净收益计算

2. 一个可以粗算的模型

量	影响
每秒写入行数	线性放大
索引数量	线性放大
索引键长度	影响单页条目数与树高
键的随机性	影响页分裂与缓存

第二项（键的随机性）可以用一个数感受：UUID 主键的插入点均匀分布在整个树中，而自增主键总在最后一页。前者让缓冲池命中率大幅下降，实测差异常在 2–5 倍。

粗算方式是"每行写入的索引操作数 ≈ 索引数量"，所以五个索引意味着每行数据要维护五处。这个数字不精确，但它足以判断"该不该继续加"。

// 粗算：写入放大与索引数量的关系
行数 10,000/次导入
索引 2 → 约 2 万次索引维护
索引 7 → 约 7 万次索引维护（3.5 倍）
// 实测里写入耗时的增长通常略低于线性（有批量优化），但趋势一致

3. 加索引之前该问的四个问题

问题	用途
这条查询每天跑多少次	判断读的收益
不加索引能不能改写法解决	避免不必要索引
现有索引能否覆盖	用复合索引替代新增
这张表的写入频率	判断写的代价

第四问（写入频率）最难得到准确答案，因为写入量往往分散在多个入口（用户操作、定时任务、批量导入、数据同步）。取一个"总行数/天"的近似值即可，但必须把批量任务单独算账。

第三项最容易被跳过：很多"需要新索引"的情况其实是"现有索引的顺序不对"，调整复合索引的列顺序就能覆盖，而不增加索引数量。

-- 已有索引 (a)
-- 需求：WHERE a = ? AND b = ?
-- 方案一：新增 (a, b)  → 索引数 +1
-- 方案二：把 (a) 改成 (a, b) → 索引数不变，且覆盖新查询
ALTER TABLE t DROP INDEX idx_a, ADD INDEX idx_a_b (a, b);
-- 前提：确认没有查询依赖 (a) 单独存在的顺序特性（通常没有）

4. 冗余索引的识别

冗余形态	判定
前缀重复	(a) 是 (a,b) 的前缀
完全重复	列与顺序都相同
低选择性	区分度极低的列做首列

冗余索引的另一种形态是**"看起来不同"**：(a, b) 与 (b, a) 不是冗余，但多数查询只按 a 过滤时，后者几乎无用。判据是"实际查询的过滤前缀"，不是索引的列集合。

第一项是"最容易被忽略的浪费"：(a) 与 (a,b) 同时存在时，(a) 在多数查询里是多余的，但删除前要确认没有只用到 (a) 的排序需求。

-- MySQL 8：查冗余索引（前缀重复）
SELECT * FROM sys.schema_redundant_indexes;
-- 输出 redundant_index_name 与 dominant_index_name，前者可评估删除

5. 写入与读取的取舍怎么算

判断	依据
读的次数 × 单次节省	读侧收益
写的次数 × 维护代价	写侧成本
净收益是否为正	决定

这个算法粗糙但有效：一条每天跑 100 次的查询省 200ms，一天省 5.5 秒；如果索引让每次导入多花 3 分钟、每天导两次，就是净亏。

这个算法还有一层用法：当净收益接近零时，说明"改查询"比"加索引"更值得做。因为改查询的代价是一次性的工程投入，而索引的写入成本是永久的。

判断的关键是两边用同一个单位（时间）来表达，而不是"读变快了"与"写变慢了"这样不可比的感受。

// 用同一单位算净收益
读侧：100 次/天 × 0.2s = 20s/天
写侧：2 次/天 × 60s 新增 = 120s/天
净：-100s/天 → 不该加，应改写查询
// 若读侧是 10,000 次/天：+2000s/天 - 120s → 明显该加

6. 排错：五种"加了索引反而慢"

现象	成因	处理方向
写入变慢	索引维护成本	评估冗余索引
读也没变快	索引未被选中	查执行计划与选择性
批量导入变慢很多	每行都维护索引	导入前禁用/后重建
磁盘占用暴涨	索引体积	评估键长度
唯一索引冲突变多	并发插入	评估业务约束是否必要

第三项是可操作的手段：大批量导入时先删索引、导入后重建，代价从"每行维护"降到"一次排序构建"，通常快五到十倍。

7. 边界：什么情况不必算这笔账

场景	为什么	做法
只读表/日志归档表	无写入成本	索引随便加
写入量极低（每天几百行）	成本可忽略	按读需求加
已有覆盖索引	无需新增	复用
临时分析用	一次性	不建永久索引

还有一条与"索引数量上限"有关的边界：单表索引数超过五个后，边际收益迅速下降。因为第五个索引服务的查询通常已经是低频查询，而它给所有写入增加的成本是持续且无差别的。

判断口径一句话：这张表每天写入多少次，每次多花的时间乘以次数，能不能被读侧的收益盖过。盖不过就不加，去改查询。

8. 自测

写入成本的三个来源里，哪一个与索引键的选择强相关？用自增主键与 UUID 的差别是什么？
为什么"索引数量"可以粗略乘以行数来估算写入开销？
加索引前该问的四个问题里，哪一个最常被跳过？"调整列顺序"为什么能避免新增索引？
冗余索引最常见的形态是什么？删除前必须确认什么？
为什么读写取舍要用同一个单位来表达？举一个净收益为负的例子。
大批量导入时"先删索引后重建"为什么更快？判断"该不该算这笔账"的那个问题是什么？