對于SQL調(diào)優(yōu)，要調(diào)就調(diào)到極致，小編并不是處女座，而是因為在一個并發(fā)量很大的業(yè)務(wù)系統(tǒng)中，對于頻繁執(zhí)行的單條SQL性能的提升，可能對整體數(shù)據(jù)庫的性能提升都有很大的意義。
但是遇到order by字段后面的字段，特別是當(dāng)這個字段不在過濾條件中時，小編就會心里打鼓，是加到索引里面呢，還是不加到索引里面呢，加進(jìn)去會不會沒有起到提升性能的作用，反而讓索引變得更加復(fù)雜，給系統(tǒng)帶來不必要的額外負(fù)擔(dān)，“偷雞不成蝕把米”，開個玩笑。但是如果直接忽略掉這個問題，很可能這個提升系統(tǒng)性能的機(jī)會就被錯過了。
所以今天小編就和大家探討一下，面對order by字段后面的條件，特別是這個條件不在過濾條件中時，到底要不要加入索引中，對于SQL調(diào)優(yōu)這筆賬，索引中加入order by字段，是賺了還是賠了?

創(chuàng)新新互聯(lián)，憑借十載的做網(wǎng)站、網(wǎng)站制作經(jīng)驗，本著真心·誠心服務(wù)的企業(yè)理念服務(wù)于成都中小企業(yè)設(shè)計網(wǎng)站有1000+案例。做網(wǎng)站建設(shè)，選創(chuàng)新互聯(lián)建站。

Part 1

空話不多說，先來一個小實驗，熱一下身。通過多次復(fù)制dba_objects中的數(shù)據(jù)，生成測試表T1，大約1000萬行數(shù)據(jù)。做一個簡單的查詢，查詢T1表中object_id最小的10行數(shù)據(jù)，select * from (select * from T1 order by object_id) where rownum<=10，耗時‘Elapsed: 00:00:35.92’，執(zhí)行計劃如下：

執(zhí)行計劃中可以看到，先作了一個全表掃，取到了結(jié)果集11M行（可以粗略理解為11百萬行，這個測試表T1行數(shù)為11943842）。然后作了一個排序，截取最小的10條記錄，最后返回結(jié)果。下面我們在object_id字段上建一個索引I_T1_ORDER3，作一個比較。
耗時從剛才的35秒，直接降到了 ‘Elapsed: 00:00:00.01’，提升性能的效果非常明顯。索引和執(zhí)行計劃如下：


從執(zhí)行計劃中可以看到，優(yōu)化器直接從索引中找到了最小的10條記錄，然后回表取得結(jié)果集返回。相比上一個執(zhí)行計劃，省去了全表掃描，省去了排序，所以執(zhí)行時間和系統(tǒng)資源消耗都大大減少。
在這里作一個簡單的分析，首先索引和數(shù)據(jù)不同，是按照有序的排列存儲的，當(dāng)結(jié)果集要求按照順序取得一部分?jǐn)?shù)據(jù)時，索引的功效會體現(xiàn)的非常明顯，本次查詢就是要取得object_id最小的10條記錄。其次，建立索引系統(tǒng)只需要消耗一次資源完成排序過程，而如果沒有索引，執(zhí)行不同的語句可能每次都要經(jīng)歷排序的過程，會消耗更多的系統(tǒng)資源。從這個實驗看，在order by字段建索引是非常劃算的，而且order by字段并不一定非要加入到where條件中也可以生效。

這里小編要和大家分享一個自己踩到的“坑”，就是小編起初在建了索引I_T1_ORDER3后，這條查詢語句的執(zhí)行計劃并不選擇索引，增加了hint提示也不選擇，小編都有點懷疑人生了，明顯使用索引會好，為什么優(yōu)化器偏偏不選擇索引呢，而且是加了hint也不走。在修改object_id列為非空屬性（NOT NULL）后，優(yōu)化器才選擇了這個索引。小編這里是這么理解的，如果這一列存在NULL值，NULL值是沒有大小這一說法的，而且不會被保存在索引中。如果優(yōu)化器無法確定該列沒有NULL值，為了保證結(jié)果集的準(zhǔn)確性，寧愿選擇更慢的全表掃描，也不會選擇走可能存在NULL的索引，即使用戶指定了hint也不會選擇（這里的幾句話有點繞，大家耐心讀一下）。從這一點來看，開發(fā)Oracle優(yōu)化器的小伙伴是非?？孔V的。

Part 2

上面的實驗中order by字段加入索引的作用非常明顯?？墒窃趯嶋H生產(chǎn)環(huán)境中，能有這么簡單的SQL來給DBA調(diào)優(yōu)的機(jī)會并不多，實際生產(chǎn)中的SQL往往要更復(fù)雜一些。下面我們就把測試變得復(fù)雜一點，復(fù)制測試表T1，生成測試表T2，查詢object_type類似INDEX中object_id最小的10條記錄，select * from (select * from T2 where object_type like '%INDEX%' order by object_id) where rownum<=10。
這條語句比第一個實驗中多了過濾條件，但是使用了like方法。按通常的經(jīng)驗建索引首先會考慮where條件后的字段，但是在使用like的過濾條件上建立索引，效果可能并不好?？墒侨绻@條語句是業(yè)務(wù)系統(tǒng)中執(zhí)行頻率非常高的語句呢，我們還是硬著頭皮優(yōu)化一下吧。先看一下沒有索引的情況。

執(zhí)行時間“Elapsed: 00:00:08.75”，接近9s，從執(zhí)行計劃中看到，先是全表掃描過濾出了1597K條（1597K約163萬條）記錄，然后作了個排序，返回object_id最小的10條記錄。
這樣的執(zhí)行效率在生產(chǎn)系統(tǒng)中是不能接受的，但是在like列上建索引效果可能并不好，本著敬業(yè)的精神，還是試一下吧。在僅有的兩個條件 object_type和object_id上建一個復(fù)合索引I_T2_ORDER2，并
加入hint提示，結(jié)果如下：


執(zhí)行時間“Elapsed: 00:00:17.25”，比剛才9秒還多花了8秒。從執(zhí)行計劃中可以看到，先是在索引I_T2_ORDER2中定位到1597K條記錄，然后回表取得1597K記錄的結(jié)果集，再排序取到object_id最小的10條記錄。與上一個執(zhí)行計劃相比，反而增加了一個讀索引的步驟，所以系統(tǒng)資源消耗更多，執(zhí)行時間也更長，而且雖然order by字段加入到索引中，并沒有省去排序的步驟。在這里這個索引建的就有點虧了。
“理想很豐滿，現(xiàn)實很骨感”，看來SQL變得復(fù)雜以后，order by字段在索引里面果然不靈了，這招不好使。不要著急，咱們分析一下，為什么不好使了。大家都知道索引是樹狀結(jié)構(gòu)，現(xiàn)在I_T2_ORDER2索引中有兩個字段，這個索引結(jié)構(gòu)大概是這個樣子的，如下圖。

大家可以看到，對應(yīng)INDEX節(jié)點下面的object_id“3,9,13”是有序的， INDEX PARTITION節(jié)點也類似。但是把INDEX節(jié)點和INDEX PARTITION節(jié)點對應(yīng)的object_id放到一起，“3,9,13…2,15,17”，就變得無序了，所以優(yōu)化器雖然使用了索引，但不得不再做一遍排序，order by索引的功效并沒有發(fā)揮出來。
看到這里是不是有點灰心了，這條語句沒法優(yōu)化了。看下本文的標(biāo)題，換個角度想一下，說不定這條語句還有救。與測試表T1一樣，在object_id上建一個索引I_T2_ORDER3試一下。


執(zhí)行時間從17s，直接變?yōu)椤癊lapsed: 00:00:00.01”，從執(zhí)行計劃可以看到，優(yōu)化器通過索引過濾了817條記錄后得到了想要的10條結(jié)果，之后回表取得結(jié)果返回。與上面的執(zhí)行計劃相比，時間消耗和資源消耗都大大減少。
這里我們簡單分析一下，索引I_T2_ORDER3是按照object_id有序排列的，當(dāng)優(yōu)化器按序處理到817條記錄時，就已經(jīng)得到了想要的object_type類似INDEX，object_id最小的10條記錄，然后回表取到結(jié)果并返回，省去了全表掃描以及排序的消耗，所以效率大大提升。索引結(jié)構(gòu)如下圖。

執(zhí)行時間和系統(tǒng)消耗，都大大減少，那么到這里我們是不是可以交差了。再看一下我們文章的開頭，“對于SQL調(diào)優(yōu)，要調(diào)就調(diào)到極致”， “對于頻繁執(zhí)行的單條SQL性能的提升，對整體數(shù)據(jù)庫的性能提升都有很大的意義”。我們再想一下還可不可以更優(yōu)。小編在這里又建了一個索引I_T2_ORDER4，再執(zhí)行這條查詢語句。


執(zhí)行時間“Elapsed: 00:00:00.01”，從執(zhí)行計劃中可以看到，優(yōu)化器通過索引直接定位到了想要的10條記錄，回表取得10條記錄并返回。最終結(jié)果只有10條記錄，優(yōu)化器也只處理了10條記錄，幾乎沒有任何的資源浪費。I_T2_ORDER4索引的結(jié)構(gòu)圖如下，可以看到，過濾條件已經(jīng)在索引中存儲了，所以優(yōu)化器可以在索引中直接定位到最終的10條記錄。

到這里，從建索引的角度出發(fā)，小編認(rèn)為這條SQL的優(yōu)化可以交差了。

Part 3

最后小編想說的是，遇到類似order by字段是否加入索引的問題，或者其他一些大家猶豫的問題，可以大膽的嘗試，并打開思路，從不同的角度考慮，多做測試，不要錯過任何一個提升性能的機(jī)會。
對于order by字段加入索引本身這個問題，如果最終的結(jié)果集是以order by字段為條件篩選的，將order by字段加入索引，并放在索引中正確的位置，會有明顯的性能提升。不過這里要注意小編前面提到的那個坑，order by字段需要是非空的屬性，否則會無效。

文章屬于轉(zhuǎn)載文章

本文名稱：一個容易被忽略的SQL調(diào)優(yōu)技巧---orderby字段到底要不要加入索引
URL分享：http://aaarwkj.com/article18/jechdp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供、用戶體驗、網(wǎng)站內(nèi)鏈、軟件開發(fā)、網(wǎng)站改版、網(wǎng)站設(shè)計

廣告

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