分页查询的排序问题

本文探讨了在Oracle数据库中进行分页查询时遇到的问题,特别是当排序字段不是唯一字段时可能出现的数据重复现象,并提供了两种解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

近日客户说系统导出数据不正确,丢失数据,自己查了看了一下程序没问题,以前测试也没发现。后来定位到是sql语句问题,原来分页sql查询的时候,order by的字段[b]必须是唯一的字段[/b]。
网上有提到这样的,自己留个记号。
SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6236 BCB CCC
6235 AL_U1 CCC
6234 AL_P CCC
6240 BCF_U1 CCC
6239 BCF_P CCC
6238 BCF CCC
6237 BCB_U1 CCC
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

上面例子给出的就是分页查询的标准写法,对于查询前N条数据具有最高的效率。
但是这种分页排序语句存在一个问题:
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6245 BDF_P CCC
6243 BDF_I_BS_KEY CCC
6241 BCF_U2 CCC
6239 BCF_P CCC
6237 BCB_U1 CCC
6236 BCB CCC
6235 AL_U1 CCC
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

对比这次的结果和第一次的结果,就会发现ID为6235的数据出现了两次。第一次在前10条返回记录中,6235出现了,而第二次在11到第20条记录中,6235又出现了。一条数据重复出现两次,就必然意味着有数据在两次查询中都不会出现。
其实造成这个问题的原因很简单,是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性,也就是说,对于键值相等的数据,这种算法完成排序后,不保证这些键值相等的数据保持排序前的顺序。
在这个例子中,OWNER列包含了大量键值为CCC的数据,而且Oracle的排序算法不具有稳定性,因此前10行记录和前20行记录中键值的顺序不能保证一致。因此,就造成某些数据会重复出现,而有些数据不会出现的现象。
解决这个问题其实也很简单。有两种方法可以考虑。
一,在使用不唯一的字段排序时,后面跟一个唯一的字段。
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6235 AL_U1 CCC
6236 BCB CCC
6237 BCB_U1 CCC
6238 BCF CCC
6239 BCF_P CCC
6240 BCF_U1 CCC
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6241 BCF_U2 CCC
6242 BDF CCC
6243 BDF_I_BS_KEY CCC
6244 BDF_I_DF_KEY CCC
6245 BDF_P CCC
6246 BDF_U1 CCC
6247 BP CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6249 BP_P CCC
6250 BP_U1 CCC
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

一般在排序字段后面跟一个主键就可以了,如果表不存在主键,跟ROWID也可以。
这种方法最简单,且对性能的影响最小。另一种方法就是使用前面给出过多次的BETWEEN AND的方法。
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 1 AND 10;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6238 BCF CCC
6240 BCF_U1 CCC
6242 BDF CCC
6244 BDF_I_DF_KEY CCC
6246 BDF_U1 CCC
6255 BRL_U1 CCC
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 11 AND 20;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6254 BRL_P CCC
6253 BRL_I_DTS CCC
6252 BRL_I_BS_KEY CCC
6251 BRL CCC
6250 BP_U1 CCC
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6264 CCF CCC
6263 CCB_U1 CCC
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

这种方式由于采用表数据的全排序,每次只取全排序中的某一部分数据,因此不会出现上面提到的重复数据问题。
但是正是由于使用了全排序,而且ROWNUM信息无法推到查询内部,导致这种写法的执行效率很低。对比二种效率的例子,前面分页查询的文章中以及有很多了,这里就不在重复描述了。虽然这种方式也可以避免重复数据问题,但是不推荐使用这种方式。
### MyBatisPlus 分页查询排序方法 在 MyBatisPlus 中,分页查询可以通过 `Page` 对象实现,并支持通过该对象完成排序操作。以下是具体的实现方式: #### 使用 `addOrder` 方法手动添加排序规则 可以利用 `Page` 类中的 `addOrder` 方法动态添加排序项。具体来说,先创建一个 `OrderItem` 列表,其中每个 `OrderItem` 定义了一个字段及其升序/降序属性[^3]。 ```java import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import java.util.ArrayList; import java.util.List; public class MPtest { @Autowired private ILearningLessonService lessonService; public void test() { // 创建分页对象,参数分别为当前页码和每页大小 Page<LearningLesson> page = new Page<>(1, 10); // 添加排序规则 List<OrderItem> itemList = new ArrayList<>(); OrderItem orderItem = new OrderItem(); orderItem.setColumn("latest_learn_time"); // 指定排序字段 orderItem.setAsc(false); // 设置为降序 itemList.add(orderItem); page.addOrder(itemList); // 执行分页查询并附加条件 lessonService.lambdaQuery() .eq(LearningLesson::getUserId, "2") // 条件过滤 .page(page); // 进行分页查询 } } ``` 上述代码展示了如何通过 `addOrder` 方法向 `Page` 对象中添加排序规则。此方法适用于需要灵活控制多个排序字段的场景。 #### Lambda 查询链式调用内置排序功能 除了手动构建 `OrderItem` 的方式外,还可以直接使用 MyBatisPlus 提供的 lambda 表达式语法,在查询过程中指定排序逻辑[^4]。 ```java import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; public void testLambdaSort() { // 创建分页对象 Page<LearningLesson> page = new Page<>(1, 10); // 构建查询条件并指定排序 LambdaQueryWrapper<LearningLesson> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(LearningLesson::getUserId, "2") // 等值匹配 .orderByDesc(LearningLesson::getLatestLearnTime); // 字段降序排列 // 调用 service 层执行分页查询 lessonService.page(page, queryWrapper); } ``` 这种方式更加简洁明了,适合简单的单字段排序需求。 --- #### 配置分页插件优化性能 需要注意的是,默认情况下 MyBatisPlus 在分页查询时会额外生成一条 SQL (`_count`) 计算总数,这可能导致效率低下特别是当数据量较大时[^2]。如果不需要精确统计总记录数,则可通过调整配置关闭 `_count` 查询。 另外,为了避免因默认限制导致无法返回预期数量的结果(如默认最大 500 条),可重新设置 `PaginationInterceptor` 的上限值[^5]。 ```java @Configuration public class MyBatisPlusConfig { @Bean public PaginationInterceptor mybatisPlusInterceptor(){ PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); paginationInterceptor.setLimit(1000); // 自定义最大分页条目数 return paginationInterceptor; } } ``` 以上代码片段说明了如何扩展 Spring Boot 应用程序上下文中 MyBatisPlus 插件的功能以满足特定业务需求。 --- ### 总结 MyBatisPlus 支持多种方式进行分页查询的同时也提供了丰富的排序选项。无论是基于 `addOrder` 或者 lambda 链式表达式的解决方案都可以很好地应对实际开发过程中的不同复杂度的需求][^[^34]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值