在数据库优化的讨论中，索引几乎总是与“提升查询速度”画上等号，但索引是一把双刃剑：它在加速读取的同时，会显著拖慢写入操作。一张表上每多一个索引，每次INSERT、UPDATE、DELETE都需要额外维护一棵B+树。本文将从底层机制出发，详细分析MySQL索引为什么会影响插入更新性能，并给出系统性的写操作优化方法。
一、索引为什么会影响写入性能：底层机制分析
要理解索引对写操作的影响，首先要明白MySQL（以InnoDB为例）的存储结构。InnoDB采用聚簇索引组织数据，主键索引的叶子节点直接存放整行数据，而每一个二级索引（辅助索引）都是一棵独立的B+树，叶子节点存放的是索引列的值和对应的主键值。这意味着一张表上有几个索引，磁盘上就存在几棵独立的B+树。
当执行一条INSERT语句时，InnoDB不仅要往聚簇索引中插入一行数据，还要在每一个二级索引的B+树中插入一条对应的索引记录。假设一张表上有1个主键和5个二级索引，那么一次插入实际上要写6个位置。这就是所谓的“写放大”现象：写入的数据量被索引成倍放大。索引越多，redo log、undo log的产生量也越大，buffer pool被占用的页面也越多，整体写入吞吐量自然下降。
UPDATE操作的情况更复杂一些。如果UPDATE语句修改的列恰好是某个索引列，那么MySQL无法原地修改这条索引记录（因为修改后它在B+树中的位置可能变化），只能先删除旧的索引记录，再在正确的位置插入新记录，代价接近两次操作。另外，即使更新的列不属于任何二级索引，二级索引本身不受影响，但聚簇索引上的行级锁和MVCC版本链依然会带来开销。
除了写放大，还有两个不容忽视的成本：随机IO和页分裂。如果二级索引的列是随机值（例如UUID、哈希值），新插入的记录会落在B+树的随机位置，导致大量离散的磁盘读写；当某个数据页已满时，插入会触发页分裂（page split），把一页数据拆成两页，这既消耗CPU，也导致页的填充率下降、空间浪费，进一步加剧IO压力。
二、评估现状：如何判断索引拖慢了写入
优化之前，先要确认问题确实出在索引上。最直接的手段是观察插入或更新的耗时，然后临时性地对比：新建一张结构相同但没有二级索引的表，执行相同的写入语句，如果速度差距在数倍以上，基本可以断定索引是主要瓶颈。也可以直接对比有索引和无索引环境下批量导入同一份数据的耗时，差距往往非常直观。
其次可以借助MySQL自带的工具和状态变量进行分析。SHOW ENGINE INNODB STATUS的输出中包含INSERT BUFFER AND ADAPTIVE HASH INDEX部分，可以观察change buffer的使用情况；SHOW GLOBAL STATUS LIKE 'Innodb_pages_written'等指标能反映页写入压力。慢查询日志配合performance_schema也能定位具体是哪条写语句开销最大。
另外要检查索引是否存在冗余。如果一个索引的前缀与另一个索引完全重合，例如同时存在idx(a)和idx(a, b)，那么idx(a)就是冗余的，可以直接删除。通过sys.schema_unused_indexes和sys.schema_redundant_indexes视图可以快速找出从未被使用过的索引和冗余索引，这类“只付出维护成本、从不产生查询收益”的索引是优化的首要目标。
三、索引写操作的核心优化方法
1. 控制索引数量，删除无用索引
这是成本最低、收益最直接的优化手段。核心原则是：每一个索引都必须有明确的查询场景支撑，否则就是纯粹的写入负担。在实际项目中，表上十个八个索引的情况并不少见，多数是历史遗留或临时排查问题时加的，事后没有清理。定期审计索引使用情况，把使用频率接近零的索引删除，写入性能往往能有立竿见影的提升。删除前建议在测试环境验证，并保留一段时间的回滚脚本。
2. 利用Change Buffer减少随机IO
InnoDB的Change Buffer是专为二级索引随机写入设计的优化机制。当插入的索引页不在buffer pool中时，如果该页是二级索引页，MySQL可以先把这次修改记录到change buffer中，不必立即从磁盘读取该页，等到页被访问或后台合并时再应用这些修改。这大幅减少了随机读IO。
需要注意的是，MySQL 8.0.30之后change buffer相关参数被标记废弃，且MySQL 8.4中哈希搜索的change buffer支持有所调整，但整体机制在8.x版本中依然可用。可以通过innodb_change_buffer_max_size参数控制change buffer占buffer pool的最大比例（默认25%）。对于写多读少、二级索引基数大的业务表，这个参数适当调高能明显改善写入吞吐。
3. 主键设计：顺序写入避免页分裂
主键的选择对写入性能影响极大。最理想的主键是严格单调递增的，比如自增ID或雪花算法生成的趋势递增ID。这样的主键保证新记录永远追加到B+树最右侧的页，既避免了页分裂，也把随机IO变成了顺序IO，性能最优。相反，如果使用UUID v4这类完全随机的值做主键，每次插入都可能在树的任意位置触发页分裂，buffer pool命中率下降，表空间碎片化严重。
-- 推荐的主键设计：使用自增主键，保证顺序写入
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id), -- 单调递增，追加写入，无页分裂
KEY idx_user_created (user_id, created_at) -- 二级索引按查询场景保留，宁少勿多
) ENGINE=InnoDB;
-- 不推荐：随机主键会导致频繁页分裂和随机IO
-- PRIMARY KEY (uuid_col) -- UUID v4 是完全随机的值
4. 批量导入：先删索引后重建
对于大批量数据导入场景，比如数据迁移、初始化装载、批量补数，标准做法是：先删除所有非主键、非唯一约束必需的二级索引，导入完成后再统一重建索引。这样写入时只需维护一棵聚簇索引树，速度可以快一个数量级。重建索引时尽量使用排序好的数据，使得索引构建接近顺序构建，减少页分裂。如果表数据需要清空重建，使用TRUNCATE TABLE代替DELETE，同时注意ALTER TABLE ... IMPORT TABLESPACE等方案在特定迁移场景下的应用。
-- 大批量导入的标准流程 -- 1. 先删除二级索引（唯一约束索引视数据情况决定是否保留） ALTER TABLE big_table DROP INDEX idx_a, DROP INDEX idx_b; -- 2. 分批导入数据，单批控制在合理大小 INSERT INTO big_table (a, b, c) VALUES (...), (...), ...; -- 每批几千行 -- 3. 导入完成后重建索引 ALTER TABLE big_table ADD INDEX idx_a(a), ADD INDEX idx_b(b);
5. 批量插入与多值INSERT
即使是常规业务写入，也应该尽量使用多值INSERT代替逐条INSERT。每条独立语句都有解析、网络往返、事务提交的开销，合并成一条多值INSERT或使用批量LOAD语法，能把这部分固定成本摊薄。同时确保innodb_flush_log_at_trx_commit和sync_binlog的设置符合业务的可靠性要求——双1配置最安全但写入最慢，在允许极小概率丢数据的场景（如日志表）可适当放宽，换取明显的写入提升。
四、读写平衡：不要为了写入速度盲目砍索引
所有针对写操作的索引优化，都必须在读写之间取得平衡。索引的本质是用写入成本换取读取收益，删掉一个索引让写入快了10%，但可能让某个核心查询慢了10倍，得不偿失。正确的做法是建立量化评估机制：统计每个索引在一段时间内的使用次数、它支撑的查询的QPS和重要性，再结合写入压力，计算每个索引的“性价比”。
对于写入压力极大、查询模式相对固定的场景，还可以考虑架构层面的手段：通过写入缓冲层（先写Redis或消息队列，再异步批量落库）、读写分离（写入走主库，查询走从库）、分表分库分散单表索引维护压力等方式，从整体上缓解索引维护成本。冷热分离也是一种思路，把历史数据归档到索引更少的归档表中，主表保持精简。
最后，优化是一个持续迭代的过程。表结构、数据量、查询模式都会随时间变化，建议把索引审计纳入定期的数据库维护流程，结合慢查询日志和performance_schema的数据，动态调整索引策略，才能让数据库长期保持在读写两端都健康的状态。