2 亿行大表上 11 个二级索引：用 pt-query-digest 量化识别死索引，删 3 个后写 QPS +26%

结论先行：「这个索引没人用」不能凭感觉说，也不能凭 EXPLAIN 说——EXPLAIN 只告诉你「这条 SQL 用了哪个索引」，不告诉你「过去 7 天哪个索引从未被用过」。InnoDB 至今没有任何「索引级使用次数」的内建统计，必须从 SQL 侧反推。这篇文章给一套可复现的四层漏斗。

一、现象与背景：220GB 大表上挂着 14.2GB 的「可能没人用」的索引

上个月给一套电商结算系统做写路径治理。核心表 t_settle_bill（结算账单表）：

• 规模：2.1 亿行、单表 220GB、InnoDB、MySQL 8.0.36、16C64G 主从架构
• 索引：聚簇 + 11 个二级索引，合计 14.2GB（SHOW INDEX 加总 Index_length）
• 写入：结算主链路 INSERT ~1800 QPS + 对账 UPDATE ~2400 QPS，单机写 P99 = 18ms
• 业务方反馈：「最近几个索引是历史版本加的，估计没人用了，能删吗？」

直接删？不行。误删一个被定时任务用 3 次/天的索引，第二天就是 P1。不删？每写一行就要维护 11 棵 B+ 树，磁盘 14.2GB 常驻 buffer pool 里挤占热数据。

最终结果：删 3 个死索引，释放 6.4GB、写 QPS 从 4200 提到 5300（+26%）、写 P99 从 18ms 降到 12ms。下面是怎么把这 3 个「找出来」并「证明能删」的。

二、原理：为什么 InnoDB 不告诉你「这个索引用了多少次」

先把机制讲清楚，避免走弯路：

1. SHOW INDEX 的 Cardinality 是基数估计（InnoDB 采样 innodb_stats_persistent_sample_pages=20 页算出来的），跟「使用次数」完全无关。
2. performance_schema.table_io_waits_summary_by_table 只到表粒度，COUNT_READ / COUNT_WRITE 是整张表的读写事件数，不区分走了哪个索引。
3. performance_schema.table_accesses、index_statistics（8.0 新增的 information_schema.INDEX_STATISTICS 也没有使用次数字段）——InnoDB 的 B+ 树访问路径上没有「计数器」埋点，因为每个二级索引的每一次页访问都要多一次原子自增，代价在热路径上不划算。
4. EXPLAIN 是「这条 SQL 现在会怎么走」，不是「过去 N 天被用了几次」——把 EXPLAIN 当使用度统计是新手最常见的误区。

所以唯一可靠的路径是：从 SQL 侧反推——把一段时间内的 SQL 全量（或至少慢日志 + 关键日志）解析出来，按「列 → 索引」做映射，看哪些列从未出现在任何 WHERE / JOIN / ORDER BY / GROUP BY 里。

三、具体做法：四层漏斗 + 一条灰度验证

第 0 层（前置）：确认数据窗口够长

SHOW GLOBAL STATUS LIKE 'Uptime';

performance_schema 的 counter 实例重启即清零。Uptime < 7 天的实例，下面所有基于「计数为 0」的判断都不可信。我们的实例 Uptime = 23 天，OK。

第 1 层：performance_schema 找「高频写表」

SELECT OBJECT_SCHEMA, OBJECT_NAME,
       COUNT_READ, COUNT_WRITE,
       SUM_TIMER_WAIT/1000000000 AS sum_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'settle'
ORDER BY COUNT_WRITE DESC
LIMIT 10;

t_settle_bill 的 COUNT_WRITE = 8.4 亿、COUNT_READ = 2.1 亿——写远大于读，典型的写路径热点表，死索引在这里的代价最大（每次 INSERT/UPDATE 都要维护所有二级索引）。

第 2 层：pt-query-digest 解析 7 天日志，按列聚合

开启 general_log（或走 ProxySQL 的 stats）7 天，然后：

pt-query-digest \
  --review /var/lib/mysql/general_20260901.log \
  --output-type text \
  --limit 5000 \
  > digest.txt

digest.txt 里每个指纹（fingerprint）都保留了 WHERE 子句的列名清单。再人工/脚本提取「每个索引的列是否出现在任何指纹的 WHERE/JOIN/ORDER BY/GROUP BY 里」——这一步 11 个索引 × 7 天日志，用 grep -E + awk 半小时能跑完。

我们得到的结论：

| 索引 | 列 | 7 天指纹命中 | |---|---|---| | idx_merchant_time | merchant_id, created_at | 412 次指纹 | | idx_bill_no | bill_no | 87 次指纹 | | idx_status | status | 0 | | idx_old_partner | partner_id_v1 | 0 | | idx_remark | remark | 0 | | …（其余 6 个） | | 均 ≥ 3 次 |

第 3 层：交叉 SHOW INDEX 确认「索引只建在那一列上」

SHOW INDEX FROM t_settle_bill WHERE Key_name IN ('idx_status','idx_old_partner','idx_remark');

三个候选索引都是单列索引、非唯一、非外键——满足可删的硬条件。如果是复合索引，需要确认该列不在任何复合索引的「有效前缀」里（remark 这种字段经常是 idx_partner_remark 的第二列，删了不影响）。

第 4 层：pt-online-schema-change 灰度删 24h

pt-online-schema-change \
  --alter "DROP INDEX idx_status, DROP INDEX idx_old_partner, DROP INDEX idx_remark" \
  --no-drop-old-table \
  --chunk-size=500 \
  --sleep=0.05 \
  D=settle,t=t_settle_bill,h=<host>,u=repl,p=***

关键点：

• --no-drop-old-table：删完先保留影子表 24h，期间盯 performance_schema 的写 QPS / P99 / Threads_running，以及业务告警群。
• --chunk-size=500 --sleep=0.05：2.1 亿行表一次搬 500 行 + 睡 50ms，主从延迟峰值压在 1.2s 以内（默认 chunk 太大，我们实测能拉到 8s+）。
• 24h 无告警 → 手动 DROP TABLE _t_settle_bill_gho → 再跑 ANALYZE TABLE t_settle_bill 刷新统计信息。

收益量化（删前 vs 删后，同一压测脚本）

| 指标 | 删前 | 删后 | 变化 | |---|---|---|---| | 写 QPS（INSERT 1800 + UPDATE 2400 混合） | 4200 | 5300 | +26% | | 写 P99 | 18ms | 12ms | -33% | | 二级索引总大小 | 14.2GB | 7.8GB | 释放 6.4GB | | buffer pool 命中率 | 98.2% | 99.1% | +0.9pp |

buffer pool 命中率提升是「隐性收益」：6.4GB 死索引页不再挤占 young list，热数据的存活率上来了。

四、踩过的坑 & 反直觉之处

1. 重启即清零：有一次我们在一个 Uptime = 2 天的实例上跑这套流程，把 2 个「看起来 0 命中」的索引删了，第二天就被定时任务打爆。performance_schema 的 counter 不是累积值，Uptime < 7 天一律不判死。
2. 慢日志 ≠ 全量日志：pt-query-digest 只喂慢日志会漏掉「P99 = 3ms 但 QPS = 5000」的高频快查询——这种查询不写慢日志，但索引被用得最多。必须用 general_log 或代理层日志。
3. 唯一索引不能直接删：idx_bill_no 是 UNIQUE，业务上有「账单号幂等」的语义依赖，即使 7 天没查到也不能删——要改成「应用层幂等表 + 唯一约束保留」才能省。
4. ALGORITHM=INPLACE 不省维护成本：删索引本身是 INPLACE 的（不重建表），但 pt-online-schema-change 的影子表同步阶段每个 chunk 都要在新表上建索引，2.1 亿行搬了 11 小时。想快就选业务低峰 + 大 chunk（--chunk-size=5000），但主从延迟要自己盯。
5. 删完必须 ANALYZE TABLE：InnoDB 的 innodb_stats_persistent 不会自动重算，不 ANALYZE 的话优化器还会按「11 个索引」的成本模型做选择，可能选错。
6. 反直觉：死索引不只是「占磁盘」，它的主要代价是写放大 + buffer pool 挤占。我们这次删 3 个索引，写 QPS 提升 26% 比磁盘释放 6.4GB 更有价值——因为磁盘还能加，写路径的延迟砍不下来。

五、结论与可复用清单

核心判断：InnoDB 没有「索引使用度」内建统计，必须从 SQL 侧反推。EXPLAIN、SHOW INDEX、performance_schema.table_io_waits 都不能直接回答「这个索引过去 7 天被用了几次」。

可复用清单（按顺序执行）：

• [ ] SHOW GLOBAL STATUS LIKE 'Uptime' ≥ 7 天
• [ ] performance_schema.table_io_waits_summary_by_table 找 COUNT_WRITE Top10 表
• [ ] 开 general_log 或走 ProxySQL 7 天
• [ ] pt-query-digest --review 解析，按列聚合指纹
• [ ] SHOW INDEX 交叉：候选索引 = 单列 / 非唯一 / 非外键 / 列 0 命中
• [ ] pt-online-schema-change --no-drop-old-table 灰度 24h
• [ ] 盯 Threads_running / 主从延迟 / 业务告警
• [ ] 无异常 → DROP TABLE _xxx_gho + ANALYZE TABLE
• [ ] 压测对比：写 QPS / P99 / buffer pool 命中率

经验值：高频写表上每删一个死二级索引，写吞吐提升 15%~30%（我们这次 3 个 = +26%）；buffer pool 命中率提升 0.5~1pp。这个数字区间来自我们 3 套系统的实测，可作为下次治理的 baseline。

一句话：死索引治理不是「删几个索引」，而是「用 7 天 SQL 日志证明它们死了」+「用 24h 灰度证明删了没出事」。两步都过了，才能动 DROP INDEX。