[摘錄] 基於索引的SQL優化之降龍十八掌

1 前言 .................................................................................................................................. 2
2 總綱 .................................................................................................................................. 2
3 降龍十八掌 ....................................................................................................................... 3
第一掌 避免對列的操作 ................................................................................................. 3
第二掌 避免不必要的型別轉換 ..................................................................................... 4
第三掌 增加查詢的範圍限制 ......................................................................................... 4
第四掌 儘量去掉"IN"、"OR" ........................................................................................ 4
第五掌 儘量去掉 "<>" ................................................................................................... 5
第六掌 去掉Where子句中的IS NULL和IS NOT NULL ............................................... 5
第七掌 索引提高資料分佈不均勻時查詢效率 ............................................................. 5
第八掌 利用HINT強制指定索引 ................................................................................... 6
第九掌 遮蔽無用索引 ..................................................................................................... 6
第十掌 分解複雜查詢，用常量代替變數 ..................................................................... 7
第十一掌 like子句儘量前端匹配 ................................................................................... 7
第十二掌 用Case語句合併多重掃描 ............................................................................. 7
第十三掌 使用nls_date_format ....................................................................................... 8
第十四掌 使用基於函式的索引 ..................................................................................... 8
第十五掌 基於函式的索引要求等式匹配 ..................................................................... 9
第十六掌 使用分割槽索引 ................................................................................................. 9
第十七掌 使用點陣圖索引 ................................................................................................. 9
第十八掌 決定使用全表掃描還是使用索引 ................................................................. 9
4 總結 ................................................................................................................................ 10

1 前言
客服業務受到SQL語句的影響非常大，在規模比較大的局點，往往因為一個小的SQL語
句不夠優化，導致資料庫效能急劇下降，小型機idle所剩無幾，應用伺服器斷連、超時，嚴
重影響業務的正常執行。因此，稱低效的SQL語句為客服業務的‘惡龍’並不過分。資料庫
的優化方法有很多種，在應用層來說，主要是基於索引的優化。本次祕笈根據實際的工作經
驗，在研發原來已有的方法的基礎上，進行了一些擴充，總結了基於索引的SQL語句優化的
降龍十八掌，希望有一天你能用其中一掌來馴服客服業務中橫行的‘惡龍’。
2 總綱
? 建立必要的索引
這次傳授的降龍十八掌，總綱只有一句話：建立必要的索引，這就是後面降龍十八掌的
內功基礎。這一點看似容易實際卻很難。難就難在如何判斷哪些索引是必要的，哪些又是不
必要的。判斷的最終標準是看這些索引是否對我們的資料庫效能有所幫助。具體到方法上，
就必須熟悉資料庫應用程式中的所有SQL語句，從中統計出常用的可能對效能有影響的部分
SQL，分析、歸納出作為Where條件子句的欄位及其組合方式；在這一基礎上可以初步判斷
出哪些表的哪些欄位應該建立索引。其次，必須熟悉應用程式。必須瞭解哪些表是資料操作
頻繁的表；哪些表經常與其他表進行連線；哪些表中的資料量可能很大；對於資料量大的表，
其中各個欄位的資料分佈情況如何；等等。對於滿足以上條件的這些表，必須重點關注，因
為在這些表上的索引，將對SQL語句的效能產生舉足輕重的影響。不過下面還是總結了一下
降龍十八掌內功的入門基礎，建立索引常用的規則如下：
1、表的主鍵、外來鍵必須有索引；
2、資料量超過300的表應該有索引；
3、經常與其他表進行連線的表，在連線欄位上應該建立索引；
4、經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引；
5、索引應該建在選擇性高的欄位上；
6、索引應該建在小欄位上，對於大的文字欄位甚至超長欄位，不要建索引；
7、複合索引的建立需要進行仔細分析；儘量考慮用單欄位索引代替：
A、正確選擇複合索引中的主列欄位，一般是選擇性較好的欄位；

B、複合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位查詢
是否極少甚至沒有？如果是，則可以建立複合索引；否則考慮單欄位索引；
C、如果複合索引中包含的欄位經常單獨出現在Where子句中，則分解為多個單欄位
索引；
D、如果複合索引所包含的欄位超過3個，那麼仔細考慮其必要性，考慮減少複合的
欄位；
E、如果既有單欄位索引，又有這幾個欄位上的複合索引，一般可以刪除複合索引；
8、頻繁進行資料操作的表，不要建立太多的索引；
9、刪除無用的索引，避免對執行計劃造成負面影響；
以上是一些普遍的建立索引時的判斷依據。一言以蔽之，索引的建立必須慎重，對每個
索引的必要性都應該經過仔細分析，要有建立的依據。因為太多的索引與不充分、不正確的
索引對效能都毫無益處：在表上建立的每個索引都會增加儲存開銷，索引對於插入、刪除、
更新操作也會增加處理上的開銷。 另外，過多的複合索引，在有單欄位索引的情況下，一
般都是沒有存在價值的；相反，還會降低資料增加刪除時的效能，特別是對頻繁更新的表來
說，負面影響更大。
3 降龍十八掌
第一掌 避免對列的操作
任何對列的操作都可能導致全表掃描，這裡所謂的操作包括資料庫函式、計算表示式等
等，查詢時要儘可能將操作移至等式的右邊，甚至去掉函式。
例1：下列SQL條件語句中的列都建有恰當的索引，但30萬行資料情況下執行速度卻非
常慢：
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子句中對列的任何操作結果都是在SQL執行時逐行計算得到的，因此它不得
不進行表掃描，而沒有使用該列上面的索引；如果這些結果在查詢編譯時就能得到，那麼就
可以被SQL優化器優化，使用索引，避免表掃描，因此將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秒）
差別是很明顯的！
第二掌 避免不必要的型別轉換
需要注意的是，儘量避免潛在的資料型別轉換。如將字元型資料與數值型資料比較，
ORACLE會自動將字元型用to_number()函式進行轉換，從而導致全表掃描。
例2：表tab1中的列col1是字元型（char)，則以下語句存在型別轉換：
select col1,col2 from tab1 where col1>10，
應該寫為： select col1,col2 from tab1 where col1>'10'。
第三掌 增加查詢的範圍限制
增加查詢的範圍限制，避免全範圍的搜尋。
例3：以下查詢表record 中時間ActionTime小於2001年3月1日的資料：
select * from record where ActionTime < to_date ('20010301' ,'yyyymm')
查詢計劃表明，上面的查詢對錶進行全表掃描，如果我們知道表中的最早的資料為2001
年1月1日，那麼，可以增加一個最小時間，使查詢在一個完整的範圍之內。修改如下： select
* from record where
ActionTime < to_date ('20010301' ,'yyyymm')
and ActionTime > to_date ('20010101' ,'yyyymm')
後一種SQL語句將利用上ActionTime欄位上的索引，從而提高查詢效率。把'20010301'
換成一個變數，根據取值的機率，可以有一半以上的機會提高效率。同理，對於大於某個值
的查詢，如果知道當前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最
大值)”。
第四掌 儘量去掉"IN"、"OR"
含有"IN"、"OR"的Where子句常會使用工作表，使索引失效；如果不產生大量重複值，
可以考慮把子句拆開；拆開的子句中應該包含索引。
例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語句相比，查詢速度更快。
第五掌 儘量去掉 "<>"
儘量去掉 "<>"，避免全表掃描，如果資料是列舉值，且取值範圍固定，則修改為"OR"
方式。
例5：
UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;
以上語句由於其中包含了"<>"，執行計劃中用了全表掃描（TABLE ACCESS FULL），
沒有用到state欄位上的索引。實際應用中，由於業務邏輯的限制，欄位state為列舉值，只能
等於0，1或2，而且，值等於=1，2的很少，因此可以去掉"<>"，利用索引來提高效率。
修改為：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。進一步的
修改可以參考第4種方法。
第六掌 去掉Where 子句中的IS NULL 和IS NOT NULL
Where字句中的IS NULL和IS NOT NULL將不會使用索引而是進行全表搜尋，因此需要
通過改變查詢方式，分情況討論等方法，去掉Where子句中的IS NULL和IS NOT NULL。
第七掌 索引提高資料分佈不均勻時查詢效率
索引的選擇性低，但資料的值分佈差異很大時，仍然可以利用索引提高效率。A、資料
分佈不均勻的特殊情況下，選擇性不高的索引也要建立。
表ServiceInfo中資料量很大，假設有一百萬行，其中有一個欄位DisposalCourseFlag，取
值範圍為列舉值：[0，1，2，3，4，5，6，7]。按照前面說的索引建立的規則，“選擇性不
高的欄位不應該建立索引，該欄位只有8種取值，索引值的重複率很高，索引選擇性明顯很
低，因此不建索引。然而，由於該欄位上資料值的分佈情況非常特殊，具體如下表：

取值範圍                         1~5                6                     7
佔總資料量的百分比    1%                 98%                1%

而且，常用的查詢中，查詢DisposalCourseFlag<6 的情況既多又頻繁，毫無疑問，如果
能夠建立索引，並且被應用，那麼將大大提高這種情況的查詢效率。因此，我們需要在該字

段上建立索引。
第八掌 利用HINT 強制指定索引
在ORACLE優化器無法用上合理索引的情況下，利用HINT強制指定索引。
繼續上面7的例子，ORACLE預設認定，表中列的值是在所有資料行中均勻分佈的，也
就是說，在一百萬資料量下，每種DisposalCourseFlag值各有12.5萬資料行與之對應。假設SQL
搜尋條件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引進行資料搜尋效率，往
往不比全表掃描的高，ORACLE因此對索引“視而不見”，從而在查詢路徑的選擇中，用其
他欄位上的索引甚至全表掃描。根據我們上面的分析，資料值的分佈很特殊，嚴重的不均勻。
為了利用索引提高效率，此時，一方面可以單獨對該欄位或該表用analyze語句進行分析，
對該列蒐集足夠的統計資料，使ORACLE在查詢選擇性較高的值時能用上索引；另一方面，
可以利用HINT提示，在SELECT關鍵字後面，加上“/*+ INDEX(表名稱，索引名稱）*/”的
方式，強制ORACLE優化器用上該索引。
比如： select * from serviceinfo where DisposalCourseFlag=1 ;
上面的語句，實際執行中ORACLE用了全表掃描，加上藍色提示部分後，用到索引查詢。
如下：
select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ *
from serviceinfo where DisposalCourseFlag=1;
請注意，這種方法會加大程式碼維護的難度，而且該欄位上索引的名稱被改變之後，必須
要同步所有指定索引的HINT程式碼，否則HINT提示將被ORACLE忽略掉。
第九掌 遮蔽無用索引
繼續上面8的例子，由於實際查詢中，還有涉及到DisposalCourseFlag=6的查詢，而此時
如果用上該欄位上的索引，將是非常不明智的，效率也極低。因此這種情況下，我們需要用
特殊的方法遮蔽該索引，以便ORACLE選擇其他欄位上的索引。比如，如果欄位為數值型的
就在表示式的欄位名後，新增“+ 0”，為字元型的就並上空串：“||""”
如： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' 。
不過，不要把該用的索引遮蔽掉了，否則同樣會產生低效率的全表掃描。

第十掌 分解複雜查詢，用常量代替變數
對於複雜的Where條件組合，Where中含有多個帶索引的欄位，考慮用IF語句分情況進
行討論；同時，去掉不必要的外來引數條件，減低複雜度，以便在不同情況下用不同欄位上
的索引。
繼續上面9的例子，對於包含
Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null)
and ....的查詢，(這裡v_DisPosalCourseFlag為一個輸入變數，取值範圍可能為[NULL，0，1，
2，3，4，5，6，7])，可以考慮分情況用IF語句進行討論，類似：
IF v_DisPosalCourseFlag =1 THEN
Where DisposalCourseFlag = 1 and ....
ELSIF v_DisPosalCourseFlag =2 THEN
Where DisposalCourseFlag = 2 and ....
。。。。。。
第十一掌 like 子句儘量前端匹配
因為like引數使用的非常頻繁，因此如果能夠對like子句使用索引，將很高的提高查詢的
效率。
例6：select * from city where name like ‘%S%’
以上查詢的執行計劃用了全表掃描（TABLE ACCESS FULL），如果能夠修改為：
select * from city where name like ‘S%’
那麼查詢的執行計劃將會變成（INDEX RANGE SCAN），成功的利用了name欄位的索
引。這意味著Oracle SQL優化器會識別出用於索引的like子句，只要該查詢的匹配端是具體
值。因此我們在做like查詢時，應該儘量使查詢的匹配端是具體值，即使用like ‘S%’。
第十二掌 用Case 語句合併多重掃描
我們常常必須基於多組資料表計算不同的聚集。例如下例通過三個獨立查詢：
例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;
這樣我們需要進行三次全表查詢，但是如果我們使用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;
這樣查詢的結果一樣，但是執行計劃只進行了一次全表查詢。
第十三掌 使用 nls_date_format
例9：
select * from record where to_char(ActionTime,'mm')='12'
這個查詢的執行計劃將是全表查詢，如果我們改變nls_date_format，
SQL>alert session set nls_date_formate=’MM’;
現在重新修改上面的查詢：
select * from record where ActionTime='12'
這樣就能使用actiontime上的索引了，它的執行計劃將是（INDEX RANGE SCAN）。
第十四掌 使用基於函式的索引
前面談到任何對列的操作都可能導致全表掃描，例如：
select * from emp where substr(ename,1,2)=’SM’;
但是這種查詢在客服系統又經常使用，我們可以建立一個帶有substr函式的基於函式的
索引，
create index emp_ename_substr on eemp ( substr(ename,1,2) );
這樣在執行上面的查詢語句時，這個基於函式的索引將排上用場，執行計劃將是
（INDEX RANGE SCAN）。

第十五掌 基於函式的索引要求等式匹配
上面的例子中，我們建立了基於函式的索引，但是如果執行下面的查詢：
select * from emp where substr(ename,1,1)=’S’
得到的執行計劃將還是（TABLE ACCESS FULL），因為只有當資料列能夠等式匹配時，
基於函式的索引才能生效，這樣對於這種索引的計劃和維護的要求都很高。請注意，向表中
新增索引是非常危險的操作，因為這將導致許多查詢執行計劃的變更。然而，如果我們使用
基於函式的索引就不會產生這樣的問題，因為Oracle只有在查詢使用了匹配的內建函式時才
會使用這種型別的索引。
第十六掌 使用分割槽索引
在用分析命令對分割槽索引進行分析時，每一個分割槽的資料值的範圍資訊會放入Oracle的
資料字典中。Oracle可以利用這個資訊來提取出那些只與SQL查詢相關的資料分割槽。
例如，假設你已經定義了一個分割槽索引，並且某個SQL語句需要在一個索引分割槽中進行
一次索引掃描。Oracle會僅僅訪問這個索引分割槽，而且會在這個分割槽上呼叫一個此索引範圍
的快速全掃描。因為不需要訪問整個索引，所以提高了查詢的速度。
第十七掌 使用點陣圖索引
點陣圖索引可以從本質上提高使用了小於1000個唯一資料值的資料列的查詢速度，因為在
點陣圖索引中進行的檢索是在RAM中完成的，而且也總是比傳統的B樹索引的速度要快。對於
那些少於1000個唯一資料值的資料列建立點陣圖索引，可以使執行效率更快。
第十八掌 決定使用全表掃描還是使用索引
和所有的祕笈一樣，最後一招都會又回到起點，最後我們來討論一下是否需要建立索引，
也許進行全表掃描更快。在大多數情況下，全表掃描可能會導致更多的物理磁碟輸入輸出，
但是全表掃描有時又可能會因為高度並行化的存在而執行的更快。如果查詢的表完全沒有順
序，那麼一個要返回記錄數小於10％的查詢可能會讀取表中大部分的資料塊，這樣使用索引
會使查詢效率提高很多。但是如果表非常有順序，那麼如果查詢的記錄數大於40％時，可能
使用全表掃描更快。因此，有一個索引範圍掃描的總體原則是：
1）對於原始排序的表 僅讀取少於表記錄數40％的查詢應該使用索引範圍掃描。反之，
讀取記錄數目多於表記錄數的40％的查詢應該使用全表掃描。

2）對於未排序的表 僅讀取少於表記錄數7％的查詢應該使用索引範圍掃描。反之，
讀取記錄數目多於表記錄數的7％的查詢應該使用全表掃描。
4 總結
以上的招式，是完全可以相互結合同時運用的。而且各種方法之間相互影響，緊密聯絡。
這種聯絡既存在一致性，也可能帶來衝突，當衝突發生時，需要根據實際情況進行選擇，沒
有固定的模式。最後決定SQL優化功力的因素就是對ORACLE內功的掌握程度了。
另外，值得注意的是：隨著時間的推移和資料的累計與變化，ORACLE對SQL語句的執
行計劃也會改變，比如：基於代價的優化方法，隨著資料量的增大，優化器可能錯誤的不選
擇索引而採用全表掃描。這種情況可能是因為統計資訊已經過時，在資料量變化很大後沒有
及時分析表；但如果對錶進行分析之後，仍然沒有用上合理的索引，那麼就有必要對SQL
語句用HINT提示，強制用合理的索引。但這種HINT提示也不能濫用，因為這種方法過於復
雜，缺乏通用性和應變能力，同時也增加了維護上的代價；相對來說，基於函式右移、去掉
“IN ，OR ，<> ，IS NOT NULL ”、分解複雜的SQL語句等等方法，卻是“放之四海皆
準”的，可以放心大膽的使用。
同時，優化也不是“一勞永逸”的，必須隨著情況的改變進行相應的調整。當資料庫設
計發生變化，包括更改表結構：欄位和索引的增加、刪除或改名等；業務邏輯發生變化：如
查詢方式、取值範圍發生改變等等。在這種情況下，也必須對原有的優化進行調整，以適應
效率上的需求。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-609305/，如需轉載，請註明出處，否則將追究法律責任。