Oracle分頁排序的實現與分頁資料重複問題

標籤：tab   rom   desc   執行   必看   顯示   根據   列表   關係   

對於關聯式資料庫來說，直接寫SQL拉資料在列表中顯示是很常用的做法。但如此便帶來一個問題：當資料量大到一定程度時，系統記憶體遲早會耗光。另外，網路傳輸也是問題。如果有1000萬條資料，使用者想看最後一條，這時即便有足夠的記憶體，在網路上傳輸這麼多資料也得一兩小時吧，恐怕沒幾個使用者有這麼耐心等。因此分頁是必須的。

 

現在網上的論壇、部落格什麼的，基本上都會有分頁功能，有些是SQL分頁的，有些可能是NOSQL用其它方法分頁，都有很成熟的東西了。本文根據我自己的經驗，以ORACLE為例，講下簡單的SQL分頁和排序問題，對剛接觸SQL準備要做分頁的人有些協助吧，大牛們就不必看了。

 

假設ORALCE資料庫中有一個TAB001表，主鍵為ID，有1000萬條記錄，索引什麼的都有了。我們有一個需求，是在介面上列出指定條件的記錄，原始SQL如下：

select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE=‘SOME_TYPE‘

 

 

如果要排序，比如要按CREATOR倒排序，我們會在SQL後面再加一句：order by CREATEOR desc

 

 

現在，我們發現這個SQL下來有500萬條記錄，顯然，如果不分頁，系統很容易就會翹掉。於是我們準備分頁。

 

分頁前，我們可能要在介面上擺上幾個按鈕和狀態顯示：上一頁、下一頁、第一頁、最後頁、每頁X條、共M頁、當前第N頁、跳到第N頁，等。顯然，我們分頁的步驟如下：

1. 計算總記錄數；
2. 根據總記錄數和每頁記錄數，計算總頁數；
3. 根據當前要顯示的頁碼，計算起始和結束的記錄號；
4. 產生分頁SQL，執行之，返回本頁資料，顯示之。

首先，計算總記錄數。這個簡單，嵌套一個select count(*)就行了：

select count(*)  from (             select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS  from TAB001  where ATYPE=‘SOME_TYPE‘          ) xx 

然後，總頁數=ceil(總記錄數/每頁記錄數)，不足一頁也當一頁處理。

 

接著，假設現在是第N頁，則本頁的開始、結束記錄號為：

  開始記錄號=N*每頁記錄數

  結束記錄號=min((N+1)*每頁記錄數-1,總記錄數)

 

最後，產生分頁SQL。由於分頁需要有記錄號，因此先要嵌套一個子查詢產生ROWNUM：

 

select rownum as recordno  from (              select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS  from TAB001  where ATYPE=‘SOME_TYPE‘          ) xx 

 

這樣，我們就有了記錄號，可以再對記錄號進行過濾，只選出本頁開始記錄號之後、結束記錄號之前的記錄：

 

select xxx.*  from (            select rownum as recordno              from (                          select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS  from TAB001  where ATYPE=‘SOME_TYPE‘                      ) xx           ) xxxwhere recordno >= :開始記錄號    and recordno <= :結束記錄號 

 

至此似乎分頁SQL已經完成了，表面上看這個SQL挺正確，運行起來似乎也沒問題。但經過我們實踐檢驗，其實這個SQL是不安全的，在某些情況下會出錯，原因在於它沒有排序。在分頁情況下，第一頁和第二頁的資料是來自兩次相對獨立的SQL，如果沒有排序，則SQL第一次和第二次執行時返回的結果是不一致的。

 

不一致是什麼意思？假設有一個無排序的SQL，我們把SQL執行兩次：

• 第一次執行後會返回有1、2、3、4、5共5條記錄
• 第二次執行後還是會返回有1、2、3、4、5共5條記錄

大部分情況下，這兩次返回結果的順序是完全一樣的。但不幸的是，也許資料庫有問題了，也許有人改了資料，反正有時候它會不一樣，比如第二次執行時第2條和第4條對調了，返回的是1、4、3、2、5共5條記錄，如下：

• 第一次：1、2、3、4、5
• 第二次：1、4、3、2、5

假設我們對這個SQL進行分頁，每頁3條記錄，共兩頁，正常情況下結果是這樣的：

• 拉第一頁時，執行第一次SQL，按1、2、3、4、5排序，返回1、2、3三條記錄
• 拉第二頁時，執行第一次SQL，按1、2、3、4、5排序，返回4、5兩條記錄

但如果發生排序混亂的問題，結果會這樣：

• 拉第一頁時，執行第一次SQL，按1、2、3、4、5排序，返回1、2、3三條記錄
• 拉第二頁時，執行第二次SQL，按1、4、3、2、5排序，返回2、5兩條記錄

結果我們會發現，分頁結果很不正常，2這條記錄出現了兩次，4則消失了。正常來說，我們不會注意到有資料丟失，但我們會注意到分頁的資料有重複。

 

怎麼辦呢？那我們就加一個排序吧，排序子句要加在最裡層的SQL裡，這樣分頁出來的結果才會是排序後的結果。比如按名稱、類別或作者排序的order by子句：

select xxx.*  from (            select rownum as recordno              from (                          select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS  from TAB001  where ATYPE=‘SOME_TYPE‘                          order by NAME,ATYPE,CREATOR                      ) xx           ) xxxwhere recordno >= :開始記錄號    and recordno <= :結束記錄號 

這樣是不是可以了呢？答案還是不行，因為這些欄位的值不是唯一的。可考慮一個極端情況，就是這個表裡500萬條記錄的名稱、類別和作者都完全一樣，會有什麼結果呢？結果仍然是無序。

 

最終解決這個問題的辦法，就是一定要用ID主鍵排序。不管前面有多少個order by欄位，最後面一定要加上ID主鍵：

 select xxx.*  from (            select rownum as recordno              from (                          select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS  from TAB001  where ATYPE=‘SOME_TYPE‘                          order by NAME,ATYPE,CREATOR, ID                      ) xx           ) xxxwhere recordno >= :開始記錄號    and recordno <= :結束記錄號

由於主鍵ID是唯一的，所以只要ID不變，按ID排序就能保證每次執行分頁SQL都是一致的順序了。

Oracle分頁排序的實現與分頁資料重複問題