• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。

前言 之前的大多数人分页采用的都是这样：

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查询效率 数据准备

• 【LIMIT和OFFSET分页性能差！今天来介绍如何高性能分页】本文测试使用的环境：
  

  [root@zhyno1 ~]# cat /etc/system-release CentOS Linux release 7.9.2009 (Core)[root@zhyno1 ~]# 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 |

  
  
• 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

• rows in set (0.00 sec)
  

  插入350万条数据作为测试：

  
  

  mysql> SELECT COUNT(*) FROM limit_test;
  COUNT(*)
  3500000

• 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

• 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

• 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

• 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连续的场景且不能有其他字段的排序。

Enjoy GreatSQL :)
## 关于 GreatSQL
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
相关链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：

欢迎来GreatSQL社区发帖提问
https://greatsql.cn/

文章图片

技术交流群：

微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。

文章图片

推荐阅读

• 

  对于韩国化妆品,你有什么看法呢？
• 

  在word中更正拼写和语法 word2010中更正拼写时仅根据主词典提供建议的方法教程
• 

  月饼出油是什么原因
• 

  把软件做成安装包，怎么把软件变成安装包发给别人
• 

  iphone6plus怎样分屏（iphone6plus怎么分屏）
• 

  柳花月叶子打一成语
• 

  怎样洗蚬子才能洗干净 蚬子怎么洗才干净
• 

  cad文字回车后消失 cad文字按回车不见了
• 

  东宫当年有多火 东宫好看吗评价
• 

  冬天还有蚊子怎么办
• 

  如何正确清零富士施乐DocuCentre2056打印机
• 

  黄色毛毛虫是什么品种的 黄色毛毛虫是什么品种
• 

  圣诞祝福语给孩子
• 

  为什么说“药补不如食补”
• 

  长孙和嫡孙区别 长孙和嫡孙是什么
• 

  捡到的钱隔天花可以吗
• 

  sql词义分析,成果结果后果的词义分析
• 

  血玉的价格是多少 血玉的鉴定方法
• 

  面包能放冰箱冷藏，面包买回来放冰箱里能放几天
• 

  如何正确拆卸和安装抽拉式服务器？ 抽拉式服务器怎么拆装

• 简析XDP的重定向机制
• MySQL之COUNT(*)性能到底如何（）
• Mysql 获取表中 对某个字段进行分组 然后在分组之后的基础上 再组内排序 获取 分组后最新或者最大记录
• 简单分析 MySQL 中 NOT IN 比 NOT EXISTS 效率低的原因
• CMAKE编译时如何自动下载第三方库并解压、安装到指定目录