活跃事务表中使用MySQL INDEX的合理性分析

核心问题解答 #

对于频繁执行增删改（DML）同时需要快速查询（SELECT）的活跃事务表，不是必须完全避免索引，而是要按需创建精准的索引。索引的本质是「以写操作的额外开销换取读操作的性能提升」，是否合适取决于你的业务读写比例、操作优先级：

• 如果读操作的频率或延迟要求远高于写操作，合理的索引带来的收益远大于写性能的损耗
• 如果写操作是核心业务且对延迟极度敏感，才需要严格控制索引数量甚至部分场景放弃索引

平衡读写性能的实践方案 #

• 只给高频查询字段建索引：仅针对WHERE过滤、ORDER BY排序、JOIN关联的核心字段创建索引，避免给低查询频率的字段加索引，减少DML操作时的索引维护开销
• 优先使用覆盖索引：如果查询只需要返回特定字段，创建包含这些字段的联合索引（即覆盖索引），这样查询时无需回表读取整行数据，既提升读性能，也相对降低索引维护的资源消耗
• 删除冗余索引：避免重复或冗余的索引（比如已存在联合索引(a,b)，就无需单独创建索引a），冗余索引只会增加DML的负担，没有额外收益
• 定期维护索引：在业务低峰期执行OPTIMIZE TABLE或ALTER TABLE ... FORCE INDEX清理索引碎片，避免索引膨胀导致的读写性能下降

额外的MySQL索引基础知识补充 #

• 常见索引类型：
  • B+树索引：InnoDB引擎默认索引类型，支持等值查询、范围查询和排序，是绝大多数场景的首选
  • 哈希索引：仅Memory引擎支持，适合等值查询，但不支持范围查询和排序
  • 全文索引：针对文本内容的模糊匹配优化，适合博客、文档类场景的关键词搜索
• 索引失效场景：避免在索引字段上执行函数操作（如WHERE YEAR(create_time) = 2024）、使用OR连接非索引字段、模糊查询以%开头（如WHERE name LIKE '%john'），这些操作会导致索引无法生效，退化为全表扫描
• 聚簇与非聚簇索引：InnoDB的主键索引是聚簇索引，叶子节点存储整行数据；普通索引（非聚簇）叶子节点仅存储主键值，因此普通索引查询需要回表，而覆盖索引可以绕过回表步骤

内容的提问来源于stack exchange，提问作者Stuart William