❤️MySql Oracle和SQL Server的分页查询

小明的学习圈子2023-08-13数据库分页 MySQL Oracle

MySql Oracle和SQL Server的分页查询

假设当前是第PageNo页,每页有PageSize条记录,现在分别用Mysql、Oracle和SQL Server分页查询student表。

1、Mysql的分页查询:

SELECT *
FROM student
LIMIT (PageNo - 1) * PageSize, PageSize;

理解:(Limit n,m) =>从第n行开始取m条记录,n从0开始算。

2、Oracel的分页查询:

SELECT
    *
FROM
    (
        SELECT
           S.*, ROWNUM rn 
        FROM
           (SELECT * FROM Student) S
        WHERE
            Rownum <= pageNo * pageSize
    )
WHERE
    rn > (pageNo - 1) * pageSize

或者

SELECT
    *
FROM
    (
        SELECT
           S.*, ROWNUM rn 
        FROM
           (SELECT * FROM Student) S
    )
WHERE
     rn BETWEEN  (pageNo - 1) * pageSize AND  pageNo * pageSize

理解:假设pageNo = 1,pageSize = 10,先从student表取出行号小于等于10的记录,然后再从这些记录取出rn大于0的记录,从而达到分页目的。ROWNUM从1开始。

分析:对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。

这是由于CBO 优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。对于第一个查询语句,第二层的查询条件WHERE ROWNUM <=pageNo * pageSize就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。

而第二个查询语句,由于查询条件BETWEEN (pageNo - 1) * pageSize AND pageNo * pageSize是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。

3、SQL Server分页查询:

 SELECT
     TOP PageSize *
 FROM
     (
         SELECT
             ROW_NUMBER () OVER (ORDER BY id ASC) RowNumber ,*
         FROM
             student
     ) A
 WHERE
     A.RowNumber > (PageNo - 1) * PageSize
Last Updated 2024/4/6 10:47:15