MySQL 二级索引为什么会增加写入成本？如何控制索引数量？

简化版

二级索引能提升查询，但每次插入、删除、更新相关字段时，MySQL 不只要改数据页，还要维护所有受影响的二级索引页。索引越多，写入成本、磁盘空间、Buffer Pool 压力和页分裂概率越高。控制索引数量的原则是：保留高价值查询索引，合并可复用联合索引，删除长期不用或重复索引，避免为低频查询无限加索引。

详细版

InnoDB 的二级索引叶子节点保存索引列和主键值。写入一行数据时，除了聚簇索引要写，每个二级索引也要插入对应条目。更新索引列时，可能相当于删除旧索引项再插入新索引项。

这就是为什么“索引不是越多越好”。读多写少的表可以适当多建索引，写入频繁的表要更谨慎。优化时不能只看单条 SELECT 变快，还要看整体写入吞吐、锁等待、redo 量和存储成本。

完整版教学

一、二级索引维护成本来自哪里

插入一行要更新聚簇索引。

还要给每个二级索引插入条目。

删除一行要删除多个索引条目。

更新索引列会影响对应索引结构。

索引页可能发生分裂和合并。

二、二级索引结构回顾

InnoDB 表数据按主键组织。

二级索引叶子节点保存二级索引键和主键值。

通过二级索引查到主键后，可能再回表查完整行。

这也是覆盖索引能减少回表的原因。

写入时，这些二级索引都要保持一致。

三、写入成本示例

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  status VARCHAR(16),
  created_at DATETIME,
  KEY idx_user (user_id),
  KEY idx_status (status),
  KEY idx_created (created_at)
);

插入一条订单时，至少要维护主键和三个二级索引。

如果索引更多，写入路径会更重。

四、索引过多的影响

影响	表现	后果
写入变慢	INSERT/UPDATE 成本上升	吞吐下降
空间增加	索引文件变大	存储成本增加
缓存压力	Buffer Pool 装更多索引页	命中率下降
优化器复杂	候选索引变多	可能误选
DDL 变慢	建索引耗时	发布风险增加

五、如何控制索引数量

统计慢查询和真实查询模式。

识别重复索引和左前缀冗余索引。

优先设计可复用的联合索引。

低频后台查询可以接受慢一点，或走离线报表。

写多表要更严格控制索引数量。

每个索引都在向写入链路收费，只是账单常常到高并发时才显现。

六、如何判断索引价值

看查询频率。

看过滤选择性。

看是否能覆盖查询。

看是否能同时满足排序。

看写入成本是否可接受。

长期不用的索引要有清理机制。

七、误区和追问

误区：索引只影响查询，不影响写入。 所有相关写入都要维护索引结构。
误区：查询慢就给每个条件都建单列索引。 多个单列索引不一定等于好的联合索引。
误区：索引越多优化器选择越多越好。 候选过多也可能增加计划不稳定风险。
追问：怎么发现重复索引？ 比较索引列顺序、左前缀关系和实际使用情况。
追问：写多读少表怎么设计索引？ 只保留核心访问路径，避免为偶发查询加重写入。
追问：删除索引有风险吗？ 有，要先确认查询依赖和回滚方案，最好灰度观察。

八、面试收束

回答时先讲二级索引维护机制。

再说明写放大、空间和缓存成本。

最后给出索引治理原则：真实查询驱动、可复用、定期清理。

九、常见误区与追问

误区：只记住 MySQL 二级索引为什么会增加写入成本？如何控制索引数量？的结论就够了。 数据库题通常还要解释索引、事务、锁、执行计划或一致性边界，否则很容易被追问打穿。
误区：能查出结果就说明 SQL 或设计没问题。 还要看数据量扩大到 100 万行后是否仍能走合适索引、是否产生临时表或锁等待。
误区：所有场景都追求强一致。 读写分离、缓存、异步任务都可能牺牲一部分实时性，关键是说明业务是否允许。
追问：线上变慢时你先看什么？ 先看慢 SQL、执行计划、扫描行数、锁等待和连接池，再判断是 SQL 写法、索引还是并发问题。
追问：如何证明这个方案可落地？ 给出一个小数据例子，再补充约束、失败场景和回滚方案，避免只停留在概念层。

十、加强记忆

记 MySQL 二级索引为什么会增加写入成本？如何控制索引数量？时，把它压成“语义正确、执行高效、并发安全”三件事。先说清这个知识点解决什么数据库问题，再用 1 个带数字的小例子说明数据量一大为什么会出差异，最后补上索引、锁、事务或执行计划里的易错点。这样面试官无论追问 SQL 写法、线上慢查，还是并发一致性，你都能沿着同一条线继续展开。

简化版

详细版