MySQL：一张表到底建几个索引算多，单表超过5个索引对写入的拖累到底有多大，有没有实测数据？

一、问题的重新定义：为什么“5个索引”不是一个可靠阈值

1.1 经验法则的来源与失效条件

在MySQL运维圈里，“单表索引不超过5个”几乎与“单表不超过2000万行”并列，成为口口相传的容量红线。追溯其来源，这类经验大多形成于机械硬盘（HDD）时代、InnoDB默认配置、以OLTP短事务为主的场景：当时一次二级索引维护往往意味着一次真实的随机寻道，代价以毫秒计，索引数量与写入延迟近似线性相关。本文评述：这条法则在SSD/NVMe普及、change buffer与redo日志组提交机制成熟的今天，其定量基础已经发生根本变化，继续把它当作硬阈值使用，容易导致两个方向的误判——要么在宽表上过度保守，牺牲读性能；要么在高频小表上放任索引膨胀，直到写入瓶颈才被动治理。

更关键的是，“索引数量”本身是一个信息量很低的变量。一个建在TINYINT列上的普通索引，与一个建在VARCHAR(255)上的唯一索引，其维护成本可能相差一个数量级。因此，本文主张把问题从“建几个”转换为“索引写入预算（index write budget）”：即单位时间内，所有二级索引维护所消耗的页写次数、日志字节数与I/O等待时间之和，是否超出了存储介质的可持续写入能力。

1.2 三个必须区分的计量口径

讨论“拖累多大”之前，必须先固定口径，否则不同来源的数据无法比较。笔者认为至少应区分以下三层：

很多网络文章给出的“索引多了写入慢X倍”，往往只报告吞吐口径，且未说明硬件、页大小、并发度与数据分布，这类数据只能作为方向性参考。本文后续给出的区间同样需要放在明确条件下理解，这也是第3节专门讨论压测方法论的原因。

二、写入路径解剖：一次INSERT到底触发了什么

2.1 聚簇索引与二级索引的维护差异

InnoDB以主键组织聚簇索引（clustered index），表数据本身即B+树的叶子节点。二级索引（secondary index）则是独立的B+树，其叶子节点存储“索引列值 + 主键值”。这意味着一次插入需要：向聚簇索引插入完整行；向每个二级索引插入一条“索引键+主键”的条目。若主键非自增（如UUID），聚簇索引的插入点随机分布，页分裂概率显著上升；而二级索引的插入点由其索引列决定，若该列取值随机（如邮箱、哈希值），同样会引发随机页写。

这里存在一个常被忽视的放大链条：二级索引条目包含主键值，因此主键越宽，所有二级索引的条目也越宽。一个使用CHAR(36) UUID主键的表，每个二级索引条目至少多出36字节，在索引数量为5时，这部分额外写入量相当于多维护了近一个中等索引。本文评述：讨论索引数量时若不控制主键宽度，结论会系统性偏离。

2.2 change buffer：随机写的第一道缓冲

对于非唯一二级索引，当目标页不在buffer pool中时，InnoDB会把插入操作记录到change buffer，待该页被读入时再合并（merge）。这一机制把大量随机写转化为顺序的change buffer记录写，显著降低了对I/O的即时冲击。但需注意三个边界：其一，唯一索引无法使用change buffer，因为必须立即读页判重；其二，change buffer占用buffer pool空间（由innodb_change_buffer_max_size控制，默认25%）；其三，合并阶段仍会产生真实的随机页写，只是被推迟和批量化。

工程含义：在写入密集且索引列取值随机的场景中，普通二级索引的“瞬时延迟”可能被change buffer掩盖，但“持续写放大”并未消失。监控上应同时观察 Innodb_buffer_pool_pages_made_young 之外的 change buffer 合并速率与 Innodb_data_pending_fsyncs，否则容易误判索引无害。

2.3 redo与binlog的二次放大

每一次索引页修改都会产生redo记录，事务提交时还需写入binlog（ROW格式下记录行变更）。索引越多，单事务产生的redo量越大，组提交（group commit）能摊薄fsync次数，但无法减少字节量。在SSD上，写入带宽与写放大系数（WA）共同决定设备寿命；在云盘上，IOPS与吞吐配额则直接体现为成本。因此，“索引拖累写入”不仅是延迟问题，也是存储成本与设备寿命问题。

关于InnoDB存储结构与change buffer的权威说明，可参考MySQL官方手册的InnoDB Change Buffer 文档与物理结构章节；对B+树分裂的可视化理解，可参考B+树可视化演示。

三、实测方法论：如何得到可信的索引写入代价数据

3.1 控制变量与实验设计

要回答“5个索引拖累多大”，必须让“索引数量”成为唯一自变量。本文建议的最小实验矩阵如下：固定表结构（除索引外）、固定数据生成分布、固定并发度与事务大小、固定MySQL版本与关键参数（innodb_buffer_pool_size、innodb_flush_log_at_trx_commit、sync_binlog），仅改变二级索引个数（0、1、2、3、5、8），每组重复至少3轮并取中位数。

数据分布建议设置两组对照：一组索引列取单调递增值（顺序插入，页分裂少），一组取随机值（模拟邮箱、订单号哈希）。这两组结果的差异，往往比“索引从3个到5个”的差异更大，也更能说明问题的本质。

3.2 可复现的压测步骤

以下步骤基于sysbench 1.0与MySQL 8.0，读者可在测试环境复现。sysbench的安装与用法见sysbench官方仓库，参数细节可参考MySQL 8.0参考手册。

# 1. 准备基础表（仅主键）
sysbench oltp_write_only \
  --mysql-host=127.0.0.1 --mysql-db=idxbench \
  --tables=1 --table-size=5000000 \
  --db-driver=mysql prepare

# 2. 基线：无二级索引，写入60秒
sysbench oltp_write_only \
  --mysql-host=127.0.0.1 --mysql-db=idxbench \
  --tables=1 --table-size=5000000 \
  --threads=16 --time=60 --report-interval=10 run

# 3. 依次添加二级索引后重复步骤2
# ALTER TABLE sbtest1 ADD INDEX idx_c (c);
# ALTER TABLE sbtest1 ADD INDEX idx_k (k);
# ... 每次只增加一个，避免DDL本身干扰

# 4. 采集服务端指标（另开窗口）
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_flushed';"
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -i "merge"

需要强调：sysbench的oltp_write_only包含UPDATE与DELETE，若只想观察纯插入，应改用自建脚本或--oltp-skip-trx=off配合自定义Lua。本文评述：很多二手数据未说明写入类型构成，导致“索引拖累”被UPDATE的读放大混入，结论不可直接套用。

3.3 数据预处理与归一化

原始TPS受预热、checkpoint抖动影响较大，建议丢弃前10秒数据，取稳定区间；同时用“每插入一行产生的redo字节数”做归一化，公式为：

WA_idx = (RedoBytes_n索引 − RedoBytes_0索引) / (RowsInserted × n)

该指标剔除主键维护的公共成本，近似反映“每个二级索引每行”的日志放大，便于跨硬件比较。若读者使用云数据库，可参考各厂商公开的RDS MySQL文档中关于写放大的说明，但需注意其测试条件与自建环境不同。

四、数据与解读：5个索引之后的真实拖累有多大

4.1 公开基准与模拟数据的量级结论

需要坦白说明：截至目前，并不存在一个被广泛引用的、专门针对“二级索引数量—写入吞吐”的标准化基准。可获得的证据主要来自三类：数据库厂商的性能白皮书、学术论文中对B+树写放大的建模、以及社区可复现的压测报告。下表为笔者基于公开资料与模拟实验整合的量级区间，标注为“模拟/整合数据”，不代表任何单一来源的精确测量。

上表条件假设：NVMe SSD、16KB页、buffer pool可容纳热数据、16并发、自增主键、索引列宽约20–40字节。本文评述：区间之所以宽，是因为change buffer命中率与checkpoint节奏会显著改变结果；在buffer pool不足、热数据无法驻留时，5个索引的降幅可能接近区间下限甚至更低。因此，工程上不应把“5个”当作悬崖，而应把“每索引约6%–12%吞吐损失”作为规划系数。

4.2 为什么唯一索引更“贵”

唯一索引在插入时必须确认键值不重复，这要求目标页在buffer pool中，否则触发同步读。在随机分布下，这一读操作几乎无法被批量摊薄。学术上，B+树唯一约束的插入代价可建模为“一次点查 + 一次页修改”，而普通索引在change buffer命中时可近似为“一次内存追加”。这解释了为何在包含唯一索引的测试组中，吞吐下降往往比同等数量的普通索引更陡。笔者认为，在写入密集表上，应优先评估唯一约束是否可由应用层幂等或分布式锁替代，而非默认交给数据库。

4.3 更新与删除的放大更隐蔽

UPDATE若修改了被索引的列，InnoDB需在对应二级索引上执行“删除旧条目+插入新条目”，本质是两次B+树操作；若未修改索引列，则二级索引不产生维护。DELETE则在所有索引上标记删除，并由purge线程异步清理。这意味着：索引对写入的拖累高度依赖于更新模式。一个频繁更新状态列的表，若该列被索引，其代价可能远超静态列上的多个索引。监控上可通过Innodb_rows_updated与二级索引页变更速率交叉验证。

五、边界条件：哪些情况下索引代价被高估或低估

5.1 被高估的场景

当写入以批量导入为主、且索引列取值单调递增时，B+树插入集中在最右页，页分裂极少，change buffer与顺序写共同作用，索引数量对吞吐的影响会明显小于上表区间。此外，若表本身很窄、行数少、全量驻留内存，索引维护几乎只消耗CPU，I/O代价可忽略。此时“5个索引”的告警意义有限。

5.2 被低估的场景

相反，以下几种情况会让索引代价被严重低估：其一，主键为随机UUID，导致聚簇索引与所有二级索引同时随机化；其二，索引列宽大（如长字符串、JSON提取列），单条目字节数高；其三，buffer pool远小于热数据，change buffer频繁合并；其四，使用云盘且IOPS配额较低，随机写被限流。本文评述：在这些条件下，5个索引造成的P99延迟恶化可能达到数倍，而平均吞吐只下降三成，尾延迟才是业务真正感知到的痛点。

操作提示：评估索引代价时，至少同时看三个数——平均TPS、P99提交延迟、以及单位时间redo字节数。只看平均TPS，容易在“平均值尚可、尾部已崩”的状态下继续加索引。

六、索引预算模型与治理操作路径

6.1 一个可落地的预算公式

笔者建议用“索引写入预算”替代“索引个数上限”。设表T的峰值插入速率为R行/秒，第i个二级索引的平均条目字节为B_i，页利用率为u，则单位时间索引页写入量近似为：

W = Σ_i (R × B_i / (u × PageSize))

将该值与存储介质的可持续随机写IOPS、以及云盘的写入配额比较，即可得到“还能加几个索引”的定量答案。该模型忽略了页分裂的瞬时尖峰，因此应保留30%以上余量。本文评述：公式的价值不在于精确，而在于把讨论从“数个数”拉回到“算字节”，让索引评审有共同语言。

6.2 冗余索引识别步骤

治理的第一步往往不是删索引，而是找出可合并的索引。可按以下顺序执行：

1. 用SHOW INDEX FROM t导出所有索引的列顺序与前缀长度；
2. 识别“最左前缀包含”关系：若索引A的列序列是索引B的前缀，则A通常可被B覆盖（需结合唯一性判断）；
3. 结合performance_schema.table_io_waits_summary_by_index_usage确认低使用率索引；
4. 在影子库执行删除，观察慢查询与执行计划变化，再灰度上线。

关于索引使用统计的采集方法，可参考MySQL手册的索引使用统计表说明。需要提醒：该表在实例重启后清零，长期观察需定期采样落库。

6.3 面向写入的索引设计原则

综合上述机制，笔者归纳几条可操作原则：优先使用自增或趋势递增主键以降低聚簇索引分裂；控制主键宽度，避免其成为所有二级索引的“隐形负载”；对写入密集表，谨慎添加唯一索引，能用应用层幂等解决的不下沉到数据库；用联合索引覆盖多个查询，减少索引总数；对宽列索引考虑前缀索引或生成列，压缩条目字节。每一条都对应第2节的某个放大环节，而非泛泛而谈的“少建索引”。

七、前沿与展望：HTAP、LSM与索引自动化的冲击

7.1 存储引擎差异决定结论边界

本文的定量结论主要适用于InnoDB这类B+树、原地更新（in-place update）引擎。在LSM-tree引擎（如RocksDB、MyRocks）中，写入先进入内存表并顺序落盘，索引维护被转化为多层级合并（compaction），其“索引数量—写放大”曲线形状与B+树不同：短期内索引增多对前台写入影响较小，但compaction压力与空间放大在后期显现。因此，跨引擎套用“5个索引”经验是危险的。本文评述：讨论索引治理时，必须先声明存储引擎与更新模式，否则数据不可比。

7.2 自动化索引推荐与“索引即服务”

近年来，主流云厂商与开源社区都在推进索引推荐（index advisor）能力，其基本思路是从慢查询日志与执行计划中提取候选索引，再用代价模型或机器学习排序。这类工具能缓解“凭感觉建索引”，但其代价模型通常以读性能为主目标，对写入放大的建模仍不充分。笔者认为，下一代索引治理的关键，是把写入预算作为约束条件纳入推荐器，形成“读收益—写成本”的双目标优化，而非单方面追求查询加速。

此外，HTAP架构通过列存副本承接分析查询，客观上减少了对行存表“为分析而建索引”的需求，这对控制索引数量是结构性利好。但列存同步本身带来额外写入链路，需整体评估。对希望深入了解索引内部结构的读者，可参考Use The Index, Luke这一系统性教程，以及InnoDB索引内部结构相关视频。

八、结论

回到最初的问题：一张表建几个索引算多？本文的答案是——不存在普适的数字，只有可计算的预算。在典型的NVMe、InnoDB、随机写入场景下，每增加一个中等宽度二级索引，纯插入吞吐约损失6%–12%，5个索引累计降幅约30%–45%，唯一索引、宽列与随机主键会显著加剧这一趋势；而在顺序写入、内存驻留、批量导入场景下，同样的索引数量代价要小得多。因此，“5个”既不是安全线，也不是悬崖，它只是经验法则在特定历史条件下的一个粗略投影。

更具操作性的做法是：建立以写放大为核心的监控口径，用索引写入预算模型评估新增索引，定期清理冗余与低使用率索引，并在存储引擎与架构演进中持续校准结论。索引是读性能的杠杆，也是写路径的税；治理的本质，是在两者之间做有数据支撑的权衡，而不是背诵一个数字。

主要参考文献