分页查询

1、为什么需要分页？

​ 当数据库的数据过的时候，客服端无法一次性显示所有数据,例如我们数据库表里有十万条数据,如果一下加载,查询的速度慢,用户体验差,而且用户也不可能一次性读完这个十万条数据

2、分页技术分类

  1. 物理分页(推荐)

    在数据库执行查询时（实现分页查询），查询需要的数据依赖数据库SQL语句，属于后台分页

  2. 逻辑分页

    先查询所有数据到内存，再从内存截取需要数据采用程序内部逻辑，属于前台分页

3、 认识 数据伪列

​ 在此之前，其实我们已经使用过了一个数据伪列 SYSDATE ，但是在 Oracle中 还提供很多数据的伪列，其中最常用也是最重要的有 ROWNUM、ROWID 两个数据伪列；

  • ROWNUM：程序开发中使用最多（重点）

  • ROWID：在数据库程序分析的时候使用（与我们应用开发无关。

    ​

3.1 ROWNUM 伪列

​ ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出。

​ 将ROWNUM伪列 作为emp表的查询字段进行查询，观察结果：

  1. 测试一

    SELECT ROWNUM,empno,ename,job FROM emp ;

    ​

  2. 测试二

SELECT ROWNUM,empno,ename,job FROM emp WHERE deptno=20;

​

观察发现，ROWNUM 其实就是在查询出的结果后，对查询的结果进行顺序编号，从1开始。

3.2 对ROWNUM进行 >、>=、=操作

​ 不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果。

  1. 测试一

    SELECT * FROM emp WHERE ROWNUM >1;

    ​

  2. 测试二

    SELECT * FROM emp WHERE ROWNUM >=2;

    ​

  3. 测试三

    SELECT * FROM emp WHERE ROWNUM = 2;

    ​

  4. 测试四：

    SELECT * FROM emp WHERE ROWNUM = 1;

    1. 观察发现：以上测试 只有当 ROWNUM = 1 的时候才能查询出结果。

    这是因为：

    • ROWNUM是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值；

    • 返回结果记录的ROWNUM是从1开始排序的，因此第一条始终是1；

    所以：

    ​ 这样一来，当查询到第一条记录时，该记录的ROWNUM为1，但条件要求 ROWNUM>1，因此不符合，继续取出下一条进行判断；但因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM还是为1，如此循环，就不会产生结果。

    ​ 而 默认查询第一条记录的 ROWNUM为1，所以SELECT * FROM emp WHERE ROWNUM = 1;可以查询出数据库的第一条记录。

    ​ 在很多时候程序员在确认某个表中是否有存在数据时，可能你第一反应是使用SELECT * FROM emp;但如果数据表存在大量数据的话，几乎有可能会把操作系统的内存占满卡死。

    ​ 所以一般，我们在确定一张表书否有数据的时候，直接在查询条件加上ROWNUM=1 即可，其思路就是只要存在一条数据就说明有相应数据，查询就可以直接返回了，这样就能提高性能了。

    ​

4、Oracle 实现

​ 上面我们已经对 ROWNUM 进行 >、>=、= 的操作做了详细的介绍，那么 使用 ROWNUM 进行 <或者<=会是怎样的效果呢？

范例：查询出emp表的前5条数据。

SELECT * FROM emp WHERE ROWNUM <=5;

观察发现：使用 ROWNUM < 或者 <= 可以取出 数据库的前 N 条数据。

4.1、 分页查询示例

范例一：查询出 emp表的第 6~10条数据。

-- 我们想当然会这么去解决问题
SELECT * FROM emp WHERE ROWNUM BETWEEN 6 AND 10;
-- 或者
SELECT * FROM emp WHERE ROWNUM >=6 AND ROWNUM <=10;

​

​ 发现，查询结果并没有返回任何数据。

​ 其实在上诉问题中我们已经对 ROWNUM 这个伪列做了详细的说明。

​ 因为 SELECT 是在 FROM、WHERE 之后执行的，在取出1条数据 进行 WHERE判断的时候，此时该条数据的ROWNUM默认为1，而你的条件是 要求 ROWNUM 在 6~10之间，条件不满足；则继续取出下一条在和 WHERE的条件匹配，而因为上一条数据不满足，所以新取出的数据的 ROWNUM也是1。以此类推，条件不可能成立。

问题分析：

  1. 取出前10条数据，返回一个多行多列的数据结果。既然是多行多列，我们可以讲查询结果当做一个虚拟表作为FROM子句查询表。

  2. 针对子查询的虚拟表数据，再从虚拟表中取出满足条件的数据。

问题解决：

  1. 查询前10条记录

    SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM<=10;

  2. 将查询的结果作为 虚拟数据表 继续查询，而此时查询的结果作为虚拟表的话 ROWNUM 就是该虚拟表一列真是的数据，不再是伪列。可以当做普通的字段查询判断。

    SELECT * 
FROM (SELECT ROWNUM rn,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM<=10) temp
WHERE temp.rn BETWEEN 6 AND 10;

范例二：查询出 emp表的第 11~15条数据。

SELECT * 
FROM (SELECT ROWNUM rn,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM<=15) temp
WHERE temp.rn BETWEEN 11 AND 15;

​ 发现数据表只剩下最后4行数据。那么，这其实就是我们分页查询中的随后一页的数据。

说明：

​ 以上 范例其实就是我们Oracle 所谓的分页查询。我们每次查询的条数为 5条(每页条数)： 那么：

​ 1~5 就是第 1 页的数据

​ 6~10 是第2页数据

​ 11~15 是第3页数据(由于结果条数少于5，证明最后一页)

​ 那么在java中。如果 我们要查询数据的页数为 pageNum，每页需要显示的条数为 pageSize。

则，页数为pageNum的该页中数据序号最小的是第((pageNum-1)*pagesize+1)条数据，最大的是第(pageNum*pagesize)条数据。

4.2、 分页查询的总结

​ 1、说明：

ORACLE分页采用ROWNUM
2、 rownum序列化的原理：
1.先执行查询操作

2.将第一行的rownum设置为1

3.将得到的行的rownum与条件相比，如果不匹配，则抛弃行，如果匹配，则返回行

4.Oracle获取下一行，然后将rownum增1

5.返回第3步
3 、注意事项：
1.rownum不能使用表名，别名做前缀（rownum是属于系统的）

2.子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。

3. 查询rownum在某区间的数据，rownum对小于某值的查询条件为true，rownum对于大于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。那就必须使用子查询。
4.只要查询的条件不包含rownum=1这条数据则查询每行数据时rownum都动态生成为1，直到最后都没有满足条件的rownum,所以查询结束后结果返回null（通过机试去观测就明白了）

4.3、 分页查询的使用规则

4.3.1 固定格式（性能优化后的标准格式）
    select *
    from (select rownum rn, temp.*
          from (select * from 表名) temp   
          where rownum <= end)
    where rn >= start

    --end:当页最大数=pageNum*pagesize
    --start:当页最小数=(pageNum-1)*pagesize+1

说明

嵌套第三层<select * from 表名>这个子查询你可能会有疑问，觉得可以不需要，但是，优化就是这么规定处理的，Oracle内部机制做了处理，所以不要去怀疑和纠结。

4.3.2 固定格式2（性能较差）
select * 
from (select rownum rn ,temp.* 
          from (select * from 表名) temp) 
where rn between start and end;

--end:当页最大数=pageNum*pagesize
--start:当页最小数=(pageNum-1)*pagesize+1

说明

这种查询方式性能很差，使用时注意伪列rownum必须取别名使用，因为rownum是属于系统的。

4.3.3两种格式效率对比

​ CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第1个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。而第2个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第2个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

5、Mysql实现

5.1、说明

​ Mysql分页采用LIMIT关键字

5.2、语法格式

  1. 格式

    SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset

  2. 说明

    1、LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

2、LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

3、如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

4、初始记录行的偏移量是 0(而不是 1)

3.3、示例代码

  1. 查询6-10条数据

     SELECT * FROM table LIMIT 5,5; 
--检索记录行 6-10条数据

  2. 查询从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1

    SELECT * FROM emp LIMIT 5,-1; // 检索记录行 6-最后一条.

  3. 查询前 5 条记录

    SELECT * FROM emp LIMIT 5
--LIMIT n 等价于 LIMIT 0,n

    ​

results matching ""

    No results matching ""