1     前言... 2

2     總綱... 2

3     降龍十八掌... 3

第一掌 避免對列的操作... 3

第二掌 避免不必要的類型轉(zhuǎn)換... 4

第三掌 增加查詢的范圍限制... 4

第四掌 盡量去掉"IN"、"OR" 4

第五掌 盡量去掉 "<>" 5

第六掌 去掉Where子句中的IS NULL和IS NOT NULL. 5

第七掌 索引提高數(shù)據(jù)分布不均勻時查詢效率... 5

第八掌 利用HINT強(qiáng)制指定索引... 6

第九掌 屏蔽無用索引... 6

第十掌 分解復(fù)雜查詢，用常量代替變量... 7

第十一掌 like子句盡量前端匹配... 7

第十二掌 用Case語句合并多重掃描... 7

第十三掌 使用nls_date_format 8

第十四掌 使用基于函數(shù)的索引... 8

第十五掌 基于函數(shù)的索引要求等式匹配... 9

第十六掌 使用分區(qū)索引... 9

第十七掌 使用位圖索引... 9

第十八掌 決定使用全表掃描還是使用索引... 9

4     總結(jié)... 10

1         前言

客服業(yè)務(wù)受到SQL語句的影響非常大，在規(guī)模比較大的局點，往往因為一個小的SQL語句不夠優(yōu)化，導(dǎo)致數(shù)據(jù)庫性能急劇下降，小型機(jī)idle所剩無幾，應(yīng)用服務(wù)器斷連、超時，嚴(yán)重影響業(yè)務(wù)的正常運行。因此，稱低效的SQL語句為客服業(yè)務(wù)的‘惡龍’并不過分。數(shù)據(jù)庫的優(yōu)化方法有很多種，在應(yīng)用層來說，主要是基于索引的優(yōu)化。本次秘笈根據(jù)實際的工作經(jīng)驗，在研發(fā)原來已有的方法的基礎(chǔ)上，進(jìn)行了一些擴(kuò)充，總結(jié)了基于索引的SQL語句優(yōu)化的降龍十八掌，希望有一天你能用其中一掌來馴服客服業(yè)務(wù)中橫行的‘惡龍’。

2         總綱

l       建立必要的索引

這次傳授的降龍十八掌，總綱只有一句話：建立必要的索引，這就是后面降龍十八掌的內(nèi)功基礎(chǔ)。這一點看似容易實際卻很難。難就難在如何判斷哪些索引是必要的，哪些又是不必要的。判斷的最終標(biāo)準(zhǔn)是看這些索引是否對我們的數(shù)據(jù)庫性能有所幫助。具體到方法上，就必須熟悉數(shù)據(jù)庫應(yīng)用程序中的所有SQL語句，從中統(tǒng)計出常用的可能對性能有影響的部分SQL，分析、歸納出作為Where條件子句的字段及其組合方式；在這一基礎(chǔ)上可以初步判斷出哪些表的哪些字段應(yīng)該建立索引。其次，必須熟悉應(yīng)用程序。必須了解哪些表是數(shù)據(jù)操作頻繁的表；哪些表經(jīng)常與其他表進(jìn)行連接；哪些表中的數(shù)據(jù)量可能很大；對于數(shù)據(jù)量大的表，其中各個字段的數(shù)據(jù)分布情況如何；等等。對于滿足以上條件的這些表，必須重點關(guān)注，因為在這些表上的索引，將對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)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；

7、復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：

      A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；

      B、復(fù)合索引的幾個字段是否經(jīng)常同時以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；

      C、如果復(fù)合索引中包含的字段經(jīng)常單獨出現(xiàn)在Where子句中，則分解為多個單字段索引；

      D、如果復(fù)合索引所包含的字段超過3個，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；

      E、如果既有單字段索引，又有這幾個字段上的復(fù)合索引，一般可以刪除復(fù)合索引；

8、頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引；

9、刪除無用的索引，避免對執(zhí)行計劃造成負(fù)面影響；

       以上是一些普遍的建立索引時的判斷依據(jù)。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。因為太多的索引與不充分、不正確的索引對性能都毫無益處：在表上建立的每個索引都會增加存儲開銷，索引對于插入、刪除、更新操作也會增加處理上的開銷。 另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價值的；相反，還會降低數(shù)據(jù)增加刪除時的性能，特別是對頻繁更新的表來說，負(fù)面影響更大。

3         降龍十八掌

第一掌 避免對列的操作

任何對列的操作都可能導(dǎo)致全表掃描，這里所謂的操作包括數(shù)據(jù)庫函數(shù)、計算表達(dá)式等等，查詢時要盡可能將操作移至等式的右邊，甚至去掉函數(shù)。  

例1：下列SQL條件語句中的列都建有恰當(dāng)?shù)乃饕?/SPAN>30萬行數(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子句中對列的任何操作結(jié)果都是在SQL運行時逐行計算得到的，因此它不得不進(jìn)行表掃描，而沒有使用該列上面的索引；如果這些結(jié)果在查詢編譯時就能得到，那么就可以被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會自動將字符型用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 中時間ActionTime小于2001年3月1日的數(shù)據(jù)：

       select * from record where ActionTime < to_date ('20010301' ,'yyyymm')

查詢計劃表明，上面的查詢對表進(jìn)行全表掃描，如果我們知道表中的最早的數(shù)據(jù)為2001年1月1日，那么，可以增加一個最小時間，使查詢在一個完整的范圍之內(nèi)。修改如下：        select * from record where

            ActionTime < to_date ('20010301' ,'yyyymm')

         and   ActionTime > to_date ('20010101' ,'yyyymm')

后一種SQL語句將利用上ActionTime字段上的索引，從而提高查詢效率。把'20010301'換成一個變量，根據(jù)取值的機(jī)率，可以有一半以上的機(jī)會提高效率。同理，對于大于某個值的查詢，如果知道當(dāng)前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

第四掌 盡量去掉"IN"、"OR"

含有"IN"、"OR"的Where子句常會使用工作表，使索引失效；如果不產(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'

然后再做一個簡單的加法，與原來的SQL語句相比，查詢速度更快。

第五掌 盡量去掉 "<>"

盡量去掉 "<>"，避免全表掃描，如果數(shù)據(jù)是枚舉值，且取值范圍固定，則修改為"OR"方式。

例5：

UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;

以上語句由于其中包含了"<>"，執(zhí)行計劃中用了全表掃描（TABLE ACCESS FULL），沒有用到state字段上的索引。實際應(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將不會使用索引而是進(jìn)行全表搜索，因此需要通過改變查詢方式，分情況討論等方法，去掉Where子句中的IS NULL和IS NOT NULL。

第七掌 索引提高數(shù)據(jù)分布不均勻時查詢效率

索引的選擇性低，但數(shù)據(jù)的值分布差異很大時，仍然可以利用索引提高效率。A、數(shù)據(jù)分布不均勻的特殊情況下，選擇性不高的索引也要創(chuàng)建。

表ServiceInfo中數(shù)據(jù)量很大，假設(shè)有一百萬行，其中有一個字段DisposalCourseFlag，取值范圍為枚舉值：[0，1，2，3，4，5，6，7]。按照前面說的索引建立的規(guī)則，“選擇性不高的字段不應(yīng)該建立索引，該字段只有8種取值，索引值的重復(fù)率很高，索引選擇性明顯很低，因此不建索引。然而，由于該字段上數(shù)據(jù)值的分布情況非常特殊，具體如下表：

而且，常用的查詢中，查詢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ù)行與之對應(yīng)。假設(shè)SQL搜索條件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高，ORACLE因此對索引“視而不見”，從而在查詢路徑的選擇中，用其他字段上的索引甚至全表掃描。根據(jù)我們上面的分析，數(shù)據(jù)值的分布很特殊，嚴(yán)重的不均勻。為了利用索引提高效率，此時，一方面可以單獨對該字段或該表用analyze語句進(jìn)行分析，對該列搜集足夠的統(tǒng)計數(shù)據(jù)，使ORACLE在查詢選擇性較高的值時能用上索引；另一方面，可以利用HINT提示，在SELECT關(guān)鍵字后面，加上“/*+ INDEX(表名稱，索引名稱）*/”的方式，強(qiáng)制ORACLE優(yōu)化器用上該索引。

比如： select * from  serviceinfo where DisposalCourseFlag=1 ;

上面的語句，實際執(zhí)行中ORACLE用了全表掃描，加上藍(lán)色提示部分后，用到索引查詢。如下：

       select /*+  INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG)  */  *

         from  serviceinfo where DisposalCourseFlag=1;

請注意，這種方法會加大代碼維護(hù)的難度，而且該字段上索引的名稱被改變之后，必須要同步所有指定索引的HINT代碼，否則HINT提示將被ORACLE忽略掉。

第九掌 屏蔽無用索引

繼續(xù)上面8的例子，由于實際查詢中，還有涉及到DisposalCourseFlag=6的查詢，而此時如果用上該字段上的索引，將是非常不明智的，效率也極低。因此這種情況下，我們需要用特殊的方法屏蔽該索引，以便ORACLE選擇其他字段上的索引。比如，如果字段為數(shù)值型的就在表達(dá)式的字段名后，添加“+ 0”，為字符型的就并上空串：“||""”

如： select * from  serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo =  '36' 。

不過，不要把該用的索引屏蔽掉了，否則同樣會產(chǎn)生低效率的全表掃描。

第十掌 分解復(fù)雜查詢，用常量代替變量

對于復(fù)雜的Where條件組合，Where中含有多個帶索引的字段，考慮用IF語句分情況進(jìn)行討論；同時，去掉不必要的外來參數(shù)條件，減低復(fù)雜度，以便在不同情況下用不同字段上的索引。

繼續(xù)上面9的例子，對于包含

Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and ....的查詢，(這里v_DisPosalCourseFlag為一個輸入變量，取值范圍可能為[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子句盡量前端匹配

因為like參數(shù)使用的非常頻繁，因此如果能夠?qū)?/SPAN>like子句使用索引，將很高的提高查詢的效率。

例6：select * from city where name like ‘%S%’

以上查詢的執(zhí)行計劃用了全表掃描（TABLE ACCESS FULL），如果能夠修改為：

select * from city where name like ‘S%’

那么查詢的執(zhí)行計劃將會變成（INDEX RANGE SCAN），成功的利用了name字段的索引。這意味著Oracle SQL優(yōu)化器會識別出用于索引的like子句，只要該查詢的匹配端是具體值。因此我們在做like查詢時，應(yīng)該盡量使查詢的匹配端是具體值，即使用like ‘S%’。

第十二掌 用Case語句合并多重掃描

我們常常必須基于多組數(shù)據(jù)表計算不同的聚集。例如下例通過三個獨立查詢：

例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ìn)行了一次全表查詢。

第十三掌 使用nls_date_format

例9：

select * from record where  to_char(ActionTime,'mm')='12'

這個查詢的執(zhí)行計劃將是全表查詢，如果我們改變nls_date_format，

SQL>alert session set nls_date_formate=’MM’;

現(xiàn)在重新修改上面的查詢：

select * from record where  ActionTime='12'

這樣就能使用actiontime上的索引了，它的執(zhí)行計劃將是（INDEX RANGE SCAN）。

第十四掌 使用基于函數(shù)的索引

前面談到任何對列的操作都可能導(dǎo)致全表掃描，例如：

       select * from emp where substr(ename,1,2)=’SM’;

但是這種查詢在客服系統(tǒng)又經(jīng)常使用，我們可以創(chuàng)建一個帶有substr函數(shù)的基于函數(shù)的索引，

       create index emp_ename_substr on eemp ( substr(ename,1,2) );

這樣在執(zhí)行上面的查詢語句時，這個基于函數(shù)的索引將排上用場，執(zhí)行計劃將是（INDEX RANGE SCAN）。

第十五掌 基于函數(shù)的索引要求等式匹配

上面的例子中，我們創(chuàng)建了基于函數(shù)的索引，但是如果執(zhí)行下面的查詢：

       select * from emp where substr(ename,1,1)=’S’

得到的執(zhí)行計劃將還是（TABLE ACCESS FULL），因為只有當(dāng)數(shù)據(jù)列能夠等式匹配時，基于函數(shù)的索引才能生效，這樣對于這種索引的計劃和維護(hù)的要求都很高。請注意，向表中添加索引是非常危險的操作，因為這將導(dǎo)致許多查詢執(zhí)行計劃的變更。然而，如果我們使用基于函數(shù)的索引就不會產(chǎn)生這樣的問題，因為Oracle只有在查詢使用了匹配的內(nèi)置函數(shù)時才會使用這種類型的索引。

第十六掌 使用分區(qū)索引

在用分析命令對分區(qū)索引進(jìn)行分析時，每一個分區(qū)的數(shù)據(jù)值的范圍信息會放入Oracle的數(shù)據(jù)字典中。Oracle可以利用這個信息來提取出那些只與SQL查詢相關(guān)的數(shù)據(jù)分區(qū)。

例如，假設(shè)你已經(jīng)定義了一個分區(qū)索引，并且某個SQL語句需要在一個索引分區(qū)中進(jìn)行一次索引掃描。Oracle會僅僅訪問這個索引分區(qū)，而且會在這個分區(qū)上調(diào)用一個此索引范圍的快速全掃描。因為不需要訪問整個索引，所以提高了查詢的速度。

第十七掌 使用位圖索引

位圖索引可以從本質(zhì)上提高使用了小于1000個唯一數(shù)據(jù)值的數(shù)據(jù)列的查詢速度，因為在位圖索引中進(jìn)行的檢索是在RAM中完成的，而且也總是比傳統(tǒng)的B樹索引的速度要快。對于那些少于1000個唯一數(shù)據(jù)值的數(shù)據(jù)列建立位圖索引，可以使執(zhí)行效率更快。

第十八掌 決定使用全表掃描還是使用索引

和所有的秘笈一樣，最后一招都會又回到起點，最后我們來討論一下是否需要建立索引，也許進(jìn)行全表掃描更快。在大多數(shù)情況下，全表掃描可能會導(dǎo)致更多的物理磁盤輸入輸出，但是全表掃描有時又可能會因為高度并行化的存在而執(zhí)行的更快。如果查詢的表完全沒有順序，那么一個要返回記錄數(shù)小于10％的查詢可能會讀取表中大部分的數(shù)據(jù)塊，這樣使用索引會使查詢效率提高很多。但是如果表非常有順序，那么如果查詢的記錄數(shù)大于40％時，可能使用全表掃描更快。因此，有一個索引范圍掃描的總體原則是：

1）對于原始排序的表  僅讀取少于表記錄數(shù)40％的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的40％的查詢應(yīng)該使用全表掃描。

2）對于未排序的表    僅讀取少于表記錄數(shù)7％的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的7％的查詢應(yīng)該使用全表掃描。

4         總結(jié)

以上的招式，是完全可以相互結(jié)合同時運用的。而且各種方法之間相互影響，緊密聯(lián)系。這種聯(lián)系既存在一致性，也可能帶來沖突，當(dāng)沖突發(fā)生時，需要根據(jù)實際情況進(jìn)行選擇，沒有固定的模式。最后決定SQL優(yōu)化功力的因素就是對ORACLE內(nèi)功的掌握程度了。

另外，值得注意的是：隨著時間的推移和數(shù)據(jù)的累計與變化，ORACLE對SQL語句的執(zhí)行計劃也會改變，比如：基于代價的優(yōu)化方法，隨著數(shù)據(jù)量的增大，優(yōu)化器可能錯誤的不選擇索引而采用全表掃描。這種情況可能是因為統(tǒng)計信息已經(jīng)過時，在數(shù)據(jù)量變化很大后沒有及時分析表；但如果對表進(jìn)行分析之后，仍然沒有用上合理的索引，那么就有必要對SQL語句用HINT提示，強(qiáng)制用合理的索引。但這種HINT提示也不能濫用，因為這種方法過于復(fù)雜，缺乏通用性和應(yīng)變能力，同時也增加了維護(hù)上的代價；相對來說，基于函數(shù)右移、去掉“IN ，OR ，<> ，IS NOT NULL ”、分解復(fù)雜的SQL語句等等方法，卻是“放之四海皆準(zhǔn)”的，可以放心大膽的使用。

同時，優(yōu)化也不是“一勞永逸”的，必須隨著情況的改變進(jìn)行相應(yīng)的調(diào)整。當(dāng)數(shù)據(jù)庫設(shè)計發(fā)生變化，包括更改表結(jié)構(gòu)：字段和索引的增加、刪除或改名等；業(yè)務(wù)邏輯發(fā)生變化：如查詢方式、取值范圍發(fā)生改變等等。在這種情況下，也必須對原有的優(yōu)化進(jìn)行調(diào)整，以適應(yīng)效率上的需求。