在实际的软件系统开发过程中，随着使用的用户群体越来越多，表数据也会随着时间的推移，单表的数据量会越来越大。以订单表为例，假如每天的订单量在4万左右，那么一个月的订单量就是120多万
在实际的软件系统开发过程中，随着使用的用户群体越来越多，表数据也会随着时间的推移，单表的数据量会越来越大。
以订单表为例，假如每天的订单量在 4 万左右，那么一个月的订单量就是 120 多万，一年就是 1400 多万，随着年数的增加和单日下单量的增加，订单表的数据量会越来越庞大，订单数据的查询不会像最初那样简单快速，如果查询关键字段没有走索引，会直接影响到用户体验，甚至会影响到服务是否能正常运行！
下面我以某个电商系统的客户表为例，数据库是 Mysql，数据体量在 100 万以上，详细介绍分页查询下，不同阶段的查询效率情况（订单表的情况也是类似的，只不过它的数据体量比客户表更大）。
下面我们一起来测试一下，每次查询客户表时最多返回 100 条数据，不同的起始下，数据库查询性能的差异。
当起点位置在 1000 的时候，仅耗时：23 ms
当起点位置在 10000 的时候，仅耗时：54 ms
当起点位置在 100000 的时候，仅耗时：268 ms
当起点位置在 500000 的时候，仅耗时：1.16 s
当起点位置在 1000000 的时候，仅耗时：2.35 s
可以非常清晰的看出，随着起点位置越大，分页查询效率成倍的下降，当起点位置在 1000000 以上的时候，对于百万级数据体量的单表，查询耗时基本上以秒为单位。
而事实上，一般查询耗时超过 1 秒的 SQL 都被称为慢 SQL，有的公司运维组要求的可能更加严格，如果 SQL 的执行耗时超过 0.2s，也被称为慢 SQL，必须在限定的时间内尽快优化，不然可能会影响服务的正常运行和用户体验。
对于千万级的单表数据查询，使用分页查询，起点位置在 10000000，查询耗时结果：39 秒！
事实上，这还只是数据库层面的耗时，还没有算后端服务的处理链路时间，以及返回给前端的数据渲染时间。
以百万级的单表查询为例，如果数据库查询耗时 1 秒，再经过后端的数据封装处理，前端的数据渲染处理，以及网络传输时间，没有异常的情况下，差不多在 3～4 秒之间。
据互联网软件用户体验报告，当平均请求耗时在1秒之内，用户体验是最佳的，此时的软件也是用户留存度最高的；2 秒之内，还勉强过的去，用户能接受；当超过 3 秒，体验会稍差；超过 5 秒，基本上会卸载当前软件。
有的公司为了提升用户体验，会严格控制请求时长，当请求时长超过 3 秒，自动放弃请求，从而倒逼技术优化调整 SQL 语句查询逻辑，甚至调整后端整体架构，比如引入缓存中间件 redis，搜索引擎 elasticSearch 等等。
继续回到我们本文所需要探讨的问题，当单表数据量到达百万级的时候，查询效率急剧下降，如何优化提升呢？
这篇文章就来讲一讲跨库/表分页查询的解决方案。
架构背景
笔者曾经做过大型的电商系统中的订单服务，在企业初期时业务量很少，单库单表基本扛得住，但是随着时间推移，数据量越来越多，订单服务在读写的性能上逐渐变差，架构组也尝试过各种优化方案，比如前面介绍过的：冷热分离 、查询分离 各种方案。虽说提升一些性能，但是在每日百万数据增长的情况下，也是杯水车薪。
最终经过架构组的讨论，选择了分库分表；至于如何拆分，分片键如何选择等等细节不是本文重点，不再赘述。
在分库分表之前先来拆解一下业务需求：
C端用户需要查询自己所有的订单 后台管理员、客服需要查询订单信息（根据订单号、用户信息.....查询） B端商家需要查询自己店铺的订单信息 针对以上三个需求，判断下优先级，当然首先需要满足C端用户的业务场景，因此最终选用了uid作为了shardingKey
当然选择uid作为shardingKey仅仅满足了C端用户的业务场景，对于后台和C端用户的业务场景如何做呢？很简单，只需要将数据异构一份存放在ES或者HBase中就可以实现，比较简单，不再赘述。
假设将订单表根据hash(uid%2+1)拆分成了两张表，如下图：
假设现在需要根据订单的时间进行排序分页查询（这里不讨论shardingKey路由，直接全表扫描），在单表中的SQL如下：
select * from t_order order by time asc limit 5,5;
这条SQL非常容易理解，就是翻页查询第2页数据，每页查询5条数据，其中offest=5
假设现在t_order_1和t_order_2中的数据如下：
以上20条数据从小到大的排序如下：
t_order_1中对应的排序如下：
t_order_2中对应的排序如下：
那么单表结构下最终结果只需要查询一次，结果如下：
分表的架构下如何分页查询呢？下面介绍几种方案
1. 全局查询法
在数据拆分之后，如果还是上述的语句，在两个表中直接执行，变成如下两条SQL：
select * from t_order_1 order by time asc limit 5,5;
将获取的数据然后在内存中再次进行排序，那么最终的结果如下：
可以看到上述的结果肯定是不对的。
所以正确的SQL改写成如下：
select * from t_order_1 order by time asc limit 0,10;
也就是说，要在每个表中将前两页的数据全部查询出来，然后在内存中再次重新排序，最后从中取出第二页的数据，这就是全局查询法
该方案的缺点非常明显：
随着页码的增加，每个节点返回的数据会增多，性能非常低 服务层需要进行二次排序，增加了服务层的计算量，如果数据过大，对内存和CPU的要求也非常高 不过这种方案也有很多的优化方法，比如Sharding-JDBC中就对此种方案做出了优化，采用的是流式处理 + 归并排序 的方式来避免内存的过量占用。
Sharding-Jdbc分页修正 从多个数据库获取分页数据与单数据库的场景是不同的。 假设每10条数据为一页，取第2页数据。在分片环境下获取LIMIT 10, 10，归并之后再根据排序条件取出前10条数据是不正确的。 举例说明，若SQL为：
SELECT score FROM t_score ORDER BY score DESC LIMIT 1, 2;
不进行Sql改写的分页执行结果：
通过图中所示，想要取得两个表中共同的按照分数排序的第2条和第3条数据，应该是95和90。 由于执行的SQL只能从每个表中获取第2条和第3条数据，即从t_score_0表中获取的是90和80；从t_score_0表中获取的是85和75。 因此进行结果归并时，只能从获取的90，80，85和75之中进行归并，那么结果归并无论怎么实现，都不可能获得正确的结果。
正确的做法是将分页条件改写为LIMIT 0, 3，取出所有前两页数据，再结合排序条件计算出正确的数据。 下图展示了进行SQL改写之后的分页执行结果。
image.png
性能瓶颈 查询偏移量过大的分页会导致数据库获取数据性能低下，以MySQL为例：
select * from t_order order by id limit 100000,10;
这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后，再获取10条记录，其性能可想而知。 而在分库分表的情况下（假设分为2个库），为了保证数据的正确性，SQL会改写为：
select * from t_order order by id limit 0,100000;
即将偏移量前的记录全部取出，并仅获取排序后的最后10条记录。这会在数据库本身就执行很慢的情况下，进一步加剧性能瓶颈。 因为原SQL仅需要传输10条记录至客户端，而改写之后的SQL则会传输1,000,010 * 2的记录至客户端。
Sharding-Jdbc的优化 （1）采用流式处理 + 归并排序的方式来避免内存的过量占用。由于SQL改写不可避免的占用了额外的带宽，但并不会导致内存暴涨。
（2）Sharding-JDBC对仅落至单分片的查询进行进一步优化。 落至单分片查询的请求并不需要改写SQL也可以保证记录的正确性，因此在此种情况下，Sharding-JDBC并未进行SQL改写，从而达到节省带宽的目的。
分页优化方案：
由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案：
select * from t_order where id > 10000 and id <100010 order by id;
或通过记录上次查询结果的最后一条记录的ID进行下一页的查询：
select * from t_order where id > 10000 limit 10;
@Autowired page = orderInfoRepository.findAll(pageable); list = page.getContent();
数据量很大时，可以禁止跳页查询，只提供下一页的查询方法，比如APP或者小程序中的下拉翻页，这是一种业务折中的方案，但是却能极大的降低业务复杂度
比如第一页的排序数据如下：
那么查询第二页的时候可以将上一页的最大值1664088392 作为查询条件，此时的两个表中的SQL改写如下：
select * from t_order_1 where time>1664088392 order by time asc limit 5;
然后同样是需要在内存中再次进行重新排序，最后取出前5条数据
但是这样的好处就是不用返回前两页的全部数据了，只需要返回一页数据，在页数很大的情况下也是一样，在性能上的提升非常大
此种方案的缺点也是非常明显：不能跳页查询，只能一页一页地查询，比如说从第一页直接跳到第五页，因为无法获取到第四页的最大值，所以这种跳页查询肯定是不行的。
3. 二次查询法
以上两种方案或多或少的都有一些缺点，下面介绍一下二次查询法，这种方案既能满足性能要求，也能满足业务的要求，不过相对前面两种方案理解起来比较困难。
还是上面的SQL：
select * from t_order order by time asc limit 5,5;
第一步需要对上述的SQL进行改写：
select * from t_order order by time asc limit 2,5;
注意 ：原先的SQL的offset=5，称之为全局offset，这里由于是拆分成了两张表，因此改写后的offset=全局offset/2=5/2=2
最终的落到每张表的SQL如下：
select * from t_order_1 order by time asc limit 2,5;
执行后的结果如下：
下图中红色部分则为最终结果：
2. 返回数据的最小值
t_order_1 ：5条数据中最小值为：1664088479
t_order_2 ：5条数据中最小值为：1664088392
那么两张表中的最小值为1664088392 ，记为time_min ，来自t_order_2这张表，这个过程只需要比较各个分库第一条数据，时间复杂度很低
3. 查询二次改写
第二次的SQL改写也是非常简单，使用between语句，起点就是第2步返回的最小值time_min，终点就是每个表中在第一次查询时的最大值。
t_order_1这张表，第一次查询时的最大值为1664088581，则SQL改写后：
select * from t_order_1 where time between $time_min and 1664088581 order by time asc;
t_order_2这张表，第一次查询时的最大值为1664088481，则SQL改写后：
select * from t_order_2 where time between $time_min and 1664088481 order by time asc;
此时查询的结果如下（红色部分）：
上述例子只是数据巧合导致第2步的结果和第3步的结果相同，实际情况下一般第3步的结果会比第2步的结果返回的数据会多。
4. 在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset
在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset，下图蓝色部分为虚拟的time_min，红色部分为第2步的查询结果集
因为第1步改后的SQL的offset为2，所以查询结果集中每个分表的第一条数据offset为3（2+1）；
t_order_1中的第一条数据为1664088479 ，这里的offset为3，则向上推移一个找到了虚拟的time_min，则offset=2
t_order_2中的第一条数据就是time_min，则offset=3
那么此时的time_min的全局offset=2+3=5
5. 查找最终数据
找到了time_min的最终全局offset=5之后，那么就可以知道排序的数据了。
将第2步获取的两个结果集在内存中重新排序后，结果如下：
现在time_min也就是1664088392 的offset=5，那么原先的SQL：select * from t_order order by time asc limit 5,5;的结果显而易见了，向后推移一位，则结果为：
刚好符合之前的结果，说明二次查询的方案没问题
这种方案的优点：可以精确地返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量
缺点也是很明显：需要进行两次查询
总结
本篇文章中介绍了分库分表后的分页查询的三种方案：
全局查询法：这种方案最简单，但是随着页码的增加，性能越来越低 禁止跳页查询法：这种方案是在业务上更改，不能跳页查询，由于只返回一页数据，性能较高 二次查询法：数据精确，在数据分布均衡的情况下适用，查询的数据较少，不会随着翻页增加数据的返回量，性能较高
京公网安备 11010802041100号