避免在分页查询中使用 OFFSET 和 LIMIT

您不必担心优化数据库性能的日子已经一去不复返了。 时间不会静止。 每个新技术企业家都想创建下一个 Facebook，同时尝试收集他们能掌握的所有数据。 企业需要这些数据来更好地训练模型，帮助他们赚钱。 在这种情况下，程序员需要创建 API，使他们能够快速、可靠地处理大量信息。

如果您设计应用程序或数据库后端已有一段时间，您可能已经编写过运行分页查询的代码。 例如，像这样：

SELECT * FROM table_name LIMIT 10 OFFSET 40

事情是这样的？

但如果这就是你的分页方式，我很遗憾地说你没有以最有效的方式做到这一点。

你想反对我吗？ 可以 没有 花 时间. 松弛, Shopify商铺 и 混合最大 他们已经在使用我今天要讨论的技术。

至少列出一位从未使用过的后端开发人员 OFFSET и LIMIT 执行分页查询。 在MVP（最小可行产品）以及使用少量数据的项目中，这种方法非常适用。 可以这么说，它“确实有效”。

但是，如果您需要从头开始创建可靠且高效的系统，则应该提前注意查询此类系统中使用的数据库的效率。

今天我们将讨论分页查询引擎的常用（太糟糕）实现的问题，以及如何在执行此类查询时实现高性能。

OFFSET 和 LIMIT 有什么问题？

正如已经说过的， OFFSET и LIMIT 它们在不需要处理大量数据的项目中表现良好。

当数据库的大小增长到服务器内存无法容纳时，就会出现问题。 但是，在使用此数据库时，您需要使用分页查询。

要使这个问题显现出来，必须存在一种情况，即DBMS对每个分页查询都采取低效的全表扫描操作（虽然可能会发生插入和删除操作，但我们不需要过时的数据！）。

什么是“全表扫描”（或“顺序表扫描”，Sequential Scan）？ 在该操作中，DBMS 顺序读取表的每一行（即表中包含的数据），并检查它们是否符合给定条件。 众所周知，这种类型的表扫描是最慢的。 事实上，当它执行时，会执行许多涉及服务器磁盘子系统的输入/输出操作。 与处理存储在磁盘上的数据相关的延迟以及将数据从磁盘传输到内存是一项资源密集型操作的事实使情况变得更糟。

例如，您有 100000000 个用户的记录，并且使用以下结构运行查询 OFFSET 50000000。 这意味着 DBMS 必须加载所有这些记录（我们甚至不需要它们！），将它们放入内存中，然后在其中报告 20 个结果 LIMIT.

假设它可能看起来像这样：“从 50000 中选择 50020 到 100000 行”。 也就是说，系统首先需要加载50000行才能完成查询。 你知道她还要做多少不必要的工作吗？

如果您不相信我，请看一下我使用这些功能创建的示例 db-fiddle.com. 

db-fiddle.com 上的示例

那里，在左边，在田野里 Schema SQL，有代码将 100000 行插入数据库，在右侧的字段中 Query SQL，显示两个查询。 第一个，缓慢的，看起来像这样：

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

第二个是同一问题的有效解决方案，如下所示：

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

为了满足这些请求，只需单击按钮 Run 在页面顶部。 完成此操作后，我们比较有关查询执行时间的信息。 事实证明，执行一个无效查询比执行第二个查询至少花费 30 倍的时间（这个时间因运行而异；例如，系统可能会报告第一个查询花了 37 毫秒才能完成，但执行第二个查询需要 1 毫秒）。第二个 - XNUMX 毫秒）。

如果有更多数据，那么一切看起来都会更糟（要确信这一点，请看看我的 例子 有 10 万行）。

我们刚刚讨论的内容应该能让您深入了解数据库查询的实际处理方式。

请注意，数值越高 OFFSET — 完成请求所需的时间越长。

我应该使用什么来代替 OFFSET 和 LIMIT 的组​​合？

而不是组合 OFFSET и LIMIT 值得使用根据以下方案构建的结构：

SELECT * FROM table_name WHERE id > 10 LIMIT 20

这是使用基于游标的分页的查询执行。

而不是在本地存储当前的 OFFSET и LIMIT 并在每个请求中传输它们，您需要存储最后收到的主键（通常是 ID）和 LIMIT，结果会得到类似上面的查询。

为什么？ 要点是，通过显式指定最后读取的行的标识符，您可以告诉 DBMS 需要从哪里开始搜索必要的数据。 此外，由于使用了密钥，搜索将高效地进行；系统不必因指定范围之外的行而分心。

下面我们来看看各种查询的性能对比。 这是一个无效的查询。

请求缓慢

这是此请求的优化版本。

快速请求

两个查询返回完全相同的数据量。 但第一个需要 12,80 秒才能完成，第二个需要 0,01 秒。 你感觉到有什么不同吗？

可能的问题

为了使所提出的查询方法有效地工作，表必须有一列（或多列），其中包含唯一的顺序索引，例如整数标识符。 在某些特定情况下，这可能决定使用此类查询是否成功提高使用数据库的速度。

当然，在构建查询时，您需要考虑表的特定架构，并选择最适合现有表的机制。 例如，如果您需要处理大量相关数据的查询，您可能会发现它很有趣 这 文章。

如果我们面临缺少主键的问题，例如，如果我们有一个多对多关系的表，那么使用传统的方法 OFFSET и LIMIT，保证适合我们。 但它的使用可能会导致查询速度变慢。 在这种情况下，我建议使用自动递增主键，即使它只需要处理分页查询。

如果您对这个话题感兴趣—— 这里, 这里 и 这里 - 一些有用的材料。

结果

我们可以得出的主要结论是，无论我们谈论的数据库大小如何，始终需要分析查询执行的速度。 如今，解决方案的可扩展性非常重要，如果从在某个系统上工作的一开始就设计正确，那么将来可以使开发人员免于许多问题。

如何分析和优化数据库查询？

来源： habr.com