超过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分页过多时, 如何优化