百万行数据表索引优化咨询：现有复合索引下新增哪种索引最优

针对你的场景，方案2：INDEX (Code, Name) 是最合理且性能最优的选择，以下是具体分析：

各方案优劣对比 #

• 方案1：独立Name索引
  这个方案完全无法适配你的查询逻辑。你的查询以Code = 'someCode'的等值条件为核心，再配合Name LIKE 'ABC%'的前缀匹配。单独的Name索引无法利用Code的过滤条件，数据库要么先扫描Name索引拿到所有前缀匹配的记录，再逐一过滤Code；要么先通过原复合索引拿到所有对应Code的记录，再逐一校验Name。两种方式都会处理大量无关数据，在百万行表中效率极低。

• 方案2：INDEX (Code, Name)
  这个复合索引完美匹配你的查询需求：

  1. 索引首列是Code，数据库可以快速通过等值查询定位到所有Code = 'someCode'的索引条目；
  2. 第二列是Name，由于你的查询是前缀匹配（%在末尾），索引的有序性可以被利用，直接在已定位的Code分组内做范围扫描，快速筛选出符合Name LIKE 'ABC%'的记录；
  3. 如果你的查询只需要返回这两列或者包含在索引里的列，还能触发覆盖索引，不需要回表查询原数据，性能进一步提升。

  关于你担心的Code重复出现在两个复合索引的问题：索引确实会增加写入操作（INSERT/UPDATE/DELETE）的维护开销，但只要这个新查询的频率足够高，这种开销完全值得。百万行表的场景下，除非是极高并发的写入，否则额外的索引维护成本几乎可以忽略，而查询性能的提升是非常显著的。

• 方案3：INDEX (Name, Code)
  这个索引顺序完全错误。你的查询是以Code等值过滤为前提，而索引首列是Name，数据库只能先扫描所有Name LIKE 'ABC%'的条目，再从中筛选Code匹配的记录，会扫描大量无关数据，效率远低于方案2。

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