Oracle分页查询（效率分析与原理）

布满荆棘的人生 2023-09-27 20:48 22阅读 0赞

### Oracle的分页查询不像MySQL这么友好，要搞清楚Oracle的分页我们需要了解Oracle特有字段rownum ###

> `rownum`：是`Oracle`系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且 `rownum` 不能以任何表的名称作为前缀。
> 
> `rownum`字段无法使用 `>符号` ：如果想找到从第二行记录以后的记录，当使用 `rownum > 2` 是查不出记录的，原因是由于 `rownum` 是一个总是从1开始的伪列，`Oracle` 认为 `rownum > n(n>1的自然数)` 这种条件依旧不成立，所以查不到记录。
> 
> 所以直接对单表 `rownum` 字段的使用 `BETWEEN ... AND ...` 也是不可取的
> 
> 所以我们需要用到子查询的方式来实现分页查询：就是我们先把数据查出来，利用 `rownum` 得到的字段进行分页

#### 第一种方法 ####

##### 无ORDER BY排序 #####

> 写法1：虽然别的博主说这样写的效率快，因为
> 
> **写法2** 需要将内层的结果集全部排序，再从中取出需要的部分；而 **写法1** 只需要获取排序后`<= #{pageNo} * #{pageSize}`部分的结果就可以了。一般分页查询访问前面部分页面的几率较大，内层查询的结果集越大，性能差距越明显。如果是访问分页的最后部分的页面，基本上就没什么差别了。
> 
> 但是在我测试了 `3w` 条数据量的情况下，区别不是很大，但大家尽量选 **写法1** ，因为光看 SQL 语句看着也是 **写法1** 快一些

select b.*,rn from (
            select a.*,rownum rn
            from TABLE1 a
            where 1=1 and rownum <= #{pageNo} * #{pageSize}
     ) b where b.rn >= (#{pageNo} - 1) * #{pageSize} + 1

> 写法2

select b.*,rn from (
            select a.*,rownum rn
            from TABLE1 a
            where 1=1
     ) b where b.rn >= (#{pageNo} - 1) * #{pageSize} + 1 and b.rn  <= #{pageNo} * #{pageSize}

##### 有ORDER BY排序 #####

如果遇见复杂的查询，可以再套一层

select * from (
                select C.*,rownum rn from (
                    select A.*
                    from TABLE1 A
                        inner join TABLE2 B
                            on A.id = B.id
                    where 1=1 and A.id = #{id}
                    order by A.id
                ) C where rownum <= #{pageNo} * #{pageSize}
            ) D where D.rn >= (#{pageNo} - 1) * #{pageSize} + 1

#### 第二种方法 ####

select b.*,rn from (
            select a.*,rownum rn
            from TABLE1 a
            where 1=1 order by a.id
     ) b where rn BETWEEN 1 and 10  # 后面就是 11 到 20 、 21 到 30 以此类推