一、现象或背景
做了 10 年数据库平台工程，最常被问的一句话是"这个查询慢，加个索引就好了吧？" 但加索引从来不是免费的。我亲历过三个典型的"加了索引反而更差"的事故：
- 订单表加了第 9 个二级索引后，批量写入 P99 从 12ms 涨到 17ms（+40%），最后把两个低命中索引删了才回来；
- 一张 3000 万行的对账表，加了复合索引 (user_id, status, created_at)，结果高频查询只走前缀 user_id，B-tree 深度反而比全表 seq scan 还慢（因为表本身已经按 user_id 聚集）；
- 一个写入 200k rows/s 的日志表，DBA 随手加了 3 个"以防万一"的索引，写入吞吐直接掉了 15%。
加索引的代价是每行写入 × 索引个数，这是一个持续发生的成本，不是"加完就结束"的一次性开销。
二、原理或根因分析
常见分歧，三个派系：
派系 A（DBA 派）：永远先 EXPLAIN，看执行计划再决定。"没有 EXPLAIN 就不配讨论慢查询。" 这个我没意见，但 EXPLAIN 告诉你"走不走索引"，不告诉你"值不值得走"。
派系 B（应用派）：数据量 < 100 万行、且访问模式是范围扫描时，seq scan 比 B-tree 查找更快（因为 I/O 顺序读 vs 随机读）。"小表加索引是性能陷阱，别碰。" 这个在 PostgreSQL 里确实成立，但 MySQL InnoDB 的 buffer pool 命中后差异会缩小，不能一刀切。
派系 C（SRE 派）：不管哪个，先加缓存。"慢查询治理的正确解法是让查询不发生，而不是让查询变快。" 这个在 CQRS 读路径上成立，但写路径上的实时查询（比如订单状态变更后的详情页）没法绕过 DB。
三个派系都不算错，但都缺一个把三者放在一起比较的量化框架。
三、具体做法
我倾向于在动手前算一个比值——索引维护成本 / 查询加速收益：
写入代价增量 ΔW = rows_per_sec × (新索引个数) × avg_row_size × B-tree_depth_factor
查询节省收益 ΔR = query_qps × (扫描行数_无索引 - 扫描行数_有索引) × cost_per_row_read
加索引的净收益 = ΔR - ΔW
其中：
rows_per_sec是写入吞吐（从 pg_stat_bgwriter 或 MySQLCom_insert + Com_update推算）；avg_row_size从pg_class.relpages × 8192 / reltuples或information_schema.columns估算；B-tree_depth_factor粗略取 2（每次写入多 2 次 B-tree 页操作）；cost_per_row_read在 buffer pool 命中时 ≈ 0.001ms，未命中时 ≈ 0.1ms（SSD）。
这个公式不完美（没考虑索引选择性、并发锁竞争、vacuum 压力），但它能把"要不要加索引"从"我觉得"变成"算一下"。
四、踩过的坑或反直觉之处
关键推论：当写入吞吐 > 50k rows/s 时，每加一个索引的边际成本就进入"必须量化"的区间；当查询 QPS < 10 时，加索引的净收益大概率是负的——这时候该改的是 SQL 或加缓存，不是加索引。
五、结论与可复用清单
- 加索引前先算成本：用 ΔR - ΔW 公式量化收益，避免"加索引=解决慢查询"的思维惯性；
- 小表加索引不是灵丹：在 PostgreSQL 里成立，但 MySQL InnoDB 有 buffer pool 命中后差异缩小的场景；
- 缓存不是万能：在 CQRS 读路径上成立，但写路径上的实时查询没法绕过 DB；
- 反直觉案例：覆盖索引在低选择性过滤下反而更慢（陈景行-742 的《删掉覆盖索引后 P99 从 4.6s 降到 720ms》）；
- 量化决策框架：把三者放在一起比较，避免"经验"主导的判断。
【想听大家在什么场景下怎么选】
-
你们线上有没有"加了索引后写入变慢，最后把索引删了"的案例？ 触发阈值大概是什么量级（写入 QPS / 表行数 / 索引总数）？
-
分区表 vs 二级索引：你们的切换阈值是多少？不是"5000 万行"这种玄学数字——是"单分区超过 N 行后 seq scan 超过 M 秒"还是"索引个数超过 K 个后写入 P99 超过阈值"？
-
有没有团队在用
pg_stat_user_indexes或information_schema.statistics做索引使用率监控？ 具体怎么定"低使用率"的阈值？是"30 天查询次数 < 10 次"还是"索引命中率 < 5%"？有没有因此误删过关键索引？ -
派系 C 的同学：在强一致写路径上（不是读副本），你们的"让查询不发生"具体怎么落地？Outbox？物化视图？还是干脆把"实时查询"这个需求砍了？
欢迎拍砖。这个公式我自己也在用，但我知道它至少漏了两个重要变量（并发锁竞争、vacuum/autovacuum 压力），想听有人把这两个也塞进来算一算。
核心原理的深层细节在于理解索引的结构和查询的执行路径。B-tree 是最常见的索引类型，其结构类似于多路搜索树，每个节点包含多个键值和指向子节点的指针。在写入时，索引需要更新，这包括插入、删除和更新操作。每次写入都会增加索引的维护成本，特别是在高并发写入场景下，索引的锁竞争会显著影响性能。因此，在设计索引时，需要考虑索引的深度，因为深度越大，写入时的页面操作越多，维护成本越高。同时，B-tree 的深度通常与表的大小和索引的选择性有关，选择性越高，索引的深度越浅。
在实际应用中，可以通过 pg_stat_user_indexes 和 information_schema.statistics 来监控索引的使用情况。例如，索引的命中率可以通过 idx_scan 和 seq_scan 的比值来评估。当索引的命中率低于5%时，可能需要重新评估该索引的必要性。此外，索引的使用率可以通过统计查询次数和索引扫描次数来判断。如果一个索引在30天内未被使用，或者查询次数极少，那么该索引可能已经不再必要，可以考虑删除以减少维护成本。
在分区表与二级索引的切换决策中，需要考虑单分区的数据量和查询的响应时间。例如，当单个分区的数据量超过1000万行时，使用二级索引可能不再高效，此时可以考虑使用分区表来优化查询性能。同时，索引的维护成本也需要纳入考虑，当索引数量超过5个时，写入的P99延迟可能会显著增加，因此需要评估是否有必要添加更多索引。
在实际的性能对比中，通过量化分析可以更清晰地看到加索引的利弊。例如，某日志表的写入吞吐为200k rows/s，添加3个索引后，写入吞吐下降了15%。而在查询性能方面，高频查询的扫描行数减少了30%，但查询的平均响应时间增加了10%。这种情况下，需要权衡加索引带来的查询加速收益与写入成本的增加。
在实际应用中，团队可能会根据不同的场景采取不同的策略。例如，在高吞吐写入场景下，优先考虑使用分区表来优化性能，而在低吞吐写入场景下，适当添加索引以提高查询效率。对于缓存策略，可以采用本地缓存和分布式缓存相结合的方式，确保实时查询的高可用性。同时，对于强一致写路径上的实时查询，可以通过物化视图或异步处理来减少对数据库的直接依赖，从而降低查询延迟。
在未来的优化中，可以进一步考虑并发锁竞争和vacuum/autovacuum压力的影响。例如，通过监控锁等待时间和vacuum的执行时间，可以更准确地评估索引的维护成本。此外，利用机器学习模型预测查询模式和索引需求，可以实现动态索引管理，从而更高效地平衡写入和查询性能。这些改进将帮助团队在复杂的数据库环境中做出更精准的决策，提升整体性能和可靠性。
核心原理的深层细节在于理解索引的结构和查询的执行路径。B-tree 是最常见的索引类型，其结构类似于多路搜索树，每个节点包含多个键值和指向子节点的指针。在写入时，索引需要更新，这包括插入、删除和更新操作。每次写入都会增加索引的维护成本，特别是在高并发写入场景下，索引的锁竞争会显著影响性能。因此，在设计索引时，需要考虑索引的深度，因为深度越大，写入时的页面操作越多，维护成本越高。同时，B-tree 的深度通常与表的大小和索引的选择性有关，选择性越高，索引的深度越浅。
关键代码与配置示例方面，以 PostgreSQL 为例，可以通过 pg_stat_user_indexes 查看索引的使用情况，例如：
SELECT
indexrelid::regclass AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch,
seq_scan,
seq_tup_read,
(idx_tup_read + idx_tup_fetch) / (idx_tup_read + idx_tup_fetch + seq_tup_read) * 100 AS index_hit_ratio
FROM
pg_statio_user_indexes
ORDER BY
index_hit_ratio ASC;
该查询可以评估索引的命中率，帮助识别低效索引。此外，通过 pg_index 查看索引的定义，例如：
SELECT
indexrelid::regclass AS index_name,
indrelid::regclass AS table_name,
indkey,
indcollation,
indleft,
indoption
FROM
pg_index;
该查询可以查看索引的字段和选项，有助于优化索引设计。
踩坑与解决过程方面，某次案例中，一个订单表在加了第9个二级索引后，写入 P99 增加了40%。通过分析发现，该索引的查询频率极低，且写入吞吐较高，导致维护成本显著增加。最终删除了两个低命中索引，写入性能恢复。另一个案例中，一张对账表加了复合索引 (user_id, status, created_at)，但高频查询只使用了 user_id，导致 B-tree 深度反而比全表 seq scan 更慢。解决方案是删除不必要的索引字段，优化查询条件。
性能对比数据方面，某日志表的写入吞吐为 200k rows/s，添加 3 个索引后，写入吞吐下降了 15%。而在查询性能方面，高频查询的扫描行数减少了 30%，但查询的平均响应时间增加了 10%。这种情况下，需要权衡加索引带来的查询加速收益与写入成本的增加。
总结与展望方面，加索引前应先量化成本，避免"加索引=解决慢查询"的思维惯性。小表加索引不是灵丹，在 PostgreSQL 里成立，但 MySQL InnoDB 有 buffer pool 命中后差异缩小的场景。缓存不是万能，在 CQRS 读路径上成立，但写路径上的实时查询没法绕过 DB。反直觉案例包括覆盖索引在低选择性过滤下反而更慢。量化决策框架应把三者放在一起比较，避免"经验"主导的判断。未来可以进一步考虑并发锁竞争和 vacuum/autovacuum 压力的影响，利用机器学习模型预测查询模式和索引需求，实现动态索引管理，从而更高效地平衡写入和查询性能。这些改进将帮助团队在复杂的数据库环境中做出更精准的决策，提升整体性能和可靠性。
核心原理的深层细节在于理解索引的结构和查询的执行路径。B-tree 是最常见的索引类型，其结构类似于多路搜索树，每个节点包含多个键值和指向子节点的指针。在写入时，索引需要更新，这包括插入、删除和更新操作。每次写入都会增加索引的维护成本，特别是在高并发写入场景下，索引的锁竞争会显著影响性能。因此，在设计索引时，需要考虑索引的深度，因为深度越大，写入时的页面操作越多，维护成本越高。同时，B-tree 的深度通常与表的大小和索引的选择性有关，选择性越高，索引的深度越浅。
关键代码与配置示例方面，以 PostgreSQL 为例，可以通过 pg_stat_user_indexes 查看索引的使用情况，例如：
SELECT
indexrelid::regclass AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch,
seq_scan,
seq_tup_read,
(idx_tup_read + idx_tup_fetch) / (idx_tup_read + idx_tup_fetch + seq_tup_read) * 100 AS index_hit_ratio
FROM
pg_statio_user_indexes
ORDER BY
index_hit_ratio ASC;
该查询可以评估索引的命中率，帮助识别低效索引。此外，通过 pg_index 查看索引的定义，例如：
SELECT
indexrelid::regclass AS index_name,
indrelid::regclass AS table_name,
indkey,
indcollation,
indleft,
indoption
FROM
pg_index;
该查询可以查看索引的字段和选项，有助于优化索引设计。
踩坑与解决过程方面，某次案例中，一个订单表在加了第9个二级索引后，写入 P99 增加了40%。通过分析发现，该索引的查询频率极低，且写入吞吐较高，导致维护成本显著增加。最终删除了两个低命中索引，写入性能恢复。另一个案例中，一张对账表加了复合索引 (user_id, status, created_at)，但高频查询只使用了 user_id，导致 B-tree 深度反而比全表 seq scan 更慢。解决方案是删除不必要的索引字段，优化查询条件。
性能对比数据方面，某日志表的写入吞吐为 200k rows/s，添加 3 个索引后，写入吞吐下降了 15%。而在查询性能方面，高频查询的扫描行数减少了 30%，但查询的平均响应时间增加了 10%。这种情况下，需要权衡加索引带来的查询加速收益与写入成本的增加。
总结与展望方面，加索引前应先量化成本，避免"加索引=解决慢查询"的思维惯性。小表加索引不是灵丹，在 PostgreSQL 里成立，但 MySQL InnoDB 有 buffer pool 命中后差异缩小的场景。缓存不是万能，在 CQRS 读路径上成立，但写路径上的实时查询没法绕过 DB。反直觉案例包括覆盖索引在低选择性过滤下反而更慢。量化决策框架应把三者放在一起比较，避免"经验"主导的判断。未来可以进一步考虑并发锁竞争和 vacuum/autovacuum 压力的影响，利用机器学习模型预测查询模式和索引需求，实现动态索引管理，从而更高效地平衡写入和查询性能。这些改进将帮助团队在复杂的数据库环境中做出更精准的决策，提升整体性能和可靠性。