MySQL、SqlServer、Oracle三大主流資料庫分頁查詢 (MySQL分頁不能用top，因為不支援)

標籤：

一、 MySQL 資料庫 分頁查詢
MySQL資料庫實現分頁比較簡單，提供了 LIMIT函數。一般只需要直接寫到sql語句後面就行了。
LIMIT子 句可以用來限制由SELECT語句返回過來的資料數量，它有一個或兩個參數，如果給出兩個參數， 第一個參數指定返回的第一行在所有資料中的位置，從0開始（注意不是1），第二個參數指定最多返回行數。例如：
select * from table WHERE … LIMIT 10; #返回前10行
select * from table WHERE … LIMIT 0,10; #返回前10行
select * from table WHERE … LIMIT 10,20; #返回第10行開始 共計20行資料

對於有大資料量的mysql表來說，使用LIMIT分頁存在很嚴重的效能問題。

查詢從第1000000之後的30條記錄：

SQL代碼1：平均用時6.6秒 SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

SQL代碼2：平均用時0.6秒 SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

因為要取出所有欄位內容，第一種需要跨越大量資料區塊並取出，而第二種基本通過直接根據索引欄位定位後，才取出相應內容，效率自然大大提升。

可以看出，越往後分頁，LIMIT語句的位移量就會越大，兩者速度差距也會越明顯。

二、 SQLServer數 據庫分頁查詢
SQLServer資料庫又分為 SQLServer2000和SQLServer2005。一般比較簡單的方法是通過TOP函數來實現。如下：
SELECT TOP 10 * FROM sql WHERE (
code NOT IN (SELECT TOP 20 code FROM TestTable ORDER BY id))
ORDER BY ID
這條語句，從理論上講，整條語句的執行時間應該比子句的執行時 間長，但事實相反。因為，子句執行後返回的是20條記錄，而整條語句僅返回10條語句，所以影響資料庫回應時間最大的因素是物理I/O操作。而限制物理I /O操作此處的最有效方法之一就是使用TOP關鍵詞了。TOP關鍵詞是SQL SERVER中經過系統最佳化過的一個用來提取前幾條或前幾個百分比資料的詞。
以上語句的有一個致命的缺點，就是它含有NOT IN字樣，要換成用not exists來代替not in，二者的執行效率實際上是沒有區別的。
在以上分頁演算法中，影響我們查 詢速度的關鍵因素有兩點：TOP和NOT IN。TOP可以提高我們的查詢速度，而NOT IN會減慢我們的查詢速度，所以要提高我們整個分頁演算法的速度，就要徹底改造NOT IN，同其他方法來替代它。
我們知道，幾乎任何欄位，我們都可以通過max(欄位)或min(欄位)來提取某個欄位中的最大 或最小值，所以如果這個欄位不重複，那麼就可以利用這些不重複的欄位的max或min作為分水嶺，使其成為分頁演算法中分開每頁的參照物。在這裡，我們可以 用操作符“>”或“<”號來完成這個使命。如：

Select top 10 * from table1 where id>200

於是就有了如下分頁方案：

select top 頁大小 *

from table1

where id>

(select max (id) from

(select top ((頁碼-1)*頁大小) id from table1 order by id) as T

)

order by id

這種方法執行 多少始終沒有大的降勢，後勁仍然很足。尤其對於資料量大的時候，該方法執行速度一點也不會降低。
使用TOP要求主鍵必須唯一，不能是聯合主鍵。如果是聯合主鍵，則查詢出的結果會亂序的。
目前SQLServer2005提供了一個row_number()函數。ROW_NUMBER() 就是產生一個順序的行號,而他產生順序的標準,就是後面緊跟的OVER(ORDER BY ReportID)，其中ReportID可 以是聯合主鍵。下面,我們看看怎麼具體應用這個RowNo進行分頁.
SELECT TOP 10 * FROM
(
SELECT top 10 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM TABLE
) AS A
WHERE RowNo > " + pageIndex*10
pageIndex就是我們需要資料的頁數.

但對於SQLServer2000的話，如果是聯合主鍵，我還沒有解決辦法，如果大家有可跟我聯 系。謝謝大家了。

三、 ORCALE資料庫 分頁查詢

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的範圍。

MySQL、SqlServer、Oracle三大主流資料庫分頁查詢 (MySQL分頁不能用top，因為不支援)