Rails关联表迁移执行顺序及联合索引不同写法的区别与影响

关系型数据库的联合索引遵循最左前缀匹配原则，存储结构会先按第一个字段的值排序，同第一个字段值的条目再按第二个字段的值排序，只有查询条件包含索引最左侧的连续字段时，该索引才能被触发使用，因此两个索引的顺序差异会直接决定可覆盖的查询场景：

• 未注释的 t.index [:user_id, :brand_id] 按user_id在前、brand_id在后的顺序创建联合索引，可覆盖的查询场景：
  • 同时按user_id和brand_id两个字段过滤的查询
  • 仅按user_id单个字段过滤的查询
    无法单独覆盖仅按brand_id过滤的查询
• 被注释的 t.index [:brand_id, :user_id] 按brand_id在前、user_id在后的顺序创建联合索引，可覆盖的查询场景：
  • 同时按brand_id和user_id两个字段过滤的查询
  • 仅按brand_id单个字段过滤的查询
    无法单独覆盖仅按user_id过滤的查询

实际业务选型影响 #

如果你的业务查询以「查询指定用户关联的所有品牌」为主，极少有「查询指定品牌关联的所有用户」的需求，仅保留第一个索引即可，可同时兼顾双字段过滤和单user_id过滤的查询性能。
如果两种单字段过滤的场景都高频出现，可选择两种方案：

1. 同时启用两个联合索引，缺点是会额外占用存储空间、降低关联表的写入性能
2. 仅保留一个联合索引，再为后置字段新增单列索引，比如保留[:user_id, :brand_id]联合索引，新增t.index :brand_id，性能和存储成本与双联合索引基本一致

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