LIMIT和OFFSET分页性能差!今天来介绍如何高性能分页
* G r e a t S Q L 社 区 原 创 内 容 未 经 授 权 不 得 随 意 使 用 , 转 载 请 联 系 小 编 并 注 明 来 源 。
-
前言
-
LIMIT和OFFSET有什么问题
-
初探LIMIT查询效率
-
数据准备
-
开始测试
-
分析耗时的原因
-
优化
-
再优化
-
总结
前言
之前的大多数人分页采用的都是这样:
SELECT * FROM table LIMIT 20 OFFSET 50
可能有的小伙伴还是不太清楚LIMIT和OFFSET的具体含义和用法,我介绍一下:
-
LIMIT X 表示
: 读取 X 条数据 -
LIMIT X, Y 表示
: 跳过 X 条数据,读取 Y 条数据 -
LIMIT Y OFFSET X 表示
: 跳过 X 条数据,读取 Y 条数据
对于简单的 小型应用程序 和 数据量不是很大 的场景,这种方式还是没问题的。
但是你想构建一个 可靠且高效 的系统,一定要一开始就要把它做好。
今天我们将探讨已经被广泛使用的分页方式存在的问题,以及如何实现 高性能分页
。
LIMIT和OFFSET有什么问题
OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的,但是,当数据库里的 数据量超过服务器内存能够存储的能力 ,并且需要对所有数据进行分页,问题就会出现,为了实现分页,每次收到分页请求时,数据库都需要进行低效的 全表遍历 。
全表遍历就是一个全表扫描的过程,就是根据双向链表把磁盘上的数据页加载到磁盘的缓存页里去,然后在缓存页内部查找那条数据。这个过程是非常慢的,所以说当数据量大的时候,全表遍历性能非常低,时间特别长,应该尽量避免全表遍历。
这意味着,如果你有 1 亿个用户,OFFSET 是 5 千万,那么它需要获取所有这些记录 (包括那么多根本不需要的数据),将它们放入内存,然后获取 LIMIT 指定的 20 条结果。
为了获取一页的数据:10万行中的第5万行到第5万零20行需要先获取 5 万行,这么做非常低效!
初探LIMIT查询效率
数据准备
-
本文测试使用的环境:
[[email protected] ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)
[[email protected] ~]# uname -a
Linux zhyno1 3.10.0-1160.62.1.el7.x86_64 #1 SMP Tue Apr 5 16:57:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
-
测试数据库采用的是(存储引擎采用InnoDB,其它参数默认):
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25-16 |
+-----------+
1 row in set (0.00 sec)
表结构如下:
CREATE TABLE `limit_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`column1` decimal(11,2) NOT NULL DEFAULT '0.00',
`column2` decimal(11,2) NOT NULL DEFAULT '0.00',
`column3` decimal(11,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`)
)ENGINE=InnoDB
mysql> DESC limit_test;
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| column1 | decimal(11,2) | NO | | 0.00 | |
| column2 | decimal(11,2) | NO | | 0.00 | |
| column3 | decimal(11,2) | NO | | 0.00 | |
+---------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
插入350万条数据作为测试:
mysql> SELECT COUNT(*) FROM limit_test;
+----------+
| COUNT(*) |
+----------+
| 3500000 |
+----------+
1 row in set (0.47 sec)
开始测试
首先偏移量设置为0,取20条数据(中间输出省略):
mysql> SELECT * FROM limit_test LIMIT 0,20;
+----+----------+----------+----------+
| id | column1 | column2 | column3 |
+----+----------+----------+----------+
| 1 | 50766.34 | 43459.36 | 56186.44 |
#...中间输出省略
| 20 | 66969.53 | 8144.93 | 77600.55 |
+----+----------+----------+----------+
20 rows in set (0.00 sec)
可以看到查询时间基本忽略不计,于是我们要一步一步的加大这个偏移量然后进行测试,先将偏移量改为10000(中间输出省略):
mysql> SELECT * FROM limit_test LIMIT 10000,20;
+-------+----------+----------+----------+
| id | column1 | column2 | column3 |
+-------+----------+----------+----------+
| 10001 | 96945.17 | 33579.72 | 58460.97 |
#...中间输出省略
| 10020 | 1129.85 | 27087.06 | 97340.04 |
+-------+----------+----------+----------+
20 rows in set (0.00 sec)
可以看到查询时间还是非常短的,几乎可以忽略不计,于是我们将偏移量直接上到340W(中间输出省略):
mysql> SELECT * FROM limit_test LIMIT 3400000,20;
+---------+----------+----------+----------+
| id | column1 | column2 | column3 |
+---------+----------+----------+----------+
| 3400001 | 5184.99 | 67179.02 | 56424.95 |
#...中间输出省略
| 3400020 | 8732.38 | 71035.71 | 52750.14 |
+---------+----------+----------+----------+
20 rows in set (0.73 sec)
这个时候就可以看到非常明显的变化了,查询时间猛增到了0.73s。
分析耗时的原因
根据下面的结果可以看到三条查询语句都进行了全表扫描:
mysql> EXPLAIN SELECT * FROM limit_test LIMIT 0,20;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | limit_test | NULL | ALL | NULL | NULL | NULL | NULL | 3491695 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM limit_test LIMIT 10000,20;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | limit_test | NULL | ALL | NULL | NULL | NULL | NULL | 3491695 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM limit_test LIMIT 3400000,20;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | limit_test | NULL | ALL | NULL | NULL | NULL | NULL | 3491695 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
此时就可以知道的是,在偏移量非常大的时候,就像案例中的LIMIT 3400000,20这样的查询。
此时MySQL就需要查询3400020行数据,然后在返回最后20条数据。
前边查询的340W数据都将被抛弃,这样的执行结果可不是我们想要的。
接下来就是优化大偏移量的性能问题
优化
你可以这样做:
SELECT * FROM limit_test WHERE id>10 limit 20
这是一种 基于指针 的分页。你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT,而不是 OFFSET 和 LIMIT,那么每一次的查询可能都与此类似。
为什么?因为通过显式告知数据库最新行,数据库就确切地知道从哪里开始搜索(基于有效的索引),而不需要考虑目标范围之外的记录。
我们再来一次测试(中间输出省略):
mysql> SELECT * FROM limit_test WHERE id>3400000 LIMIT 20;
+---------+----------+----------+----------+
| id | column1 | column2 | column3 |
+---------+----------+----------+----------+
| 3400001 | 5184.99 | 67179.02 | 56424.95 |
#...中间输出省略
| 3400020 | 8732.38 | 71035.71 | 52750.14 |
+---------+----------+----------+----------+
20 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM limit_test WHERE id>3400000 LIMIT 20;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | limit_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 185828 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
返回同样的结果,第一个查询使用了 0.73 sec
,而第二个仅用了 0.00 sec
。
注意:如果我们的表没有主键,比如是具有多对多关系的表,那么就使用传统的 OFFSET/LIMIT 方式,只是这样做存在潜在的慢查询问题。所以建议在需要分页的表中使用自动递增的主键,即使只是为了分页。
再优化
类似于查询 SELECT * FROM table_name WHERE id > 3400000 LIMIT 20;
这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是 ID必须是连续的
,并且查询不能有WHERE语句,因为WHERE语句会造成过滤数据。那使用场景就非常的局限了,于是我们可以这样:
使用覆盖索引优化
MySQL的查询完全命中索引的时候,称为覆盖索引,是非常快的,因为查询只需要在索引上进行查找,之后可以直接返回,而不用再回数据表拿数据。因此我们可以先查出索引的 ID,然后根据 Id 拿数据。
SELECT * FROM (SELECT id FROM table_name LIMIT 3400000,20) a LEFT JOIN table_name b ON a.id = b.id;
#或者是
SELECT * FROM table_name a INNER JOIN (SELECT id FROM table_name LIMIT 3400000,20) b USING (id);
总结
-
数据量大的时候不能使用OFFSET/LIMIT来进行分页,因为OFFSET越大,查询时间越久。
-
当然不能说所有的分页都不可以,如果你的数据就那么几千、几万条,那就很无所谓,随便使用。
-
如果我们的表没有主键,比如是具有多对多关系的表,那么就使用传统的 OFFSET/LIMIT 方式。
-
这种方法适用于要求ID为数值类型,并且查出的数据ID连续的场景且不能有其他字段的排序。
En j o y G r e a t S Q L : )
《零基础学习MySQL 》 视 频 课 程
戳 此 小 程 序 即 可 直 达 B 站
文 章 推 荐 :
G r e a t S Q L 是 由 万 里 数 据 库 维 护 的 M y S Q L 分 支 , 专 注 于 提 升 M G R 可 靠 性 及 性 能 , 支 持 I n n o D B 并 行 查 询 特 性 , 是 适 用 于 金 融 级 应 用 的 M y S Q L 分 支 版 本 。
GreatSQL社区官网:
http://greatsql.cn/G i t e e :
h t t p s : / / g i t e e . c o m / G r e a t S Q L / G r e a t S Q LG i t H u b :
h t t p s : / / g i t h u b . c o m / G r e a t S Q L / G r e a t S Q LB i l i b i l i :
h t t p s : / / s p a c e . b i l i b i l i . c o m / 1 3 6 3 8 5 0 0 8 2 / v i d e o
http://greatsql.cn/
(对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~)
微 信 & Q Q 群 :
可 扫 码 添 加 G r e a t S Q L 社 区 助 手 微 信 好 友 , 发 送 验 证 信 息 “ 加 群 ” 加 入 G r e a t S Q L / M G R 交 流 微 信 群 , 亦 可 直 接 扫 码 加 入 G r e a t S Q L / M G R 交 流 Q Q 群 。
微 信
![](http://mdimg.wxwenku.com/getimg/356ed03bdc643f9448b3f6485edc229baa750da789efbcda1a44a4d0d967d32d10448de16bd69abc638423ef2364e2de.jpg)
Q Q
想 看 更 多 技 术 好 文 , 点 个 “ 在 看 ” 吧 !
- LIMIT和OFFSET分页性能差!今天来介绍如何高性能分页
- MySQL8.0修改lower_case_table_names参数导致重启失败
- Linux利用Cgroup资源控制
- 包拯断案 | 别再让慢sql背锅@还故障一个真相
- 浅析TIMESTAMP类型
- 同样是删用户,为啥还有差别?
- MySQL主键自增值为什么有“空洞”?
- MySQL 存储过程运行的内存管理
- 数据中间件如何与MySQL数据同步?
- MySQL存储过程中包含HINT导致升级失败纪实
- MySQL报障之coredump收集处理流程
- MySQL 8.0有趣的新特性:CHECK约束
- 技术分享 | 微服务架构的数据库为什么喜欢分库分表?
- 技术分析 | 浅析MySQL与ElasticSearch的组合使用
- 构建MySQL智能化高可用架构
- 面向开发的内存调试神器,如何使用ASAN检测内存泄漏、堆栈溢出等问题
- 有事务冲突时节点怎么加入MGR集群
- 重磅丨生态共荣——万里数据库推动GreatSQL携手openEuler 共建数据库产业生态
- 为MySQL MGR实现简单的负载均衡代理
- 4.直方图介绍和使用|MySQL索引学习