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

專注于為中小企業(yè)提供網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)建昌免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了上千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

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

 

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

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

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

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

 

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

 

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

下面我們來一步一步解開事情的真相。

SQL語句如下：

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

 

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

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

 

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

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

 

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

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

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

 

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

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

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

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

 

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

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

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

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

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

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

 

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

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

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

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

 

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

 

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

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

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

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

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

 

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

為什么沒有走索引全掃呢？

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

 

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

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

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

 

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

完全可以。

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

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ù)庫基礎(chǔ)理論的重要性的時(shí)候了，如果只學(xué)習(xí)那些用得到的知識(shí)，書到用時(shí)方恨少，只有打開堅(jiān)實(shí)的基礎(chǔ)，才能在實(shí)戰(zhàn)中臨場應(yīng)對。

 

清空一下共享池再看看。

alter system flush SHARED_POOL;

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

數(shù)據(jù)亂了還要高興，什么原因？因?yàn)樗缒闼福憧吹搅讼胍慕Y(jié)果。

 

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

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

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

 

總結(jié)：

至此，這個(gè)問題搞清楚了，我們再來回看一下SQL，重新梳理一下：

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

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

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

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

 

為了避免統(tǒng)計(jì)信息收集導(dǎo)致的這種問題，在各種用戶環(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)我們在寫Rownum<5這類SQL時(shí)，也需要注意排序的問題，應(yīng)該先在子查詢中寫排序，外面再限制返回行數(shù)。

 

思考：

為了加深對這個(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ù)不樣呢？對比一下執(zhí)行計(jì)劃看看。

 

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

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

特別提醒，兩個(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)。
對性能優(yōu)化技術(shù)學(xué)習(xí)感興趣的同學(xué)，歡迎訂閱，共同學(xué)習(xí)，相互交流。

本文名稱：Rownum和OrderBy的執(zhí)行順序造成的影響
標(biāo)題來源：http://sd-ha.com/article2/geesic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、品牌網(wǎng)站設(shè)計(jì)、微信公眾號(hào)、App設(shè)計(jì)、定制網(wǎng)站、用戶體驗(yàn)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)