我有一个嵌套的集合设置,如下所示:
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
列来访问表。