《揭開索引讓SQL舉步維艱的另一面》要點：
本文介紹了揭開索引讓SQL舉步維艱的另一面，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

梁敬彬,福富研究院副理事長、公司唯一四星級內訓師,國內一線知名數據庫專家,在數據庫優化和培訓領域有著豐富的經驗.多次應邀擔任國內外數據庫大會的演講嘉賓,在業界有著廣泛的影響力.著有多本暢銷書籍,代表作有《收獲,不止Oracle》.

任何事物都有它的兩面性,索引也不例外,本文我們就來闡述一下索引的壞處.

大家可能沒注意到,在查詢語句中,如果更新語句出現,索引的缺點將很明顯.因為索引本身是有序的,而更新數據的過程中也要更新索引,更新完后還要保持索引的有序性,這就需要付出很大的開銷了,索引不好的一面就從這里開始顯示出來了.

這里我們先簡要綜述一下索引的不足之處有哪些,而后詳細闡述,隨后一起和大家探索各種工作中的案例,最后為思考回顧.

總體學習思路如下圖所示：

 

一、索引的不足之處

關于索引的不足之處,我們可以從索引的開銷和容易失效這兩個方面來討論,如下圖所示：

索引的各種開銷

還記得前面關于索引結構的分析嗎?通過系列步驟,我們明白了索引的結構,推導出索引的三大特性,并應用這些特性讓SQL跑得更快.

這只是索引好的一面.真正有問題的一面被掩蓋了.那到底都有什么問題

1.熱塊競爭

你看,索引最新的數據塊一般是在最右邊,而我們訪問數據時正常來說也是訪問比較新的數據,歷史數據很少有人關注.然而問題來了,大家都一起訪問最新的數據,不就都集中于同一個目標來訪問了嗎?這就很容易產生熱塊競爭.

2.回表開銷

另外,大家都知道索引存儲索引列的值和rowid,通過rowid來定位回到表中.其實這個回到表中的開銷也是很大,具體情況我們隨后可以了解到.

3.更新開銷

索引的有序性是一個非常重要的特性,這個特性能夠消除排序等開銷,但是索引塊要保持有序性,可不是一件容易的事.畢竟索引列的數據是隨機插入的,比如你在原來的索引列中存儲的是100、110、111等等時,現在要插入101,就應該在100和111之間插入,為了保證這個順序索引需要做很多事,比如索引塊分裂.而索引列的增刪改的開銷是很大的.

4.建立開銷

還有千萬別忽略了建立索引的開銷,這也和索引的有序性有關.我們在建索引的過程中,首先把索引列的數據排序提取出來,再插入到塊中形成索引塊,這時如果數據不斷地插入,排序提取這個動作什么時候能結束呢?所以還必須要鎖表,這就是一個很大的開銷(Online建索引是一種特殊的思路,這里不做描述).當然建索引過程中排序這個動作本身也是一個不小的開銷.

索引使用失效

索引的不足之處除了上述的幾點外,從另一個維度看,還會有失效的可能.我們現在知道建索引對查詢一般比較有利,對更新一般比較有害.不過有的時候,雖然建了索引,但其對查詢毫無幫助,這種情況還是有的.比如索引失效了,這分為邏輯失效和物理失效兩種.

1.邏輯失效

邏輯失效是索引本身并沒有真正失效,只是由于寫法的問題導致索引用不上,比如對SQL的條件列進行運算,類似select * from t where upper(name)=‘ABC’等,這時在name列上建了Btree索引是用不上的.再或者比如被人強制用了全表掃描的Hint等導致數據庫被迫不用索引等.

2.物理失效

物理失效就是索引真的失效了,比如被人誤設了unusable動作,或者是一些類似分區表的不規范操作導致的索引失效.對此后續有詳細的例子說明.

二、感受美好索引另一面

前面簡要描述了索引的不足之處,接下來我們進行更加詳細的展開說明,具體細節如下：

索引各種開銷

1.訪問集中導致熱塊的競爭

由于一般來說,最新的值都是最新產生的,所以訪問它容易產生熱塊競爭.舉例來說,如： select * from t where id=100000,select * from t where id=99999;select * from t where id=99998;select * from t where id=99997;這些數據很可能是相鄰的,那么它們就會在同一個索引塊上,這樣很容易產生熱點索引塊競爭.

2.回表性能取決于聚合因子

結論：索引查詢要盡可能避免回表,如果不可避免,則需要關注聚合因子是否過大.(注：這個例子在前面的章節已經說過了,這里就不再詳述了.)在該例子中,構造腳本organized表的聚合因子比較小,回表的代價較低,產生了2900個buffer,如下：

而disorganized表的聚合因子比較大,回表的代價很高,如下,產生21360個buffer：

3.索引更新方面的開銷比較大

環境搭建：

腳本1：無索引,表記錄增加,插入不怎么變慢

由上面代碼可以看出,雖然t_small是小表,t_big是大表.但是插入一般不會隨著記錄的增加越插越慢.什么時候會越插越慢,就是當表有索引的時候.因為索引需要維護,越大維護越困難.我們繼續做一組試驗.

環境準備(建3張結構和記錄都一樣的表,只是索引分別是6個、2個及無索引)：

分別往這三張表里插記錄：

腳本2：有索引的表,記錄越多,插入越慢

表記錄越大,索引越多,插入越慢,從試驗結果來看,這一點還是非常明顯的.

4.建索引的過程開銷也很大

(1)建索引過程會產生全表鎖

可以通過如下方式查看被鎖的情況：

腳本3：建索引產生鎖

(2)建索引過程會產生全表排序

未建索引前,觀察一下數字字典中記錄的系統排序情況,如下：

建索引后,繼續觀察,發現排序次數sorts (memory)增加了,如下：

腳本4：建索引產生排序

索引使用失效

索引邏輯失效

(1)用索引反而代價變得更高

這個道理比較簡單,如果應用索引范圍檢索數據,返回大量記錄且幾乎是所有的記錄,這時候用索引肯定有錯,索引范圍查詢訪問一般適合返回少量記錄的情況,否則用全表掃描或者全索引掃描就可以.

(2)發生索引列的類型轉換

在表字段設計的時候有一個非常重要的原則,什么類型的字段存什么類型的值,否則就會發生類型轉化,具體請看如下例子：

實際上只有如下寫法才可以用到索引,這個很不應該,如果什么類型的取值就設置什么樣的字段,把ID字段類型改為Number,就順暢了,如下：

腳本5：索引列的類型轉換

(3)對索引進行了各種運算

對索引列進行了各種運算,詳見后面的案例部分.

索引物理失效

(1)long列調整導致索引失效

環境準備(建表,建long字段)：

接下來將long修改為clob,發現索引失效了,必須重建索引,如下：

腳本6：long列調整導致索引失效

(2)move操作會導致索引失效

move是一個危險系數非常高的操作,雖然它可以收縮表降低高水平位,卻會導致索引失效,因而需要重建索引,請看下面例子：

腳本7：move操作導致索引失效

(3)分區表導致索引失效的操作

這在前面已經描述過了,這里就不再重復了,請讀者自行回到前面的章節進行復習總結.歸納如下：

truncate分區會導致全局索引失效,不會導致局部索引失效.如果對truncate 增加update global indexes,則全局索引不會失效.

drop分區會導致全局索引失效,局部索引因為drop分區,所以也不存在該分區的局部索引了.如果對drop分區增加update global indexes,全局索引不會失效.

split分區會導致全局索引失效,也會導致局部索引失效.如果對split分區增加update global indexes,則全局索引不會失效.

add 分區不會導致全局索引失效,也不會導致局部索引失效.

exchange會導致全局索引失效,不會導致局部索引失效.如果對exchange分區增加update global indexes,則全局索引不會失效.

重要結論：

所有的全局索引,只要用到update global indexes ,都不會失效,其中add分區甚至不需要增加update global indexes都可以生效.

局部索引的操作都不會失效,除了split分區.切記split分區的時候,要將局部索引進行rebuild.

索引取舍控制

1.避免表交叉重復建立索引

假如t表有nbr、area_code兩列的聯合索引,單列的nbr索引就顯得多余,因為nbr、area_code索引可以用在單列nbr索引上,具體如下：

腳本8：組合索引前綴與單列索引

2.刪除系統從未用到的索引

環境搭建,建表建索引并完成某列索引的監控：

接下來繼續執行一個用索引的查詢,然后再觀察Used字段,發現索引被用過：

停止對索引的監控,觀察v$object_usage狀態變化,發現MONITORING的值為NO,且END_MONITORING記錄了停止監控的時間,如下：

腳本9：刪除系統從未用到的索引

3.組合列過多的索引很可疑

組合索引一般不宜過多,如果組合索引列達到4個以上,那這個索引本身就很大,就不一定高效.另外,索引更新也會出現比較大的性能問題.

文章來自微信公眾號：DBAplus社群

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/3756.html