? ? ? ?索引是SQL優(yōu)化使用頻度最高的優(yōu)化武器，甚至可以說你對(duì)索引有了深刻的理解，你基本可以優(yōu)化身邊60%以上的SQL。下面從五個(gè)方面來說明索引有關(guān)的知識(shí)：

1.索引的結(jié)構(gòu)

? ? ? ? ?索引由ROOT（根塊），Branch（莖塊），Leaf（葉子塊）三部分組成，其中Leaf(葉子塊）主要存儲(chǔ)key column value（索引列具體值），以及能具體定位到數(shù)據(jù)庫所在位置的rowid。

2.索引的特性

根據(jù)索引的結(jié)構(gòu)，可提煉出索引的三大特性：

a.索引高度較低

b .索引存儲(chǔ)列值

c.索引本身有序

3.索引三大特性在優(yōu)化中的應(yīng)用

3.1.索引高度較低

? ? ? ? 一般表的索引高度都比較低，大部分都在3到5層，很少有超過六層（見過一個(gè)90億的表，也就六層）。索引高度低，這就有利于使用索引范圍掃描，而且隨著記錄的增加，相對(duì)于全表訪問，索引訪問的優(yōu)勢也越來越明顯。

3.2索引存儲(chǔ)列值

（1）優(yōu)化count

? ? ? ? 在未建索引的情況下，直接用count去計(jì)算表的記錄數(shù)，一般都會(huì)走全表掃，如果在表上建有索引，將會(huì)大大減少邏輯讀，極大地提高SQL的性能。

（2）優(yōu)化avg和sum

? ? ? ? 在未建索引的情況下，直接用avg和sum，一般都會(huì)走全表掃，如果在對(duì)應(yīng)列上建有索引，將會(huì)大大減少邏輯讀，極大地提高SQL的性能。

3.3索引本身有序

（1）優(yōu)化order by

? ? ? ? ?當(dāng)sql語句中有order by語句的時(shí)候，必然會(huì)產(chǎn)生排序，排序是一個(gè)非常耗費(fèi)資源的操作。新增索引后，Oracle會(huì)利用索引本身有序的特點(diǎn)，來避免排序。

（2）優(yōu)化max和min

利用索引有序的特性，在葉子塊的最左邊或最右邊找到最大值和最小值。

4.組合索引的選用

? ? ? ? ?組合索引適用于單列查詢返回多，組合查詢返回少的場景；組合查詢的組合順序，要考慮單獨(dú)的前綴查詢；僅等值無范圍查詢時(shí)，組合的順序不影響性能；組合索引最佳順序一般是將等值查詢的列置前。

5.索引的不利之處

? ? ? ? ?索引對(duì)于查詢語句性能的提升是顯而易見的，但是索引的缺點(diǎn)也很明顯，由于索引本身是有序的，在更新數(shù)據(jù)的過程中也要更新索引，更新完后還要保持索引的有序性，這就需要很大的開銷。索引的不足之處從索引的開銷和容易失效兩個(gè)方面來討論:

(1)索引的各種開銷

a.熱塊競爭

? ? ? ? 索引最新的數(shù)據(jù)一般在最右邊，而我們訪問數(shù)據(jù)時(shí)正常來說也是訪問比較新的數(shù)據(jù)，歷史數(shù)據(jù)很少有人關(guān)注，這就容易產(chǎn)生熱塊的競爭。

b.回表開銷

? ? ? ? 索引存儲(chǔ)索引列和rowid，當(dāng)要訪問表中其它列時(shí)，需要通過rowid來定位回到表中，其實(shí)這個(gè)開銷也是很大的。索引查詢盡可能要避免回表，如果不可避免，則需要關(guān)注聚合因子是否過大。

c.更新開銷

? ? ? ? ?索引的有序性是一個(gè)非常重要的特性，這個(gè)特性能夠消除排序等開銷，但是索引要保持有序性，也是非常不容易的一件事。比較索引列的數(shù)據(jù)是隨機(jī)插入的，為了保證這個(gè)順序，索引需要做很多事，比如索引塊分裂，而索引列的增刪改的開銷是很大的。在沒有建索引的情況下，數(shù)據(jù)量不會(huì)影響插入的速度，但是當(dāng)建有索引的時(shí)候，數(shù)量量越大，插入會(huì)越來越慢。

d.建立開銷

? ? ? ? 我們在建索引的過程中，首先要把索引列的數(shù)據(jù)排序提取出來，再插入到塊中形成索引塊，這時(shí)如果數(shù)據(jù)不斷地插入，就會(huì)影響索引的建立，所以還必須鎖表，這就是一個(gè)極大的開銷（一般工作中會(huì)noline建索引，可以避免這個(gè)）。

（2）索引的使用失效

? ? ? ?索引的使用失效包括邏輯失效和物理失效兩種。邏輯失效指的是索引本身沒有失效，只是由于寫法的問題導(dǎo)致索引用不到。物理失效就是索引真的失效，比如被人誤設(shè)了unable動(dòng)作，或者是一些類似分區(qū)表的不規(guī)范操作導(dǎo)致索引失效。

a.索引邏輯失效

（1）用索引反而代價(jià)變得更高

（2）發(fā)生索引列的類型轉(zhuǎn)換

b.索引物理失效

（1）long列調(diào)整導(dǎo)致索引失效

（2）move操作會(huì)導(dǎo)致索引失效

（3）分區(qū)表的操作導(dǎo)致索引失效

? ? ? ? ? 分區(qū)表的truncate，drop，split，exchange等操作，都會(huì)導(dǎo)致全局索引失效，當(dāng)操作時(shí)加上update global indexes，則全局索引不會(huì)失效。