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

Question

哪一个更好（在大多数因素中，例如内存使用情况、可伸缩性，在这两种情况下：总数据少于 RAM 或数据库中的总数据多于 RAM）：

每 1k 查询多次，直到没有更多行

SELECT *

FROM foo

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

ORDER BY created

LIMIT ?*1000, 1000 

然后

n := 0

for {

   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 times

ORDER BY created

然后

rows, err := db.Query(sql) // assume this is prepared statement

if 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 2 ) 算法。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 timesWHERE (foo.created = X AND foo.id > Y) OR foo.created > XORDER BY foo.created, foo.idLIMIT 1000这里，X 和 Y 分别是上一个查询中最后一行的 created 和 id 列的值（省略第一个查询中的 WHERE 子句）。这样，您可以在恒定时间内检索连续的页面。根据将行插入 foo 或任何连接表的方式，执行此操作时可能会丢失行或获得重复的行（与 OFFSET 相同）。如果这不是一个选项，您必须坚持一个查询。