mysql、mssql及oracle分頁查詢方法詳解

本文實例講述了mysql、mssql及oracle分頁查詢方法。分享給大家供大家參考。具體分析如下：

分頁查詢在web開發(fā)中是最常見的一種技術(shù)，最近在通過查資料，有一點自己的心得

一、mysql中的分頁查詢

注：

m=(pageNum-1)*pageSize;n= pageSize;

pageNum是要查詢的頁碼，pageSize是每次查詢的數(shù)據(jù)量，

方法一：

select * from table order by id limit m, n;

該語句的意思為，查詢m+n條記錄，去掉前m條，返回后n條記錄。無疑該查詢能夠?qū)崿F(xiàn)分頁功能，但是如果m的值越大，查詢的性能會越低（越后面的頁數(shù)，查詢性能越低），因為MySQL同樣需要掃描過m+n條記錄。

方法二：

select * from table where id > #max_id# order by id limit n;

該查詢每次會返回n條記錄，卻無需像方式1掃描過m條記錄，在大數(shù)據(jù)量的分頁情況下，性能可以明顯好于方式1，但該分頁查詢必須要每次查詢時拿到上一次查詢（上一頁）的一個最大id（或最小id）。該查詢的問題就在于，我們有時沒有辦法拿到上一次查詢（上一頁）的最大id（或最小id），比如當前在第3頁，需要查詢第5頁的數(shù)據(jù)，該查詢方法便愛莫能助了。

方法三：

為了避免能夠?qū)崿F(xiàn)方式二不能實現(xiàn)的查詢，就同樣需要使用到limit m, n子句，為了性能，就需要將m的值盡力的小，比如當前在第3頁，需要查詢第5頁，每頁10條數(shù)據(jù)，當前第3頁的最大id為#max_id#：

select * from table where id > #max_id# order by id limit 20,10;

其實該查詢方式是部分解決了方式二的問題，但如果當前在第2頁，需要查詢第100頁或1000頁，性能仍然會較差。

方法四：

方式五：

該查詢方式同方式四，同樣通過子查詢掃描字段id，效果同方式四。至于性能的話，方式五的性能會略好于方式四，因為方式5不需要在進行表的關(guān)聯(lián)，而是一個簡單的比較。

二、Sql Server分頁查詢

方法一：

適用于 SQL Server 2000/2005

SELECT TOP 頁大小 *
 FROM table1
 WHERE id NOT IN
      (
      SELECT TOP 頁大小*(頁數(shù)-1) id FROM table1 ORDER BY id
      )
 ORDER BY id

方法二：

適用于 SQL Server 2000/2005

--順序?qū)懛ǎ?/p>

 SELECT TOP 頁大小 *
 FROM table1
 WHERE id >=
 (
 SELECT ISNULL(MAX(id),0) 
 FROM 
 (
 SELECT TOP 頁大小*(頁數(shù)-1)+1 id FROM table1 ORDER BY id
 ) A
 )
 ORDER BY id

--降序?qū)懛ǎ?br />

 SELECT TOP 頁大小 *
 FROM table1
 WHERE id =
 (
 SELECT ISNULL(MIN(id),0) 
 FROM 
 (
 SELECT TOP 頁大小*(頁數(shù)-1)+1 id FROM table1 ORDER BY id Desc
 ) A
 )
 ORDER BY id Desc

方法三：

適用于 SQL Server 2005

 SELECT TOP 頁大小 * 
 FROM 
     (
     SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
     ) A
 WHERE RowNumber > 頁大小*(頁數(shù)-1)

說明，頁大?。好宽摰男袛?shù)；頁數(shù)：第幾頁。使用時，請把“頁大小”和“頁大小*(頁數(shù)-1)”替換成數(shù)字。

其它的方案：如果沒有主鍵，可以用臨時表，也可以用方案三做，但是效率會低。
建議優(yōu)化的時候，加上主鍵和索引，查詢效率會提高。

通過SQL 查詢分析器，顯示比較：我的結(jié)論是:

分頁方案二：(利用ID大于多少和SELECT TOP分頁）效率最高，需要拼接SQL語句
分頁方案一：(利用Not In和SELECT TOP分頁) 效率次之，需要拼接SQL語句
分頁方案三：(利用SQL的游標存儲過程分頁) 效率最差，但是最為通用

三、oracle分頁查詢

方法一：

SELECT * FROM 
( SELECT A.*, ROWNUM RN FROM 
  (SELECT * FROM tab) A 
   WHERE ROWNUM = 40 ) 
     WHERE RN >= 21;

這個分頁比下面的執(zhí)行時間少，效率高。當數(shù)據(jù)量較大時oracle會自動優(yōu)化！

方法二：

select * from 
(select c.*,rownum rn from tab c) where rn between 21 and 40

對比這兩種寫法，絕大多數(shù)的情況下，第一個查詢的效率比第二個高得多。

這是由于CBO優(yōu)化模式下，Oracle可以將外層的查詢條件推到內(nèi)層查詢中，以提高內(nèi)層查詢的執(zhí)行效率。

對于第一個查詢語句，第二層的查詢條件WHERE ROWNUM = 40就可以被Oracle推入到內(nèi)層查詢中，這樣Oracle查詢的結(jié)果一旦超過了ROWNUM限制條件，就終止查詢將結(jié)果返回了。

而第二個查詢語句，由于查詢條件BETWEEN 21 AND 40是存在于查詢的第三層，而Oracle無法將第三層的查詢條件推到最內(nèi)層

（即使推到最內(nèi)層也沒有意義，因為最內(nèi)層查詢不知道RN代表什么）。因此，對于第二個查詢語句，Oracle最內(nèi)層返回給中間層的是所有滿足條件的數(shù)據(jù)，而中間層返回給最外層的也是所有數(shù)據(jù)。數(shù)據(jù)的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。

上面分析的查詢不僅僅是針對單表的簡單查詢，對于最內(nèi)層查詢是復雜的多表聯(lián)合查詢或最內(nèi)層查詢包含排序的情況一樣有效。

希望本文所述對大家的數(shù)據(jù)庫程序設(shè)計有所幫助。