SQL養成一個好習慣是一筆財富

我們做軟件開發的，大部分人都離不開跟數據庫打交道，特別是erp開發的，跟數據庫打交道更是頻繁，存儲過程動不動就是上千行，如果數據量大，人員流動大，那么我么還能保證下一段時間系統還能流暢的運行嗎？我么還能保證下一個人能看懂我么的存儲過程嗎？那么我結合公司平時的培訓和平時個人工作經驗和大家分享一下，希望對大家有幫助。

要知道SQL語句，我想我們有必要知道SQL Server查詢分析器怎么執行我們的SQL語句的，我們很多人會看執行計劃，或者用Profiler來監視和調優查詢語句或者存儲過程慢的原因，但是如果我們知道查詢分析器的執行邏輯順序，下手的時候就胸有成竹，那么下手是不是有把握點呢？

一、查詢的邏輯執行順序

(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 …  

(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

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