分页查询中的问题

背景

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

那些小坑

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

漏数据？

假设有以下场景，我们需要对每一天的订单进行统计对账。订单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条数据来确定当前是否还有下一页数据以及下一页的起始位置；