MySQL5.7 带固定前置查询条件的联合索引构建最佳实践咨询

结论 #

优先为每个常用检索字段创建 (company_id, shop_id, 检索字段) 格式的联合索引，该方案性能远优于「(company_id, shop_id)联合索引+单列索引」的组合。

核心原因 #

基于MySQL 5.7 InnoDB的最左前缀匹配原则，结合你的业务查询特征（所有WHERE条件都以company_id = ? AND shop_id = ?开头），该联合索引的优势非常明确：

• 所有以这两个字段为前置条件、加上后续检索字段的查询，都可以完全命中索引，不需要额外扫描就能完成过滤，查询效率最高
• 索引本身已经按照company_id+shop_id分组排序，相同company_id和shop_id下的后续字段也是有序的，范围查询、排序等操作也能直接利用索引完成
• 如果查询要返回的字段也包含在联合索引中，还能触发覆盖索引特性，不需要回表读取整行数据，进一步降低IO开销

为什么不推荐第二种方案 #

如果仅建(company_id, shop_id)联合索引+col_a/col_c单列索引，会存在两个明显问题：

• MySQL查询优化器单次查询只能选择一个索引生效：要么走(company_id, shop_id)联合索引，过滤出对应门店的所有数据后，再逐个匹配col_a/col_c的条件，数据量大时过滤成本很高；要么走col_a单列索引，还需要回表判断company_id和shop_id是否匹配，额外增加IO开销
• 单列索引无法和前置的联合索引产生协同效果，完全浪费了查询一定会带两个id的前置特征

特殊场景的折中方案 #

如果你的表后续需要用到的检索字段非常多（超过10个），为每个字段都建联合索引会导致索引体积过大、写入（增删改）性能下降太多，可以退而求其次选择第二种方案，但要注意：

• (company_id, shop_id)联合索引要作为基础索引必建
• 仅对选择性高的后续字段建单列索引，选择性低于20%的字段建单列索引的收益很低，不需要额外创建
• 如果有多个字段经常同时出现在查询条件中，可以把这些字段按使用频率组合到同一个联合索引中，比如(company_id, shop_id, col_a, col_c)，进一步提升查询效率

内容的提问来源于stack exchange，提问作者Mads Mønster