SQl語句查詢效能最佳化

標籤：

【摘要】本文從DBMS的查詢最佳化工具對SQL查詢語句進行效能最佳化的角度出發，結合資料庫理論，從查詢運算式及其多種查詢條件組合對資料庫查詢效能最佳化進行分析，總結出多種提高資料庫查詢效能最佳化策略，介紹索引的合理建立和使用以及高品質SQL查詢語句的書寫原則，從而實現高效的查詢，提高系統的可用性。 

【關鍵詞】SQL查詢語句，索引，效能最佳化 

1.引言

在應用系統開發初期，由於開發資料庫資料比較少，對於查詢SQL語句，索引的運用與複雜視圖的編寫等體會不出SQL語句各種寫法的效能優劣，但是應用系統實際應用後，隨著資料庫中資料的增加，系統的響應速度就成為目前系統需要解決的最主要的問題之一。系統最佳化中一個很重要的方面就是SQL語句的最佳化。對于海量資料，劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍，可見對於一個系統不是簡單地能實現其功能就可，而是要寫出高品質的SQL語句，提高系統的可用性。 面對海量資料查詢，分時段對大批量資料進行刪除、更新和插入操作，抓住需要最佳化的主要方面，針對不同的情況從如何採用高效的SQL入手來進行。

2.索引的正確使用 在海量資料表中，基本每個表都有一個或多個的索引來保證高效的查詢，索引的使用需要遵循以下使用原則：

(1) 當插入的資料為資料表中的記錄數量10%以上時, 首先需要刪除該表的索引來提高資料的插入效率，當資料全部插入後再建立索引。 
(2) 避免在索引列上使用函數或計算，在WHERE子句中，如果索引列是函數的一部分，最佳化器將不使用索引而使用全表掃描。舉例:      

　　　　wk_ad_begin({pid : 21});wk_ad_after(21, function(){$(‘.ad-hidden‘).hide();}, function(){$(‘.ad-hidden‘).show();});

低效： select  *  from  table  where  salary * 12  >  25000;  高效： select  *  from  table  where  salary  >  25000/12; 低效： select * from table1 where name=‘zhangsan‘  and  tID > 10000 高效： select * from table1 where tID > 10000 and name=‘zhangsan‘

如果tID是一個彙總索引，那麼後一句僅僅從表的10000條以後的記錄中尋找就行了；而前一句則要先從全表中尋找看有幾個name=‘zhangsan‘的，而後再根據限制條件條件tID>10000來提出查詢結果。 

(3) 避免在索引列上使用NOT和”!=”或<> ， 索引只能告訴什麼存在於表中， 而不能告訴什麼不存在於表中，當資料庫遇到NOT和”!=”時，就會停止使用索引轉而執行全表掃描。 
(4) 索引列上用>=替代> 

高效：   select  *  from  table  where  Deptno >=4  
低效：   select  *  from  table  where  Deptno >3 

兩者的區別在於， 前者table將直接跳到第一個Deptno等於4的記錄而後者將首先定位到Deptno=3的記錄並且向前掃描到第一個Deptno大於3的記錄。 

(5) 函數的列啟用索引方法，如果一定要對使用函數的列啟用索引，Oracle9i以上版本新的功能：基於函數的索引(Function-Based Index)是一個較好的方案，但該類型索引的缺點是只能針對某個函數來建立和使用該函數。 

create  index  EMP_I  ON  EMP (upper( ename)); /*建立基於函數的索引*/  

select  *  from EMP  where  upper( ename) = „BLACKSNAIL?; /*將使用索引*/ 

3.  SQL語句效能最佳化

 3.1 WHERE子句中的串連順序 

ORACLE採用自下而上的順序解析where子句，根據這個原理，表之間的串連必須寫在其它where條件之前，那些可以過濾掉最大數量記錄的條件必須寫在where子句的末尾。 

低效：Select  *  from  table where Salary > 50000  and Job = ‘MANAGER’ and 25 < (Select count(*) from table  where Mgr=table.Empno);  高效：Select  *  from  table where 25 < (select count(*) from table   where Mgr=table.Empno)  and Salary> 50000 and Job = ‘MANAGER’;

3.2 用EXISTS替代IN 

在許多基於基礎資料表的查詢中，為了滿足一個條件往往需要對另一個表進行聯結，例如在ETL過程寫資料到模型時經常需要關聯10個左右的維表，從ORACLE執行的步驟來分析用IN的SQL與不用IN的SQL有以下區別：ORACLE試圖將其轉換成多個表的串連，如果轉換不成功則先執行IN裡面的子查詢，再查詢外層的表記錄，如果轉換成功則直接採用多個表的串連方式查詢。由此可見用IN的SQL至少多了一個轉換的過程, 在這種情況下，使用EXISTS而不用IN將提高查詢的效率。

3.3 用NOT EXISTS替代NOT IN 

子查詢中，NOT IN子句將執行一個內部的排序和合并，無論在哪種情況下，NOT IN都是最低效的，因為它對子查詢中的表執行了一個全表遍曆。用NOT EXISTS替代NOT IN將提高查詢的效率。

3.4  !=或<> 操作符（不等於）

不等於操作符是永遠不會用到索引的，因此對它的處理只會產生全表掃描。推薦方案：用其它相同功能的操作運算代替，如a<>0 改為 a>0 or a<0 。 

3.5  IS NULL 或IS NOT NULL操作（判斷欄位是否為空白） 

判斷欄位是否為空白一般是不會應用索引的，因為B樹索引是不索引空值的。推薦方案：用其它相同功能的操作運算代替，如a is not null 改為 a>0 或a>’’等。不允許欄位為空白，而用一個預設值代替空值。 

3.6  > 及 < 操作符（大於或小於操作符） 

大於或小於操作符一般情況下是不用調整的，因為它有索引就會採用索引尋找，但有的情況下可以對它進行最佳化，如一個表有100萬記錄，一個數值型欄位A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那麼執行A>2與A>=3的效果就有很大的區別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。

3.7 最佳化GROUP BY 

提高GROUP BY 語句的效率，可以通過將不需要的記錄在GROUP BY 之前過濾掉。  

低效： Select  Job, Avg(Salary)  from  table  group  by  Job  having  Job = ‘PRESIDENT’ OR  Job  = ‘MANAGER’ 高效： Select  Job, Avg(Salary)  from  table  Where  Job  = ‘PRESIDENT’ OR Job  = ‘MANAGER’ group  by  Job  

3.8  LIKE操作符 

LIKE操作符可以應用萬用字元查詢，裡面的萬用字元組合可能達到幾乎是任意的查詢，但是如果用得不好則會產生效能上的問題，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用範圍索引。一個實際例子：用Students表中學生編號後面的標識號可來查詢學生 Sno LIKE ‘%5400%’ 這個條件會產生全表掃描，如果改成Sno LIKE ’X5400%’ OR  Sno LIKE ’B5400%’ 則會利用Sno的索引進行兩個範圍的查詢，效能肯定大大提高。 

3.9 有條件的使用UNION-ALL 替換UNION 

針對多表串連操作的情況很多，有條件的使用UNION-ALL 替換UNION的前提是：所串連的各個表中無主關鍵字相同的記錄，因為UNION ALL 將重複輸出兩個結果集合中相同記錄。UNION在進行錶鏈接後會篩選掉重複的記錄，所以在錶鏈接後會對所產生的結果集進行排序運算，重複資料刪除的記錄再返回結果。當SQL語句需要UNION兩個查詢結果集合時，這兩個結果集合會以UNION-ALL的方式被合并，然後在輸出最終結果前進行排序。如果用UNION ALL替代UNION， 這樣排序就不是必要了，效率就會因此得到提高3-5倍。

3.10 避免where 子句中對欄位進行運算式操作或對欄位進行函數操作 

因為若where 子句中如果索引列是運算式或函數的一部分,最佳化器就不能使用分布統計資訊，這將導致引擎放棄使用索引而進行全表掃描。 

低效：Select  name from table  where  substring (id, 1, 1) = ‘C‘ 低效：Select  name from table  where  salary * 10 > 40000

4.其它的最佳化方法

資料庫的查詢最佳化方法不僅僅是索引和SQL語句的最佳化，其他方法的合理使用同樣也能很好的對資料庫查詢功能起到最佳化作用。我們就來列舉幾種簡單實用的方法。

4.1 使用暫存資料表加速查詢 

   建立暫存資料表在一些情況下可以避免多重排序操作。但所建立的暫存資料表的行要比主表的行少，其物理順序就是所要求的順序，這樣就減少了輸入和輸出，降低了查詢的工作量，提高了查詢效率，而且暫存資料表的建立並不會反映主表的修改。  
   比如：如果直接在儲存上萬條資料的永久表上重複迴圈進行統計、查詢，其執行效率非常低，但是，先從儲存大量資料的永久表中提取符和條件的存放到暫存資料表後，在暫存資料表上執行操作，效率會大大提高。 

4.2 避免對大型表 行資料的順序存取 

在巢狀查詢中，對錶的順序存取對查詢效率可能產生致命的影響。 
比如採用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢10億行資料。 
避免這種情況的主要方法就是對串連的列進行索引。 兩個表：學生表（學號、姓名、年齡……）       選課表（學號、課程號、成績）  
如果兩個表要做串連，就要在“學號”這個串連欄位上建立索引。  

4.3  避免或簡化排序  

應當簡化或避免對大型表進行重複的排序。當能夠利用索引自動以適當的次序產生輸出時，最佳化器就避免了排序的步驟。以下是一些影響因素：

1.索引中不包括一個或幾個待排序的列

2. group by或order by子句中列的次序與索引的次序不一樣 

3. 排序的列來自不同的表 為了避免不必要的排序，就要正確地增建索引，合理地合并資料庫表（儘管有時可能影響表的正常化，但相對於效率的提高是值得的）。如果排序不可避免，那麼應當試圖簡化它，如縮小排序的列的範圍等。

 

4.4 避免相互關聯的子查詢如果在主查詢和where子句中的查詢中同時出現了一個列的標籤，這樣就會使主查詢的列值改變後，子查詢也必須重新進行一次查詢。因為查詢的嵌套層次越多，查詢的效率就會降低，所以我們應當避免子查詢。如果無法避免，就要在查詢的過程中過濾掉儘可能多的。 

4.5 用排序來取代非順序存取 非順序磁碟存取是最慢的操作，表現在磁碟存取臂的來回移動。SQL語句隱藏了這一情況，使得我們在寫應用程式時很容易寫出要求存取大量非順序頁的查詢。 有些時候，用資料庫的排序能力來替代非順序的存取能改進查詢。 

5. 總結 

對于海量資料的查詢最佳化，我們要抓住關鍵問題，寫出高品質的SQL語句，提高系統的可用性。在計算實際的查詢處理代價時，必須考慮最佳化本身的時間和空間開銷。如果選擇最佳的處理方案的開銷太大，則可能得不償失。實際上，查詢最佳化在合理的最佳化處理開銷下，尋找較好的處理方案，而不一定是最佳方案

SQl語句查詢效能最佳化