项目收获总结--MySQL深度分页优化

一、深度分页介绍

查询偏移量过大的场景称为深度分页，这会导致查询性能较低，例如：

-- MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

二、数据准备

2.1 建表

执行如下语句建表：

CREATE TABLE `test_big_data` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`add_time` bigint NOT NULL,
`update_time` bigint DEFAULT NULL,
`del` tinyint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.2 创建存储过程初始化数据

CREATE PROCEDURE test_insert_bigdata()
begin
declare i int(11) default 1;
while i < 10000000
DO
INSERT INTO test_big_data(name, add_time, update_time, del) VALUES (CONCAT("itlgitlg",SUBSTRING(MD5(RAND()),1,30)),
(SELECT 1419955200- (FLOOR(1 + (RAND() * 12)) * 2678400) - (FLOOR(1 + (RAND() * 31)) * 86400) - FLOOR(1 + (RAND() * 86400))),
(SELECT 1419955200- (FLOOR(1 + (RAND() * 12)) * 2678400) - (FLOOR(1 + (RAND() * 31)) * 86400) - FLOOR(1 + (RAND() * 86400))), 0);
SET i = i + 1;
END WHILE ;
commit;
end

2.3 执行存储过程，可多窗口执行

call test_insert_bigdata()

2.4 创建联合索引

导入数据后才创建，保证初始化数据速度

CREATE INDEX idx_name_update_time USING BTREE ON test.test_big_data (name,update_time);

2.5 查询当前数据信息

执行语句

// 查看数据库最大id记录
select max(id) from test_big_data

三、深度分页场景

当MyBatis执行SQL语句：

select * from test_big_data where name like 'itlgitlg%' limit 800000,10

该查询分页需要扫描800000 +10行数据，然后丢掉前面800000行记录，同时进行800000+10次回表，总计耗时7.5秒；
limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据;
limit 800000 +10 扫描更多的行数，也意味着回表更多（800000 +10）的次数。

这个任务还是在MySQL没有其他任务处理时的执行情况，若在生产环境，效率会更差，接口响应会更慢，因此这也是一种SQL优化场景。

四、优化方案

4.1 范围查询

当可以保证 ID 的连续性时（有索引更佳），可根据 ID 范围进行分页：

# 查询指定 ID 范围的数据
SELECT * FROM test_big_dataWHERE id > 800000 AND id <= 100010 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询：
SELECT * FROM test_big_dataWHERE id > 800000 LIMIT 10

4.2 子查询

先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快一些。

# 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询
SELECT * FROM test_big_dataWHERE id >= (SELECT id FROM test_big_data limit 8000000, 1) LIMIT 10;

只适用于 ID 是正序的，子查询的结果会产生一张新表，会影响性能，应该尽量避免大量使用子查询。

4.3 采用覆盖索引优化

由于name、update_time创建联合索引，若业务上，所需的字段都在索引上，可以使用覆盖索引来优化SQL，减少数据库的回表操作；如下sql：

select name,update_time from test_big_data where name like 'itlgitlg%' limit 800000,10

sql耗时470ms左右：

避免 InnoDB 表进行索引的二次查询，也就是回表操作，可以把随机 IO 变成顺序 IO 加快查询效率。
不过，当查询的结果集占表的总行数的很大一部分时，可能就不会走索引，自动转换为全表扫描。就算强制索引force index(id) 效果也不明显，需要读取大量的索引页，频繁回表等随机IO。

4.4 延迟关联

优化思路：跟子查询的优化思路是一样的，把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用inner join代替子查询。
先通过二级索引查询主键，再通过主键关联，减少回表提升性能，,优化后SQL为：

select a.* from test_big_data a
inner join
(select id from test_big_data
where name like 'itlgitlg%' limit 800000,10) t
on a.id = t.id

sql耗时为466ms：

ps：

MySQL B+树索引：
InnoDB存储引擎中，索引分主键索引（聚簇索引）和二级索引
主键索引：叶子节点存放的是整行数据
二级索引：叶子节点存放的是主键的值；
回表：在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)。

4.5 标签记录

limit 深度分页问题本质原因：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。
标签记录的思想：就是标记一下上次查询到哪一条，下次再来查的时候，从该条开始往下扫描。
则SQL可以修改为：

select * from test_big_data where name like 'itlgitlg%' and id > 800000 order by id asc limit 0,10

执行耗时10ms左右：

求按照id连续查询，或者其他连续自增字段查询（如更新时间）每次查询后将最大值返给前端；下一次查询时，前端将最大值带到后端查询，实际中可能很多场景不支持。

4.6 单路排序和双路排序

4.6.1 单路排序

• 根据条件将所有查询字段数据取出到sort buffer缓冲区。
• 缓冲区若满，根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。
• 将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。
• 从临时文件中读取满足分页条件所需数据返回，如果首次归并就可以取到分页数据则直接返回（浅分页）。

4.6.2 双路排序

• 根据查询条件将row_id和排序字段取出放到sort buffer （区别点1）。
• 缓冲区若满，根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。
• 将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。
• 从临时文件中读取满足分页条件的row_id，再通过row_id读取对应行数据返回（区别点2）

MySQL在4.1之前都是双路排序，之后优化改为满足条件默认单路排序，条件为：查询字段数据大小小于max_length_for_sort_data值，但改到最小值测试也没有看到变化。

因此，子查询方式快的原因是：子查询只取create_time+id到sort buffer（相当于双路排序的做法）, 相比直接查询，省去绝大部分字段，减少大量临时文件IO操作，因此提高查询效率。

另一个方法调整sort_buffer_size大小，锦上添花，不能做为深分页的优化方案

4.6 使用GTID

4.7 换库，使用TiDB

五、总结

避免深度分页，可以从以下方面考虑：

5.1 业务方面

• 可参考谷歌/百度搜索分页，每次只能跳转到当前页前后10页，也就是最多可以跳10页，要想达到深分页情况需要耐心。
• 若前端没有页码不支持跳页，多使用last_* 方式。

5.2 技术方面

• 对没有排序条件的分页查询增加主键排序
• 尽量对排序字段加索引
• 无论是否有索引，当分页页数达到一定阈值强制使用双路排序方式（通过子查询或代码发起两次查询）
• 适当调高sort_buffer_size大小
• 联合索引情况，避免跨列使用