原文轉自：
SQL編碼規范(收集)
http://hi.baidu.com/rambochow/blog/item/3ebcbb3db3758903bba16743.html
1.選擇最有效率的表名順序(只在基于規則的優化器中有效)
1.選擇最有效率的表名順序(只在基于規則的優化器中有效)
SQLSERVER的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表（基礎表driving table）將被最先處理，在FROM子句中包含多個表的情況下，必須選擇記錄條數最少的表作為基礎表，當SQLSERVER處理多個表時，會運用排序及合并的方式連接它們，
首先，掃描第一個表（FROM子句中最后的那個表)并對記錄進行排序；然后掃描第二個表（FROM子句中最后第二個表)；最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并
例如: 表 TAB1 16,384 條記錄表 TAB2 5 條記錄，選擇TAB2作為基礎表 (最好的方法) select count(*) from tab1,tab2 執行時間0.96秒，選擇TAB2作為基礎表 (不佳的方法) select count(*) from tab2,tab1 執行時間26.09秒；
如果有3個以上的表連接查詢，那就需要選擇交叉表（intersection table）作為基礎表，交叉表是指那個被其他表所引用的表
例如:
EMP表描述了LOCATION表和CATEGORY表的交集
SELECT *
FROM LOCATION L,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
將比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 20002.WHERE子句中的連接順序
2.WHERE子句中的連接順序
SQLSERVER采用自下而上的順序解析WHERE子句，根據這個原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾
例如：
(低效,執行時間156.3秒)
SELECT *
FROM EMP E
WHERE SAL > 50000
AND JOB = 'MANAGER'
AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
(高效,執行時間10.6秒)
SELECT *
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = 'MANAGER';
4.減少訪問數據庫的次數
4.減少訪問數據庫的次數。
當執行每條SQL語句時，SQLSERVER在內部執行了許多工作：解析SQL語句，估算索引的利用率，綁定變量，讀數據塊等等
由此可見，減少訪問數據庫的次數，就能實際上減少SQLSERVER的工作量，例如：
以下有三種方法可以檢索出雇員號等于0342或0291的職員
方法1 (最低效)
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,…,…;
…
OPEN C1(291);
FETCH C1 INTO …,…,…;
…
CLOSE C1;
END;
方法2 (高效)
SELECT A.EMP_NAME, A.SALARY, A.GRADE,
B.EMP_NAME, B.SALARY, B.GRADE
FROM EMP A, EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;
5.使用DECODE函數來減少處理時間
5.使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表
例如：
SELECT COUNT(*), SUM(SAL)
FROM EMP
WHERE DEPT_NO = '0020'
AND ENAME LIKE 'SMITH%';
SELECT COUNT(*), SUM(SAL)
FROM EMP
WHERE DEPT_NO = '0030'
AND ENAME LIKE 'SMITH%';
你可以用DECODE函數高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NO, '0020', 'X', NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO, '0030', 'X', NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO, '0020', SAL, NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL
FROM EMP
WHERE ENAME LIKE 'SMITH%';
'X'表示任何一個字段
類似的，DECODE函數也可以運用于GROUP BY和ORDER BY子句中
6.用Where子句替換HAVING子句
6.用Where子句替換HAVING子句
避免使用HAVING子句，HAVING只會在檢索出所有記錄之后才對結果集進行過濾，這個處理需要排序、統計等操作
如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷
例如：
低效
SELECT REGION, AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != 'SYDNEY'
AND REGION != 'PERTH'
高效
SELECT REGION, AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != 'SYDNEY'
AND REGION != 'PERTH'
GROUP BY REGION
7.減少對表的查詢
7.減少對表的查詢
在含有子查詢的SQL語句中，要特別注意減少對表的查詢
例如：
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER = (SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update多個Column例子：
低效
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY)
FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效
UPDATE EMP
SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
8.使用表的別名(Alias)
8.使用表的別名(Alias)
當在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上，這樣可以減少解析的時間并減少那些由Column歧義引起的語法錯誤
9.用EXISTS替代IN
9.用EXISTS替代IN
在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接
在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率
低效
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'MELB')
高效
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 'MELB')
10.用NOT EXISTS替代NOT IN
10.用NOT EXISTS替代NOT IN
在子查詢中，NOT IN子句將執行一個內部的排序和合并
無論在哪種情況下，NOT IN都是最低效的，因為它對子查詢中的表執行了一個全表遍歷
為了避免使用NOT IN，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS
例如：
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT = 'A');
為了提高效率改寫為
高效
SELECT …
FROM EMP A, DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = 'A'
最高效
SELECT …
FROM EMP E
WHERE NOT EXISTS (SELECT 'X'
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = 'A');
11.用表連接替換EXISTS
11.用表連接替換EXISTS
通常來說，采用表連接的方式比EXISTS更有效率
例如：
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = 'A');
更高效
SELECT ENAME
FROM DEPT D, EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = 'A';
12.用EXISTS替換DISTINCT
12.用EXISTS替換DISTINCT
當提交一個包含多表信息（比如部門表和雇員表）的查詢時，避免在SELECT子句中使用DISTINCT，一般可以考慮用EXIST替換
例如：
低效
SELECT DISTINCT DEPT_NO, DEPT_NAME
FROM DEPT D, EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效
SELECT DEPT_NO, DEPT_NAME
FROM DEPT D
WHERE EXISTS (SELECT 'X'
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結果
13.用索引提高效率
13.用索引提高效率
索引是表的一個概念部分，用來提高檢索數據的效率。實際上，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 LODGING
WHERE LODGING = 'ROSE HILL';
在內部，上述SQL將被分成兩步執行：
首先，LODGING_PK索引將通過索引唯一掃描的方式被訪問，獲得相對應的ROWID；然后通過ROWID訪問表的方式執行下一步檢索。如果被檢索返回的列包括在INDEX列中，SQLSERVER將不執行第二步的處理（通過ROWID訪問表）。因為檢索數據保存在索引中，單單訪問索引就可以完全滿足查詢結果
2).索引范圍查詢(INDEX RANGE SCAN)
適用于兩種情況:
1>.基于唯一性索引的一個范圍的檢索
2>.基于非唯一性索引的檢索
例1
SELECT LODGING
FROM LODGING
WHERE LODGING LIKE 'M%';
WHERE子句條件包括一系列值，SQLSERVER將通過索引范圍查詢的方式查詢LODGING_PK
由于索引范圍查詢將返回一組值，它的效率就要比索引唯一掃描低一些
例2
SELECT LODGING
FROM LODGING
WHERE MANAGER = 'BILL GATES';
這個SQL的執行分兩步，LODGING$MANAGER的索引范圍查詢（得到所有符合條件記錄的ROWID），通過ROWID訪問表得到LODGING列的值
由于LODGING$MANAGER是一個非唯一性的索引，數據庫不能對它執行索引唯一掃描
WHERE子句中，如果索引列所對應的值的第一個字符由通配符（WILDCARD）開始，索引將不被采用
SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE '％HANMAN';
在這種情況下，SQLSERVER將使用全表掃描
14.避免在索引列上使用計算
14.避免在索引列上使用計算
WHERE子句中，如果索引列是函數的一部分，優化器將不使用索引而使用全表掃描
例如：
低效
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
請務必注意，檢索中不要對索引列進行處理，如：TRIM，TO_DATE，類型轉換等操作，破壞索引，使用全表掃描，影響SQL執行效率
15.避免在索引列上使用IS NULL和IS NOT NULL
15.避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以為空的列，SQLSERVER將無法使用該索引
對于單列索引，如果列包含空值，索引中將不存在此記錄；
對于復合索引，如果每個列都為空，索引中同樣不存在此記錄。如果至少有一個列不為空，則記錄存在于索引中
如果唯一性索引建立在表的A列和B列上，并且表中存在一條記錄的A,B值為(123,null)，
SQLSERVER將不接受下一條具有相同A,B值（123,null）的記錄插入
如果所有的索引列都為空，SQLSERVER將認為整個鍵值為空，而空不可能等于空，因此你可以插入1000條具有相同鍵值的記錄，當然它們都是空！
因為空值不存在于索引列中，所以WHERE子句中對索引列進行空值比較將使SQLSERVER停用該索引
低效（索引失效）
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL
16.使用UNION-ALL和UNION
16.使用UNION-ALL和UNION
當SQL語句需要UNION兩個查詢結果集合時，這兩個結果集合會以UNION-ALL的方式被合并，然后在輸出最終結果前進行排序
如果用UNION ALL替代UNION，這樣排序就不是必要了，效率就會因此得到提高
需要注意的是，UNION ALL將重復輸出兩個結果集合中相同記錄，因此還是要從業務需求分析使用UNION ALL的可行性
關于索引下列經驗請參考:
1).如果檢索數據量超過30%的表中記錄數，使用索引將沒有顯著的效率提高
2).在特定情況下，使用索引也許會比全表掃描慢，但這是同一個數量級上的差距；而通常情況下，使用索引比全表掃描要快幾倍乃至幾千倍！
posted on 2008-09-02 18:20
黃小二 閱讀(513)
評論(1) 編輯 收藏