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

川长思鸟来 2023-10-11 18:10 3阅读 0赞

随着业务的扩增，用户使用也在呈井喷式增长，不需要担心数据库性能优化问题的日子已经一去不复返了。

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

select * from table limit 10 offset 40

当然，很多人都是使用的如：PageHelper、MP等插件，他们也是将分页语句替我们加进去罢了，其本质还是limit分页。

对于简单的小型应用程序和数据量不是很大的场景，这种方式还是能够“应付”的。

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

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

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

可能也有部分小伙伴还是不太清楚limit和offset的具体含义和用法，我简单说一下：

*  limit y 分句表示: 读取 y 条数据
 *  limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
 *  limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

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

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

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

<table> 
 <tbody> 
  <tr> 
   <td>什么是全表扫描？全表扫描 (又称顺序扫描) 就是在数据库中进行逐行扫描，顺序读取表中的每一行记录，然后检查各个列是否符合查询条件。这种扫描是已知最慢的，因为需要进行大量的磁盘 I/O，而且从磁盘到内存的传输开销也很大。</td> 
  </tr> 
 </tbody> 
</table>

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

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

<table> 
 <tbody> 
  <tr> 
   <td>10万行中的第5万行到第5万零20行 </td> 
  </tr> 
 </tbody> 
</table>

需要先获取 5 万行。这么做是多么低效？

## 2.替代方案 ##

你应该这样做：

select * from table_name where id>10 limit 20

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

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

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

比较下面这两个查询：  
![在这里插入图片描述][5b18d7f1efd64a0498b7867b2902f601.png_pic_center]

优化的版本：  
![在这里插入图片描述][22091558e9ad4047b778b42bac0d26e7.png_pic_center]

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

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

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

如果我们的表没有主键，比如是具有多对多关系的表，那么就使用传统的 OFFSET/LIMIT 方式，只是这样做存在潜在的慢查询问题。所以建议在需要分页的表中使用**自动递增**的主键，即使只是为了分页。

[5b18d7f1efd64a0498b7867b2902f601.png_pic_center]: https://img-blog.csdnimg.cn/5b18d7f1efd64a0498b7867b2902f601.png#pic_center
[22091558e9ad4047b778b42bac0d26e7.png_pic_center]: https://img-blog.csdnimg.cn/22091558e9ad4047b778b42bac0d26e7.png#pic_center