MySQL高并发写入索引过多导致性能抖动解决方案

索引过多，是高并发写入场景下极易踩中的性能陷阱——而且其影响常常被低估。每增加一个索引，INSERT、UPDATE、DELETE 操作就必须额外维护一棵 B+ 树，写放大效应直接翻倍。这不仅仅是“变慢一点”的问题，而是会引发锁等待时间延长、CPU 刷脏页压力飙升，以及 Innodb_buffer_pool_write_requests 与 Innodb_data_writes 比值严重失衡的连锁反应。

如何诊断索引拖慢写入性能

不要凭感觉猜测，用数据说话：

• 执行 SHOW TABLE STATUS LIKE 'your_table'，重点关注 Index_length。如果该值接近甚至超过 Data_length，说明索引开销已与实际数据持平，这是非常危险的信号。
• 对比 Innodb_rows_inserted 与 Innodb_pages_written 的比值。正常范围应在 10 到 50 之间；若跌至 2 到 5 区间，则大概率是索引在频繁刷盘。
• 使用 EXPLAIN FORMAT=JSON 检查写操作是否触发了大量二级索引更新（观察 used_columns 中是否出现非主键字段）。
• 查看 SHOW ENGINE INNODB STATUS\G 中的 LOG 段：如果 log i/o's done 的频率远高于事务提交频率，说明 redo 日志正在为索引更新“擦屁股”。

哪些索引删除最安全、见效最快

优先砍掉那些对写入毫无帮助、仅服务于低频查询的索引：

• 单列 WHERE 索引，但该列的 SELECT 查询占比（通过 performance_schema.events_statements_summary_by_digest 查询）极低。
• 前缀过长的 VARCHAR 索引，例如 INDEX (content(255))——实际查询几乎用不到这么长的前缀。
• 重复索引：已有 (user_id, status)，又单独建了 (user_id)，后者完全可以安全删除。
• 未被任何 EXPLAIN 命中的索引（使用 sys.schema_unused_indexes 视图，MySQL 8.0+）。
• 写入频繁但查询从不带 ORDER BY 或 GROUP BY 的字段索引，例如单独为 updated_at 建立的索引。

改写索引结构以降低写入成本

保留必要的索引，但让它们变得更“轻量”：

• 将宽索引精简为覆盖索引的最小集：例如原 INDEX (a,b,c,d,e) 仅用于 WHERE a=? AND b=? ORDER BY c，可优化为 INDEX (a,b,c)。
• 用 TINYINT 或 ENUM 替代 VARCHAR 作为状态字段索引（如 status ENUM('pending','done','failed')），索引体积更小，比较速度更快。
• 避免在索引列上使用函数转换：WHERE YEAR(created_at) = 2024 会导致索引失效，应改为 WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'。
• 对高频写入的表，考虑将部分查询索引迁移到 GENERATED COLUMN + STORED 上，再建立索引——从而减少主表 DML 对索引的直接冲击。

为什么删完索引后仍然抖动？盯住这几个隐藏点

索引删除了，但抖动依然存在，往往卡在以下细节上：

• innodb_flush_neighbors = 1 在 SSD 上未关闭：即使索引少了，该参数仍会强制批量刷相邻页，造成 I/O 放大。务必执行 SELECT @@innodb_flush_neighbors 确认值为 0。
• 主键不是自增整数：使用 UUID 或 CHAR(36) 会导致页分裂持续发生，新插入时总要调整 B+ 树结构——这种抖动与索引数量无关，但表现相似。
• 表上存在 FULLTEXT 索引：它不走普通 B+ 树，而是独立的倒排索引，每次写入都需同步更新，且无法用常规方式禁用。
• innodb_log_file_size 太小（例如默认 48MB）：即使索引删了，日志切换过于频繁，照样会触发 checkpoint 抖动。建议设置为 1G～2G。