本文主要介紹了基於ROWNUM、主鍵列/非空唯一性列、分析函數、OFFSET-FETCH NEXT機制的幾種SQL分頁查詢方案的性能對比。
分頁查詢可分為邏輯分頁和物理分頁兩種。邏輯分頁是應用代碼級別實現的分頁，指用戶通過一次查詢就取出所有的數據結果集並進行緩存，然後根據當前頁所需要展示的數據內容進行切分並遍歷顯示，若需要查詢的數據量非常大，則會消耗大量的內存來緩存數據，並且在會話生命周期內重複訪問數據時，可直接訪問緩存的數據，不過此時有可能訪問不到最新的數據。物理分頁是指使用資料庫自帶的分頁機制，比如MySQL的limit offset機制，Oracle的rownum和offset-fetch機制進行分頁查詢，是對資料庫表數據進行分頁條件查詢，每一次物理分頁都會直接訪問資料庫，可以保證數據是最新的，並且不需要在會話級別緩存過多的數據。
本文主要介紹的SQL分頁，即物理分頁，主要用於在數據結果集較大時控制數據在前臺(比如報表，列表框，頁面等)的分頁顯示，這樣既可以降低內存消耗，提高查詢效率，也可以方便數據在前臺的展示。文中如有疏漏之處，望指正！
Oracle 版本：19.3.0.0.0
MySQL版本：8.0.18
OS版本：CentOS 8.0
方案及性能對比
3.通過rownum實現分頁查詢(不使用order by排序)
SQL: select * from ( select rownum rowno,e.* from emp e where rownum<=&ROW_NUM1) t where t.rowno>=&ROW_NUM2;
執行計劃信息:
通過執行計劃和評估開銷可以看出，該方法將使用全表掃描，前段的分頁查詢效率會比較高，但是隨著ROWNUM值的增大，在分頁後期查詢的速度會越來越慢，這個情況和MySQL的limit機制一樣，當表中數據量較大時，隨著查詢範圍的擴大，每次需要讀取的表數據塊越來越多，查詢效率越來越低。如下圖所示：
SQL: select * from ( select rownum rowno,e.* from (select * from emp order by id) e where rownum<=&2) t where t.rowno>=&1;
執行計劃信息：
由執行計劃信息可以看出，當使用order by對數據集進行排序後再分頁時，由於索引數據在存儲的時候默認已經進行了升序排序(若有需要，也可以創建降序索引，該案例是基於Oracle環境，對於MySQL資料庫，從8.0開始也支持了真正意義的降序索引)，因此使用了索引全掃描(即索引遍歷)來避免排序，後期需要遍歷的索引塊越來越多，並且由於index full scan是單塊讀，所以該方法會出現在分頁後期查詢效率越來越慢的情況。如下圖所示：
5.直接使用主鍵代替ROWNUM進行分頁查詢
查出id的最大值和最小值：
SQL: select * from emp where id between &1 and &2;
執行計劃信息：
從執行計劃信息可以看出，該方法使用了主鍵索引的range scan，當表數據量較大時，不會出現隨著查詢範圍的擴大而查詢效率越來越低的情況，因為可以直接通過主鍵或非空唯一性索引讀取到符合條件的rowid，然後直接通過rowid找到數據塊讀取數據，如下圖所示：
說明：
該方法需要主鍵值是連續的，否則有可能出現分頁查詢時每一頁的數據行數不一樣的情況。
假如表上有其他的非空唯一性索引列，則同樣可以基於該列做分頁查詢。
若在分頁查詢時表上有一定的DML操作，則可以考慮進行最後一頁查詢時將SQL中的變量2設置較大一些(也可以通過子查詢直接獲取max(id))。
SQL: select * from ( select e.*, row_number() over (order by id) rn from emp e) where rn between &1 and &2;
執行計劃信息：
從執行計劃信息可以看出，該方法使用了窗口函數進行分頁查詢，同樣使用了INDEX FULL SCAN來避免排序，該方法也會出現在分頁後期查詢效率越來越慢的情況，因為後期需要遍歷的索引塊越來越多，並且由於index full scan是單塊讀，因此後期的效率有可能會比使用ROWNUM的方式更為低下，如下圖所示：
SQL: select * from emp order by id OFFSET &1 ROWS FETCH NEXT &2 ROWS ONLY;
執行計劃信息：
從執行計劃可以看出，offset-fetch機制在底層本質上還是基於分析函數實現的，同樣使用了索引全掃描(即索引遍歷)來避免排序，因此該方法也會出現在分頁後期查詢效率越來越慢的情況，因為後期需要遍歷的索引塊越來越多，並且由於index full scan是單塊讀，從而產生的物理IO和邏輯IO次數更多，因此後期的效率有可能會比使用ROWNUM的方式更為低下，如下圖所示：
8.排序列的選擇
當列可為NULL時，Oracle不能使用該列上的索引來避免排序，因為Oracle的索引是不記錄NULL值的，如下圖所示：
通過對比分析，我們可以得出如下結論：
1.當主鍵值或者非空唯一性列值是連續時，推薦使用主鍵值或者非空唯一性列進行分頁，此時分頁效率較高且數據量較大時分頁後期性能不會越來越差。
2.當對分頁後每頁的數據行數沒有較高要求時，同樣推薦使用主鍵值或者非空唯一性列進行分頁。
3.使用分析函數和OFFSET-FETCH實現分頁，分頁後期的性能衰減率可能會比通過ROWNUM的方式高，這是因為index full scan是單塊讀，從而產生了更多次的物理IO和邏輯IO。
4.在使用分析函數和OFFSET-FETCH機制時，需要基於主鍵或非空唯一性列進行order by排序，此時會通過列上的索引來避免排序操作。若選擇的排序列可為NULL，則Oracle資料庫只能通過全表掃描來訪問數據，因為Oracle資料庫的索引是不記錄NULL值的，因此不能基於該列上的索引來避免排序，從而保證不會丟失數據。
5.在MySQL中，索引是會記錄NULL值的，這也是為什麼MySQL中IS NULL可以走索引的原因。
6.MySQL資料庫的分頁中，可以使用可為null的非唯一性列作為排序列，因為此時MySQL會將null值當作最小值參加排序，不會丟失數據。
作者介紹：
吳海存，10g/11g/12c OCM, Oracle Exadata/Golden Gate 專家, 曾於Amazon和Oracle公司擔任全球業務資深DBA，目前供職於中國農業銀行，擔任資深資料庫專家。
更多閱讀推薦