千万级别的表分页查询优化

一、前言

　　日常项目随着业务扩张，数据库的表数据也会随着时间的推移，单表的数据量会越来越大，这时需要使用分页查询，而分页查询随着页码的增加，查询效率会变得低下。数据量在万级别时，任何查询只要SQL正常，用户体验估计无感知，但是到了千万级别时，则不会那样简单快速。如果查询关键字没有走索引，会直接影响到用户体验，甚至会影响到服务是否能正常运行!

二、问题场景

　　下面我们针对已有的一张客户表来说明分页查询在不同阶段的查询效率情况，数据体量在100万以上。

　　每次查询客户表时最多返回100条数据，不同的起始位置下，数据库查询性能的差异，详见以下查询执行情况：

1. 当起始位置在0的时候，仅耗时：18.1ms

SQL：select * from bizuser order by id limit 0,100;查询时间如下：

2. 当起始位置在1000的时候，仅耗时：23.6ms

SQL：select * from bizuser order by id limit 1000,100;查询时间如下：

3. 当起始位置在10000的时候，仅耗时：54.7ms

SQL：select * from bizuser order by id limit 10000,100;查询时间如下：

4. 当起始位置在100000的时候，仅耗时：268ms

SQL：select * from bizuser order by id limit 100000,100;查询时间如下：

5. 当起始位置在500000的时候，仅耗时：1.16s

SQL：select * from bizuser order by id limit 500000,100;查询时间如下：

6. 当起始位置在1000000的时候，仅耗时：2.35s

SQL：select * from bizuser order by id limit 1000000,100;查询时间如下：

　　可以非常清晰地看出，数据查询数量一致，唯一的区别在于limit的起始位置变化，而随着起始位置越大，分页查询效率会成倍地下降，对于百万级数据体量的单表，查询耗时基本上以秒为单位。    

        对于千万级别的单表数据查询，起始位置在10000000，查询时间竟达到了39s! 截图如下：

　　没有接触过大体量的数据可能会对这种查询结果感到惊讶。事实上，这还仅是数据库层面的耗时，还未计算后端服务处理时间以及返回到前端的页面数据渲染时间。

　　以百万级别的单表查询为例，如果数据库查询耗时1s，再经过后端的数据封装处理，前端的数据渲染处理，以及网络传输时间，在没有异常的情况下，耗时差不多在3~4s之间。据互联网软件用户体验报告表明，当平均请求耗时在1s内，用户体验是最佳的，这种软件也是用户留存度最高的；2s内，用户能接受，还算勉强；超过3s，用户体验感会稍差；超过5s，用户基本上会卸载该软件。有些公司为了提升用户体验，会严格控制请求时长，当请求时长超过3s，自动放弃请求，从而让技术优化调整SQL语句查询逻辑，甚至调整后端整体架构，比如引入缓存中间件Redis，搜索引擎ElasticSearch等等。

下面我们一起来看看当单表数据量到达百万级的时候，查询效率急剧下降，应该要如何优化提升呢?

三、解决方案

方案一：查询时，通过主键ID过滤
　　该方案要求主键ID必须是数字类型，然后取上一次查询结果的ID最大值作为过滤条件，并且排序字段必须是主键ID，不然分页排序会错乱。

1. 查询500000~5000100 区间段的数据，仅耗时：18.9 ms

SQL：select * from bizuser where id > 500332 order by id limit 100;查询时间如下：

2. 查询1000000～1000100 区间段的数据，仅耗时：18.4 ms

SQL：select * from bizuser where id > 1000000 order by id limit 100;查询时间如下：

　　由上可看出，带上主键ID作为过滤条件，查询性能很稳定，基本上查询结果都在20ms内。如果当前业务对排序要求不多，则可采用该方案是可行的，如果对排序有要求，要通过某字段来排序的，那么可以用马上要介绍的方案二。

方案二：查询时，只返回主键ID    

　　该方案是将select * 改成 select id，简化返回的字段。1. 当起点位置在 500000的时候，仅耗时：274ms

SQL：select id from bizuser order by id limit 500000,100;查询时间如下：

2. 当起点位置1000000 的时候，仅耗时：471ms

SQL：select id from bizuser order by id limit 1000000,100;查询时间如下：

　　由上可看出，通过简化返回的字段，是可以很明显地提升查询效率，实际的操作思路就是先通过分页查询满足条件的主键 ID，然后通过主键 ID 查询部分数据，可以明显提升查询效果。

方案三：采用ElasticSearch作为搜索引擎   

　　ElasticSearch可以存储数据和对数据进行搜索分析，它是一个基于Lucene的搜索服务器，它提供了一个分布式多用户能力的全文搜索引擎。

　　当数据量越来越大的时候，尤其是出现分库分表的数据库，通过主键ID进行过滤查询，效果肯定会很差，比较好的解决办法就是将大体量的数据存储到ElasticSearch中，通过ElasticSearch实现快速分页和搜索，效果提升也是非常明显。

四、小结

　　技术选型永远伴随着业务场景的考量，每种数据库都有自己要解决的问题(或者说擅长的领域)，对应的就有自己的数据结构，而不同的使用场景和数据结构，需要用不同的方式，才能起到最大化加快查询的目的。    

        如上文中介绍的表主键ID都是数值类型的，之所以采用数字类型作为主键，是因为数字类型的字段能很好的进行排序，具体的数值类型主键ID的生成方案有很多种，如自增、雪花算法等等，都能很好的满足我们的需求。但如果当前表的主键ID是字符串类型，比如uuid这种，就没办法实现这种排序特性，而且搜索性能也非常差。

免责声明：本公众号部分分享的资料来自网络收集和整理，所有文字和图片版权归属于原作者所有，文章仅供读者学习交流使用，并请自行核实相关内容，如文章内容涉及侵权，请联系后台管理员删除。