SQL SERVER 效能最佳化

標籤：lang   raw   section   ble   動態sql   時間   server   primary   範圍查詢   

1.　選擇最有效率的表名順序(只在基於規則的最佳化器中有效)

　　SQLSERVER的解析器按照從右至左的順序處理FROM子句中的表名，因此FROM子句中寫在最後的表（基礎資料表driving table）將被最先處理，在FROM子句中包含多個表的情況下，必須選擇記錄條數最少的表作為基礎資料表，當SQLSERVER處理多個表時，會運用排序及合并的方式串連它們。 首先，掃描第一個表（FROM子句中最後的那個表)並對記錄進行排序；然後掃描第二個表（FROM子句中最後第二個表)；最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并。

　　例如: 表A 26,888 條記錄,表B 50 條記錄，選擇B作為基礎資料表 (最好的方法) select count(*) from A,B 執行時間1秒，選擇A作為基礎資料表 (不佳的方法) select count(*) from B,A 執行時間28.08秒；如果有3個以上的表串連查詢，那就需要選擇交叉表（intersection table）作為基礎資料表，交叉表是指那個被其他表所引用的表。　

　　例如:

　　 EMP表描述了T1表和T2表的交集

　　

 SELECT *　　 FROM T1 a,　　 T2 b,　　 EMP c　　 WHERE c.EmpID BETWEEN 1000 AND 2000　　 AND c.EmpID = b.BID　　 AND E.DeptID =a.DeptID

　　 將比下列SQL更有效率

　　

 SELECT *　　 FROM EMP a ,　　 T1 b ,　　 T2 c　　 WHERE a.DeptID= c.DeptID　　 AND a.EmpID=b.BID　　 AND a.EmpID BETWEEN 1000 AND 2000

 

2.　 WHERE子句中的串連順序

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

       例如：

　　 (低效,執行時間13.8秒)　　

SELECT *　　 FROM EMP a　　 WHERE Cost > 20000　　 AND Job = ‘經理‘　　 AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=a.EmpID);

　　 (高效,執行時間1.6秒)

SELECT *　　 FROM EMP a　　 WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=a.EmpID)　　 AND Cost > 50000　　 AND Job = ‘經理‘;

 

3.　SELECT子句中避免使用‘*‘

　　當你想在SELECT子句中列出所有的COLUMN時，使用動態SQL列引用‘*‘是一個方便的方法，不幸的是，這是一個非常低效的方法。實際上，SQLSERVER在解析的過程中，會將‘*‘依次轉換成所有的列名，這個工作是通過查詢資料字典完成的，這意味著將耗費更多的時間。

4.　使用DECODE函數來減少處理時間

　   使用DECODE函數可以避免重複掃描相同記錄或重複串連相同的表。　　

　　例如：

　　 

SELECT COUNT(*), SUM(Cost)　　 FROM EMP　　 WHERE DeptID = ‘0020‘　　 AND EmpName LIKE ‘John%‘;

SELECT COUNT(*), SUM(Cost)　　 FROM EMP　　 WHERE DeptID= ‘0030‘　　 AND EmpName LIKE ‘John%‘;

　　你可以用DECODE函數高效地得到相同結果（‘X‘表示任何一個欄位）:

 SELECT COUNT(DECODE(DeptID, ‘0020‘, ‘X‘, NULL)) D0020_COUNT,　　 COUNT(DECODE(DeptID, ‘0030‘, ‘X‘, NULL)) D0030_COUNT,　　 SUM(DECODE(DeptID, ‘0020‘, Cost, NULL)) D0020_Cost,　　 SUM(DECODE(DeptID,‘ 0030‘, Cost, NULL)) D0030_Cost　　 FROM EMP　　 WHERE EmpName LIKE ‘John%‘;

　　類似的，DECODE函數也可以運用於GROUP BY和ORDER BY子句中

5.　用EXISTS替代IN

　　在許多基於基礎資料表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯結，在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。

       低效：

SELECT *　　 FROM EMP (基礎資料表)　　 WHERE EmpID> 0　　 AND DeptID IN (SELECT DeptID　　 FROM DEPT　　 WHERE LOC = ‘RD‘)

　　高效：

SELECT *　　 FROM EMP (基礎資料表)　　 WHERE EmpID > 0　　 AND EXISTS (SELECT ‘X‘　　 FROM DEPT　　 WHERE DEPT.DeptID= EMP.DeptID　　 AND LOC = ‘RD‘)

6.　用NOT EXISTS替代NOT IN

　　在子查詢中，NOT IN子句將執行一個內部的排序和合并，無論在哪種情況下，NOT IN都是最低效的，因為它對子查詢中的表執行了一個全表遍曆，為了避免使用NOT IN，我們可以把它改寫成外串連(Outer Joins)或NOT EXISTS。

　　 例如：

SELECT …　　 FROM EMP　　 WHERE DeptID NOT IN (SELECT DeptID　　 FROM DEPT　　 WHERE DType= ‘A‘);

　　高效處理：

　　為了提高效率改寫為

SELECT …　　 FROM EMP A, DEPT B　　 WHERE A.DeptID= B.DeptID　　 AND B.DeptID IS NULL　　 AND B.DType  = ‘A‘

　　最高效：

最高效　　 SELECT …　　 FROM EMP E　　 WHERE NOT EXISTS (SELECT ‘X‘　　 FROM DEPT D　　 WHERE D.DeptID= E.DeptID　　 AND DType= ‘A‘);

7.　用索引提高效率

　　索引是表的一個概念部分，用來提高檢索資料的效率。實際上，SQLSERVER使用了一個複雜的自平衡B-tree結構 。通常，通過索引查詢資料比全表掃描要快。當SQLSERVER找出執行查詢和Update語句的最佳路徑時，SQLSERVER最佳化器將使用索引。同樣，在連接多個表時使用索引也可以提高效率。另一個使用索引的好處是，它提供了主鍵（primary key）的唯一性驗證。　除了那些LONG或LONG RAW資料類型，你可以索引幾乎所有的列。雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價，索引需要空間來儲存，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改，這意味著每條記錄的INSERT、DELETE、UPDATE將為此多付出4、5次的磁碟I/O。因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢反應時間變慢。　　

　　 SQLSERVER對索引有兩種訪問模式：　　

　　 1).索引唯一掃描（INDEX UNIQUE SCAN）　　

　　 大多數情況下, 最佳化器通過WHERE子句訪問INDEX　　

　　 例如：

　　 表LODGING有兩個索引：建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER

　　 

SELECT *　　 FROM User　　 WHERE LODGING = ‘FLOWER‘;

　　 在內部，上述SQL將被分成兩步執行：　　

　　 首先，LODGING_PK索引將通過索引唯一掃描的方式被訪問，獲得相對應的ROWID；然後通過ROWID訪問表的方式執行下一步檢索。　　

　　 如果被檢索返回的列包括在INDEX列中，SQLSERVER將不執行第二步的處理（通過ROWID訪問表）　　

　　 因為檢索資料儲存在索引中，單單訪問索引就可以完全滿足查詢結果。

　　

　　 2).索引範圍查詢(INDEX RANGE SCAN)　　

　　 適用於兩種情況:　　

　　 1>.基於唯一性索引的一個範圍的檢索　　

　　 2>.基於非唯一性索引的檢索

　　

　　 例1

　　 

SELECT LODGING　　 FROM User　　 WHERE LODGING LIKE ‘M%‘;

　　 WHERE子句條件包括一系列值，SQLSERVER將通過索引範圍查詢的方式查詢LODGING_PK

　　 由於索引範圍查詢將返回一組值，它的效率就要比索引唯一掃描低一些

　　 例2

SELECT LODGING　　 FROM User　　 WHERE MANAGER = ‘MRZHANG‘;

　　 這個SQL的執行分兩步，LODGING$MANAGER的索引範圍查詢（得到所有符合條件記錄的ROWID），通過ROWID訪問表得到LODGING列的值。　　

　　 由於LODGING$MANAGER是一個非唯一性的索引，資料庫不能對它執行索引唯一掃描。　　

　　 WHERE子句中，如果索引列所對應的值的第一個字元由萬用字元（WILDCARD）開始，索引將不被採用　 

SELECT LODGING　　 FROM User　　 WHERE MANAGER LIKE ‘％HANMAN‘;

　　 在這種情況下，SQLSERVER將使用全表掃描。

8.　避免在索引列上使用計算

　　WHERE子句中，如果索引列是函數的一部分，最佳化器將不使用索引而使用全表掃描。　　

　　 例如：　　

　　 低效

　　 

SELECT …　　 FROM DEPT　　 WHERE Cost * 12 > 25000;

　　 高效

　　 

SELECT …　　 FROM DEPT　　 WHERE Cost > 25000/12;

　　 請務必注意，檢索中不要對索引列進行處理，如：TRIM，TO_DATE，類型轉換等操作，破壞索引，使用全表掃描，影響SQL執行效率。

SQL SERVER 效能最佳化