LIMIT和OFFSET分页性能差！今天来介绍如何高性能分页

前言

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

SELECT*FROMtableLIMIT20 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查询效率

数据准备

本文测试使用的环境：

[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 516:57:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

测试数据库采用的是（存储引擎采用InnoDB，其它参数默认）：

mysql>select version();
+-----------+
| version()|
+-----------+
|8.0.25-16|
+-----------+
1 row inset(0.00 sec)

表结构如下：

CREATETABLE `limit_test` (
  `id` int(11)NOTNULL AUTO_INCREMENT,
  `column1` decimal(11,2)NOTNULL DEFAULT '0.00',
  `column2` decimal(11,2)NOTNULL DEFAULT '0.00',
  `column3` decimal(11,2)NOTNULL 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 inset(0.00 sec)

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

mysql>SELECTCOUNT(*)FROM limit_test;
+----------+
|COUNT(*)|
+----------+
|3500000|
+----------+
1 row inset(0.47 sec)

开始测试

首先偏移量设置为0，取20条数据(中间输出省略)：

mysql>SELECT*FROM limit_test LIMIT0,20;
+----+----------+----------+----------+
| id | column1  | column2  | column3  |
+----+----------+----------+----------+
|1|50766.34|43459.36|56186.44|
 #...中间输出省略
|20|66969.53|8144.93|77600.55|
+----+----------+----------+----------+
20 rows inset(0.00 sec)

可以看到查询时间基本忽略不计，于是我们要一步一步的加大这个偏移量然后进行测试，先将偏移量改为10000(中间输出省略)：

mysql>SELECT*FROM limit_test LIMIT10000,20;
+-------+----------+----------+----------+
| id    | column1  | column2  | column3  |
+-------+----------+----------+----------+
|10001|96945.17|33579.72|58460.97|
 #...中间输出省略
|10020|1129.85|27087.06|97340.04|
+-------+----------+----------+----------+
20 rows inset(0.00 sec)

可以看到查询时间还是非常短的，几乎可以忽略不计，于是我们将偏移量直接上到340W(中间输出省略)：

mysql>SELECT*FROM limit_test LIMIT3400000,20;
+---------+----------+----------+----------+
| id      | column1  | column2  | column3  |
+---------+----------+----------+----------+
|3400001|5184.99|67179.02|56424.95|
 #...中间输出省略
|3400020|8732.38|71035.71|52750.14|
+---------+----------+----------+----------+
20 rows inset(0.73 sec)

这个时候就可以看到非常明显的变化了，查询时间猛增到了0.73s。

分析耗时的原因

根据下面的结果可以看到三条查询语句都进行了全表扫描：

mysql> EXPLAIN SELECT*FROM limit_test LIMIT0,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 inset,1 warning (0.00 sec)

mysql> EXPLAIN SELECT*FROM limit_test LIMIT10000,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 inset,1 warning (0.00 sec)

mysql> EXPLAIN SELECT*FROM limit_test LIMIT3400000,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 inset,1 warning (0.00 sec)

此时就可以知道的是，在偏移量非常大的时候，就像案例中的LIMIT 3400000,20这样的查询。

此时MySQL就需要查询3400020行数据，然后在返回最后20条数据。

前边查询的340W数据都将被抛弃，这样的执行结果可不是我们想要的。

接下来就是优化大偏移量的性能问题

优化

你可以这样做：

SELECT*FROM limit_test WHERE id>10limit20

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

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

我们再来一次测试(中间输出省略)：

mysql>SELECT*FROM limit_test WHERE id>3400000LIMIT20;
+---------+----------+----------+----------+
| id      | column1  | column2  | column3  |
+---------+----------+----------+----------+
|3400001|5184.99|67179.02|56424.95|
 #...中间输出省略
|3400020|8732.38|71035.71|52750.14|
+---------+----------+----------+----------+
20 rows inset(0.00 sec)

mysql> EXPLAIN SELECT*FROM limit_test WHERE id>3400000LIMIT20;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 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 inset,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 拿数据。

ELECT *FROM(SELECT id FROM table_name LIMIT3400000,20) a LEFT JOIN table_name b ON a.id= b.id;

#或者是

SELECT*FROM table_name a INNER JOIN(SELECT id FROM table_name LIMIT3400000,20) b USING (id);