高效的SQLSERVER分頁方案

標籤：比較   索引   tween   注意   article   ber   art   number   int   

Sqlserver資料庫分頁查詢一直是Sqlserver的短板，假設有表ARTICLE,欄位ID、YEAR，資料53210條(客戶真實資料，量不大)，分頁查詢每頁30條，查詢第1500頁（即第45001-45030條資料），欄位ID叢集索引，YEAR無索引，Sqlserver版本：2008R2

第一種方案：
SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC  

平均查詢100次所需時間：45s

第二種方案：
SELECT * FROM (　　SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC  

平均查詢100次所需時間：138S

第三種方案：
SELECT * FROM ARTICLE w1, 
(
    SELECT TOP 30 ID FROM 
    (
        SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
    ) w ORDER BY w.YEAR ASC, w.ID ASC
) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

平均查詢100次所需時間：21S

第四種方案：
SELECT * FROM ARTICLE w1 
    WHERE ID in 
        (
            SELECT top 30 ID FROM 
            (
                SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
            ) w ORDER BY w.YEAR ASC, w.ID ASC
        ) 
    ORDER BY w1.YEAR DESC, w1.ID DESC

平均查詢100次所需時間：20S

第五種方案：
SELECT w2.n, w1.* FROM ARTICLE w1, (　　SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC 

平均查詢100次所需時間：15S

在查詢頁數靠前時，效率3>4>5>2>1，頁碼靠後時5>4>3>1>2，再根據使用者習慣，一般使用者的檢索只看最前面幾頁，因此選擇3 4 5方案均可，若綜合考慮方案5是最好的選擇，但是要注意SQL2000不支援row_number()函數

總結分頁SQL：
1、 使用兩個select和兩個order，雙倒序：比如你有尋找相關記錄中10001-10020的20條記錄。你先按照某欄位排序，尋找前10020條記錄，形成資料集，再進行倒序尋找前20條。這種方法比較簡單，但進行多欄位排序時，有時候會出現莫名奇妙的錯誤（可能我不夠經驗）。
2、 使用兩個select，併產生臨時序號：一樣比如你有尋找相關記錄中10001-10020的20條記錄。你首先尋找所有相關資料，並增加新的列儲存記錄臨時序號，形成資料集，再根據尋找資料集，而條件增加了臨時序號在10001-10020之間。這種方法必須使用陌生的產生臨時序號函數，而產生臨時序號的函數只有SQL Serrver 2005 才支援。
3、 建立暫存資料表，產生臨時ID：一樣比如你有尋找相關記錄中10001-10020的20條記錄。你可以首先建立一個只有自增ID和你需要尋找相關記錄的自己欄位的暫存資料表，並將你尋找記錄的主鍵填入暫存資料表。再查詢暫存資料表，增加條件臨時序號在10001-10020之間。最後刪除暫存資料表。這種方法最複雜，但可擴充的空間比較大，效能的最佳化的潛力也應該比較強。
----------------兩個select 加兩個order，雙倒序（27秒鐘）
select top 20 * from (select top 349980 * from Test where type >5 order by type desc) as temp order by type desc
"select top”+numcount+” * from Jokes where id not in ( select top "+numcount*(當前頁-1)+" id from Jokes order by id desc ) order by id desc";
 
----------------兩個select 產生臨時序號（1秒鐘）
select * from (
select * ,ROW_NUMBER() OVER(ORDER BY type,ID) AS RowRank from Test where type >5) as temp
where RowRank between 350001 and 350020
----------------暫存資料表，create＋insert＋select＋drop（3秒鐘）
----------------暫存資料表，create＋insert＋select（2秒鐘）
CREATE TABLE #PageIndexForUsers
(
    IndexID int IDENTITY (0, 1) NOT NULL,
    ID int
)
INSERT INTO #PageIndexForUsers (ID)
SELECT ID
FROM Test
WHERE type >5
ORDER BY type,ID

SELECT T.* FROM Test T, #PageIndexForUsers p
WHERE  p.ID = T.ID AND
       p.IndexId between 350000 and 350019

drop table #PageIndexForUsers
運行結果佔用了時間是第一種方式用了27秒，第二種用了1秒，第三種不加drop用了2秒，加drop用了3秒。
 
查詢 31到40條記錄 的各種玩法
1. 如果ID連續
select * from A where ID between 31 and 40 
2. 如果ID不連續，提供三種寫法
--兩次對錶A查詢效率較低
select top 10 * from A where ID not in (select top 30 ID from A)
 
--外層查詢沒有對錶A查詢，效率大有提高
select top 10 * from (select top 40 * from A order by ID) as t order by t.ID desc
 
--ROW_NUMBER()函數效率更高，sqlserver2005以及以上版本中才可以使用
select * from (select ROW_NUMBER() over(order by ID) as ‘sequence‘,A.* from A ) as t where t.sequence between 31 and 40
 
--查詢16-20之間記錄
select * from (select ROW_NUMBER() over(order by ID) as ‘sequence‘,ent_pos.* from ent_pos ) as t where t.sequence between 16 and 20
 
select top 5 * from (select top 20 * from ent_pos order by ID) as t order by t.ID desc
 
select top 5 * from ent_pos where ID not in (select top 15 ID from ent_pos)
 
--如果是海量資料做查詢的話，這個是更高效的，這個不錯
select top 10 * from A where id in
(select top 10 id from (select top 40 id from A order by ID desc) as t order by t.ID )
order by A.ID desc
--這哥們給出了sql2012的新寫法，我機器上沒裝這麼前衛的工具，在我老大的機器上測試可行，效能效率暫不明確
SELECT * FROM A Order by ID OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

 

高效的SQLSERVER分頁方案