一個容易被忽略的SQL調優技巧 --- order by字段到底要不要加入索引

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

Part 1

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

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


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

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

Part 2

上面的實驗中order by字段加入索引的作用非常明顯。可是在實際生產環境中，能有這么簡單的SQL來給DBA調優的機會并不多，實際生產中的SQL往往要更復雜一些。下面我們就把測試變得復雜一點，復制測試表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方法。按通常的經驗建索引首先會考慮where條件后的字段，但是在使用like的過濾條件上建立索引，效果可能并不好。可是如果這條語句是業務系統中執行頻率非常高的語句呢，我們還是硬著頭皮優化一下吧。先看一下沒有索引的情況。

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


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

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


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

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


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

到這里，從建索引的角度出發，小編認為這條SQL的優化可以交差了。

Part 3

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

文章屬于轉載文章