作者 | 中國農(nóng)業(yè)銀行 吳海存

責(zé)編 | 晉兆雨

頭圖 |?CSDN下載自視覺中國

導(dǎo)讀

本文主要介紹了基于ROWNUM、主鍵列/非空唯一性列、分析函數(shù)、OFFSET-FETCH NEXT機(jī)制的幾種SQL分頁查詢方案的性能對比。

分頁查詢可分為邏輯分頁和物理分頁兩種。邏輯分頁是應(yīng)用代碼級別實(shí)現(xiàn)的分頁，指用戶通過一次查詢就取出所有的數(shù)據(jù)結(jié)果集并進(jìn)行緩存，然后根據(jù)當(dāng)前頁所需要展示的數(shù)據(jù)內(nèi)容進(jìn)行切分并遍歷顯示，若需要查詢的數(shù)據(jù)量非常大，則會消耗大量的內(nèi)存來緩存數(shù)據(jù)，并且在會話生命周期內(nèi)重復(fù)訪問數(shù)據(jù)時(shí)，可直接訪問緩存的數(shù)據(jù)，不過此時(shí)有可能訪問不到最新的數(shù)據(jù)。物理分頁是指使用數(shù)據(jù)庫自帶的分頁機(jī)制，比如MySQL的limit offset機(jī)制，Oracle的rownum和offset-fetch機(jī)制進(jìn)行分頁查詢，是對數(shù)據(jù)庫表數(shù)據(jù)進(jìn)行分頁條件查詢，每一次物理分頁都會直接訪問數(shù)據(jù)庫，可以保證數(shù)據(jù)是最新的，并且不需要在會話級別緩存過多的數(shù)據(jù)。

本文主要介紹的SQL分頁，即物理分頁，主要用于在數(shù)據(jù)結(jié)果集較大時(shí)控制數(shù)據(jù)在前臺(比如報(bào)表，列表框，頁面等)的分頁顯示，這樣既可以降低內(nèi)存消耗，提高查詢效率，也可以方便數(shù)據(jù)在前臺的展示。文中如有疏漏之處，望指正！

環(huán)境版本信息

• Oracle 版本：19.3.0.0.0

• MySQL版本：8.0.18

• OS版本：CentOS 8.0

方案及性能對比

1.確認(rèn)測試表emp中的數(shù)據(jù)量

2.確認(rèn)表結(jié)構(gòu)和索引信息

3.通過rownum實(shí)現(xiàn)分頁查詢(不使用order by排序)

SQL:?select?*?from?(?select?rownum?rowno,e.*?from?emp?e?where?rownum<=&ROW_NUM1)?t?where?t.rowno>=&ROW_NUM2;

?執(zhí)行計(jì)劃信息:

通過執(zhí)行計(jì)劃和評估開銷可以看出，該方法將使用全表掃描，前段的分頁查詢效率會比較高，但是隨著ROWNUM值的增大，在分頁后期查詢的速度會越來越慢，這個(gè)情況和MySQL的limit機(jī)制一樣，當(dāng)表中數(shù)據(jù)量較大時(shí)，隨著查詢范圍的擴(kuò)大，每次需要讀取的表數(shù)據(jù)塊越來越多，查詢效率越來越低。如下圖所示：

?

4.通過rownum實(shí)現(xiàn)分頁查詢(使用order by排序)

SQL:?select?*?from?(?select?rownum?rowno,e.*?from?(select?*?from?emp?order?by?id)?e?where?rownum<=&2)?t?where?t.rowno>=&1;

執(zhí)行計(jì)劃信息：

由執(zhí)行計(jì)劃信息可以看出，當(dāng)使用order by對數(shù)據(jù)集進(jìn)行排序后再分頁時(shí)，由于索引數(shù)據(jù)在存儲的時(shí)候默認(rèn)已經(jīng)進(jìn)行了升序排序(若有需要，也可以創(chuàng)建降序索引，該案例是基于Oracle環(huán)境，對于MySQL數(shù)據(jù)庫，從8.0開始也支持了真正意義的降序索引)，因此使用了索引全掃描(即索引遍歷)來避免排序，后期需要遍歷的索引塊越來越多，并且由于index full scan是單塊讀，所以該方法會出現(xiàn)在分頁后期查詢效率越來越慢的情況。如下圖所示：

5.直接使用主鍵代替ROWNUM進(jìn)行分頁查詢

查出id的最大值和最小值：

SQL:?select?*?from?emp?where?id?between?&1?and?&2;

執(zhí)行計(jì)劃信息：?

從執(zhí)行計(jì)劃信息可以看出，該方法使用了主鍵索引的range scan，當(dāng)表數(shù)據(jù)量較大時(shí)，不會出現(xiàn)隨著查詢范圍的擴(kuò)大而查詢效率越來越低的情況，因?yàn)榭梢灾苯油ㄟ^主鍵或非空唯一性索引讀取到符合條件的rowid，然后直接通過rowid找到數(shù)據(jù)塊讀取數(shù)據(jù)，如下圖所示：

說明：

• 該方法需要主鍵值是連續(xù)的，否則有可能出現(xiàn)分頁查詢時(shí)每一頁的數(shù)據(jù)行數(shù)不一樣的情況。

• 假如表上有其他的非空唯一性索引列，則同樣可以基于該列做分頁查詢。

• 若在分頁查詢時(shí)表上有一定的DML操作，則可以考慮進(jìn)行最后一頁查詢時(shí)將SQL中的變量2設(shè)置較大一些(也可以通過子查詢直接獲取max(id))。

6.使用分析函數(shù)進(jìn)行分頁查詢

SQL:?select?*?from?(?select?e.*,?row_number()?over?(order?by?id)?rn?from?emp?e)?where?rn?between?&1?and?&2;?

執(zhí)行計(jì)劃信息：

從執(zhí)行計(jì)劃信息可以看出，該方法使用了窗口函數(shù)進(jìn)行分頁查詢，同樣使用了INDEX FULL SCAN來避免排序，該方法也會出現(xiàn)在分頁后期查詢效率越來越慢的情況，因?yàn)楹笃谛枰闅v的索引塊越來越多，并且由于index full scan是單塊讀，因此后期的效率有可能會比使用ROWNUM的方式更為低下，如下圖所示：

SQL:?select?*?from?emp?order?by?id?OFFSET?&1?ROWS?FETCH?NEXT?&2?ROWS?ONLY;

執(zhí)行計(jì)劃信息：

從執(zhí)行計(jì)劃可以看出，offset-fetch機(jī)制在底層本質(zhì)上還是基于分析函數(shù)實(shí)現(xiàn)的，同樣使用了索引全掃描(即索引遍歷)來避免排序，因此該方法也會出現(xiàn)在分頁后期查詢效率越來越慢的情況，因?yàn)楹笃谛枰闅v的索引塊越來越多，并且由于index full scan是單塊讀，從而產(chǎn)生的物理IO和邏輯IO次數(shù)更多，因此后期的效率有可能會比使用ROWNUM的方式更為低下，如下圖所示：?

8.排序列的選擇

當(dāng)列可為NULL時(shí)，Oracle不能使用該列上的索引來避免排序，因?yàn)镺racle的索引是不記錄NULL值的，如下圖所示：

通過對比分析，我們可以得出如下結(jié)論：

1.當(dāng)主鍵值或者非空唯一性列值是連續(xù)時(shí)，推薦使用主鍵值或者非空唯一性列進(jìn)行分頁，此時(shí)分頁效率較高且數(shù)據(jù)量較大時(shí)分頁后期性能不會越來越差。

2.當(dāng)對分頁后每頁的數(shù)據(jù)行數(shù)沒有較高要求時(shí)，同樣推薦使用主鍵值或者非空唯一性列進(jìn)行分頁。

3.使用分析函數(shù)和OFFSET-FETCH實(shí)現(xiàn)分頁，分頁后期的性能衰減率可能會比通過ROWNUM的方式高，這是因?yàn)閕ndex full scan是單塊讀，從而產(chǎn)生了更多次的物理IO和邏輯IO。

4.在使用分析函數(shù)和OFFSET-FETCH機(jī)制時(shí)，需要基于主鍵或非空唯一性列進(jìn)行order by排序，此時(shí)會通過列上的索引來避免排序操作。若選擇的排序列可為NULL，則Oracle數(shù)據(jù)庫只能通過全表掃描來訪問數(shù)據(jù)，因?yàn)镺racle數(shù)據(jù)庫的索引是不記錄NULL值的，因此不能基于該列上的索引來避免排序，從而保證不會丟失數(shù)據(jù)。

5.在MySQL中，索引是會記錄NULL值的，這也是為什么MySQL中IS NULL可以走索引的原因。

6.MySQL數(shù)據(jù)庫的分頁中，可以使用可為null的非唯一性列作為排序列，因?yàn)榇藭r(shí)MySQL會將null值當(dāng)作最小值參加排序，不會丟失數(shù)據(jù)。

作者介紹：

吳海存，10g/11g/12c OCM, Oracle Exadata/Golden Gate 專家, 曾于Amazon和Oracle公司擔(dān)任全球業(yè)務(wù)資深DBA，目前供職于中國農(nóng)業(yè)銀行，擔(dān)任資深數(shù)據(jù)庫專家。

更多閱讀推薦

• 藍(lán)色巨人IBM全力奔赴的混合云之旅能順利嗎？

• 大數(shù)據(jù)給教育帶來怎樣的可能？

• 對話阿里云：開源與自研如何共處？

• 除了云原生，2021 年還有這八大趨勢值得關(guān)注

• 算力至上？四大AI芯片大對決

總結(jié)

以上是生活随笔為你收集整理的SQL分页查询方案的性能对比的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。