1 前言
客服業(yè)務(wù)受到SQL語句的影響非常大，在規(guī)模比較大的局點(diǎn)，往往因?yàn)橐粋�(gè)小的SQL語句不夠優(yōu)化，導(dǎo)致數(shù)據(jù)庫性能急劇下降，小型機(jī)idle所剩無幾，應(yīng)用服務(wù)器斷連、超時(shí)，嚴(yán)重影響業(yè)務(wù)的正常運(yùn)行。因此，稱低效的SQL語句為客服業(yè)務(wù)的‘惡龍’并不過分。數(shù)據(jù)庫的優(yōu)化方法有很多種，在應(yīng)用層來說，主要是基于索引的優(yōu)化。本次秘笈根據(jù)實(shí)際的工作經(jīng)驗(yàn)，在研發(fā)原來已有的方法的基礎(chǔ)上，進(jìn)行了一些擴(kuò)充，總結(jié)了基于索引的SQL語句優(yōu)化的降龍十八掌，希望有一天你能用其中一掌來馴服客服業(yè)務(wù)中橫行的‘惡龍’。
2 總綱
建立必要的索引
這次傳授的降龍十八掌，總綱只有一句話：建立必要的索引，這就是后面降龍十八掌的內(nèi)功基礎(chǔ)。這一點(diǎn)看似容易實(shí)際卻很難。難就難在如何判斷哪些索引是必要的，哪些又是不必要的。判斷的最終標(biāo)準(zhǔn)是看這些索引是否對(duì)我們的數(shù)據(jù)庫性能有所幫助。具體到方法上，就必須熟悉數(shù)據(jù)庫應(yīng)用程序中的所有SQL語句，從中統(tǒng)計(jì)出常用的可能對(duì)性能有影響的部分SQL，分析、歸納出作為Where條件子句的字段及其組合方式；在這一基礎(chǔ)上可以初步判斷出哪些表的哪些字段應(yīng)該建立索引。其次，必須熟悉應(yīng)用程序。必須了解哪些表是數(shù)據(jù)操作頻繁的表；哪些表經(jīng)常與其他表進(jìn)行連接；哪些表中的數(shù)據(jù)量可能很大；對(duì)于數(shù)據(jù)量大的表，其中各個(gè)字段的數(shù)據(jù)分布情況如何；等等。對(duì)于滿足以上條件的這些表，必須重點(diǎn)關(guān)注，因?yàn)樵谶@些表上的索引，將對(duì)SQL語句的性能產(chǎn)生舉足輕重的影響。不過下面還是總結(jié)了一下降龍十八掌內(nèi)功的入門基礎(chǔ)，建立索引常用的規(guī)則如下：
1、表的主鍵、外鍵必須有索引；
2、數(shù)據(jù)量超過300的表應(yīng)該有索引；
3、經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；
4、經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；
5、索引應(yīng)該建在選擇性高的字段上；
6、索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長字段，不要建索引；
7、復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：
A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；
B、復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；
C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個(gè)單字段索引；
D、如果復(fù)合索引所包含的字段超過3個(gè)，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；
E、如果既有單字段索引，又有這幾個(gè)字段上的復(fù)合索引，一般可以刪除復(fù)合索引；
8、頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引；
9、刪除無用的索引，避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響；
以上是一些普遍的建立索引時(shí)的判斷依據(jù)。一言以蔽之，索引的建立必須慎重，對(duì)每個(gè)索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。因?yàn)樘嗟乃饕c不充分、不正確的索引對(duì)性能都毫無益處：在表上建立的每個(gè)索引都會(huì)增加存儲(chǔ)開銷，索引對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開銷。 另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價(jià)值的；相反，還會(huì)降低數(shù)據(jù)增加刪除時(shí)的性能，特別是對(duì)頻繁更新的表來說，負(fù)面影響更大。
1 降龍十八掌
第一掌 避免對(duì)列的操作
任何對(duì)列的操作都可能導(dǎo)致全表掃描，這里所謂的操作包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等式的右邊，甚至去掉函數(shù)�！�
例1：下列SQL條件語句中的列都建有恰當(dāng)?shù)乃饕�，�?0萬行數(shù)據(jù)情況下執(zhí)行速度卻非常慢：
select * from record where substrb(CardNo,1,4)='5378'(13秒)
select * from record where amount/30< 1000（11秒）
select * from record where to_char(ActionTime,'yyyymmdd')='19991201'（10秒）
由于where子句中對(duì)列的任何操作結(jié)果都是在SQL運(yùn)行時(shí)逐行計(jì)算得到的，因此它不得不進(jìn)行表掃描，而沒有使用該列上面的索引；如果這些結(jié)果在查詢編譯時(shí)就能得到，那么就可以被SQL優(yōu)化器優(yōu)化，使用索引，避免表掃描，因此將SQL重寫如下：
select * from record where CardNo like '5378%'（< 1秒）
select * from record where amount < 1000*30（< 1秒）
select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')（< 1秒）
差別是很明顯的！
第二掌 避免不必要的類型轉(zhuǎn)換
需要注意的是，盡量避免潛在的數(shù)據(jù)類型轉(zhuǎn)換。如將字符型數(shù)據(jù)與數(shù)值型數(shù)據(jù)比較，ORACLE會(huì)自動(dòng)將字符型用to_number()函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致全表掃描。
例2：表tab1中的列col1是字符型（char)，則以下語句存在類型轉(zhuǎn)換：
select col1,col2 from tab1 where col1>10，
應(yīng)該寫為： select col1,col2 from tab1 where col1>'10'。
第三掌 增加查詢的范圍限制
增加查詢的范圍限制，避免全范圍的搜索。
例3：以下查詢表record 中時(shí)間ActionTime小于2001年3月1日的數(shù)據(jù)：
select * from record where ActionTime < to_date ('20010301' ,'yyyymm')
查詢計(jì)劃表明，上面的查詢對(duì)表進(jìn)行全表掃描，如果我們知道表中的最早的數(shù)據(jù)為2001年1月1日，那么，可以增加一個(gè)最小時(shí)間，使查詢?cè)谝粋�(gè)完整的范圍之內(nèi)。修改如下： select * from record where
ActionTime < to_date ('20010301' ,'yyyymm')
and ActionTime > to_date ('20010101' ,'yyyymm')
后一種SQL語句將利用上ActionTime字段上的索引，從而提高查詢效率。把'20010301'換成一個(gè)變量，根據(jù)取值的機(jī)率，可以有一半以上的機(jī)會(huì)提高效率。同理，對(duì)于大于某個(gè)值的查詢，如果知道當(dāng)前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。
第四掌 盡量去掉"IN"、"OR"
含有"IN"、"OR"的Where子句常會(huì)使用工作表，使索引失效；如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開；拆開的子句中應(yīng)該包含索引�！�
例4： select count(*) from stuff where id_no in('0','1')（23秒）
可以考慮將or子句分開：
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
然后再做一個(gè)簡單的加法，與原來的SQL語句相比，查詢速度更快。
第五掌 盡量去掉 "<>"
盡量去掉 "<>"，避免全表掃描，如果數(shù)據(jù)是枚舉值，且取值范圍固定，則修改為"OR"方式。
例5：
UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;
以上語句由于其中包含了"<>"，執(zhí)行計(jì)劃中用了全表掃描（TABLE ACCESS FULL），沒有用到state字段上的索引。實(shí)際應(yīng)用中，由于業(yè)務(wù)邏輯的限制，字段state為枚舉值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉"<>"，利用索引來提高效率。
修改為：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。進(jìn)一步的修改可以參考第4種方法。
第六掌 去掉Where子句中的IS NULL和IS NOT NULL
Where字句中的IS NULL和IS NOT NULL將不會(huì)使用索引而是進(jìn)行全表搜索，因此需要通過改變查詢方式，分情況討論等方法，去掉Where子句中的IS NULL和IS NOT NULL。
第七掌 索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率
索引的選擇性低，但數(shù)據(jù)的值分布差異很大時(shí)，仍然可以利用索引提高效率。A、數(shù)據(jù)分布不均勻的特殊情況下，選擇性不高的索引也要?jiǎng)?chuàng)建。
表ServiceInfo中數(shù)據(jù)量很大，假設(shè)有一百萬行，其中有一個(gè)字段DisposalCourseFlag，取值范圍為枚舉值：[0，1，2，3，4，5，6，7]。按照前面說的索引建立的規(guī)則，“選擇性不高的字段不應(yīng)該建立索引，該字段只有8種取值，索引值的重復(fù)率很高，索引選擇性明顯很低，因此不建索引。然而，由于該字段上數(shù)據(jù)值的分布情況非常特殊，具體如下表：
取值范圍 1~5 6 7
占總數(shù)據(jù)量的百分比 1% 98% 1%
而且，常用的查詢中，查詢DisposalCourseFlag<6 的情況既多又頻繁，毫無疑問，如果能夠建立索引，并且被應(yīng)用，那么將大大提高這種情況的查詢效率。因此，我們需要在該字段上建立索引。
第八掌 利用HINT強(qiáng)制指定索引
在ORACLE優(yōu)化器無法用上合理索引的情況下，利用HINT強(qiáng)制指定索引。
繼續(xù)上面7的例子，ORACLE缺省認(rèn)定，表中列的值是在所有數(shù)據(jù)行中均勻分布的，也就是說，在一百萬數(shù)據(jù)量下，每種DisposalCourseFlag值各有12.5萬數(shù)據(jù)行與之對(duì)應(yīng)。假設(shè)SQL搜索條件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高，ORACLE因此對(duì)索引“視而不見”，從而在查詢路徑的選擇中，用其他字段上的索引甚至全表掃描。根據(jù)我們上面的分析，數(shù)據(jù)值的分布很特殊，嚴(yán)重的不均勻。為了利用索引提高效率，此時(shí)，一方面可以單獨(dú)對(duì)該字段或該表用analyze語句進(jìn)行分析，對(duì)該列搜集足夠的統(tǒng)計(jì)數(shù)據(jù)，使ORACLE在查詢選擇性較高的值時(shí)能用上索引；另一方面，可以利用HINT提示，在SELECT關(guān)鍵字后面，加上“/*+ INDEX(表名稱，索引名稱）*/”的方式，強(qiáng)制ORACLE優(yōu)化器用上該索引。
比如： select * from serviceinfo where DisposalCourseFlag=1 ;
上面的語句，實(shí)際執(zhí)行中ORACLE用了全表掃描，加上藍(lán)色提示部分后，用到索引查詢。如下：
select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ *
from serviceinfo where DisposalCourseFlag=1;
請(qǐng)注意，這種方法會(huì)加大代碼維護(hù)的難度，而且該字段上索引的名稱被改變之后，必須要同步所有指定索引的HINT代碼，否則HINT提示將被ORACLE忽略掉。
第九掌 屏蔽無用索引
繼續(xù)上面8的例子，由于實(shí)際查詢中，還有涉及到DisposalCourseFlag=6的查詢，而此時(shí)如果用上該字段上的索引，將是非常不明智的，效率也極低。因此這種情況下，我們需要用特殊的方法屏蔽該索引，以便ORACLE選擇其他字段上的索引。比如，如果字段為數(shù)值型的就在表達(dá)式的字段名后，添加“+ 0”，為字符型的就并上空串：“||""”
如： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' 。
不過，不要把該用的索引屏蔽掉了，否則同樣會(huì)產(chǎn)生低效率的全表掃描。
第十掌 分解復(fù)雜查詢，用常量代替變量
對(duì)于復(fù)雜的Where條件組合，Where中含有多個(gè)帶索引的字段，考慮用IF語句分情況進(jìn)行討論；同時(shí)，去掉不必要的外來參數(shù)條件，減低復(fù)雜度，以便在不同情況下用不同字段上的索引。
繼續(xù)上面9的例子，對(duì)于包含
Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and ....的查詢，(這里v_DisPosalCourseFlag為一個(gè)輸入變量，取值范圍可能為[NULL，0，1，2，3，4，5，6，7])，可以考慮分情況用IF語句進(jìn)行討論，類似：
IF v_DisPosalCourseFlag =1 THEN
Where DisposalCourseFlag = 1 and ....
ELSIF v_DisPosalCourseFlag =2 THEN
Where DisposalCourseFlag = 2 and ....
。。。。。。
第十一掌 like子句盡量前端匹配
因?yàn)閘ike參數(shù)使用的非常頻繁，因此如果能夠?qū)ike子句使用索引，將很高的提高查詢的效率。
例6：select * from city where name like ‘%S%’
以上查詢的執(zhí)行計(jì)劃用了全表掃描（TABLE ACCESS FULL），如果能夠修改為：
select * from city where name like ‘S%’
那么查詢的執(zhí)行計(jì)劃將會(huì)變成（INDEX RANGE SCAN），成功的利用了name字段的索引。這意味著Oracle SQL優(yōu)化器會(huì)識(shí)別出用于索引的like子句，只要該查詢的匹配端是具體值。因此我們?cè)谧鰈ike查詢時(shí)，應(yīng)該盡量使查詢的匹配端是具體值，即使用like ‘S%’。
第十二掌 用Case語句合并多重掃描
我們常常必須基于多組數(shù)據(jù)表計(jì)算不同的聚集。例如下例通過三個(gè)獨(dú)立查詢：
例8：1）select count(*) from emp where sal<1000;
2）select count(*) from emp where sal between 1000 and 5000;
3）select count(*) from emp where sal>5000;
這樣我們需要進(jìn)行三次全表查詢，但是如果我們使用case語句：
select
count (sale when sal <1000
then 1 else null end) count_poor,
count (sale when between 1000 and 5000
then 1 else null end) count_blue_collar,
count (sale when sal >5000
then 1 else null end) count_poor
from emp;
這樣查詢的結(jié)果一樣，但是執(zhí)行計(jì)劃只進(jìn)行了一次全表查詢。
第十三掌 使用nls_date_format
例9：
select * from record where to_char(ActionTime,'mm')='12'
這個(gè)查詢的執(zhí)行計(jì)劃將是全表查詢，如果我們改變nls_date_format，
SQL>alert session set nls_date_formate=’MM’;
現(xiàn)在重新修改上面的查詢：
select * from record where ActionTime='12'
這樣就能使用actiontime上的索引了，它的執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）。
第十四掌 使用基于函數(shù)的索引
前面談到任何對(duì)列的操作都可能導(dǎo)致全表掃描，例如：
select * from emp where substr(ename,1,2)=’SM’;
但是這種查詢?cè)诳头到y(tǒng)又經(jīng)常使用，我們可以創(chuàng)建一個(gè)帶有substr函數(shù)的基于函數(shù)的索引，
create index emp_ename_substr on eemp ( substr(ename,1,2) );
這樣在執(zhí)行上面的查詢語句時(shí)，這個(gè)基于函數(shù)的索引將排上用場，執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）。
第十五掌 基于函數(shù)的索引要求等式匹配
上面的例子中，我們創(chuàng)建了基于函數(shù)的索引，但是如果執(zhí)行下面的查詢：
select * from emp where substr(ename,1,1)=’S’
得到的執(zhí)行計(jì)劃將還是（TABLE ACCESS FULL），因?yàn)橹挥挟?dāng)數(shù)據(jù)列能夠等式匹配時(shí)，基于函數(shù)的索引才能生效，這樣對(duì)于這種索引的計(jì)劃和維護(hù)的要求都很高。請(qǐng)注意，向表中添加索引是非常危險(xiǎn)的操作，因?yàn)檫@將導(dǎo)致許多查詢執(zhí)行計(jì)劃的變更。然而，如果我們使用基于函數(shù)的索引就不會(huì)產(chǎn)生這樣的問題，因?yàn)镺racle只有在查詢使用了匹配的內(nèi)置函數(shù)時(shí)才會(huì)使用這種類型的索引。
第十六掌 使用分區(qū)索引
在用分析命令對(duì)分區(qū)索引進(jìn)行分析時(shí)，每一個(gè)分區(qū)的數(shù)據(jù)值的范圍信息會(huì)放入Oracle的數(shù)據(jù)字典中。Oracle可以利用這個(gè)信息來提取出那些只與SQL查詢相關(guān)的數(shù)據(jù)分區(qū)。
例如，假設(shè)你已經(jīng)定義了一個(gè)分區(qū)索引，并且某個(gè)SQL語句需要在一個(gè)索引分區(qū)中進(jìn)行一次索引掃描。Oracle會(huì)僅僅訪問這個(gè)索引分區(qū)，而且會(huì)在這個(gè)分區(qū)上調(diào)用一個(gè)此索引范圍的快速全掃描。因?yàn)椴恍枰L問整個(gè)索引，所以提高了查詢的速度。
第十七掌 使用位圖索引
位圖索引可以從本質(zhì)上提高使用了小于1000個(gè)唯一數(shù)據(jù)值的數(shù)據(jù)列的查詢速度，因?yàn)樵谖粓D索引中進(jìn)行的檢索是在RAM中完成的，而且也總是比傳統(tǒng)的B樹索引的速度要快。對(duì)于那些少于1000個(gè)唯一數(shù)據(jù)值的數(shù)據(jù)列建立位圖索引，可以使執(zhí)行效率更快。
第十八掌 決定使用全表掃描還是使用索引
和所有的秘笈一樣，最后一招都會(huì)又回到起點(diǎn)，最后我們來討論一下是否需要建立索引，也許進(jìn)行全表掃描更快。在大多數(shù)情況下，全表掃描可能會(huì)導(dǎo)致更多的物理磁盤輸入輸出，但是全表掃描有時(shí)又可能會(huì)因?yàn)楦叨炔⑿谢拇嬖诙鴪?zhí)行的更快。如果查詢的表完全沒有順序，那么一個(gè)要返回記錄數(shù)小于10％的查詢可能會(huì)讀取表中大部分的數(shù)據(jù)塊，這樣使用索引會(huì)使查詢效率提高很多。但是如果表非常有順序，那么如果查詢的記錄數(shù)大于40％時(shí)，可能使用全表掃描更快。因此，有一個(gè)索引范圍掃描的總體原則是：
1）對(duì)于原始排序的表 僅讀取少于表記錄數(shù)40％的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的40％的查詢應(yīng)該使用全表掃描。
2）對(duì)于未排序的表 僅讀取少于表記錄數(shù)7％的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的7％的查詢應(yīng)該使用全表掃描。
1 總結(jié)
以上的招式，是完全可以相互結(jié)合同時(shí)運(yùn)用的。而且各種方法之間相互影響，緊密聯(lián)系。這種聯(lián)系既存在一致性，也可能帶來沖突，當(dāng)沖突發(fā)生時(shí)，需要根據(jù)實(shí)際情況進(jìn)行選擇，沒有固定的模式。最后決定SQL優(yōu)化功力的因素就是對(duì)ORACLE內(nèi)功的掌握程度了。
另外，值得注意的是：隨著時(shí)間的推移和數(shù)據(jù)的累計(jì)與變化，ORACLE對(duì)SQL語句的執(zhí)行計(jì)劃也會(huì)改變，比如：基于代價(jià)的優(yōu)化方法，隨著數(shù)據(jù)量的增大，優(yōu)化器可能錯(cuò)誤的不選擇索引而采用全表掃描。這種情況可能是因?yàn)榻y(tǒng)計(jì)信息已經(jīng)過時(shí)，在數(shù)據(jù)量變化很大后沒有及時(shí)分析表；但如果對(duì)表進(jìn)行分析之后，仍然沒有用上合理的索引，那么就有必要對(duì)SQL語句用HINT提示，強(qiáng)制用合理的索引。但這種HINT提示也不能濫用，因?yàn)檫@種方法過于復(fù)雜，缺乏通用性和應(yīng)變能力，同時(shí)也增加了維護(hù)上的代價(jià)；相對(duì)來說，基于函數(shù)右移、去掉“IN ，OR ，<> ，IS NOT NULL ”、分解復(fù)雜的SQL語句等等方法，卻是“放之四海皆準(zhǔn)”的，可以放心大膽的使用。
同時(shí)，優(yōu)化也不是“一勞永逸”的，必須隨著情況的改變進(jìn)行相應(yīng)的調(diào)整。當(dāng)數(shù)據(jù)庫設(shè)計(jì)發(fā)生變化，包括更改表結(jié)構(gòu)：字段和索引的增加、刪除或改名等；業(yè)務(wù)邏輯發(fā)生變化：如查詢方式、取值范圍發(fā)生改變等等。在這種情況下，也必須對(duì)原有的優(yōu)化進(jìn)行調(diào)整，以適應(yīng)效率上的需求。
客服業(yè)務(wù)受到SQL語句的影響非常大，在規(guī)模比較大的局點(diǎn)，往往因?yàn)橐粋�(gè)小的SQL語句不夠優(yōu)化，導(dǎo)致數(shù)據(jù)庫性能急劇下降，小型機(jī)idle所剩無幾，應(yīng)用服務(wù)器斷連、超時(shí)，嚴(yán)重影響業(yè)務(wù)的正常運(yùn)行。因此，稱低效的SQL語句為客服業(yè)務(wù)的‘惡龍’并不過分。數(shù)據(jù)庫的優(yōu)化方法有很多種，在應(yīng)用層來說，主要是基于索引的優(yōu)化。本次秘笈根據(jù)實(shí)際的工作經(jīng)驗(yàn)，在研發(fā)原來已有的方法的基礎(chǔ)上，進(jìn)行了一些擴(kuò)充，總結(jié)了基于索引的SQL語句優(yōu)化的降龍十八掌，希望有一天你能用其中一掌來馴服客服業(yè)務(wù)中橫行的‘惡龍’。
2 總綱
建立必要的索引
這次傳授的降龍十八掌，總綱只有一句話：建立必要的索引，這就是后面降龍十八掌的內(nèi)功基礎(chǔ)。這一點(diǎn)看似容易實(shí)際卻很難。難就難在如何判斷哪些索引是必要的，哪些又是不必要的。判斷的最終標(biāo)準(zhǔn)是看這些索引是否對(duì)我們的數(shù)據(jù)庫性能有所幫助。具體到方法上，就必須熟悉數(shù)據(jù)庫應(yīng)用程序中的所有SQL語句，從中統(tǒng)計(jì)出常用的可能對(duì)性能有影響的部分SQL，分析、歸納出作為Where條件子句的字段及其組合方式；在這一基礎(chǔ)上可以初步判斷出哪些表的哪些字段應(yīng)該建立索引。其次，必須熟悉應(yīng)用程序。必須了解哪些表是數(shù)據(jù)操作頻繁的表；哪些表經(jīng)常與其他表進(jìn)行連接；哪些表中的數(shù)據(jù)量可能很大；對(duì)于數(shù)據(jù)量大的表，其中各個(gè)字段的數(shù)據(jù)分布情況如何；等等。對(duì)于滿足以上條件的這些表，必須重點(diǎn)關(guān)注，因?yàn)樵谶@些表上的索引，將對(duì)SQL語句的性能產(chǎn)生舉足輕重的影響。不過下面還是總結(jié)了一下降龍十八掌內(nèi)功的入門基礎(chǔ)，建立索引常用的規(guī)則如下：
1、表的主鍵、外鍵必須有索引；
2、數(shù)據(jù)量超過300的表應(yīng)該有索引；
3、經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；
4、經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；
5、索引應(yīng)該建在選擇性高的字段上；
6、索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長字段，不要建索引；
7、復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：
A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；
B、復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；
C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個(gè)單字段索引；
D、如果復(fù)合索引所包含的字段超過3個(gè)，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；
E、如果既有單字段索引，又有這幾個(gè)字段上的復(fù)合索引，一般可以刪除復(fù)合索引；
8、頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引；
9、刪除無用的索引，避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響；
以上是一些普遍的建立索引時(shí)的判斷依據(jù)。一言以蔽之，索引的建立必須慎重，對(duì)每個(gè)索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。因?yàn)樘嗟乃饕c不充分、不正確的索引對(duì)性能都毫無益處：在表上建立的每個(gè)索引都會(huì)增加存儲(chǔ)開銷，索引對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開銷。 另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價(jià)值的；相反，還會(huì)降低數(shù)據(jù)增加刪除時(shí)的性能，特別是對(duì)頻繁更新的表來說，負(fù)面影響更大。
1 降龍十八掌
第一掌 避免對(duì)列的操作
任何對(duì)列的操作都可能導(dǎo)致全表掃描，這里所謂的操作包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等式的右邊，甚至去掉函數(shù)�！�
例1：下列SQL條件語句中的列都建有恰當(dāng)?shù)乃饕�，�?0萬行數(shù)據(jù)情況下執(zhí)行速度卻非常慢：
select * from record where substrb(CardNo,1,4)='5378'(13秒)
select * from record where amount/30< 1000（11秒）
select * from record where to_char(ActionTime,'yyyymmdd')='19991201'（10秒）
由于where子句中對(duì)列的任何操作結(jié)果都是在SQL運(yùn)行時(shí)逐行計(jì)算得到的，因此它不得不進(jìn)行表掃描，而沒有使用該列上面的索引；如果這些結(jié)果在查詢編譯時(shí)就能得到，那么就可以被SQL優(yōu)化器優(yōu)化，使用索引，避免表掃描，因此將SQL重寫如下：
select * from record where CardNo like '5378%'（< 1秒）
select * from record where amount < 1000*30（< 1秒）
select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')（< 1秒）
差別是很明顯的！
第二掌 避免不必要的類型轉(zhuǎn)換
需要注意的是，盡量避免潛在的數(shù)據(jù)類型轉(zhuǎn)換。如將字符型數(shù)據(jù)與數(shù)值型數(shù)據(jù)比較，ORACLE會(huì)自動(dòng)將字符型用to_number()函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致全表掃描。
例2：表tab1中的列col1是字符型（char)，則以下語句存在類型轉(zhuǎn)換：
select col1,col2 from tab1 where col1>10，
應(yīng)該寫為： select col1,col2 from tab1 where col1>'10'。
第三掌 增加查詢的范圍限制
增加查詢的范圍限制，避免全范圍的搜索。
例3：以下查詢表record 中時(shí)間ActionTime小于2001年3月1日的數(shù)據(jù)：
select * from record where ActionTime < to_date ('20010301' ,'yyyymm')
查詢計(jì)劃表明，上面的查詢對(duì)表進(jìn)行全表掃描，如果我們知道表中的最早的數(shù)據(jù)為2001年1月1日，那么，可以增加一個(gè)最小時(shí)間，使查詢?cè)谝粋�(gè)完整的范圍之內(nèi)。修改如下： select * from record where
ActionTime < to_date ('20010301' ,'yyyymm')
and ActionTime > to_date ('20010101' ,'yyyymm')
后一種SQL語句將利用上ActionTime字段上的索引，從而提高查詢效率。把'20010301'換成一個(gè)變量，根據(jù)取值的機(jī)率，可以有一半以上的機(jī)會(huì)提高效率。同理，對(duì)于大于某個(gè)值的查詢，如果知道當(dāng)前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。
第四掌 盡量去掉"IN"、"OR"
含有"IN"、"OR"的Where子句常會(huì)使用工作表，使索引失效；如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開；拆開的子句中應(yīng)該包含索引�！�
例4： select count(*) from stuff where id_no in('0','1')（23秒）
可以考慮將or子句分開：
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
然后再做一個(gè)簡單的加法，與原來的SQL語句相比，查詢速度更快。
第五掌 盡量去掉 "<>"
盡量去掉 "<>"，避免全表掃描，如果數(shù)據(jù)是枚舉值，且取值范圍固定，則修改為"OR"方式。
例5：
UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;
以上語句由于其中包含了"<>"，執(zhí)行計(jì)劃中用了全表掃描（TABLE ACCESS FULL），沒有用到state字段上的索引。實(shí)際應(yīng)用中，由于業(yè)務(wù)邏輯的限制，字段state為枚舉值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉"<>"，利用索引來提高效率。
修改為：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。進(jìn)一步的修改可以參考第4種方法。
第六掌 去掉Where子句中的IS NULL和IS NOT NULL
Where字句中的IS NULL和IS NOT NULL將不會(huì)使用索引而是進(jìn)行全表搜索，因此需要通過改變查詢方式，分情況討論等方法，去掉Where子句中的IS NULL和IS NOT NULL。
第七掌 索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率
索引的選擇性低，但數(shù)據(jù)的值分布差異很大時(shí)，仍然可以利用索引提高效率。A、數(shù)據(jù)分布不均勻的特殊情況下，選擇性不高的索引也要?jiǎng)?chuàng)建。
表ServiceInfo中數(shù)據(jù)量很大，假設(shè)有一百萬行，其中有一個(gè)字段DisposalCourseFlag，取值范圍為枚舉值：[0，1，2，3，4，5，6，7]。按照前面說的索引建立的規(guī)則，“選擇性不高的字段不應(yīng)該建立索引，該字段只有8種取值，索引值的重復(fù)率很高，索引選擇性明顯很低，因此不建索引。然而，由于該字段上數(shù)據(jù)值的分布情況非常特殊，具體如下表：
取值范圍 1~5 6 7
占總數(shù)據(jù)量的百分比 1% 98% 1%
而且，常用的查詢中，查詢DisposalCourseFlag<6 的情況既多又頻繁，毫無疑問，如果能夠建立索引，并且被應(yīng)用，那么將大大提高這種情況的查詢效率。因此，我們需要在該字段上建立索引。
第八掌 利用HINT強(qiáng)制指定索引
在ORACLE優(yōu)化器無法用上合理索引的情況下，利用HINT強(qiáng)制指定索引。
繼續(xù)上面7的例子，ORACLE缺省認(rèn)定，表中列的值是在所有數(shù)據(jù)行中均勻分布的，也就是說，在一百萬數(shù)據(jù)量下，每種DisposalCourseFlag值各有12.5萬數(shù)據(jù)行與之對(duì)應(yīng)。假設(shè)SQL搜索條件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高，ORACLE因此對(duì)索引“視而不見”，從而在查詢路徑的選擇中，用其他字段上的索引甚至全表掃描。根據(jù)我們上面的分析，數(shù)據(jù)值的分布很特殊，嚴(yán)重的不均勻。為了利用索引提高效率，此時(shí)，一方面可以單獨(dú)對(duì)該字段或該表用analyze語句進(jìn)行分析，對(duì)該列搜集足夠的統(tǒng)計(jì)數(shù)據(jù)，使ORACLE在查詢選擇性較高的值時(shí)能用上索引；另一方面，可以利用HINT提示，在SELECT關(guān)鍵字后面，加上“/*+ INDEX(表名稱，索引名稱）*/”的方式，強(qiáng)制ORACLE優(yōu)化器用上該索引。
比如： select * from serviceinfo where DisposalCourseFlag=1 ;
上面的語句，實(shí)際執(zhí)行中ORACLE用了全表掃描，加上藍(lán)色提示部分后，用到索引查詢。如下：
select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ *
from serviceinfo where DisposalCourseFlag=1;
請(qǐng)注意，這種方法會(huì)加大代碼維護(hù)的難度，而且該字段上索引的名稱被改變之后，必須要同步所有指定索引的HINT代碼，否則HINT提示將被ORACLE忽略掉。
第九掌 屏蔽無用索引
繼續(xù)上面8的例子，由于實(shí)際查詢中，還有涉及到DisposalCourseFlag=6的查詢，而此時(shí)如果用上該字段上的索引，將是非常不明智的，效率也極低。因此這種情況下，我們需要用特殊的方法屏蔽該索引，以便ORACLE選擇其他字段上的索引。比如，如果字段為數(shù)值型的就在表達(dá)式的字段名后，添加“+ 0”，為字符型的就并上空串：“||""”
如： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' 。
不過，不要把該用的索引屏蔽掉了，否則同樣會(huì)產(chǎn)生低效率的全表掃描。
第十掌 分解復(fù)雜查詢，用常量代替變量
對(duì)于復(fù)雜的Where條件組合，Where中含有多個(gè)帶索引的字段，考慮用IF語句分情況進(jìn)行討論；同時(shí)，去掉不必要的外來參數(shù)條件，減低復(fù)雜度，以便在不同情況下用不同字段上的索引。
繼續(xù)上面9的例子，對(duì)于包含
Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and ....的查詢，(這里v_DisPosalCourseFlag為一個(gè)輸入變量，取值范圍可能為[NULL，0，1，2，3，4，5，6，7])，可以考慮分情況用IF語句進(jìn)行討論，類似：
IF v_DisPosalCourseFlag =1 THEN
Where DisposalCourseFlag = 1 and ....
ELSIF v_DisPosalCourseFlag =2 THEN
Where DisposalCourseFlag = 2 and ....
。。。。。。
第十一掌 like子句盡量前端匹配
因?yàn)閘ike參數(shù)使用的非常頻繁，因此如果能夠?qū)ike子句使用索引，將很高的提高查詢的效率。
例6：select * from city where name like ‘%S%’
以上查詢的執(zhí)行計(jì)劃用了全表掃描（TABLE ACCESS FULL），如果能夠修改為：
select * from city where name like ‘S%’
那么查詢的執(zhí)行計(jì)劃將會(huì)變成（INDEX RANGE SCAN），成功的利用了name字段的索引。這意味著Oracle SQL優(yōu)化器會(huì)識(shí)別出用于索引的like子句，只要該查詢的匹配端是具體值。因此我們?cè)谧鰈ike查詢時(shí)，應(yīng)該盡量使查詢的匹配端是具體值，即使用like ‘S%’。
第十二掌 用Case語句合并多重掃描
我們常常必須基于多組數(shù)據(jù)表計(jì)算不同的聚集。例如下例通過三個(gè)獨(dú)立查詢：
例8：1）select count(*) from emp where sal<1000;
2）select count(*) from emp where sal between 1000 and 5000;
3）select count(*) from emp where sal>5000;
這樣我們需要進(jìn)行三次全表查詢，但是如果我們使用case語句：
select
count (sale when sal <1000
then 1 else null end) count_poor,
count (sale when between 1000 and 5000
then 1 else null end) count_blue_collar,
count (sale when sal >5000
then 1 else null end) count_poor
from emp;
這樣查詢的結(jié)果一樣，但是執(zhí)行計(jì)劃只進(jìn)行了一次全表查詢。
第十三掌 使用nls_date_format
例9：
select * from record where to_char(ActionTime,'mm')='12'
這個(gè)查詢的執(zhí)行計(jì)劃將是全表查詢，如果我們改變nls_date_format，
SQL>alert session set nls_date_formate=’MM’;
現(xiàn)在重新修改上面的查詢：
select * from record where ActionTime='12'
這樣就能使用actiontime上的索引了，它的執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）。
第十四掌 使用基于函數(shù)的索引
前面談到任何對(duì)列的操作都可能導(dǎo)致全表掃描，例如：
select * from emp where substr(ename,1,2)=’SM’;
但是這種查詢?cè)诳头到y(tǒng)又經(jīng)常使用，我們可以創(chuàng)建一個(gè)帶有substr函數(shù)的基于函數(shù)的索引，
create index emp_ename_substr on eemp ( substr(ename,1,2) );
這樣在執(zhí)行上面的查詢語句時(shí)，這個(gè)基于函數(shù)的索引將排上用場，執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）。
第十五掌 基于函數(shù)的索引要求等式匹配
上面的例子中，我們創(chuàng)建了基于函數(shù)的索引，但是如果執(zhí)行下面的查詢：
select * from emp where substr(ename,1,1)=’S’
得到的執(zhí)行計(jì)劃將還是（TABLE ACCESS FULL），因?yàn)橹挥挟?dāng)數(shù)據(jù)列能夠等式匹配時(shí)，基于函數(shù)的索引才能生效，這樣對(duì)于這種索引的計(jì)劃和維護(hù)的要求都很高。請(qǐng)注意，向表中添加索引是非常危險(xiǎn)的操作，因?yàn)檫@將導(dǎo)致許多查詢執(zhí)行計(jì)劃的變更。然而，如果我們使用基于函數(shù)的索引就不會(huì)產(chǎn)生這樣的問題，因?yàn)镺racle只有在查詢使用了匹配的內(nèi)置函數(shù)時(shí)才會(huì)使用這種類型的索引。
第十六掌 使用分區(qū)索引
在用分析命令對(duì)分區(qū)索引進(jìn)行分析時(shí)，每一個(gè)分區(qū)的數(shù)據(jù)值的范圍信息會(huì)放入Oracle的數(shù)據(jù)字典中。Oracle可以利用這個(gè)信息來提取出那些只與SQL查詢相關(guān)的數(shù)據(jù)分區(qū)。
例如，假設(shè)你已經(jīng)定義了一個(gè)分區(qū)索引，并且某個(gè)SQL語句需要在一個(gè)索引分區(qū)中進(jìn)行一次索引掃描。Oracle會(huì)僅僅訪問這個(gè)索引分區(qū)，而且會(huì)在這個(gè)分區(qū)上調(diào)用一個(gè)此索引范圍的快速全掃描。因?yàn)椴恍枰L問整個(gè)索引，所以提高了查詢的速度。
第十七掌 使用位圖索引
位圖索引可以從本質(zhì)上提高使用了小于1000個(gè)唯一數(shù)據(jù)值的數(shù)據(jù)列的查詢速度，因?yàn)樵谖粓D索引中進(jìn)行的檢索是在RAM中完成的，而且也總是比傳統(tǒng)的B樹索引的速度要快。對(duì)于那些少于1000個(gè)唯一數(shù)據(jù)值的數(shù)據(jù)列建立位圖索引，可以使執(zhí)行效率更快。
第十八掌 決定使用全表掃描還是使用索引
和所有的秘笈一樣，最后一招都會(huì)又回到起點(diǎn)，最后我們來討論一下是否需要建立索引，也許進(jìn)行全表掃描更快。在大多數(shù)情況下，全表掃描可能會(huì)導(dǎo)致更多的物理磁盤輸入輸出，但是全表掃描有時(shí)又可能會(huì)因?yàn)楦叨炔⑿谢拇嬖诙鴪?zhí)行的更快。如果查詢的表完全沒有順序，那么一個(gè)要返回記錄數(shù)小于10％的查詢可能會(huì)讀取表中大部分的數(shù)據(jù)塊，這樣使用索引會(huì)使查詢效率提高很多。但是如果表非常有順序，那么如果查詢的記錄數(shù)大于40％時(shí)，可能使用全表掃描更快。因此，有一個(gè)索引范圍掃描的總體原則是：
1）對(duì)于原始排序的表 僅讀取少于表記錄數(shù)40％的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的40％的查詢應(yīng)該使用全表掃描。
2）對(duì)于未排序的表 僅讀取少于表記錄數(shù)7％的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的7％的查詢應(yīng)該使用全表掃描。
1 總結(jié)
以上的招式，是完全可以相互結(jié)合同時(shí)運(yùn)用的。而且各種方法之間相互影響，緊密聯(lián)系。這種聯(lián)系既存在一致性，也可能帶來沖突，當(dāng)沖突發(fā)生時(shí)，需要根據(jù)實(shí)際情況進(jìn)行選擇，沒有固定的模式。最后決定SQL優(yōu)化功力的因素就是對(duì)ORACLE內(nèi)功的掌握程度了。
另外，值得注意的是：隨著時(shí)間的推移和數(shù)據(jù)的累計(jì)與變化，ORACLE對(duì)SQL語句的執(zhí)行計(jì)劃也會(huì)改變，比如：基于代價(jià)的優(yōu)化方法，隨著數(shù)據(jù)量的增大，優(yōu)化器可能錯(cuò)誤的不選擇索引而采用全表掃描。這種情況可能是因?yàn)榻y(tǒng)計(jì)信息已經(jīng)過時(shí)，在數(shù)據(jù)量變化很大后沒有及時(shí)分析表；但如果對(duì)表進(jìn)行分析之后，仍然沒有用上合理的索引，那么就有必要對(duì)SQL語句用HINT提示，強(qiáng)制用合理的索引。但這種HINT提示也不能濫用，因?yàn)檫@種方法過于復(fù)雜，缺乏通用性和應(yīng)變能力，同時(shí)也增加了維護(hù)上的代價(jià)；相對(duì)來說，基于函數(shù)右移、去掉“IN ，OR ，<> ，IS NOT NULL ”、分解復(fù)雜的SQL語句等等方法，卻是“放之四海皆準(zhǔn)”的，可以放心大膽的使用。
同時(shí)，優(yōu)化也不是“一勞永逸”的，必須隨著情況的改變進(jìn)行相應(yīng)的調(diào)整。當(dāng)數(shù)據(jù)庫設(shè)計(jì)發(fā)生變化，包括更改表結(jié)構(gòu)：字段和索引的增加、刪除或改名等；業(yè)務(wù)邏輯發(fā)生變化：如查詢方式、取值范圍發(fā)生改變等等。在這種情況下，也必須對(duì)原有的優(yōu)化進(jìn)行調(diào)整，以適應(yīng)效率上的需求。