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大小
- 联合索引情况,避免跨列使用