如何在MySQL的WHERE和ORDER BY子句中查询索引？

Question

请参考MySQL 8.0新的DESC索引功能的此documentation。

假设我有一个这样的索引：

INDEX worstReviewIdx (rating ASC, hasReview DESC, id DESC)

这让我可以通过评级ASC，hasReview DESC，id DESC非常快速地订购。但是，在我的用例中，我需要提供分页。如果我将查询限制为10个，我需要能够找到保持上述排序顺序的下10个项目。

MySQL是否允许我这样做(其中lastWorstReveiewIdx是前一个分页中最后一项的索引)：

SELECT ... WHERE worstReviewIdx < {lastWorstReviewIdx} ORDER BY worstReviewIdx

Answer 1

问题不是ASC vs DESC，而是“记住你停下来的地方”，然后使用一个与索引匹配的ORDER BY来查找下10个。

在你的例子中，“你停下来的地方”是一个三重(rating, hasReview, id)，用它来表示本质上

WHERE (rating, hasReview, id) > ($r, $h, $id)   -- not quite right
ORDER BY rating ASC, hasReview DESC, id DESC
LIMIT 10

(我建议使用LIMIT 11，这样您就可以知道是否会有更多。)

我不清楚MySQL 8.0是否像WHERE中所示那样优化了“行构造函数”表达式。无论如何，它不能像您需要的那样来回切换ASC/DESC的混合。因此，您需要拼写出一个相当长的复杂布尔表达式，如下所述：http://mysql.rjweb.org/doc.php/pagination

如果添加一个计算为(- rating)的GENERATED列，然后使用它而不是rating --这将使这三个列都为DESC，则可以避免很多这种讨论和复杂性。(而且您不需要8.0的新索引功能。)