SQL Server最佳化之SQL語句最佳化

標籤：

一切都是為了效能，一切都是為了業務

一、查詢的邏輯執行順序

(1) FROM left_table(3) join_type JOIN right_table (2) ON join_condition(4) WHERE where_condition(5) GROUP BY group_by_list(6) WITH {cube | rollup}(7) HAVING having_condition(8) SELECT (9) DISTINCT (11) top_specification select_list(9) ORDER BY order_by_list

標準的 SQL 的解析順序為:

(1) FROM 子句 組裝來自不同資料來源的資料

(2) WHERE 子句 基於指定的條件對記錄進行篩選

(3) GROUP BY 子句 將資料劃分為多個分組

(4) 使用彙總函式進行計算

(5) 使用HAviNG子句篩選分組

(6) 計算所有的運算式

(7) 使用ORDER BY對結果集進行排序

二、執行順序

1. FROM：對FROM子句中前兩個表執行笛卡爾積產生虛擬表vt1

2. ON: 對vt1表應用ON篩選器只有滿足 join_condition 為真的行才被插入vt2

3. OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行將行作為外部行添加到vt2，產生t3，如果from包含兩個以上表，則對上一個連接產生的結果表和下一個表重複執行步驟和步驟直接結束。

4. WHERE：對vt3應用 WHERE 篩選器只有使 where_condition 為true的行才被插入vt4

5. GROUP BY：按GROUP BY子句中的列列表對vt4中的行分組產生vt5

6. CUBE|ROLLUP：把超組(supergroups)插入vt6，產生vt6

7. HAVING：對vt6應用HAVING篩選器只有使 having_condition 為true的組才插入vt7

8. SELECT：處理select列表產生vt8

9. DISTINCT：將重複的行從vt8中去除產生vt9

10. ORDER BY：將vt9的行按order by子句中的列列表排序產生一個遊標vc10

11. TOP：從vc10的開始處選擇指定數量或比例的行產生vt11 並返回調用者

看到這裡，那麼用過Linq to SQL的文法有點相似啊？如果我們我們瞭解了SQL Server執行順序，那麼我們就接下來進一步養成日常SQL的好習慣，也就是在實現功能的同時有考慮效能的思想，資料庫是能進行集合運算的工具，我們應該盡量的利用這個工具，所謂集合運算實際就是批量運算，就是盡量減少在用戶端進行大資料量的迴圈操作，而用SQL語句或者預存程序代替。

三、只返回需要的資料

返回資料到用戶端至少需要資料庫提取資料、網路傳輸資料、用戶端接收資料以及用戶端處理資料等環節，如果返回不需要的資料，就會增加伺服器、網路和用戶端的無效勞動，其害處是顯而易見的，避免這類事件需要注意：

A、橫向來看

(1) 不要寫SELECT * 的語句，而是選擇你需要的欄位。

(2) 當在SQL語句中串連多個表時, 請使用表的別名並把別名首碼於每個Column上。這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

如有表table1(ID,col1)和table2(ID,col2)Select A.ID, A.col1, B.col2-- Select A.ID, col1, col2 –不要這麼寫，不利於將來程式擴充from table1 A inner join table2 B on A.ID=B.ID Where …

B、縱向來看

(1) 合理寫WHERE子句，不要寫沒有WHERE的SQL語句。

(2) SELECT TOP N * – 沒有WHERE條件的用此替代。

四、盡量少做重複的工作

A、控制同一語句的多次執行，特別是一些基礎資料的多次執行是很多程式員很少注意的。

B、減少多次的資料轉換，也許需要資料轉換是設計的問題，但是減少次數是程式員可以做到的。

C、杜絕不必要的子查詢和串連表，子查詢在執行計畫一般解釋成外串連，多餘的串連錶帶來額外的開銷。

D、合并對同一表同一條件的多次UPDATE，比如

UPDATE EMPLOYEE SET FNAME=‘HAIWER‘WHERE EMP_ID=‘ VPA30890F‘UPDATE EMPLOYEE SET LNAME=‘YANG‘WHERE EMP_ID=‘ VPA30890F‘

這兩個語句應該合并成以下一個語句

UPDATE EMPLOYEE SET FNAME=‘HAIWER‘,LNAME=‘YANG‘WHERE EMP_ID=‘ VPA30890F‘

E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是效能差別是很大的。

五、注意暫存資料表和表變數的用

在複雜系統中，暫存資料表和表變數很難避免，關於暫存資料表和表變數的用法，需要注意：

A、如果語句很複雜，串連太多，可以考慮用暫存資料表和表變數分步完成。

B、如果需要多次用到一個大表的同一部分資料，考慮用暫存資料表和表變數暫存這部分資料。

C、如果需要綜合多個表的資料，形成一個結果，可以考慮用暫存資料表和表變數分步匯總這多個表的資料。

D、其他情況下，應該控制暫存資料表和表變數的使用。

E、關於暫存資料表和表變數的選擇，很多說法是表變數在記憶體，速度快，應該首選表變數，但是在實際使用中發現：

(1) 主要考慮需要放在暫存資料表的資料量，在資料量較多的情況下，暫存資料表的速度反而更快。

(2) 執行時間段與預計執行時間(多長)

F、關於暫存資料表產生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，一般情況下：

SELECT INTO會比CREATE TABLE + INSERT INTO的方法快很多，

但是SELECT INTO會鎖定TEMPDB的系統資料表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多使用者並發環境下，容易阻塞其他進程。

所以我的建議是，在並發系統中，盡量使用CREATE TABLE + INSERT INTO，而大資料量的單個語句使用中，使用SELECT INTO。

六、子查詢的用法

子查詢是一個 SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語句或其它子查詢中。

任何允許使用運算式的地方都可以使用子查詢，子查詢可以使我們的編程靈活多樣，可以用來實現一些特殊的功能。但是在效能上，往往一個不合適的子查詢用法會形成一個效能瓶頸。如果子查詢的條件中使用了其外層的表的欄位，這種子查詢就叫作相互關聯的子查詢。

相互關聯的子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入。 關於相互關聯的子查詢，應該注意：

(1) NOT IN、NOT EXISTS的相互關聯的子查詢可以改用LEFT JOIN代替寫法。比如：

SELECT PUB_NAME FROM PUBLISHERS WHERE PUB_ID NOTIN (SELECT PUB_ID FROM TITLES WHERE TYPE =‘BUSINESS‘)

可以改寫成：

SELECT A.PUB_NAME FROM PUBLISHERS A LEFTJOIN TITLES B ON B.TYPE =‘BUSINESS‘AND A.PUB_ID=B. PUB_ID WHERE B.PUB_ID IS NULL

比如NOT EXISTS：

SELECT TITLE FROM TITLESWHERE NOT EXISTS(SELECT TITLE_ID FROM SALES WHERE TITLE_ID = TITLES.TITLE_ID)

可以改寫成：

SELECT TITLEFROM TITLES LEFTJOIN SALESON SALES.TITLE_ID = TITLES.TITLE_IDWHERE SALES.TITLE_ID ISNULL

2）如果保證子查詢沒有重複 ，IN、EXISTS的相互關聯的子查詢可以用INNER JOIN 代替。比如：

SELECT PUB_NAMEFROM PUBLISHERSWHERE PUB_ID IN(SELECT PUB_IDFROM TITLESWHERE TYPE =‘BUSINESS‘)

可以改寫成：

SELECT A.PUB_NAME --SELECT DISTINCT A.PUB_NAMEFROM PUBLISHERS A INNERJOIN TITLES BON B.TYPE =‘BUSINESS‘ANDA.PUB_ID=B. PUB_ID

(3) IN的相互關聯的子查詢用EXISTS代替，比如：

SELECT PUB_NAME FROM PUBLISHERSWHERE PUB_ID IN(SELECT PUB_ID FROM TITLES WHERE TYPE =‘BUSINESS‘)

可以用下面語句代替：

SELECT PUB_NAME FROM PUBLISHERS WHERE EXISTS(SELECT1FROM TITLES WHERE TYPE =‘BUSINESS‘ANDPUB_ID= PUBLISHERS.PUB_ID)

4） 不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFT JOIN或者EXISTS，比如有人寫這樣的語句：

SELECT JOB_DESC FROM JOBSWHERE (SELECTCOUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0

應該改成：

SELECT JOBS.JOB_DESC FROM JOBS LEFTJOIN EMPLOYEEON EMPLOYEE.JOB_ID=JOBS.JOB_IDWHERE EMPLOYEE.EMP_ID ISNULLSELECT JOB_DESC FROM JOBSWHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

應該改成：

SELECT JOB_DESC FROM JOBSWHEREEXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

七：盡量使用索引

建立索引後，並不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強制指定索引，索引的選擇和使用方法是SQLSERVER的最佳化器自動作的選擇，而它選擇的根據是查詢語句的條件以及相關表的統計資訊，這就要求我們在寫SQL語句的時候盡量使得最佳化器可以使用索引。為了使得最佳化器能高效使用索引，寫語句的時候應該注意：

（1）不要對索引欄位進行運算，而要想辦法做變換，比如：

SELECT ID FROM T WHERE NUM/2=100

應改為:

SELECT ID FROM T WHERE NUM=100*2

SELECT ID FROM T WHERE NUM/2=NUM1

如果NUM有索引應改為:

SELECT ID FROM T WHERE NUM=NUM1*2

如果NUM1有索引則不應該改。

（2）發現過這樣的語句：

SELECT 年,月,金額 FROM 結餘表 WHERE100*年+月=2010*100+10

應該改為：

SELECT 年,月,金額 FROM 結餘表 WHERE 年=2010 AND 月=10

（3）不要對索引欄位進行格式轉換

日期欄位的例子：

WHERE CONVERT(VARCHAR(10), 日期欄位,120)=‘2010-07-15‘

應該改為

WHERE 日期欄位〉=‘2010-07-15‘AND 日期欄位‘2010-07-16‘

ISNULL轉換的例子：

WHERE ISNULL(欄位,”)”應改為:WHERE欄位”

WHERE ISNULL(欄位,”)=”不應修改

WHERE ISNULL(欄位,’F’) =’T’應改為: WHERE欄位=’T’

WHERE ISNULL(欄位,’F’)’T’不應修改

（4） 不要對索引欄位進行格式轉換

WHERE LEFT(NAME, 3)=‘ABC‘ 或者WHERE SUBSTRING(NAME,1, 3)=‘ABC‘

應改為: WHERE NAME LIKE’ABC%’

日期查詢的例子：

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期=‘2010-06-30‘ AND 日期 ‘2010-07-01‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期 ‘2010-06-30‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期 ‘2010-07-01‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期=‘2010-07-01‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期=‘2010-06-30‘

（5）不要對索引欄位使用函數

WHERE LEFT(NAME, 3)=’ABC’ 或者WHERE SUBSTRING(NAME,1, 3)=’ABC’

應改為:

WHERE NAME LIKE ‘ABC%‘

日期查詢的例子：

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期=‘2010-06-30‘AND 日期 ‘2010-07-01‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期 ‘2010-06-30‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期 ‘2010-07-01‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期=‘2010-07-01‘

WHERE DATEDIFF(DAY, 日期,‘2010-06-30‘)=0

應改為:

WHERE 日期=‘2010-06-30‘

（6）不要對索引欄位進行多欄位串連

比如：

WHERE FAME+‘. ‘+LNAME=‘HAIWEI.YANG‘

應改為:

WHERE FNAME=‘HAIWEI‘ AND LNAME=‘YANG‘

八：多表串連的串連條件對索引的選擇有著重要的意義，所以我們在寫串連條件條件的時候需要特別注意。

A、多表串連的時候，串連條件必須寫全，寧可重複，不要缺漏。

B、串連條件盡量使用叢集索引

C、注意ON、WHERE和HAVING部分條件的區別

ON是最先執行， WHERE次之，HAVING最後，因為ON是先把不合格記錄過濾後才進行統計，它就可以減少中間運算要處理的資料，按理說應該速度是最快的，WHERE也應該比HAVING快點的，因為它過濾資料後才進行SUM，在兩個表聯結時才用ON的，所以在一個表的時候，就剩下WHERE跟HAVING比較了。

(1) INNER JOIN

(2) LEFT JOIN (註：RIGHT JOIN 用 LEFT JOIN 替代)

(3) CROSS JOIN

其它注意和瞭解的地方有：

A、在IN後面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數。

B、注意UNION和UNION ALL的區別。– 允許重複資料用UNION ALL好

C、注意使用DISTINCT，在沒有必要時不要用。

D、TRUNCATE TABLE 與 DELETE 區別。

E、減少訪問資料庫的次數。

還有就是我們寫預存程序，如果比較長的話，最後用標記符標開，因為這樣可讀性很好，即使語句寫的不怎麼樣，但是語句工整，C# 有region，SQL我比較喜歡用的就是：

–startof 查詢在職人數

SQL語句

–end of

正式機器上我們一般不能隨便偵錯工具，但是很多時候程式在我們本機上沒問題，但是進正式系統就有問題，但是我們又不能隨便在正式機器上操作，那麼怎麼辦呢？我們可以用復原來調試我們的預存程序或者是SQL語句，從而排錯。

BEGINTRAN

UPDATE a SET 欄位=”

ROLLBACK

作業預存程序我一般會加上下面這段，這樣檢查錯誤可以放在預存程序，如果執行錯誤復原操作，但是如果程式裡面已經有了交易回復，那麼預存程序就不要寫事務了，這樣會導致交易回復嵌套降低執行效率，但是我們很多時候可以把檢查放在預存程序裡，這樣有利於我們解讀這個預存程序，和排錯。

BEGINTRANSACTION

–交易回復開始

–檢查報錯

IF ( @@ERROR0 )BEGIN--復原操作ROLLBACKTRANSACTIONRAISERROR(‘刪除工作報告錯誤‘, 16, 3)RETURNEND

–結束事務

COMMITTRANSACTION

SQL Server最佳化之SQL語句最佳化