轉 SQL語言邏輯執行順序

查詢的邏輯執行順序

FROM < left_table>

ON < join_condition>

< join_type> JOIN < right_table>

WHERE < where_condition>

GROUP BY < group_by_list>

WITH {cube | rollup}

HAVING < having_condition>

SELECT

DISTINCT

ORDER BY < order_by_list>

< top_specification> < select_list>

标準的SQL 的解析順序為:

.FROM 子句 組裝來自不同資料源的資料

.WHERE 子句 基于指定的條件對記錄進行篩選

.GROUP BY 子句 将資料劃分為多個分組

.使用聚合函數進行計算

.使用HAVING子句篩選分組

.計算所有的表達式

.使用ORDER BY對結果集進行排序

執行順序

FROM：對FROM子句中前兩個表執行笛卡爾積生成虛拟表vt1

ON:對vt1表應用ON篩選器隻有滿足< join_condition> 為真的行才被插入vt2

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

WHERE：對vt3應用 WHERE 篩選器隻有使< where_condition> 為true的行才被插入vt4

GROUP BY：按GROUP BY子句中的列清單對vt4中的行分組生成vt5

CUBE|ROLLUP：把超組(supergroups)插入vt6 生成vt6

HAVING：對vt6應用HAVING篩選器隻有使< having_condition> 為true的組才插入vt7

SELECT：處理select清單産生vt8

DISTINCT：将重複的行從vt8中去除産生vt9

ORDER BY：将vt9的行按order by子句中的列清單排序生成一個遊标vc10

TOP：從vc10的開始處選擇指定數量或比例的行生成vt11 并傳回調用者

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

隻傳回需要的資料

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

1. 橫向來看：

不要寫SELECT *的語句，而是選擇你需要的字段。

當在SQL語句中連接配接多個表時, 請使用表的别名并把别名字首于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的文法錯誤。

如有表table1（ID,col1）和table2 （ID,col2）

1

Select A.ID, A.col1, B.col2

2

 -- Select A.ID, col1, col2 –不要這麼寫，不利于将來程式擴充

3

 from table1 A inner join table2 B on A.ID=B.ID Where …

2. 縱向來看：

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

SELECT TOP N * --沒有WHERE條件的用此替代

盡量少做重複的工作

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

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

杜絕不必要的子查詢和連接配接表，子查詢在執行計劃一般解釋成外連接配接，多餘的連接配接表帶來額外的開銷。

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

UPDATE EMPLOYEE SET FNAME='HAIWER'

WHERE EMP_ID=' VPA30890F' UPDATE EMPLOYEE SET LNAME='YANG'

WHERE EMP_ID=' VPA30890F'

4

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

5

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

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

注意臨時表和表變量的用法

在複雜系統中，臨時表和表變量很難避免，關于臨時表和表變量的用法，需要注意：

如果語句很複雜，連接配接太多，可以考慮用臨時表和表變量分步完成。

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

如果需要綜合多個表的資料，形成一個結果，可以考慮用臨時表和表變量分步彙總這多個表的資料。

其他情況下，應該控制臨時表和表變量的使用。

關于臨時表和表變量的選擇，很多說法是表變量在記憶體，速度快，應該首選表變量，但是在實際使用中發現，主要考慮需要放在臨時表的資料量，在資料量較多的情況下，臨時表的速度反而更快。執行時間段與預計執行時間(多長)。

關于臨時表産生使用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 NOT IN (SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS')

可以改寫成：

SELECT A.PUB_NAME FROM PUBLISHERS A LEFT JOIN TITLES B ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_ID WHERE B.PUB_ID IS NULL

又比如：

SELECT TITLE FROM TITLES

WHERE NOT EXISTS

 (SELECT TITLE_ID FROM SALES

WHERE TITLE_ID = TITLES.TITLE_ID)

SELECT TITLE

FROM TITLES LEFT JOIN SALES

ON SALES.TITLE_ID = TITLES.TITLE_ID

WHERE SALES.TITLE_ID IS NULL

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

SELECT PUB_NAME

FROM PUBLISHERS

WHERE PUB_ID IN

 (SELECT PUB_ID

 FROM TITLES

6

 WHERE TYPE = 'BUSINESS')

SELECT A.PUB_NAME --SELECT DISTINCT A.PUB_NAME

FROM PUBLISHERS A INNER JOIN TITLES B

ON        B.TYPE = 'BUSINESS' AND

A.PUB_ID=B. PUB_ID

3. IN的相關子查詢用EXISTS代替，比如

SELECT PUB_NAME FROM PUBLISHERS

(SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS')

可以用下面語句代替：

SELECT PUB_NAME FROM PUBLISHERS WHERE EXISTS

(SELECT 1 FROM TITLES WHERE TYPE = 'BUSINESS' AND

PUB_ID= PUBLISHERS.PUB_ID)

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

SELECT JOB_DESC FROM JOBS

WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0

應該寫成：

SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE 

ON EMPLOYEE.JOB_ID=JOBS.JOB_ID

WHERE EMPLOYEE.EMP_ID IS NULL

還有

WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0

WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

盡量使用索引

建立索引後，并不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差别。隻要我們在查詢語句中沒有強制指定索引，索引的選擇和使用方法是SQLSERVER的優化器自動作的選擇，而它選擇的根據是查詢語句的條件以及相關表的統計資訊，這就要求我們在寫SQL。

語句的時候盡量使得優化器可以使用索引。為了使得優化器能高效使用索引，寫語句的時候應該注意：

A、不要對索引字段進行運算，而要想辦法做變換，比如

    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

07

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

08

發現過這樣的語句：

09

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

10

    應該改為：

11

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

B、 不要對索引字段進行格式轉換

日期字段的例子：

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'不應修改

C、 不要對索引字段使用函數

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'

12

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

13

應改為:WHERE 日期>='2010-06-30'

4. 不要對索引字段進行多字段連接配接

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

應改為:

WHERE FNAME='HAIWEI' AND LNAME='YANG'

多表連接配接的連接配接條件

多表連接配接的連接配接條件對索引的選擇有着重要的意義，是以我們在寫連接配接條件條件的時候需要特别注意。

多表連接配接的時候，連接配接條件必須寫全，甯可重複，不要缺漏。

連接配接條件盡量使用聚集索引

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

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

考慮聯接優先順序：

INNER JOIN

LEFT JOIN (注：RIGHT JOIN 用 LEFT JOIN 替代)

CROSS JOIN

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

在IN後面值的清單中，将出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數

注意UNION和UNION ALL的差別。--允許重複資料用UNION ALL好

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

TRUNCATE TABLE 與 DELETE 差別

減少通路資料庫的次數

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

--startof  查詢在職人數

     sql語句

  --end of

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

BEGIN TRAN

 UPDATE a SET 字段=''

ROLLBACK

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

view sourceprint?

BEGIN TRANSACTION  

--事務復原開始      

--檢查報錯

 IF ( @@ERROR > 0 )    

                    BEGIN       

--復原操作

                        ROLLBACK TRANSACTION       

                        RAISERROR('删除工作報告錯誤', 16, 3)       

                        RETURN         

                    END         

--結束事務

  COMMIT TRANSACTION  

·