【TUNE_ORACLE】Oracle索引設計思想（四）三星級索引

什麼是三星級索引

三星級索引是一種能夠 同時滿足下面三個條件的查詢效能較好的索引：

第一顆星：擁有最窄索引片（窄索引，把等值謂詞放在索引最前面）

第二顆星：避免排序（出現在關鍵字“order by”後面的列才能滿足第二顆星）

第三顆星：查詢語句中所有列都在索引中（寬索引，也是最容易達成的條件，因此優先順序最低）

 

三星級索引設計思想

先放例子：

select stu_id,stu_name from students where score between 90 and 100 and class_no=302 order by stu_name;

1. 先滿足第三顆星，把謂詞全部放到索引中（謂詞在索引中的順序先暫定）

2. 有一個等值謂詞“class_no=302”，因此把該謂詞放到索引第一個來 儘可能滿足第一顆星（現在還達不到第一顆星的效果，只是接近）。此時索引可以是idx1(class_no, stu_name, score，stu_id) 或 idx2(class_no, score, stu_name,stu_id)

3. 為了滿足第二顆星，必須把索引中的“stu_name”放在“score”前面（比如上面的索引idx1或者idx3(stu_name,xxx,xxx,xxx)）。如果不這樣做，就最佳化器必須先排序，因為索引是按謂詞從左到右來自動排序的， 第二顆星就是利用索引自動排序的特性來減少二次排序。但是這樣做的後果就是：不滿足第一顆星了！因為在滿足“class_no=302”條件後，沒有先對“score”排序，索引不是最窄索引（第一顆星）。

 

注意

實際情況中， 所有索引都能滿足第三顆星，但是要同時滿足第一和第二顆星的情況較少，因為絕大部分情況下謂詞條件都很複雜，所以大多數情況下只能選擇第一顆星或者第二顆星。比如上面的例子，出現了between謂詞或者其他範圍謂詞，所以不能同時擁有第一顆和第二顆星。 大多數情況下，第一顆星比第二顆星重要！

 

最後給出一個三星級索引的建立思想（僅供參考）：

首先設計一個索引片儘可能窄（第一顆星）的寬索引（第三顆星）。如果查詢語句的索引沒有涉及到排序操作（第二顆星），那這個索引就是完美的三星級索引，否則就是二星級索引，可犧牲第二顆星來達到最優效能。如果要避免排序，則可以犧牲第一顆星，保留第二顆星。這兩種情況在實際中都有用到，需要大家結合具體案例具體分析與選擇。