阅读量:0
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大小
- 联合索引情况，避免跨列使用
