Froo

 我们过去无需考虑数据库性能的优化，但这种日子一去不复返了。

随着时代发展，每个新公司都希望创造下一个Facebook，同时结合收集各种可能的数据点（data point)去提供更好的机器学习预测结果。我们作为开发者，需要准备比以往更好的API，以提供可靠并且高效的终端（endpoint），这个终端必须能够毫不费力的处理巨量的数据。

如果你做过后端或者数据库结构，你或许已经用过这种方式去分页了，对吧？

SELECT * FROM table_name LIMIT 10 OFFSET 40

不过，如果你确实像这样进行分页，我恐怕要说你这样做不对。

不同意我吗？没必要的。Slack、Shopify 和 Mixmax 的分页 API 也用了我们将要谈到的方式。

我敢打赌，你找不出一个没用过 OFFSET 和 LIMIT 分页的后端开发者。对于在 MVP 和数据量少的列表来说，这种分页方式是勉强够用。

但是，当你需要从头开始建立一个可靠并且高效的系统，你或许从一开就该把分页方式做正确。

今天我们就来讨论目前使用最广泛的分页方式存在什么问题，以及如何实现高效的分页。

OFFSET 和 LIMIT 分页存在什么问题？

如上一段所说，OFFSET 和 LIMIT 对于数据量很小的项目来说效果还不错。

不过，当数据库积累的数据量超过了服务器内存所能存储的量之后，如果还用这种方式分页，那问题就出现了。

为了完成这种分页方式，每当请求分页时，数据库都需要进行低效率的全表扫描（Full Table Scan）。（插入和删除或许也在同时进行，但是我们不想要过时的数据！）

全表扫描是什么？全表扫描（也称为有序扫描（Sequential Scan））是一种数据库扫描方式，表中的每行都需要按序读取，然后检查对应的列是否满足给定条件。这种扫描方式是众所周知最慢的了，因为需要大量的磁盘 I/O 读取去进行多重搜索，同时内存和磁盘的传输也耗费巨大。

这意味着，如果有100.000.000个用户，在请求从50.000.000开始的OFFSET，它会抓起所有这50.000.000条记录（这完全没必要），然后放进内存，做完这些之后，才获取LIMIT所需的20条结果。

随后，网站中才能显示如下的分页信息：

第50.000条至第50.020条 ，共100.000条

它必须先抓取50.000行。来看看这有多低效率吧？

如果不相信我，来看看这个我创建的 fiddle 演示（https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1）吧。左面板里是数据库结构，会插入 100.000 行数据用于测试；在右面板分别是前述存在问题的分页方式和我想要介绍的方式。点击页面上方的运行（Run），比较每种方式的执行时间。第一种方式至少花费第二种方式 30 倍的时间。

当数据更多时，差距就更大了。点击查看（https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC）我用 1000 万行数据进行的验证。

现在的这些可能会让你知道在这场景背后发生了什么。（后面是广告

TLDR：OFFSET 的位置越靠后，查询的越久。

应当使用的替代方法

SELECT * FROM table_name WHERE id>10 LIMIT 20

这才是你应该使用的方式：

SELECT * FROM table_name WHERE id>10 LIMIT 20

这是一种基于指针的分页方式。

相比于保存当前的 OFFSET 和 LIMIT 并用于每次请求，你只需要保存最后一次获取到的主键（通常是ID）和 LIMIT，能够获得相同的查询结果。

你应该存储最后接收到的主键（通常是一个ID）和LIMIT，而不是在本地存储当前的OFFSET和LIMIT并随每个请求传递它

为什么这样做？因为传递读取的最后一行，就相当于告诉数据库该从哪里开始新的查询，这基于高效的索引键，不需要考虑任何其他的行。

Take into example the following comparison:

（直接放原文图片）

Against our optimized version:

（直接放原文图片）

看一下两者的比较：

（直接放原文图片）

优化后的：

（直接放原文图片）

都是获取到了相同的记录，但是第一种查询方式花了12.80秒，第二种只花了0.01秒。能明白差距了吧？

事先声明

如果想要让基于指针的分页完美执行，你需要唯一并且序列化的列（一个或者多个），比如唯一的整数 ID 或者时间戳，这种东西对于一些特定场景来说会很有用处。

我的建议是，必须时刻考量每种表结构和所需查询的优点和缺点。

如果你需要处理大量的数据相关的查询，Rick James的文章《Lists》（mysql.rjweb.org/doc.php/lists）或许能提供更深入的指导。如果我们手头的表是没有主键的，比如如果我们有多对多（many-to-many）关系的表，传统的 OFFSET/LIMIT 的方式当然总是可行的，虽然会导致潜在的更慢的查询。如果你想分页，我建议在表中使用自动增加的主键，即使仅仅是为了分页。

总结

总的来说，你应该分别测试一下在1000行和100万行下的查询性能。可扩展性是极其重要的，如果一开始就能正确的构建，那会避免未来很多让人头疼的问题。

原文：https://hackernoon.com/please-dont-use-offset-and-limit-for-your-pagination-8ux3u4y