Rownum和Order By的執(zhí)行順序造成的影響

6月的某天下午，某用戶反饋，醫(yī)生站首頁(yè)中病案附加項(xiàng)目的順序亂了，影響醫(yī)生的正常工作。

從跟蹤出來(lái)的SQL看到，執(zhí)行得到的數(shù)據(jù)沒(méi)有按預(yù)期的方式排序，但是，相同的SQL在測(cè)試庫(kù)運(yùn)行卻能得到正常排序的結(jié)果。

院方近期沒(méi)有做什么調(diào)整，懷疑跟一個(gè)月前我們轉(zhuǎn)移歷史數(shù)據(jù)之前的一系列性能優(yōu)化調(diào)整有關(guān)。

經(jīng)過(guò)一番分析，最終，通過(guò)重新收集該SQL涉及到的表的統(tǒng)計(jì)信息后，問(wèn)題得到解決。

感覺(jué)有點(diǎn)兒不可思議吧？

統(tǒng)計(jì)信息收集不正確，還會(huì)影響產(chǎn)品功能的正常性？

有些事情的真相并不是我們看到那樣，就像雞血在磨心上點(diǎn)了幾下之后，小孩的肚子就不痛了，如果你相信這樣的巫術(shù)，可能會(huì)影響到你對(duì)很多事情的判斷，有時(shí)，我們看到的并不一定是真相，倒不是因?yàn)閷?duì)未知的知識(shí)缺乏敬畏，而是基本的邏輯推理問(wèn)題，以及探尋真相的鉆研精神。

統(tǒng)計(jì)信息收集確實(shí)是解決很多性能問(wèn)題的一副靈藥，但對(duì)這個(gè)問(wèn)題來(lái)說(shuō)，收集統(tǒng)計(jì)信息只是其中的一種臨時(shí)解決辦法，根本的原因還是SQL書寫方面存在問(wèn)題。

下面我們來(lái)一步一步解開事情的真相。

SQL語(yǔ)句如下：

select rownum as 序號(hào),編碼,名稱,內(nèi)容 from 病案項(xiàng)目 order by 編碼

多么簡(jiǎn)單的SQL，不像那些需要翻幾頁(yè)才能看得完整的變態(tài)SQL，這種簡(jiǎn)單的SQL除了理解起來(lái)更節(jié)約時(shí)間之外，還減少了很多可能導(dǎo)致驗(yàn)證結(jié)果偏差的干擾。

按開發(fā)人員的預(yù)期，先排序再對(duì)結(jié)果集進(jìn)行編號(hào)，Rownum作為Oracle下特有的“偽列”，作用就是根據(jù)數(shù)據(jù)結(jié)果行數(shù)產(chǎn)生一個(gè)從1開始遞增的行號(hào)。

有一定基礎(chǔ)的同學(xué)可能一下就看出的不對(duì)之處：

Order by和Rownum在同一層次時(shí)，排序是最后執(zhí)行的，先取行號(hào)自然就無(wú)法得到想要的按排序結(jié)果遞增的行號(hào)，所以，如下所示，序號(hào)列就是“亂序”的。

那么，問(wèn)題來(lái)了，既然這個(gè)SQL有問(wèn)題，為什么在測(cè)試庫(kù)運(yùn)行又是正常的呢？

近期產(chǎn)品中的這條SQL也沒(méi)有做過(guò)修改，為什么以前是正常的，現(xiàn)在卻突然出現(xiàn)問(wèn)題了呢？

沒(méi)有騙你喲，用戶發(fā)來(lái)了測(cè)試庫(kù)上執(zhí)行后結(jié)果正確的截圖。

剛開始看到這個(gè)現(xiàn)象，也是很納悶，會(huì)不會(huì)是數(shù)據(jù)庫(kù)的什么參數(shù)影響了排序，像經(jīng)常遇到的"_gby_hash_aggregation_enabled"這個(gè)參數(shù)對(duì)Group By排序的影響（后續(xù)可能會(huì)寫一個(gè)這方面的案例），問(wèn)題是這個(gè)SQL里沒(méi)有Group by子句。

從數(shù)據(jù)庫(kù)的基礎(chǔ)理論方面想了想，影響排序的還有哪些因素呢？

如果是有索引，那么索引本身就是排了序的，讀取數(shù)據(jù)時(shí)就不需要排序了，再用Rownum取值，是不是就可以取到預(yù)期的結(jié)果了呢？

也就是說(shuō)，變相實(shí)現(xiàn)了先排序，后取序號(hào)的作用。

為了證實(shí)這一點(diǎn)，在公司的測(cè)試庫(kù)上做了一個(gè)驗(yàn)證：

1.     相同的SQL，執(zhí)行結(jié)果跟用戶測(cè)試庫(kù)的結(jié)果是一樣的，序號(hào)正常排序。

2.     禁用主鍵“病案項(xiàng)目_PK”(以“編碼”字段為索引)后，執(zhí)行結(jié)果跟用戶正式庫(kù)的結(jié)果一樣，序號(hào)排序就亂了，再現(xiàn)了問(wèn)題。

alter table 病案項(xiàng)目 disable constraint 病案項(xiàng)目_PK;

恢復(fù)主鍵后，序號(hào)就正常排序了。

alter table 病案項(xiàng)目 enable constraint 病案項(xiàng)目_PK;

是不是用戶生產(chǎn)庫(kù)的主鍵被禁用了，索引丟了？

轉(zhuǎn)移歷史數(shù)據(jù)期間，的確會(huì)禁用一些約束，但是這張表并不是轉(zhuǎn)出相關(guān)的表呀！而且轉(zhuǎn)完數(shù)據(jù)后，我們恢復(fù)約束后也做過(guò)檢查。

會(huì)不會(huì)是用戶后期運(yùn)行過(guò)程中，某種原因?qū)е略撍饕裏o(wú)效了呢？

馬上查詢用戶的生產(chǎn)庫(kù)，主鍵是有效的，索引也是有效的。

眼看找到一條路，沒(méi)想到走到底發(fā)現(xiàn)是個(gè)死胡同，不要懈氣，既然問(wèn)題再現(xiàn)了，原理也清楚了，順著這條路，仔細(xì)找找，一定有出路。

在測(cè)試環(huán)境，對(duì)比分析一下，禁用主鍵（刪除“編碼”字段的索引）前后的執(zhí)行計(jì)劃。

對(duì)比發(fā)現(xiàn)了差異：

有索引時(shí)，執(zhí)行計(jì)劃包含” INDEX FULL SCAN”，沒(méi)有排序操作。

沒(méi)有索引時(shí)，執(zhí)行計(jì)劃包含” TABLE ACCESS FULL”，有排序操作“SORT ORDER BY”。

用戶生產(chǎn)庫(kù)的執(zhí)行計(jì)劃是什么呢？

一查詢，結(jié)果跟我在這邊測(cè)試環(huán)境下的執(zhí)行計(jì)劃一樣，走了全表掃描。

為什么沒(méi)有走索引全掃呢？

會(huì)不會(huì)是統(tǒng)計(jì)信息收集有問(wèn)題，導(dǎo)致成本評(píng)估時(shí)，認(rèn)為全表掃描的成本更低，所以選擇了它呢。

在用戶生產(chǎn)庫(kù)收集了一下統(tǒng)計(jì)信息，結(jié)果就正常了，執(zhí)行計(jì)劃變成了” INDEX FULL SCAN”。

表的統(tǒng)計(jì)信息丟失的原因，不得而知。

其實(shí)解決問(wèn)題的辦法還有其他的，例如：通過(guò)Sql Profile加提示字指定索引。

既然是統(tǒng)計(jì)信息沒(méi)有收集的問(wèn)題，那是不是可以再現(xiàn)一下問(wèn)題現(xiàn)象呢？

完全可以。

我們刪除統(tǒng)計(jì)信息后來(lái)看看，是不是就再現(xiàn)用戶生產(chǎn)庫(kù)的場(chǎng)景了呢？

exec dbms_stats.delete_table_stats(ownname => 'ZLHIS',tabname => '病案項(xiàng)目');

再PLSQL中查看剛才那條SQL的執(zhí)行計(jì)劃，確實(shí)從索引全掃描變成了全表掃描。

執(zhí)行SQL，查詢一下數(shù)據(jù)，咦，怎么數(shù)據(jù)不是亂序的？

執(zhí)行計(jì)劃不是變了嗎？

什么情況？

又到強(qiáng)調(diào)掌握數(shù)據(jù)庫(kù)基礎(chǔ)理論的重要性的時(shí)候了，如果只學(xué)習(xí)那些用得到的知識(shí)，書到用時(shí)方恨少，只有打開堅(jiān)實(shí)的基礎(chǔ)，才能在實(shí)戰(zhàn)中臨場(chǎng)應(yīng)對(duì)。

清空一下共享池再看看。

alter system flush SHARED_POOL;

再次執(zhí)行數(shù)據(jù)查詢，哈哈，這次，“編碼”字段終于亂序了，一陣竊喜。

數(shù)據(jù)亂了還要高興，什么原因？因?yàn)樗缒闼?，你看到了想要的結(jié)果。

為什么之前從PLSQL看到執(zhí)行計(jì)劃是全表掃描，但查詢數(shù)據(jù)卻跟索引全掃描的結(jié)果一樣呢？

別忘了，PLSQL只是一個(gè)工具，它的F5查看執(zhí)行計(jì)劃的功能，不知道騙了多少人，坑了多少人。

其實(shí)你明白它查看執(zhí)行計(jì)劃的原理，就知道它不是真的想騙你，如果你有興趣，可以通過(guò)10046去探個(gè)究竟，因?yàn)槠邢?，這里就不再詳述。

總結(jié)：

至此，這個(gè)問(wèn)題搞清楚了，我們?cè)賮?lái)回看一下SQL，重新梳理一下：

select rownum as 序號(hào),編碼,名稱,內(nèi)容 from 病案項(xiàng)目 order by 編碼

當(dāng)Order by和Rownum在同一層次時(shí)，排序是最后執(zhí)行的，先取行號(hào)再排序，這樣就無(wú)法根據(jù)排序結(jié)果得到正確行號(hào)，如果能夠避免排序，就能得到期望的結(jié)果。

當(dāng)Order by中的字段是非空索引時(shí)（主鍵索引屬于非空索引），如果統(tǒng)計(jì)信息收集正確，執(zhí)行計(jì)劃評(píng)估成本后，就會(huì)選擇“索引全掃描”，由于索引本身是排了序的，就能避免排序。

當(dāng)表的統(tǒng)計(jì)信息丟失，優(yōu)化器在評(píng)估成本時(shí)，由于缺乏統(tǒng)計(jì)信息作為成本計(jì)算的依據(jù)，它就會(huì)選擇“全表掃描”作為執(zhí)行計(jì)劃，然后再排序。

為了避免統(tǒng)計(jì)信息收集導(dǎo)致的這種問(wèn)題，在各種用戶環(huán)境下得到穩(wěn)定的結(jié)果，這個(gè)SQL可以改為：

Select Rownum As 序號(hào), 編碼, 名稱, 內(nèi)容 From (Select 編碼, 名稱, 內(nèi)容 From 病案項(xiàng)目 Order By 編碼)

在子查詢中先排序，外層查詢中再取Rownum，類似的，當(dāng)我們?cè)趯?span>Rownum<5這類SQL時(shí)，也需要注意排序的問(wèn)題，應(yīng)該先在子查詢中寫排序，外面再限制返回行數(shù)。

思考：

為了加深對(duì)這個(gè)知識(shí)的理解，你可以試一下這兩個(gè)SQL：

Select 姓名 From 人員表 Where Rownum < 2 Order By 建檔時(shí)間 Desc;

Select 姓名 From 人員表 Where Rownum < 2 Order By ID Desc;

ID是主鍵，順序遞增，建檔時(shí)間最大的記錄，其ID最大。

為什么兩個(gè)SQL得到的數(shù)據(jù)不樣呢？對(duì)比一下執(zhí)行計(jì)劃看看。

知識(shí)點(diǎn)的擴(kuò)展應(yīng)用：

利用這個(gè)案例中的知識(shí)點(diǎn)：排序字段如果與非空索引的字段順序相同，則可以利用索引本身已排序的特性來(lái)避免排序，這在一些高并發(fā)的高頻SQL中，對(duì)系統(tǒng)的整體性能提升將會(huì)起到非常重要的作用，因?yàn)榕判虿僮鲗?duì)cpu消耗比較大，特別是那些大的、高頻的排序。

特別提醒，兩個(gè)重要條件：字段順序相同，非空索引。


------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
公眾號(hào)：醫(yī)信系統(tǒng)性能優(yōu)化
主要寫一些日常工作中性能優(yōu)化方面的案例，包括SQL優(yōu)化，數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)優(yōu)化，Oracle系統(tǒng)性能優(yōu)化。
面向編寫SQL及相關(guān)腳本的開發(fā)人員和技術(shù)支持人員，分享一些性能優(yōu)化的經(jīng)驗(yàn)。
對(duì)性能優(yōu)化技術(shù)學(xué)習(xí)感興趣的同學(xué)，歡迎訂閱，共同學(xué)習(xí)，相互交流。