注:以下sql及数据库截图均为测试数据,无实际用途!
分页查询???
难道不是 limit pageId,pageSize ???
新建测试订单表lp_bank_order,以create_day分天记录,
数据量分别为十万级、百万级、亿级测试,
分析如下:
Mysql 在使用limit pageId,pageSize时,随着数据量的增大,性能会急剧下降
select * from lp_bank_order t where t.create_day=20181014 limit 0,10; -- 0.026
select * from lp_bank_order t where t.create_day=20181014 limit 1000,10; -- 0.032
select * from lp_bank_order t where t.create_day=20181014 limit 5000,10; -- 0.058
select * from lp_bank_order t where t.create_day=20181014 limit 10000,10; -- 0.085
select * from lp_bank_order t where t.create_day=20181014 limit 50000,10; -- 0.248
select * from lp_bank_order t where t.create_day=20181014 limit 100000,10; -- 0.451
那么问题来了,必然随着数据增多,瓶颈会越来越大?
如何实现过亿数据的分页查询,就成了一个问题!!!
常规思想行不通
转换思路!!!
-
解决思路1
Limit pageId, pageSize有瓶颈
但是
Limit pageSize是OK的 -
解决思路2
Limit pageSize是OK的,但是要取到每一段,还需要一个条件控制
推荐如下:
id
create_time
所以我们诞生了如下sql:
select * from lp_bank_order t
where
t.create_day=20181014
and t.id > 254671932
limit 10;
OK,大功告成,收工走人???
细心的人已经发现…
id是无序的
发现问题,开启排序操作:
select * from lp_bank_order t
where
t.create_day=20181014
and t.id > 254671932
order by t.id ASC
limit 10;
走到这里,往下翻页基本搞定…
处理往上翻页~~
处理往上分页:
select * from lp_bank_order t
where
t.create_day=20181014
and t.id < 254671933
order by t.id DESC
limit 10;
有没有发现幺蛾子…
往上翻页因为倒序排过,返回的列表是倒序的…
继续改进:
select * from
(
select * from lp_bank_order t
where
t.create_day=20181014
and t.id < 254671933
order by t.id DESC
limit 10
) m
ORDER BY m.id;
此时此刻,这个sql已经可以支持表内数据几百万了,查询速度刚刚滴。。。
新幺蛾子又出现了…
表内达到上亿数据的时候,就运行不出来了。。。
分析原因 -->
通过运行sql的使用索引发现,这个sql只用了主键id的索引,并没有用上create_day索引
造成了order by id的时候,是全表排序的,才会导致很慢
获取并缓存ID
每次查询最大最小ID需要耗时:1-2秒 所以需要缓存起来!!!
截止此时,分页查询的核心脚本已经OK…
我们还可以继续优化的事项:
- 部分缓存数据现在都是首次查询的时候缓存的,可以使用脚本去提前缓存,这样首次查询一样可以享受快速查询
- 订单查询的缓存时间虽然缩短至10秒,但是方案有待进一步去优化
… …
最后送大家一句话:
世上没有绝望的处境,只有对处境绝望的人。 —— 费洛姆