mysql索引对写入性能有影响吗_mysql读写平衡分析

索引越多，insert/update/delete越慢，因每次写入需同步更新聚簇索引和所有二级索引，触发多次b+树操作；唯一索引因强制重复校验无法使用change_buffer，写入开销更大；批量导入时应临时删索引、调大缓冲参数；冗余索引是写入瓶颈主因，应优先清理而非加缓存。

索引越多，INSERT/UPDATE/DELETE 越慢

mysql 每次写入数据时，不仅要修改聚簇索引（主键 B+ 树），还要同步更新所有相关二级索引。每个二级索引都是一棵独立的 B+ 树，插入一条记录可能触发多次磁盘页分裂、节点调整和缓冲池刷脏。实测表明，一张表从 0 个索引增加到 5 个二级索引，INSERT 吞吐量可能下降 40%～70%，尤其在 innodb_buffer_pool_size 不足、磁盘 I/O 瓶颈明显时更甚。

常见错误现象：SHOW PROCESSLIST 中大量写操作卡在 updating 或 insert 状态；INFORMATION_SCHEMA.INNODB_METRICS 显示 dml_inserts 增长缓慢但 index_page_splits 激增。

• 单条 INSERT 语句涉及 N 个索引 → 至少 N+1 次 B+ 树写入（含聚簇索引）
• UPDATE 修改了索引列 → 原索引项删除 + 新索引项插入，开销翻倍
• DELETE 同样需从所有索引中定位并移除对应条目，非主键删除更耗时

唯一索引和普通索引的写入代价差异很大

唯一索引（UNIQUE KEY）在写入前必须做重复值校验，而普通索引不需要。这个校验过程会强制走一次索引查找（即使使用 change buffer，也要先查唯一约束），导致事务持有行锁或间隙锁时间更长，容易引发锁等待甚至死锁。

使用场景：用户注册表的 email 字段加 UNIQUE，高并发写入时 INSERT ... ON DUPLICATE KEY UPDATE 可能因唯一检查阻塞其他线程。

• 普通索引可被 change_buffer 缓存（innodb_change_buffering = inserts 默认开启），延迟合并到磁盘
• 唯一索引无法使用 change_buffer 做插入缓存，必须实时查找校验
• INSERT IGNORE 和 REPLACE INTO 同样触发唯一性检查，不比 ON DUPLICATE KEY UPDATE 更轻量

批量写入时索引维护策略要主动干预

默认情况下，MySQL 对每条 INSERT 都立即更新索引。但在大批量导入（如 etl、日志归档）场景下，可以临时关闭非必要索引或调大缓冲参数来提速。

实操建议：

• 导入前用 ALTER table t DROP INDEX idx_xxx 删除非关键二级索引，导入完成再重建（注意重建过程仍锁表）
• 设置 SET session sort_buffer_size = 268435456（256MB）提升 CREATE INDEX 效率
• 对 LOAD DATA INFILE，确保 innodb_buffer_pool_size ≥ 数据总大小的 50%，否则频繁刷脏拖慢索引构建
• 避免在 autocommit=1 下循环执行单条 INSERT，改用批量 INSERT INTO t VALUES (...), (...), (...)

读写失衡时优先砍冗余索引，而非盲目加缓存

很多团队遇到写入变慢第一反应是加 redis 或调大 query_cache_size，但真正瓶颈常在索引设计本身。通过 pt-duplicate-key-checker 或 sys.schema_unused_indexes（MySQL 8.0+）可快速识别长期未被 select 使用却持续拖累写入的索引。

容易被忽略的点：

• 复合索引中前导列未被查询条件使用 → 整个索引对读无效，但写入照常维护
• TEXT/json 列上的全文索引（FULLTEXT）写入开销远高于普通 B+ 索引，且无法用 change_buffer
• 低基数列（如 status TINYINT 只有 0/1）建索引几乎不影响读性能，却白耗写资源

索引不是越多越好，而是越精准越省事。一个没被 EXPLAIN 用上的索引，就是纯写入税。