基於索引的SQL語句最佳化之降龍十八掌(1)[@more@]

客服業務受到SQL語句的影響非常大，在規模比較大的局點，往往因為一個小的SQL語句不夠最佳化，導致資料庫效能急劇下降，小型機idle所剩無幾，應用伺服器斷連、超時，嚴重影響業務的正常執行。因此，稱低效的SQL語句為客服業務的‘惡龍’並不過分。資料庫的最佳化方法有很多種，在應用層來說，主要是基於索引的最佳化。本次秘笈根據實際的工作經驗，在研發原來已有的方法的基礎上，進行了一些擴充，總結了基於索引的SQL語句最佳化的降龍十八掌，希望有一天你能用其中一掌來馴服客服業務中橫行的‘惡龍’。

l 建立必要的索引

這次傳授的降龍十八掌，總綱只有一句話：建立必要的索引，這就是後面降龍十八掌的內功基礎。這一點看似容易實際卻很難。難就難在如何判斷哪些索引是必要的，哪些又是不必要的。判斷的最終標準是看這些索引是否對我們的資料庫效能有所幫助。具體到方法上，就必須熟悉資料庫應用程式中的所有SQL語句，從中統計出常用的可能對效能有影響的部分SQL，分析、歸納出作為Where條件子句的欄位及其組合方式；在這一基礎上可以初步判斷出哪些表的哪些欄位應該建立索引。其次，必須熟悉應用程式。必須瞭解哪些表是資料操作頻繁的表；哪些表經常與其他表進行連線；哪些表中的資料量可能很大；對於資料量大的表，其中各個欄位的資料分佈情況如何；等等。對於滿足以上條件的這些表，必須重點關注，因為在這些表上的索引，將對SQL語句的效能產生舉足輕重的影響。不過下面還是總結了一下降龍十八掌內功的入門基礎，建立索引常用的規則如下：

1、表的主鍵、外來鍵必須有索引；

2、資料量超過300的表應該有索引；

3、經常與其他表進行連線的表，在連線欄位上應該建立索引；

4、經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引；

5、索引應該建在選擇性高的欄位上；

6、索引應該建在小欄位上，對於大的文字欄位甚至超長欄位，不要建索引；

7、複合索引的建立需要進行仔細分析；儘量考慮用單欄位索引代替：

A、正確選擇複合索引中的主列欄位，一般是選擇性較好的欄位；

B、複合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位查詢是否極少甚至沒有？如果是，則可以建立複合索引；否則考慮單欄位索引；

C、如果複合索引中包含的欄位經常單獨出現在Where子句中，則分解為多個單欄位索引；

D、如果複合索引所包含的欄位超過3個，那麼仔細考慮其必要性，考慮減少複合的欄位；

E、如果既有單欄位索引，又有這幾個欄位上的複合索引，一般可以刪除複合索引；

8、頻繁進行資料操作的表，不要建立太多的索引；

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

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

避免對列的操作

任何對列的操作都可能導致全表掃描，這裡所謂的操作包括資料庫函式、計算表示式等等，查詢時要儘可能將操作移至等式的右邊，甚至去掉函式。

例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'。