关联表外键：创建复合覆盖索引还是多个单列索引？哪种更优？

这是个非常实用的问题！咱们先把两种索引策略的核心差异拆解清楚，再结合你的sales表场景分析哪种更适合你的连接查询需求：

一、核心区别对比 #

1. 索引使用效率 #

• 联合索引（cmpindex）：
  它是一个单一的B-tree结构，按p_id → e_id → c_id的顺序排序。对于你的连接场景：
  • 如果查询同时关联三个父表（比如sales分别和products、employee、customer_table连接），这个索引可以直接匹配所有连接条件，数据库无需做额外的索引合并操作，效率极高。而且因为你的主键就是这三个列的组合，这个索引本质是覆盖索引——InnoDB的二级索引叶子节点会存储主键值，所以如果你的连接查询只需要这三个外键列，完全不需要回表访问原表数据。
  • 但如果查询只用到非前缀的单个外键（比如只通过e_id或c_id连接），联合索引无法利用前缀匹配特性，只能做全索引扫描，效率远不如对应的单列索引。不过如果是用p_id（前缀列）单独连接，联合索引依然能高效命中。
• 单列索引（pindex/eindex/cindex）：
  三个独立的B-tree结构，每个索引只对应一个外键列。对于仅用到单个外键的连接查询，对应的单列索引能快速定位数据；但如果是同时用到多个外键的连接，数据库需要执行索引合并（比如把pindex和eindex的结果做交集），这会额外消耗CPU和内存资源，整体效率不如联合索引。

2. 存储空间与维护成本 #

• 联合索引只需要维护一个B-tree，占用的磁盘空间远小于三个单列索引的总和（每个单列索引都有独立的元数据和B-tree结构）。
• 当sales表执行插入、更新、删除操作时，联合索引仅需更新一次B-tree；而三个单列索引需要分别更新三个B-tree，写操作的性能开销明显更高。

3. 覆盖查询能力 #

如前文所说，你的sales主键是{p_id,e_id,c_id}，所以cmpindex联合索引天然具备覆盖查询能力——如果你的连接查询不需要amount列，数据库可以直接从索引中获取所有需要的数据，无需回表。而单列索引只能覆盖对应单个列的查询，只要涉及其他列就必须回表。

二、针对你的场景的最优选型 #

你的查询大多是关联表的连接操作，需要结合具体的查询模式判断：

• 如果你的连接查询经常同时用到两个或三个外键（比如同时关联三个父表的多表连接），那联合索引cmpindex是更优选择——它能一次性匹配所有连接条件，避免索引合并的开销，同时节省存储空间和写操作的维护成本。
• 如果你的连接查询大多只用到单个外键（比如经常单独用p_id关联products，很少同时连接多个父表），那三个单列索引更灵活，每个单键连接都能获得最高的查询效率。

从你的表结构来看，主键是{p_id,e_id,c_id}的组合，说明业务中这三个列的组合是唯一标识，大概率会经常同时用它们做连接或过滤，因此更推荐使用联合索引。如果偶尔有高频的非前缀单键查询（比如经常单独用e_id查询），可以考虑在联合索引的基础上，额外加一个对应的单列索引，不过要权衡存储空间和写操作的开销。

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