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

作者介紹

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

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

大家可能沒注意到,在查詢語句中,如果更新語句出現(xiàn),索引的缺點(diǎn)將很明顯.因?yàn)樗饕旧硎怯行虻?而更新數(shù)據(jù)的過程中也要更新索引,更新完后還要保持索引的有序性,這就需要付出很大的開銷了,索引不好的一面就從這里開始顯示出來了.

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

總體學(xué)習(xí)思路如下圖所示：

 

一、索引的不足之處

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

索引的各種開銷

還記得前面關(guān)于索引結(jié)構(gòu)的分析嗎?通過系列步驟,我們明白了索引的結(jié)構(gòu),推導(dǎo)出索引的三大特性,并應(yīng)用這些特性讓SQL跑得更快.

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

1.熱塊競爭

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

2.回表開銷

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

3.更新開銷

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

4.建立開銷

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

索引使用失效

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

1.邏輯失效

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

2.物理失效

物理失效就是索引真的失效了,比如被人誤設(shè)了unusable動作,或者是一些類似分區(qū)表的不規(guī)范操作導(dǎo)致的索引失效.對此后續(xù)有詳細(xì)的例子說明.

二、感受美好索引另一面

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

索引各種開銷

1.訪問集中導(dǎo)致熱塊的競爭

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

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

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

而disorganized表的聚合因子比較大,回表的代價(jià)很高,如下,產(chǎn)生21360個(gè)buffer：

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

環(huán)境搭建：

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

由上面代碼可以看出,雖然t_small是小表,t_big是大表.但是插入一般不會隨著記錄的增加越插越慢.什么時(shí)候會越插越慢,就是當(dāng)表有索引的時(shí)候.因?yàn)樗饕枰S護(hù),越大維護(hù)越困難.我們繼續(xù)做一組試驗(yàn).

環(huán)境準(zhǔn)備(建3張結(jié)構(gòu)和記錄都一樣的表,只是索引分別是6個(gè)、2個(gè)及無索引)：

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

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

表記錄越大,索引越多,插入越慢,從試驗(yàn)結(jié)果來看,這一點(diǎn)還是非常明顯的.

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

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

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

腳本3：建索引產(chǎn)生鎖

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

未建索引前,觀察一下數(shù)字字典中記錄的系統(tǒng)排序情況,如下：

建索引后,繼續(xù)觀察,發(fā)現(xiàn)排序次數(shù)sorts (memory)增加了,如下：

腳本4：建索引產(chǎn)生排序

索引使用失效

索引邏輯失效

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

這個(gè)道理比較簡單,如果應(yīng)用索引范圍檢索數(shù)據(jù),返回大量記錄且?guī)缀跏撬械挠涗?這時(shí)候用索引肯定有錯(cuò),索引范圍查詢訪問一般適合返回少量記錄的情況,否則用全表掃描或者全索引掃描就可以.

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

在表字段設(shè)計(jì)的時(shí)候有一個(gè)非常重要的原則,什么類型的字段存什么類型的值,否則就會發(fā)生類型轉(zhuǎn)化,具體請看如下例子：

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

腳本5：索引列的類型轉(zhuǎn)換

(3)對索引進(jìn)行了各種運(yùn)算

對索引列進(jìn)行了各種運(yùn)算,詳見后面的案例部分.

索引物理失效

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

環(huán)境準(zhǔn)備(建表,建long字段)：

接下來將long修改為clob,發(fā)現(xiàn)索引失效了,必須重建索引,如下：

腳本6：long列調(diào)整導(dǎo)致索引失效

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

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

腳本7：move操作導(dǎo)致索引失效

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

這在前面已經(jīng)描述過了,這里就不再重復(fù)了,請讀者自行回到前面的章節(jié)進(jìn)行復(fù)習(xí)總結(jié).歸納如下：

truncate分區(qū)會導(dǎo)致全局索引失效,不會導(dǎo)致局部索引失效.如果對truncate 增加update global indexes,則全局索引不會失效.

drop分區(qū)會導(dǎo)致全局索引失效,局部索引因?yàn)閐rop分區(qū),所以也不存在該分區(qū)的局部索引了.如果對drop分區(qū)增加update global indexes,全局索引不會失效.

split分區(qū)會導(dǎo)致全局索引失效,也會導(dǎo)致局部索引失效.如果對split分區(qū)增加update global indexes,則全局索引不會失效.

add 分區(qū)不會導(dǎo)致全局索引失效,也不會導(dǎo)致局部索引失效.

exchange會導(dǎo)致全局索引失效,不會導(dǎo)致局部索引失效.如果對exchange分區(qū)增加update global indexes,則全局索引不會失效.

重要結(jié)論：

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

局部索引的操作都不會失效,除了split分區(qū).切記split分區(qū)的時(shí)候,要將局部索引進(jìn)行rebuild.

索引取舍控制

1.避免表交叉重復(fù)建立索引

假如t表有nbr、area_code兩列的聯(lián)合索引,單列的nbr索引就顯得多余,因?yàn)閚br、area_code索引可以用在單列nbr索引上,具體如下：

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

2.刪除系統(tǒng)從未用到的索引

環(huán)境搭建,建表建索引并完成某列索引的監(jiān)控：

接下來繼續(xù)執(zhí)行一個(gè)用索引的查詢,然后再觀察Used字段,發(fā)現(xiàn)索引被用過：

停止對索引的監(jiān)控,觀察v$object_usage狀態(tài)變化,發(fā)現(xiàn)MONITORING的值為NO,且END_MONITORING記錄了停止監(jiān)控的時(shí)間,如下：

腳本9：刪除系統(tǒng)從未用到的索引

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

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

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

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/3756.html