分库分表第四篇之分页查询优化方案

Sharding-Jdbc分页修正
Sharding-Jdbc分页修正的优化方案

分页查询在一个系统中一般都是占据了大部分的请求量，所以对于分页的优化就显得很重要。在分库分表中，查询就不能像以前单库那么玩了，不然效率会很低哦，这又是为什么呢？且看本节进行详细说明。

Sharding-Jdbc分页修正

从多个数据库获取分页数据与单数据库的场景是不同的。假设每10条数据为一页，取第2页数据。在分片环境下获取LIMIT 10, 10，归并之后再根据排序条件取出前10条数据是不正确的。举例说明，若SQL为：

SELECT score FROM t_score ORDER BY score DESC LIMIT 1, 2;

不进行Sql改写的分页执行结果：

image.png

通过图中所示，想要取得两个表中共同的按照分数排序的第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为例：

image.png

这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后，再获取10条记录，其性能可想而知。而在分库分表的情况下（假设分为2个库），为了保证数据的正确性，SQL会改写为：

image.png

即将偏移量前的记录全部取出，并仅获取排序后的最后10条记录。这会在数据库本身就执行很慢的情况下，进一步加剧性能瓶颈。因为原SQL仅需要传输10条记录至客户端，而改写之后的SQL则会传输1,000,010 * 2的记录至客户端。

Sharding-Jdbc的优化

（1）采用流式处理 + 归并排序的方式来避免内存的过量占用。由于SQL改写不可避免的占用了额外的带宽，但并不会导致内存暴涨。
与直觉不同，大多数人认为Sharding-JDBC会将1,000,010 * 2记录全部加载至内存，进而占用大量内存而导致内存溢出。但由于每个结果集的记录是有序的，因此Sharding-JDBC每次仅获取各个分片的当前结果集记录，驻留在内存中的记录仅为当前路由到的分片的结果集的当前游标指向而已。对于本身即有序的待排序对象，归并排序的时间复杂度仅为O(n)，性能损耗很小。

（2）Sharding-JDBC对仅落至单分片的查询进行进一步优化。落至单分片查询的请求并不需要改写SQL也可以保证记录的正确性，因此在此种情况下，Sharding-JDBC并未进行SQL改写，从而达到节省带宽的目的。

分页优化方案：

由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案：

image.png

或通过记录上次查询结果的最后一条记录的ID进行下一页的查询：

image.png

@Autowired
    private OrderInfoRepository orderInfoRepository;
    
    @Test
    public void testPage() {
        Pageable pageable = PageRequest.of(1,2,Sort.by("oid"));
        Page<OrderInfo> page =  orderInfoRepository.findAll(pageable);
        List<OrderInfo> list = page.getContent();
        for(OrderInfo info:list) {
            System.out.println("id="+info.getOid()+",status="+info.getStatus());
        }
    }