前言
随着业务的演进, 单表数据量增加到一定数量级时, 应该有一些小伙伴注意到这样一个现象, 查询分页时, 深度越深, 数据响应效率越低, 这是什么原因造成的呢? 一起带着问题来看吧
MySQL大数据量下的分页问题
题目: 假设有一个有5000W数据量的业务表,查询第一页跟查询最后一页有什么区别?
首先明确一点,无论是自行实现分页还是依靠
PageHelper或者MyBatis的分页插件来实现分页效果,最终体现在sql中的依然是依靠LIMIT和OFFSET来实现的, 计算分页参数的方式为limit (curPage-1)*pageSize<LIMIT>,pageSize<OFFSET>
接下来模拟下面试官口中的场景, 假设现有一个100W数据的业务表 a, 在不考虑索引问题,使用相同的查询条件,每页查询50条.那么根据以上的计算分页参数的结果, 进行第一页分页查询, 那么LIMIT为0 , OFFSET 为 50
select * from a limit 0, 50
查询耗时为 [28ms]
接下来我们查询第100页, 那么查询的分页参数 LIMIT 为 4950, OFFSET 为 50
select * from a limit 4950, 50
查询结果总耗时
43ms, 比查询第一页总耗时多出了15ms
那么我们再加大页深,查询第500页
select * from a limit 9950, 50
本次耗时共57ms.
点到为止,我就不往后继续分页了, 回到一开始进行实验时给定的条件,不考虑索引, 使用相同的查询条件,由以上三种结果得出一个结论,那就是
** 当对MySQL数据表数据进行分页时,查询页数越深, 查询效率越慢**
衍生的问题: 为什么查询页数越深, 查询效率越慢
在<高性能MySQL>一书中也给出答案
MySQL优化器不是万能的,在开发者进行分页查询时, 会查询出符合查询条件的 ( (curPage-1)*pageSize) + pageSize 条数据, 然后 (curPage-1)*pageSize条数据都会被抛弃,只保留pageSize条数据, 很明显,这样的方式无论数据量还是IO, 代价都是很大的.
那该如何优化大数据量情况下的分页查询呢?
1-限制最大页深度
可以根据自己的业务来限制最大分页页深度, 比如百度最大页深度是76.
但实际最大页深度你可以根据业务与性能间的取舍找到一个平衡点. 但是有些业务是不允许这么做的, 就比如CRM系统的客户列表, 你总不能让客户只能看前N页的数据吧, 哈哈
2-如果你的id是连续自增的
如果你的id是连续自增的, 可以使用id先对查询的目标页做一层过滤, 例如
select id, name, nickname from user_info where id > #{n} limit x, y
这样就避免了在查询数据时全部查询的效率问题, 只查询id从n开始的数据
3-延迟关联-子查询优化
服务端的小伙伴应该都知道什么是子查询吧, 简单来讲 就是在一个查询语句中嵌套了一个查询语句, sql 示例如下
select a* from table1 a ,(select id from table1 where 条件 limit 100000,20)b where a.id=b.id;
总结
面对大数据量深度分页时, 无法从数据库优化器层面去进行优化的话, 可以尽可能的减少分页时扫描的行数,来达到提升效率的问题, 以上三种方法是目前主流的优化方案, 当然, 你也可以自己研究实验, 把可行的方案应用到实际场景中
- 如果对各位有帮助的话, 请点个赞吧
