按主键倒序查询时，是否需要在联合索引中指定主键倒序规则？

索引使用相关问题解答 #

1. 主键倒序规则是否需要指定 #

• 对于MySQL InnoDB引擎，你创建add_index :payments, [:user_id, :status]这个联合索引时，二级索引的叶子节点会默认追加主键id作为最后一列，实际存储的索引结构为(user_id, status, id(升序))。
• 你的查询是固定user_id和status过滤后按id倒序排序，InnoDB本身支持索引反向扫描，即使索引内的id是升序存储，也可以反向遍历得到倒序结果，普通业务场景下性能差异可以完全忽略。
• 只有当这个查询的访问频率极高、且单用户符合条件的支付记录达到十万级以上时，才需要显式指定排序规则优化性能，且注意正确写法需要把id显式加入索引列：

add_index :payments, [:user_id, :status, :id], order: { id: :desc }

你问题中写的add_index :payments, [:user_id, :status], order: {id: :desc}属于无效写法，因为id不属于你声明的索引列，这个排序配置会被直接忽略。

• 绝大多数场景下直接创建add_index :payments, [:user_id, :status]即可满足需求，不需要额外指定排序规则。

2. 分页功能是否需要额外索引 #

• kaminari的page、per方法本质是生成LIMIT + OFFSET语法实现分页，不需要额外新增其他索引。
• 上述联合索引已经可以覆盖你查询的过滤、排序逻辑，仅需要回表获取分页对应的少量行数据，性能足够支撑绝大多数业务量级。
• 只有当你遇到单用户符合条件的支付记录超过百万级、深度分页（翻到数百页之后）性能下降的问题时，才需要考虑改用基于id的游标分页替代offset分页，普通场景无需做额外优化。

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