mysql - 避免对页面嵌套集进行全表扫描

我有这样的nested set设置：

Node (Id, ParentId, LeftBounds, RightBounds, Level, Name)

LeftBounds上有一个索引。

但是当我尝试选择分页结果时，

SELECT * FROM Node ORDER BY LeftBounds ASC LIMIT 500000, 1000

SQL执行全表扫描。还有什么我应该看看以避免全表扫描吗？

通常这不是什么大问题，但是对于具有几百万行的表而言，加载最后一页大约需要3-5秒。

最佳答案

您的LIMIT 5000000, 1000子句要求MySQL对结果集中的结果进行排序，跳过其中的五十万，然后显示1000。MySQL似乎认为最好用表扫描来完成。这不足为奇。

您可以尝试延迟加入操作。这样做的目的是减小需要订购的结果集的大小。它是这样的。

SELECT Node.*
  FROM Node
  JOIN (
         SELECT id
           FROM Node
          ORDER BY LeftBounds ASC
          LIMIT 500000, 1000
       ) Subset ON Node.id = Subset.id
  ORDER BY Node.LeftBounds ASC

如您所见，这将您需要纠缠的较大结果集限制为更少的列，尤其是id和LeftBounds。然后，它将使用找到的1000个不同的id值集来检索完整记录。

如果使自己成为(LeftBounds, id)上的复合索引，则可以大大加快此查询的速度。但是它仍然必须跳过半百万行，因此您的EXPLAIN可能表示您正在执行完整的索引扫描。

您可以使用此查询来加快查询速度的下一件事是摆脱SELECT *，而是命名所需的列。为什么有帮助？因为它暗示了复合索引可能有助于完全满足查询要求。您已经提到LeftBounds是唯一的，因此是JOIN准则的候选者。因此，让我们通过一个示例进行探讨。假设您要在结果集中输入ParentId, LeftBounds, RightBounds, Level, Name。然后，您可以使用以下查询：

SELECT Node.ParentId, Node.LeftBounds,
       Node.RightBounds, Node.Level, Node.Name
  FROM Node
  JOIN (
         SELECT LeftBounds
           FROM Node
          ORDER BY LeftBounds ASC
          LIMIT 500000, 1000
       ) Subset ON Node.LeftBounds = Subset.LeftBounds
  ORDER BY Node.LeftBounds ASC

如果您在需要的列上有索引，MySQL可以满足该索引的查询。该索引应按此顺序合并这些列。

LeftBounds, ParentId, RightBounds, Level, Name

LeftBounds必须在索引中排在第一位，因为这是您用于随机访问索引的列。此处的要点是不必使用id列来访问表。