嘘~ 正在从服务器偷取页面 . . .

如年
春节

SQL之SQL分页的N种玩法

SQL SQL分页
SQL
发布日期:   2020-06-16
作者: Small-Rose / 张小菜
更新日期:   2020-06-16
文章字数:   2k
阅读时长:   9 分
阅读次数:  

SQL分页的N种玩法

整理说明：

大概以取前3页为例。

page_size — >每页显示条数 10，后面不再重复说明。

page_num —> 页码 1,2,3 取前面的2页或3页

从《SQL之查询前N条记录》一文整理的“数据库和分页关键字/函数对应关系表”可以看到分页大致分4类数据库，就以代表性的数据库来列举，相同关键字或函数的数据库用法相似，不再单列。

想了解Mybtatis模糊查询？请参考《Mybatis-模糊查询的N种玩法》。

一、SQL Server 分页

（1）TOP方式

查询第一页的时候，用top方法，效率最高。

select  top 10  pagesize   from table_name 
where id not in( select top  page_size *(page_num  -1)   id from table_name)

如：

第1页：page_num =1 时

select top 10  *  from loginfo where id not in(select top 0  id from loginfo)

第2页：page_num =2 时

select top 10  *  from loginfo where id not in(select top 10 id from loginfo)

第2页：page_num =3 时

select top 10  *  from loginfo where id not in(select top 20 id from loginfo)

（2）row_number()分页

SQLServer2005以后出现。其实就是把数据编号，再根据编号分页显示。

select * 
from(select *,Rn=row_number() over(order by id asc) from loginfo) as t
where t.Rn between (page_num -1)* page_size +1 and page_num * page_size ;

如：

第1页：page_num=1 时

select * 
from(select *, row_number() over(order by id asc) as rownum from loginfo) as t
where t.rownum between 1 and  10

第2页：page_num=2 时

select * 
from(select *,row_number() over(order by id asc) as rownum from loginfo) as t
where t.rownum between 11 and  20

第2页：page_num=3 时

select * 
from(select *,Rn=row_number() over(order by id asc) from loginfo) as t
where t.Rn between 21 and  30

（3）offset fetch next 方式

Sqlserve2012开始支持。

select * from table_name order by id
offset (page_num-1) * page_size rows fetch next page_size rows only

如：

第1页：page_num=1 时

select * from loginfo order by id offset 0 rows fetch next 10 rows only

第2页：page_num=2 时

select * from loginfo order by id offset 10 rows fetch next 10 rows only

第2页：page_num=3 时

select * from loginfo order by id offset 20 rows fetch next 10 rows only

表示从第10行开始取接下来的10行数据。

二、Mysql分页

（1）Limit

Limit适用于数据量较少的情况，数据量在万以下，会进行全表扫描。

mysql数据库使用limit分页查询时，索引是从0开始的，表中第1条数据的索引是0，第2条数据的索引是1，第3条数据的索引是2，以此类推…

select * from table_name limit index, pagesize;

index表示结果集索引开始行提前数据；

pagesize表示结果集索引开始行,取出pagesize条记录数据。

实际使用时：

select * from table_name limit  (page_num-1)* page_size, page_size;

如：

第1页：page_num =1 时

select * from loginfo limit 0, 10; --从第1行开始，取10条数据，索引是[0-9]
select * from loginfo limit 10 ;

第2页：page_num =2 时

select * from loginfo limit 10, 10; --从第11行开始，取10条数据，索引是[10-19]

第3页、第4页以此类推…

（2）主键或唯一索引分页

利用索引扫描，可以提升查询速度，可用于万级数据量。

select * from table_name where id_primarykey > (page_num*page_size) order by id_primarykey asc limit page_size

如：

第1页：

select * from t_s_log where id > 10 order by id asc limit 10

第2页：

select * from t_s_log where id > 20 order by id asc limit 10

（3）索引预执行PREPARE

语法

PREPARE stmt_name FROM SELECT * FROM table_name WHERE id_primarykey > (page_num * page_size) ORDER BY id_primarykey asc limit page_size。

《Mysql预执行PREPARE》

（4）利用子查询/嵌套查询

利用子查询，如果有索引或主键尽量使用索引或主键排序，子查询是在索引上完成。

SELECT* FROM table_name WHERE  id <= (SELECT id FROM table_name ORDER
BY id desc LIMIT (page_num-1)*page_size, 1) ORDER BY id desc LIMIT  page_size

我测试实际运行数据库一张日志表中4万条数据的情况下查询速度对比，我的主键id是32位字符串。

--执行时间约：0.030 sec
SELECT * FROM t_s_log ORDER BY id DESC LIMIT 30000,10; 

--执行时间约：0.012 sec
SELECT * FROM t_s_log WHERE ID <= (SELECT ID FROM T_S_LOG ORDER BY ID   DESC LIMIT 30000,1) ORDER BY id DESC LIMIT  10;

子查询明显比单纯的limit index, page_size快。

（5）利用连接查询

SELECT * FROM table_name AS t1
JOIN ( SELECT id FROM table_name ORDER BY id desc LIMIT (page_num-1)*page_size )AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT page_size;

从查询的临时表t2可以看得出当页码越大速度会越慢，此方法慎用。

--执行时间约：11.193 sec
SELECT * FROM t_s_log AS t1 JOIN ( 
    SELECT id FROM t_s_log ORDER BY id DESC LIMIT 100 )AS t2
WHERE t1.id <= t2.id ORDER BY t1.id DESC LIMIT 10;

（6）BETWEEN AND分页

SELECT * FROM table_name where id BETWEEN start_id AND end_id;

可以看出这是直接定位ID的范围的方式，它十分依赖id的，如id的连续性问题，但是作为无删除的应用环境下是十分实用的且高效的。BETWEEN ADN的方式支持字符串ID。但是作为一种分页查询的方式，采用直接定位ID范围的方式，依旧是比较有效率的。

三、Oracle 分页

oracle主要还是使用ROWNUM进行分页。

（1）直接简单粗暴的方式

先排序再取需要的数据，数据越多越慢。

select * from (
    select t.*,rownum num from table_name t
) 
where t.num <= (page_num * page_size) and  t.num>= (page_num-1)*page_size +1
--where BETWEEN (page_num-1)*page_size +1 AND  (page_num * page_size)

排序好了直接拿，

SELECT  *，ROWNUM  rn  FROM table_name where rm BETWEEN (page_num-1)*page_size +1 AND  (page_num * page_size) ;
--注意：oracle 没有 as

（2）子查询/嵌套查询

子查询都是通过限定查询范围的方式缩小查询范围。

SELECT * FROM (
    SELECT t.*,ROWNUM r FROM table_name t WHERE r <= page_num*page_size
) WHERE r >(page_num-1)*page_size
--没有+1是因为用的大于号

​ （a）不排序的子查询

select tmp.* from (
    select t.*,ROWNUM rn from table_name t where t.rn <= (page_num * page_size)
) tmp 
where tmp.rn >= (page_num-1)*page_size +1 ;
-- +1是因为用的大于或等于号

​ （b）带 order by的排序

select tmp.* from (
    select t.*, rownum rn from (
        select * from T_BASE_PROVINCE order by id asc
    ) t
     where t.rn <=  (page_num * page_size)
) tmp
where tmp.rn >= (page_num-1)*page_size +1 ;

（3）使用minus作差

select * from table_name where rownum<=(page_num * page_size)
minus 
select * from table_name where rownum<=(page_num-1)*page_size

（4）WITH AS方式

WITH AS将SQL语句中单独的子查询语句提取出来，作为一个单独的临时表来组织命名。在原来的语句中，就可以使用新的别名来替代子查询。

WITH tmp1 AS(
(
      SELECT ROWNUM AS rowno, tt.* FROM (  
         SELECT *  FROM table_name t
              WHERE some conditions
              ORDER BY fact_up_time, flight_no
     ) tt
    WHERE ROWNUM <= page_num*page_size
)
SELECT * FROM tmp1.rowno > (page_num-1)*page_size

这里是有排序的，无排序的写法同理。

总结：能不查全表尽量不去查全表，尽快缩小查询范围，再提取分页数据。

四、DB2分页

（1）BETWEEN AND分页

select * from (
    select *,rownumber() over(order by id asc ) as rowid from table_name
)as a

where a.rowid >= (page_num-1)*page_size+1  AND a.rowid <= page_num*page_size
--where a.rowid  between (page_num-1)*page_size+1  and  page_num*page_size

在实际使用中，table_name可能会是一个真正业务数据子查询：

select * from (
    select tmp_page.*,rownumber() over() as row_id from ( 
        select  ID, LOG_MSG, OPACCOUNT, OP_TIME, OPTYPE, VERIFY_ACCOUNT, ADDRIP, DBAPP from LOGINFO WHERE ( OP_TIME >= '2020-06-15 17:05:52' and OPTYPE = '登录' ) order by id desc
    ) as tmp_page
) where row_id between 1 and 30

（2）WITH AS 方式

WITH TMP AS(
    --先找出需要的数据
   select *,rownumber() over(order by id asc ) as row_id from table_name

)
--最后根据序号取数据
SELECT TMP.* FROM TMP WHERE  row_id between (page_num-1)*page_size and page_num*page_size

本质还是借助rownumber() over()排好序，明显可以看到缺点是临时表较大。

实际使用示例：

with tmp as(
    select * from (

        select tmp_page.*,rownumber() over() as row_id from ( 

                select  ID, LOG_MSG, OPACCOUNT,OP_TIME, OPTYPE 
                from LOGINFO WHERE ( OPTYPE >='2020-06-15 17:05:52' and OPTYPE = '登陆'                     )  order by id desc
         ) as tmp_page  
    ) 
) select * from tmp where row_id between 30 and 40

（3）Fetch first rows only

使用Fetch first rows only分页依旧借助WITH AS但是可以不断缩小临时表的范围。

WITH TMP AS(
    select * from (
            select *,rownumber() over(order by id asc ) as row_id from table_name
    ) 
    where  row_id > (page_num-1)*page_size
)
SELECT TMP.* FROM TMP WHERE   fetch first  pagesize  rows only

实际使用示例：

with tmp as(
    select * from (

        select tmp_page.*,rownumber() over() as row_id from ( 

            select  ID,  LOG_MSG, OPACCOUNT, OP_APPNAME, OP_DATABASE, OP_TIME, OPTYPE 
            from LOGINFO WHERE ( OPTYPE >='2020-06-15 17:05:52' and OPTYPE = '登录' )               order by id desc

         ) as tmp_page 

    ) where row_id >20

) select * from tmp fetch first  10 rows only

文章作者: Small-Rose / 张小菜
文章链接: https://notes.zhangxiaocai.cn/posts/2cbe04e7.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY-SA 4.0 许可协议。转载请注明来源 Small-Rose / 张小菜 !
SQL SQL分页
赏
评论
  目录