阅读 563

分页查询中的问题

背景

我们项目中经常会遇到数据库分页查询的场景,如查看用户的历史订单、查看用户的联系人列表等。一般在用户全量数据不可控的时候,我们都会考虑通过分页的方式来获取数据。一方面数据库查询性能可以得到保证,另一方面也可以减少客户端数据的传输。

那些小坑

在数据库分页查询也有些场景需要特别注意,否则会容易遇到坑。

漏数据?

假设有以下场景,我们需要对每一天的订单进行统计对账。订单order表简单信息如下表所示。

属性 类型 备注
id long 自增主键
... ... ...
status int 订单
update_time long 更新时间

因为是对每一天的订单进行统计,我们很容易就想到根据订单更新时间update_time来进行条件过滤。所以基本的查询语句基本如下所示。

select * from order_:tableId where update_time>:starTime and update_time<=:endTime and status=:status order by id asc limit :limit
复制代码

在我们的服务里面会分页获取订单列表,直到获取完所有的订单记录。示例代码如下:

@Resource
private OrderDao orderDao;

public void countAllOrders(int tableId, long startTime, long endTime, int limit) {
    long tempStartTime = startTime;
    while(true) {
        List<OrderRecord> orders = orderDao.getOrderByTime(tableId, tempStartTime, endTime, limit);
        // 统计订单...
        if(order.size()<limit){
            // 当数据条数没达到指定数量时,说明没有更多数据了
            break;
        }
        tempStartTime = orders.get(order.size()-1).getUpdateTime();
    }
}
复制代码

初步看mysql查询语句没什么问题,不过再认真思考下可能会发现这个查询会有隐患,在极端情况下可能会遗漏数据。

假设有几个订单的updateTime刚好相等,又恰好处在分页的边缘,这个时候就会出现订单遗漏的情况。简化说明这种场景,假设订单表有以下3条记录,我们限定返回条数为1。

id ... update_time
1 ... 1552105405000
2 ... 1552105405000
3 ... 1552105405001

这个时候遍历订单数据,会发现我们遍历数据的时候会遗漏id=2的记录。这是因为id=1和id=2的订单记录其update_time是相等的。当遍历完id=1的订单记录后startTime已经被设置为1552105405000,然后就遍历update_time大于1552105405000的记录了,这个时候就跳过了id=2的记录。

以自增ID来迭代

这里会遗漏数据根本原因是updateTime并不是唯一的。我们可以考虑使用自增主键来进行迭代,对应的查询语句修改如下。

-- startTime和endTime仅仅作为条件范围限制,通过id来进行分页迭代
select * from order_:table_id where id>:id and update_time>:starTime and update_time<=:endTime and status=:status order by id asc limit :limit
复制代码

代码也做下相应的调整,使用id为迭代的依据。

@Resource
private OrderDao orderDao;

public void countAllOrders(int tableId, long startTime, long endTime, int limit) {
    long id = 0;
    while(true) {
        List<OrderRecord> orders = orderDao.getOrderByTime(tableId, id, startTime, endTime, limit);
        // 统计订单...
        if(order.size()<limit){
            // 当数据条数没达到指定数量时,说明没有更多数据了
            break;
        }
        id = orders.get(order.size()-1).getId();
    }
}
复制代码

通过自增ID来进行分页迭代可以避免数据遗漏,时间等其它因素可作为筛选的条件。

下一页?

在很多场景下我们都需要知道当前分页是否还有下一页,在客户端可能还需要给一个明确的”已经到底“的提示。可以通过分页数据是否达到指定数量来判断是否还有下一页,不过这种处理方式可能会多一次数据库查询(刚好在前一页返回了最后的数据,却因为数量刚好等于分页数而没办法判断)。

多查询1条数据

其实更简单的处理方式是多查询1条数据,这多出来的1条数据就可以用来判断有没下一页,以及定位下次查询的起始位置nextOffset。若返回的数据条数等于limit+1,那说明还有下一页,否则数据读取已经完成。

特别注意

多查询1条数据需要注意迭代的起始判断,应该是包含nextOffset。因为并没有将最后一条数据返回给客户端,只是用于判断有没下一页以及确定下次拉取的起始位置nextOffset。

小结

1、在分页查询的场景里面尽量使用不重复的主键等来进行迭代;

2、可以通过多查询1条数据来确定当前是否还有下一页数据以及下一页的起始位置;

关注下面的标签,发现更多相似文章
评论