有什么效果/更好的一次查询 OFFSET/LIMIT 多次与单个查询然后逐行读取

Question

哪一个更好（在大多数因素中，例如内存使用情况、可伸缩性，在这两种情况下：总数据少于 RAM 或数据库中的总数据多于 RAM）：每 1k 查询多次，直到没有更多行SELECT *FROM foo  LEFT JOIN ... ON ... -- multiple timesORDER BY createdLIMIT ?*1000, 1000 然后n := 0for {   rows, err := db.Query(sql, n) // assume this is prepared statement   if err != nil { return nil, err }   defer rows.Close()   subtotal := 0   for rows.Next() {      err = rows.Scan( ... )      if err != nil { return nil, err }      subtotal += 1   }   if subtotal == 0 { break }   n += 1}对比一次查询然后扫描它SELECT *FROM foo  LEFT JOIN ... ON ... -- multiple timesORDER BY created然后rows, err := db.Query(sql) // assume this is prepared statementif err != nil { return nil, err }defer rows.Close()for rows.Next() {   err = rows.Scan( ... )   if err != nil { return nil, err }}

Answer 1

请记住，确实的查询LIMIT 500000, 1000必须扫描 501,000 行才能获得最后 1000 行。LIMIT 按位置而不是按值选择行。所以没有办法使用索引直接跳到你想要的行。因此，它必须从第一行开始，读取所有行，直到超过您要求的偏移量。

因此，重复地对具有连续偏移量的行集进行分页非常昂贵，因为每个下一个查询都必须重新读取它之前已经读取的数千行。它基本上是一个 O(n ² ) 算法。

PS：这行不通：LIMIT ?*1000, 1000因为 LIMIT 不接受表达式。它只需要整数文字或占位符。在传递值之前，您必须LIMIT ?, 1000在 Go 代码中进行乘法运算。

Answer 2

正如比尔在他的回答中提到的，OFFSET 查询有充分的缺点。

就数据库而言，使用单个查询选择所有内容是快速的，但如果您不快速处理返回的行，它会长时间使用数据库连接，使其无法用于其他任何人。这在繁忙的服务器中可能是一个问题，其中空闲的数据库连接通常是稀缺资源。

您（可能）错过了没有这些缺点的第三个选项：键集分页（又名寻求分页）。如果 foo 至少有一个 UNIQUE NOT NULL 列（即一个主键），并且 created 被索引，则此方法有效。

键集分页在整个网络上都有很好的解释，但简而言之（假设 foo 有一个名为“id”的 UNIQUE NOT NULL 列）：

SELECT *

FROM foo

  LEFT JOIN ... ON ... -- multiple times

WHERE (foo.created = X AND foo.id > Y) OR foo.created > X

ORDER BY foo.created, foo.id

LIMIT 1000

这里，X 和 Y 分别是上一个查询中最后一行的 created 和 id 列的值（省略第一个查询中的 WHERE 子句）。

这样，您可以在恒定时间内检索连续的页面。根据将行插入 foo 或任何连接表的方式，执行此操作时可能会丢失行或获得重复的行（与 OFFSET 相同）。如果这不是一个选项，您必须坚持一个查询。