别再用 offset 和 limit 分页了，性能太差！

水深无声 2024-05-01 16:29 9阅读 0赞

本文主要去理解 offset 为什么会在大数据量下的查询带来性能问题？思考完后，可以在思考下，如果分库分表，你会这么去分页呢？

不需要担心数据库性能优化问题的日子已经一去不复返了。

随着时代的进步，随着野心勃勃的企业想要变成下一个 Facebook，随着为机器学习预测收集尽可能多数据的想法的出现。

作为开发人员，我们要不断地打磨我们的 API，让它们提供可靠和有效的端点，从而毫不费力地浏览海量数据。

如果你做过后台开发或数据库架构，你可能是这么分页的：

![图片][e4b731dce785426b19d2cb4bad0fd108.png]

如果你真的是这么分页，那么我不得不抱歉地说，你这样做是错的。

你不以为然？没关系。Slack、Shopify 和 Mixmax 这些公司都在用，我们今天将要讨论的方式进行分页。

我想你很难找出一个不使用 OFFSET 和 LIMIT 进行数据库分页的人。对于简单的小型应用程序和数据量不是很大的场景，这种方式还是能够“应付”的。

如果你想从头开始构建一个可靠且高效的系统，在一开始就要把它做好。

今天我们将探讨已经被广泛使用的分页方式存在的问题，以及如何实现高性能分页。

## 1.OFFSET 和 LIMIT 有什么问题？ ##

正如前面段落所说的那样，OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的。

但是，当数据库里的数据量超过服务器内存能够存储的能力，并且需要对所有数据进行分页，问题就会出现。

为了实现分页，每次收到分页请求时，数据库都需要进行低效的全表扫描。

什么是全表扫描？全表扫描 (又称顺序扫描) 就是在数据库中进行逐行扫描，顺序读取表中的每一行记录，然后检查各个列是否符合查询条件。

这种扫描是已知最慢的，因为需要进行大量的磁盘 I/O，而且从磁盘到内存的传输开销也很大。

这意味着，如果你有 1 亿个用户，OFFSET 是 5 千万，那么它需要获取所有这些记录 (包括那么多根本不需要的数据)，将它们放入内存，然后获取 LIMIT 指定的 20 条结果。

也就是说，为了获取一页的数据：

10万行中的第5万行到第5万零20行

需要先获取 5 万行。这么做是多么低效？如果你不相信，可以看看这个例子：

https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com

左边的 Schema SQL 将插入 10 万行数据，右边有一个性能很差的查询和一个较好的解决方案。

只需单击顶部的 Run，就可以比较它们的执行时间。第一个查询的运行时间至少是第二个查询的 30 倍。

数据越多，情况就越糟。看看我对 10 万行数据进行的 PoC：

https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com

现在你应该知道这背后都发生了什么：OFFSET 越高，查询时间就越长。

## 2.替代方案 ##

你应该这样做：

![图片][7023306b9621aa84441f508da2d22045.png]

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

你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查询可能都与此类似。

为什么？因为通过显式告知数据库最新行，数据库就确切地知道从哪里开始搜索（基于有效的索引），而不需要考虑目标范围之外的记录。

比较这个查询：

![图片][7fbd736d0bfae01a79e123bf0487dba1.png]

和优化的版本：

![图片][a3f47781921a13a83684c0bb80ac7965.png]

返回同样的结果，第一个查询使用了 12.80 秒，而第二个仅用了 0.01 秒。

要使用这种基于游标的分页，需要有一个惟一的序列字段 (或多个)，比如惟一的整数 ID 或时间戳，但在某些特定情况下可能无法满足这个条件。

我的建议是，不管怎样都要考虑每种解决方案的优缺点，以及需要执行哪种查询。

如果需要基于大量数据做查询操作，Rick James 的文章提供了更深入的指导：

http://mysql.rjweb.org/doc.php/lists

如果我们的表没有主键，比如是具有多对多关系的表，那么就使用传统的 OFFSET/LIMIT 方式，只是这样做存在潜在的慢查询问题。

我建议在需要分页的表中使用自动递增的主键，即使只是为了分页。

[e4b731dce785426b19d2cb4bad0fd108.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/01/979797c3d99b4ed0bd956213558da546.png
[7023306b9621aa84441f508da2d22045.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/01/d87b3a79d5214651afa880f7537e8707.png
[7fbd736d0bfae01a79e123bf0487dba1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/01/9a90a7b6f814432191d253938548df28.png
[a3f47781921a13a83684c0bb80ac7965.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/01/ed29c92fd0bb49b8adb17da1513f71f1.png

发表评论取消回复

表情：

评论列表（有 0 条评论，9人围观）

还没有评论，来说两句吧...

相关阅读

相关别再用 offset 和 limit 分页了，性能太差！

别再用 offset 和 limit 分页了，性能太差！

水深无声/ 2024年05月01日 16:29/ 0 赞/ 10 阅读

相关 phoenix limit 和 offset 用法。

select “threat\_start\_time” from “demo” limit 12 offset 12; limit 每次返回条数。 o

梦里梦外;/ 2023年03月13日 06:20/ 0 赞/ 186 阅读

相关分页的limit_MySQL 用 limit 分页为什么会影响性能？

一，前言首先说明一下MySQL的版本： mysql> select version();+-----------+ | version() |+

太过爱你忘了你带给我的痛/ 2023年01月06日 01:27/ 0 赞/ 116 阅读

相关别再用offset和limit分页了

> 终于要对MySQL优化下手了，本文将对分页进行优化说明，希望可以得到一个合适你的方案分页优化前言一、表结构二、初探查询效率三、分析

悠悠/ 2023年01月05日 14:13/ 0 赞/ 103 阅读

相关别再用offset和limit分页了

> 终于要对MySQL优化下手了，本文将对分页进行优化说明，希望可以得到一个合适你的方案分页优化前言一、表结构二、初探查询效率三、分析

太过爱你忘了你带给我的痛/ 2022年11月19日 12:48/ 0 赞/ 183 阅读

相关面试官：分页场景（limit, offset）为什么会慢？

你知道的越多，不知道的就越多，业余的像一棵小草！你来，我们一起精进！你不来，我和你的竞争对手一起精进！编辑：业余草 cnblogs.com/wyq178

àì夳堔傛蜴生んèń/ 2022年11月18日 05:55/ 0 赞/ 141 阅读

相关 laravel自定义分页实现方法offset()和limit()

情景：因个人使用layui在开发后台模块，因为layui自带了table模块，是都封装了分页的，并且返回数据格式也是有固定要求的所以我们就不能去使用laravel的快速分页

喜欢ヅ旅行/ 2022年05月16日 09:39/ 0 赞/ 212 阅读

相关别再耍流氓了请别再用strcpy 而用strncpy

分享一下我老师大神的人工智能教程！零基础，通俗易懂！[http://blog.csdn.net/jiangjunshow][http_blog.csdn.net_jiangju

深碍√TFBOYSˉ_/ 2022年04月17日 00:05/ 0 赞/ 349 阅读

相关 Mysql通过Limit和offset进行分页

Limit offset的使用从student表中筛选出10个学生 select from student limit 10 跳过1条数据,从

约定不等于承诺〃/ 2021年09月25日 12:24/ 0 赞/ 443 阅读

相关 mysql分页查询limit中偏移量offset过大导致性能问题

在业务中经常会遇到关于分页的需求，这就会经常会用到`MySQL`中的`limit offset`，`rows`来分段取出每页中需要的数据。但是当数据量足够大的时候，`limit

痛定思痛。/ 2021年06月26日 16:06/ 0 赞/ 593 阅读