我爱的 SQL。高效分页包含 1 亿条记录的表

最初发表在我的博客上： 您所需要的只是后端。

我超级喜欢数据库。大学的时候我甚至想过自己开发一个数据库管理系统 (DBMS)。现在我既使用关系型数据库管理系统 (RDBMS)，也使用NoSQL解决方案，对此我充满热情。要知道，没有万能的“金锤”，每个问题都有自己的解决方案。或者说，是解决方案的子集。

在“我爱的 SQL <3”系列博客文章中，我将带您了解一些我发现特别有趣的 SQL 解决方案。这些解决方案使用一个包含超过 1 亿条记录的表进行测试。所有示例都使用 MySQL，但其中的一些思路也适用于其他关系数据存储，例如 PostgreSQL、Oracle 和 SQL Server。

本章重点介绍如何使用基于offset主键的分页功能高效扫描大型表。这也称为键集分页。

背景

本章中，我们使用以下数据库结构作为示例。关于用户的典型示例应该适用于任何领域。

CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` varchar(32) NOT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `metadata` text COLLATE utf8_unicode_ci,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uf_uniq_external_id` (`external_id`),
  UNIQUE KEY `uf_uniq_name` (`name`),
  KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

关于结构的一些评论：

external_id列存储以 UUID 格式引用其他系统中的同一用户
name代表Firstname Lastname
metadata列包含 JSON blob，其中包含各种非结构化数据

该表相对较大，包含大约 1 亿条记录。让我们开始学习之旅。

扫描大表

问题：你需要遍历整个表，提取每条记录，在应用程序代码中进行转换，然后插入到另一个位置。我们重点关注后扫描表的第一阶段。

明显且错误的解决方案

SELECT user_id, external_id, name, metadata, date_created
FROM users;

在我这个有 1 亿条记录的例子中，查询永远无法完成。DBMS 直接终止了它。为什么？可能是因为它导致查询尝试将整个表加载到 RAM 中，然后再将数据返回给客户端。另一个假设是——发送数据之前预加载数据花费了太多时间，导致查询超时。
总之，我们及时获取所有记录的尝试失败了。我们需要找到其他解决方案。

解决方案 #2

我们可以尝试按页获取数据。由于表中的记录在物理或逻辑层面上无法保证有序，因此我们需要在 DBMS 端使用ORDER BY子句对其进行排序。

SELECT user_id, external_id, name, metadata, date_created
FROM users
ORDER BY user_id ASC
LIMIT 0, 10 000;

10 000 rows in set (0.03 sec)

太棒了！成功了。我们请求了第一页 10000 条记录，它只用了0.03几秒钟就返回了结果。但是，第 5000 页该如何处理呢？

SELECT user_id, external_id, name, metadata, date_created
FROM users
ORDER BY user_id ASC
LIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size

10 000 rows in set (40.81 sec)

确实很慢。我们看看获取最新一页的数据需要多长时间。

SELECT user_id, external_id, name, metadata, date_created
FROM users
ORDER BY user_id ASC
LIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size

10 000 rows in set (1 min 20.61 sec)

这简直是疯了。不过，对于在后台运行的解决方案来说，这倒是没问题。但如果在扫描过程中尝试从表中删除一条记录，这种方法的另一个隐藏问题就会暴露出来。假设你完成了第 10 页（已经访问了 100,000 条记录），打算扫描 100,001 到 110,000 条之间的记录。但是，在下次SELECT执行之前，第 99,998 条和第 99,999 条记录就被删除了。在这种情况下，以下查询将返回意外结果：

 SELECT user_id, external_id, name, metadata, date_created
 FROM users
 ORDER BY user_id ASC
 LIMIT 100 000, 10 000;

 N, id, ...
 1, 100 003, ...
 2, 100 004, ...

如您所见，查询跳过了 ID 为 100 001 和 100 002 的记录。由于在两次删除操作之后，它们出现在前 100 000 条记录中，因此该方法不会在应用程序代码中处理它们。因此，如果数据集可变，该方法并不可靠。

解决方案 #3 - 今天的最后一个

该方法与前一种方法非常相似，因为它仍然使用分页，但现在我们不再依赖扫描的记录数，而是使用user_id最新访问的记录作为offset。

简化算法：

PAGE_SIZE我们从表中获取记录数。起始偏移值为 0。
使用批次中的最大返回值user_id作为下一页的偏移量。
user_id从值高于当前值的记录中获取下一批offset。

查询针对第 5,000 页，每页包含约 10,000 个用户的数据：

SELECT user_id, external_id, name, metadata, date_created
FROM users
WHERE user_id > 51 234 123 --- value of user_id for 50 000 000th record
ORDER BY user_id ASC
LIMIT 10 000;

10 000 rows in set (0.03 sec)

哇，这比以前的方法快多了，快了1000倍以上。

请注意，的值user_id不是连续的，可能会有间隙，例如 25 348 紧接着 25 345。即使删除了后续页面中的任何记录，该解决方案仍然有效——即使在这种情况下，查询也不会跳过记录。是不是很棒？

解释绩效

为了进一步学习，我建议调查每个版本的查询结果EXPLAIN EXTENDED，以获取 50 000 000 条记录之后的 10 000 条记录。

| Solution          | Time      | Type  | Keys       | Rows | Filtered | Extra
------------------------------------------------------------------------------
| 1. Obvious        | Never     | ALL   | NULL       | 100M | 100.00   | NULL
| 2. Offset paging  | 40.81 sec | index | NULL / PRI | 50M  | 200.00   | NULL
| 3. Keyset paging  | 0.03 sec  | range | PRI / PRI  | 50M  | 100.00   | Using where

让我们关注第二和第三个解决方案的执行计划之间的主要区别，因为第一个解决方案对于大型表实际上没有用。

连接类型：indexvs range。前者表示扫描整个索引树来查找记录。range后者表示索引仅用于在指定范围内查找匹配的行。因此，range后者比更快index。
可能的键：NULLvs PRIMARY。此列显示 MySQL 可以使用的键。顺便说一句，查看“keys”列，我们可以看到最终这PRIMARY两个查询都使用了“key”。
Rows : 50 010 000vs 50 000 000。该值显示在返回结果之前分析的记录数。对于第二个查询，该值取决于滚动的深度。例如，如果我们尝试获取10 000第 9999 页之后的记录，99 990 000则会检查这些记录。相反，第三个查询的值是一个常量；即使我们加载的是最后一页的第一页的数据，它的大小也无关紧要。它始终是表格大小的一半。
已过滤：200.00与100.00。该列表示在处理之前需要过滤的表格的估计百分比。值越高越好。的值100.00表示查询会遍历整个表格。对于第二个查询，该值不是恒定的，并且取决于页码：如果我们查询第一页，则已过滤列的值为1000000.00。对于最后一页，则为100.00。
Extra：NULLvs Using where。提供有关 MySQL 如何解析查询的附加信息。使用WHEREon PRIMARYkey 可以加快查询执行速度。

我怀疑连接类型是对性能贡献最大的查询参数，从而使第三个查询更快。另一个重要的事情是，第二个查询极其依赖于需要滚动的页数。在这种情况下，更深的分页会更慢。

有关理解命令输出的更多指导，EXPLAIN请参阅您的 RDBMS 的官方文档。

概括

这篇博文的主题是关于使用offset主键（键集分页）扫描包含 1 亿条记录的大表。总的来说，我们考察了三种不同的方法，并在相应的数据集上进行了测试。如果您需要扫描可变的大表，我仅推荐其中一种。

此外，我们还修改了用于分析 MySQL 查询执行计划的命令用法EXPLAIN EXTENDED。我相信其他关系数据库管理系统 (RDBMS) 也应该有类似的功能。

下一章我们将关注数据聚合和存储优化。敬请期待！

您扫描大表的方法是什么？

您还记得在主键上使用偏移量的其他用途吗？

文章来源：https://dev.to/backendandbbq/the-sql-i-love-chapter-one