一、深分页为什么慢

深分页指的是形如 select ... from ... where ... order by ... limit offset, size 的查询语句中 offset 特别大的情况。高性能 MySQL(第三版) 的第 6.7.5 节专门讲了此问题，但比较简略。

有多慢

假设有一张 page 表包含 600 多万条数据，分别执行下面两条语句：

1
2
3
4
5
6
7


# SQL 一
select * from page order by id limit 0, 10;
# 10 rows in set (0.001 sec)

# SQL 二
select * from page order by id limit 6000000, 10;
# 10 rows in set (0.940 sec)

注意到 SQL 二其实是按主键 ID 排序的(意味着不需要进行 filesort，直接按主键索引顺序扫描即可)，耗时仍然高达 0.940 秒。

为什么慢

『查询计划』

1
2
3
4
5
6


explain select * from page order by id limit 6000000, 10;
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
|    1 | SIMPLE      | page | index | NULL          | PRIMARY | 4       | NULL | 5988448 |       |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------+

『慢查询日志』

1
2
3
4
5
6
7


# Time: 221106  0:26:54
# User@Host: root[root] @ localhost []
# Thread_id: 18  Schema: test  QC_hit: No
# Query_time: 0.939618  Lock_time: 0.000735  Rows_sent: 10  Rows_examined: 6000010
# Rows_affected: 0  Bytes_sent: 533
SET timestamp=1667665614;
select * from page order by id limit 6000000, 10;

注意到 rows 和为 rows_examined 分别为 5988448、6000010。原因显而易见：为了完成 limit offset, size 这样的查询， MySQL 要扫描至少 offset + size 行数据；offset 越大，则扫描次数越多，速度越慢。

二、深分页怎么优化

分页从原理上来讲，主要是两种：

一种就是前面讲的基于 limit offset, size 的分页，英文一般叫 offset pagination；
另一种方法放弃了 offset，改用 left off, SQL 形如 SELECT ... WHERE ... AND id < $left_off ORDER BY id DESC LIMIT 10, 英文一般叫 cursor pagination, 也有人称之为 seek method 或 keyset pagination。

Offset pagination

就像基于比较的排序算法的平均时间复杂度的下界是 O(nlogn) 一样，对于 MySQL 来说，凡是形如 limit offset, size 的查询，扫描次数的下限就是 offset + size，我们只能尽量逼近这个下限。由于在主键索引上的优化比较复杂，同样的 SQL 对于不同的数据规模效果不一样，暂不讨论。这里主要分析在二级索引上的优化。

对于拥有 600 多万条数据的 page 表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 原 SQL(强制走二级索引)
select * from page force index(idx_name) order by name limit 6000000, 10;
# 10 rows in set (4.833 sec)

-- 优化方案一
select t1.* from page t1, (select id from page order by name limit 6000000, 10) t2 where t1.id = t2.id order by t1.name;
# 10 rows in set (0.746 sec)
-- 优化方案二
select t1.* from page t1 join (select id from page order by name limit 6000000, 10) t2 on t1.id = t2.id order by t1.name;
# 10 rows in set (0.741 sec)

通过使用利用了覆盖索引的子查询，性能提升约 80%~90%：

Keyset pagination

1
2
3
4


# First page (latest 10 items):
    SELECT ... WHERE ... ORDER BY id DESC LIMIT 10
# Next page (second 10):
    SELECT ... WHERE ... AND id < $left_off ORDER BY id DESC LIMIT 10

三、优化为什么有效

优化前

考虑原始 SQL select * from page force index(idx_name) order by name limit 6000000, 10 的执行过程：

首先 Sever 层向 InnoDB 请求第一条数据；InnoDB 从 idx_name 上获取第一条二级索引记录，然后查询聚簇索引获取完整记录(即回表操作)，返回给 Server 层;
由于存在 limit 6000000 的限制, Server 层会将该数据丢弃并计数，然后向 InnoDB 请求下一条数据；
上述操作重复 600 万次；
之后的第 6000001~6000010 10 条数据则会被放入本地网络缓冲区，发给客户端；

问题在第 1 步的回表操作：

回表首先意味着先读一次二级索引，然后读一次聚簇索引，因此记录的扫描总数实际会是 2 * 600 万次=1200 万次；
其次，回表操作可能需要一次磁盘随机读；

优化后

再考虑优化后的 SQL select t1.* from page t1 join (select id from page order by name limit 6000000, 10) t2 on t1.id = t2.id order by t1.name 的执行过程：

首先 Sever 层向 InnoDB 请求第一条数据；InnoDB 从 idx_name 上获取第一条二级索引记录，~~然后查询聚簇索引获取完整记录(这一操作叫回表)~~，然后将主键 ID 返回给 Server 层;
由于存在 limit 6000000 的限制, Server 层会将该数据丢弃并计数，然后向 InnoDB 请求下一条数据；
上述操作重复 600 万次；
之后的第 6000001~6000010 10 条数据则会被放入内存缓冲区；
查询聚簇索引，获取 10 个主键 ID 对应的完整记录；

总结

在优化后：

记录的总扫描次数从 1200 万次 减少到 600 万零 10 次；
磁盘随机读的次数从 600 万次 减少到 10 次；

四、试验方法

建表和数据初始化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


-- 建表
CREATE TABLE `page`
(
    `id`      INT(11) NOT NULL AUTO_INCREMENT,
    `name`    VARCHAR(16)  DEFAULT NULL,
    `content` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_name` (`name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- 创建存储过程用于初始化数据
DELIMITER ;;
CREATE PROCEDURE init_page()
BEGIN
    DECLARE i INT;
    SET i = 1;
    WHILE(i <= 6000010)

        DO
            INSERT INTO page (`name`, `content`) VALUES (CONCAT('小瓦', i), CONCAT('xx', i));
            SET i = i + 1;
        END WHILE;
END;;
DELIMITER ;

-- 调用存储过程
CALL init_page();

启用慢查询日志

1
2
3
4
5
6


-- 启用慢查询日志
SET GLOBAL slow_query_log=1;
-- 将慢查询时间阈值设置为 0.1 秒
SET GLOBAL long_query_time=0.1;
-- 查看慢查询日志名
SHOW VARIABLES LIKE '%slow_query%';

对比 SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- 探索一级索引
--- 基础 SQL
select * from page order by id limit 6000000, 10;
--- 最慢
select * from page where id >= (select id from page order by id limit 6000000, 1) order by id limit 10;
--- 最快
select t1.* from page t1, (select id from page order by id limit 6000000, 10) t2 where t1.id = t2.id order by t1.id;

-- 探索二级索引
--- 基础 SQL，会是全表扫描，最慢
select * from page order by name limit 6000000, 10;
--- 强制走二级索引，稍快
select * from page force index(idx_name) order by name limit 6000000, 10;
--- 利用使用了覆盖索引的子查询，减少回表，最快
select t1.* from page t1, (select id from page order by name limit 6000000, 10) t2 where t1.id = t2.id order by t1.name;

查看慢查询日志和数据存储位置

1
2
3
4
5
6


# 查看 mysql 进程参数
ps aux | grep mysql
# /opt/homebrew/opt/mariadb/bin/mariadbd --basedir=/opt/homebrew/opt/mariadb --datadir=/opt/homebrew/var/mysql --plugin-dir=/opt/homebrew/opt/mariadb/lib/plugin --log-error=/opt/homebrew/var/mysql/zmz.local.err --pid-file=zmz.local.pid

# --basedir=/opt/homebrew/opt/mariadb 即慢查询日志和数据文件所在目录
# ps. 执行前面的存储计划，创建 100 万条数据，占用磁盘空间大约 100MB；创建 600 万条数据，占用空间大约 600MB；

Mysql 的深分页问题及优化方法

讨论 MySQL 深分页查询为什么慢，及如何优化