[数据库]提升分页查询性能

最新推荐文章于 2024-05-04 10:41:56 发布

关于0318

最新推荐文章于 2024-05-04 10:41:56 发布

阅读量562

点赞数

文章标签： pagination 分页 web后端

本文链接：https://blog.csdn.net/csdn372301467/article/details/107248697

版权

问题描述

分页是数据库常见操作，也是很简单的操作：

select * from Goods limit 100, 10;

如上，是查询第 100～110 条数据。

而数据量非常大时，这种查询语句就……

mysql> select * from Goods limit 230000, 3;
+--------+-------+--------------+---------------------+
| id     | title | subtitle     | createdAt           |
+--------+-------+--------------+---------------------+
| 230001 | abc   | abccccabcccc | 2020-07-10 03:37:59 |
| 230002 | abc   | abccccabcccc | 2020-07-10 03:37:59 |
| 230003 | abc   | abccccabcccc | 2020-07-10 03:37:59 |
+--------+-------+--------------+---------------------+
3 rows in set (0.08 sec)

查询时间变长了。而且数据越多，时间越长。

解决方案

sql 语句中，limit 后面的两个数，一个是“起始位置”，一个是“条数”。
而 起始位置 ≈≈ 起始id（比如，第 10000 条数据第 id 差不多就是 10000）。
所以：

mysql> select * from Goods where id > 230000 limit  3;
+--------+-------+--------------+---------------------+
| id     | title | subtitle     | createdAt           |
+--------+-------+--------------+---------------------+
| 230001 | abc   | abccccabcccc | 2020-07-10 04:02:15 |
| 230002 | abc   | abccccabcccc | 2020-07-10 04:02:15 |
| 230003 | abc   | abccccabcccc | 2020-07-10 04:02:15 |
+--------+-------+--------------+---------------------+
3 rows in set (0.02 sec)

而且，随着数据的增多，这种方案所需的查询时间几乎不变。

但是，使用“约等于”、“差不多”这样的词汇，是很不严谨的、很不负责任的行为。
只要删除过数据，起始位置就不可能等于起始 id。

但是，让我们分析一下分页的逻辑流程，现在一般是这样的：

前端传来页数（pageIndex）、每页条数（pageSize）
后端计算出起始位置（pageStart = pageIndex * pageSize）
使用 sql 查（limit pageStart, pageSize）

让我们再倒退一步，用户最开始看到的，一般是第一页吧，那么：

select * from Goods limit pageSize;

查询第二页时，就可以使用当前页最后一条数据的 id：

把“最后一条数据的 id（记作 lastId）”、pageSize 传给后端
select * from Goods where id > lastId limit pageSize;

而“上一页”就可以使用当前页的第一条数据的 id 。

这很符合现在的“滚动加载”、“下拉加载”。国外友人把这种方案叫做：cursor based pagination。

但是，有些情况下，我们需要“跳页”。比如看着第 100 页数据时，突然想看第 188 页的数据。
于是上面的解决方案就不再可用。但是稍微改一下：

跟之前一样，把“最后一条数据的 id（记作 lastId）”、pageSize 传给后端
select * from Goods where id > lastId limit (88*pageSize), pageSize;

这是传统方案与 cursor based pagination 方案的结合。

最后

这篇记录，并没有总结出一个具体的方案。因为具体方案涉及的问题有很多：

使用的哪款数据库（对应不同的 sql 规范）
使用的哪款编程语言
业务的具体要求（很多场景下就不需要跳页）

给出所有场景下的具体方案只是一个时间问题。但是不给出所有的具体方案却不是因为时间，因为为了让你的屏幕在这个网页上多停留一些时间，这些时间是值得付出的。
但是，那却是无意义的，或者说几乎无意义的。本篇的目的在于讲明一个问题，并且给这个问题提供一种解决思路。
所谓二八定律，这里就是那个“二”。
太具体的解决方案往往会让人迷失于各种细节。