mysql innodb 选择偏移量慢

Question

我在表中有 29900000 条记录，偏移量消耗了太多的查询执行时间

SELECT * FROM table_records LIMIT 50 OFFSET 1999950
this query taking 33.087 sec

我已将偏移量更改为 2000000

SELECT * FROM table_records LIMIT 50 OFFSET 2000000
this query taking 2.030 sec

解释

EXPLAIN SELECT * FROM table_records LIMIT 50 OFFSET 29941250


id | select_type | table         | type   | possible_keys | key     | key_len | ref    |rows     | Extra   
1  | SIMPLE      | table_records | index  | (NULL)        | PRIMARY | 4       | (NULL) |29900771 |         

我删除了刚设置为限制的偏移量

SELECT * FROM table_records LIMIT 50
this query taking 0.002 sec

感谢任何建议或想法。

Answer 1

一切都与缓存有关。

简而言之，OFFSET 很糟糕。它必须读取并忽略所有“偏移”行，然后传递“限制”行。

当跳过行时，它必须获取行——如果它们在磁盘上，这需要时间；如果它们缓存在 RAM 中，速度会快得多。 (通常快 10 倍。)

您的情况可能发生了什么:第一个查询发现 RAM 中的行很少(如果有的话)，因此它必须找到大部分或全部 1999950 行。

然后您的第二个查询快速扫描了 1999950 行，然后从磁盘中提取了最后 50 行。 (或者可能最后 50 条已经进来了，因为 I/O 的单位是一个记录“ block ”。)

使用 LIMIT 和/或 OFFSET，EXPLAIN 很少提供任何线索——它通常提供一个估计表中的总行数。

您的示例还有另一个问题...您没有ORDER BY。因此，引擎可以随意传送它喜欢的任何行。通常它是可以预见的，但有时你会得到惊喜。

但是，一旦您添加了一个ORDER BY，可能需要一个临时表并甚至在获得第一条记录之前进行排序!也就是说，SELECT ... ORDER BY .. LIMIT 50 可能 和所有其他的一样慢——如果您按对索引等不方便的排序，涉及的。

参见 how OFFSET sucks when paginating web pages .这包括“记住你离开的地方”的解决方法。和 this展示了如何有效地获取接下来的 1000 行，即使 ID 存在间隙。