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

SQL之SQL分页的N种玩法


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 /张小菜
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Small-Rose /张小菜 !
评论
  目录