mysql - 如何优化重复的 MySQL 排序选择

Question

假设我有一个包含 a、b 和 c 列的大型数据库。假设我随后希望根据对多列的某种排序来选择第 x 行到第 (x+100) 行。我可以使用 ORDER BY 和 LIMIT 构造来完成此操作:

SELECT * FROM table_name ORDER BY b ASC, c DESC, a DESC LIMIT x, 100

如果我随后希望使用相同的顺序(在 b 上升序，在 c 上降序，然后在 a 上降序)但具有不同的范围限制来执行许多类似的查询怎么办？直觉上，不需要为每个这样的查询重复昂贵的排序操作。

我正在研究使用索引 ( http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html ) 优化 ORDER BY 操作，但不幸的是，似乎无法创建包含混合升序和降序的索引。

有没有好的方法来优化这个？这似乎是一个相当常见的用例。

Answer 1

可能唯一的优化是将数字 b 存储为 -b 或有一个额外的列，其中冗余地包含 -b它。然后

ORDER BY b ASC, c DESC LIMIT...

将被替换为

ORDER BY minusb DESC, c DESC LIMIT...

还有

INDEX(minusb, c)

只要你确定

• 所有ORDER BY项都是同一个表中的列名，
• 方向一致，
• 和一个 INDEX 存在，它以与 ORDER BY 列表相同的顺序列出所有这些(可选地在 end 上额外的列) ,

然后优化器可以(但可能选择不)非常有效地使用那个INDEX——包括消耗LIMIT。

将它们全部设为ASC 还是全部设为DESC 都无关紧要。 (ASC 可能 稍微好一些。)

请记住，LIMIT m, n 将必须读取 m+n 行。 (OFFSET 是一个很好的功能，但它没有得到很好的优化。)如果您使用 OFFSET 和 LIMIT 在长列表中“分页” ，最好“记住你离开的地方”以避免扫描 OFFSET 行。 (如果适用，我可以为您提供更多详细信息。)