程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQLSERVER SQL機能優化技能

SQLSERVER SQL機能優化技能

編輯：MSSQL

SQLSERVER SQL機能優化技能。本站提示廣大學習愛好者：（SQLSERVER SQL機能優化技能）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLSERVER SQL機能優化技能正文

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 2000

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';

3.SELECT子句中防止應用'*'。當你想在SELECT子句中列出一切的COLUMN時，應用靜態SQL列援用'*'是一個便利的辦法，不幸的是，這是一個異常低效的辦法。現實上，SQLSERVER在解析的進程中，會將'*'順次轉換成一切的列名，這個任務是經由過程查詢數據字典完成的，這意味著將消耗更多的時光

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函數來削減處置時光

應用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子句

防止應用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.削減對表的查詢

在含有子查詢的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)，當在SQL語句中銜接多個表時，請應用表的別號並把別號前綴於每一個Column上，如許可以削減解析的時光並削減那些由Column歧義惹起的語法毛病

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

在子查詢中，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

平日來講，采取表銜接的方法比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

當提交一個包括多表信息（好比部分表和雇員表）的查詢時，防止在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.用索引進步效力

索引是表的一個概念部門，用來進步檢索數據的效力。現實上，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.防止在索引列上應用盤算

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

防止在索引中應用任何可認為空的列，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

當SQL語句須要UNION兩個查詢成果聚集時，這兩個成果聚集會以UNION-ALL的方法被歸並，然後在輸入終究成果進步行排序

假如用UNION ALL替換UNION，如許排序就不是需要了，效力就會是以獲得進步

須要留意的是，UNION ALL將反復輸入兩個成果聚集中雷同記載，是以照樣要從營業需求剖析應用UNION ALL的可行性

關於索引以下經歷請參考:

1).假如檢索數據量跨越30%的表中記載數，應用索引將沒有明顯的效力進步

2).在特定情形下，應用索引或許會比全表掃描慢，但這是統一個數目級上的差距；而平日情形下，應用索引比全表掃描要快幾倍甚至幾千倍！