乐趣区

关于mysql:LIMIT和OFFSET分页性能差今天来介绍如何高性能分页

jiezi
  • 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 查问效率

数据筹备

  • 本文测试应用的环境：

    [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 社区助手 微信好友，发送验证信息 加群。

退出移动版