求求你不要再用offset和limit了

- 日理万妓 2023-10-05 17:33 59阅读 0赞

优化慢SQL技巧

`阅读大概3分钟`

### 文章目录  @\[toc\]  前言  1. OFFSET和LIMIT有什么问题  2. 有什么解决方案  再优化 ###

### 前言 ###

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

随着时代的发展，每个新企业家都希望建立下一个Facebook，并结合收集每个可能的数据点以提供更好的机器学习预测的心态，作为开发人员，我们需要比以往更好地准备我们的API，以提供可靠，高效的端点，应该能够毫不费力地浏览大量数据。

如果您已经进行了一段时间的后端或数据库体系结构，则可能已经完成了分页查询，如下所示：

![offset][]

但是，如果你确实建立了这样的分页，很遗憾地说，这样做是错的。

你不以为然？没关系。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

在左侧面板中，您有一个基本架构，该架构将为我们的测试插入100.000行，而在右侧，则是有问题的查询和我们的解决方案。只需单击顶部的“运行”，然后比较每个执行时间。第一个查询：1秒；（问题查询）至少需要30秒钟的时间才能运行。

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

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

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

### 2. 有什么解决方案 ###

这是你应该使用的：

![替换查询方案][2021011118272988.png]

这是基于**游标的分页**。

你应该存储最后收到的主键（通常是一个ID）和LIMIT，而不是在本地存储当前的OFFSET和LIMIT并随每个请求传递它，因此查询最终可能与此类似。

为什么？因为通过显式传递最新的读取行，你可以根据有效的索引键告诉数据库确切从哪里开始搜索，而不必考虑该范围之外的任何行。

以下面的比较为例：

![优化前][20210111182924393.png]

针对我们的优化版本：

![优化后][20210111183009934.png]

接收到的记录完全相同，但是第一个查询花费了12.80秒，第二个查询花费了0.01秒。你能体会到差异吗？

要使用这种基于游标的分页，需要有一个惟一的序列字段 (或多个)，比如惟一的整数 ID 或时间戳，但是在某些特定情况下，这可能不符合我们的需求。

我的建议是始终考虑每种表体系结构的优缺点以及在每种表体系结构中需要执行哪种查询。

如果您需要在查询中处理大量相关数据，Rick James 的文章提供了更深入的指导。  
http://mysql.rjweb.org/doc.php/lists

**结论**

这样做的主要要点是始终检查查询的性能（无论是1k行还是1M行）。可伸缩性至关重要，如果从一开始就正确实施，肯定会避免将来出现许多麻烦。

### 再优化 ###

类似于查询 `SELECT * FROM table_name WHERE id > 8000000 LIMIT 10;` ，这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是ID必须是连续的,并且查询不能有where语句,因为where语句会造成过滤数据.

`SELECT * FROM table_name WHERE id > 8000000 LIMIT 10;`

**覆盖索引优化**

mysql 的查询完全命中索引的时候，称为覆盖索引,是非常快的，因为查询只需要在索引上进行查找，之后可以直接返回，而不用再回数据表拿数据。因此我们可以先查出索引的 ID，然后根据 Id 拿数据。

`select * from (select id from table_name limit 1000000,100) a left join table_name b on a.id = b.id;`

参考来源：https://hackernoon.com/please-dont-use-offset-and-limit-for-your-pagination-8ux3u4y

pub哥 2020 年终知识清单：  
![pub哥的知识清单][pub]

[offset]: https://img-blog.csdnimg.cn/20210111181532630.png
[2021011118272988.png]: https://img-blog.csdnimg.cn/2021011118272988.png
[20210111182924393.png]: https://img-blog.csdnimg.cn/20210111182924393.png
[20210111183009934.png]: https://img-blog.csdnimg.cn/20210111183009934.png
[pub]: https://img-blog.csdnimg.cn/20210111202525394.png