超过100w条数据表的分页查询优化

目录

问题描述

解决方案

参考文献


问题描述

        最近做一个运营商的项目,其中有一个需求就是需要将用户所有的通话记录保存起来,支持按照各种条件查询。最开始开发阶段,使用的单表存储,后来根据调研,确定每天的通话量至少在100w通以上,那就只能进行分表存储,不然单表的数据量太大,后面的统计查询功能就没办法做了。按照天,每天一张表存储,但是即便这样,每天的数据量也在100w条上下,前端进行分页查询的时候,还是非常的慢,查询一次要7到8秒,这完全不能接受。

        这是因为,目前的分页查询,使用的就是最基本的OFFSET的方式,如下:

select xx from table order by xx limit xx,xx

        查询的时候,越往后分页越慢。

解决方案

        这篇博文中博主说了好几种改造的思路,都非常好,我们最后选了第一种方法来实现。这种方案其实比较直观,简单来说,就是通过改变查询基数数据大小,结合索引字段来完成的。 映射到实际开发中,我么可以通过给表添加自增主键,然后前端每次点击分页查询的时候,都把当前页最后一条记录的自增id值传递到后端,后端通过该id值去筛选基数数据大小。理解起来不复杂,但是在改造的过程有几个细节点,这里记录下来。

        (1)首次进入页面,或者首次查某一个数据的时候,默认基数还是全部数据,因为需要获取到总条数。

        (2)向后翻页的时候,前端需要把当前页的页码PageNo、预期跳转的页码ToPageNo、当前的分页大小PageSize、当前页最后一条记录的自增主键id值EndIdValue传递给后端。后端首先要确定好StartPage的大小,也就是limit的第一个值的大小,即第几页。这里要注意,不能直接使用ToPageNo,实际的StartPage = ToPageNo - PageNo。因为此时的基数数据是会排除掉PageNo页及其以前的数据的,SQL参考代码如下:

select xx from table_day_xx where id>C order by id desc limit A B

        其中A =ToPageNo-PageNo,B=PageSize,C=EndIdValue

        (3)向前翻页的时候,比向后要简单些,前端需要将当前的页面PageNo,预期跳转的页面ToPageNo、当前的分页大小PageSize、当前页第一条记录的自增主键Id值StartIdValue传递给后端。这里limit的时候,可以直接使用ToPageNo,因为基数数据就是从头开始的,SQL参考如下:

select xx from table_day_xx where id<C order by id desc limit A B

        其中A=ToPageNo,B=PageSize,C=StartIdValue

        注:

        (1)分页使用的Mybatis的PageHelper的分页插件,所以默认情况是可以正常获取到基数数据的总大小及总页码的。 

        (2)前端在分页交互的时候,动作不同,对应后端查询SQL是有区别的,所以我们需要写三个SQL,分别对应首次进入、向后翻页、向前翻页三种情况的SQL。

        (3)其实这么大的数据量,存储到MySQl是不合理的,应该入到hbase,写到hadoop中比较合理的,毕竟这个查询和分析不需要那么实时,做离线的分析查看即可。

参考文献

【1】MySQL分页查询越来越慢?是时候该优化了!

【2】SQL分页过多时, 如何优化

上一篇:aria2


下一篇:nga网页版开发参考