order by 与 分页 的冲突

order by 与 分页 的冲突

问题背景

Oracle拼接SQL，JAVA使用SQLQueryExecutor执行拼接的SQL，SQL如下：

SELECT col_key, col_other_info 
FROM tb_tableName 
WHERE col_where_info = '一些筛选条件' 
order by col_updatetime desc 

该表中的数据涉及到批量更新操作，会导致大量数据的col_updatetime列属性值都相同，导致执行sqlExecutor.executeQuery(startRow, endRow)时发现传入的页数不同时，分页的数据会出现重复！

原因分析

结果已经证明现实与想像往往是有差距的，实际SQL执行时并不是按照上述方式执行的。这里其实是sql会对分页操作做优化

Mysql也存在相同的问题

官方文档里面有提到如果你将分页与order by混用，数据库会找到排序的row_count行后立马返回，而不是排序整个查询结果再返回。

如果是通过索引排序，会非常快；

如果是文件排序，所有匹配查询的行（不带分页的）都会被选中，被选中的大多数或者全部会被排序，直到分页要求的row_count被找到了。要求的row_count行一旦被找到，数据库就不会排序结果集中剩余的行了。

如果order by的字段有多个行都有相同的值，数据库是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。

基于这个我们就基本知道为什么分页会不准了，因为我们排序的字段是col_updatetime，正好又有几个相同的值的行，在实际执行时返回结果对应的行的顺序是不确定的。

解决方案

如果想在存在或不存在分页的情况下，都保证排序结果相同，可以额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。