優(yōu)化必讀-基礎(chǔ)-sql server 語句執(zhí)行順序

1、要知道SQL語句，我想我們有必要知道SQL Server查詢分析器怎么執(zhí)行我們的SQL語句的，我們很多人會看執(zhí)行計劃，或者用Profiler來監(jiān)視和調(diào)優(yōu)查詢語句或者存儲過程慢的原因一、查詢的邏輯執(zhí)行順序(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)

2、 top_specification select_list(9) ORDER BY order_by_list標(biāo)準(zhǔn)的 SQL 的解析順序為:(1) FROM 子句 組裝來自不同數(shù)據(jù)源的數(shù)據(jù)(2) WHERE 子句 基于指定的條件對記錄進行篩選(3) GROUP BY 子句 將數(shù)據(jù)劃分為多個分組(4) 使用聚合函數(shù)進行計算(5) 使用HAVING子句篩選分組(6) 計算所有的表達(dá)式(7) 使用ORDER BY對結(jié)果集進行排序二、執(zhí)行順序1. FROM：對FROM子句中前兩個表執(zhí)行笛卡爾積生成虛擬表vt12. ON: 對vt1表應(yīng)用ON篩選器只有滿足 join_condition 為真的行才被插

3、入vt23. OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行將行作為外部行添加到vt2，生成t3，如果from包含兩個以上表，則對上一個聯(lián)結(jié)生成的結(jié)果表和下一個表重復(fù)執(zhí)行步驟和步驟直接結(jié)束。4. WHERE：對vt3應(yīng)用 WHERE 篩選器只有使 where_condition 為true的行才被插入vt45. GROUP BY：按GROUP BY子句中的列列表對vt4中的行分組生成vt56. CUBE|ROLLUP：把超組(supergroups)插入vt6，生成vt67. HAVING：對vt6應(yīng)用HAVING篩選器只有使 ha

4、ving_condition 為true的組才插入vt78. SELECT：處理select列表產(chǎn)生vt89. DISTINCT：將重復(fù)的行從vt8中去除產(chǎn)生vt910. ORDER BY：將vt9的行按order by子句中的列列表排序生成一個游標(biāo)vc1011. TOP：從vc10的開始處選擇指定數(shù)量或比例的行生成vt11 并返回調(diào)用者看到這里，那么用過Linq to SQL的語法有點相似啊？如果我們我們了解了SQL Server執(zhí)行順序，那么我們就接下來進一步養(yǎng)成日常SQL的好習(xí)慣，也就是在實現(xiàn)功能的同時有考慮性能的思想，數(shù)據(jù)庫是能進行集合運算的工具，我們應(yīng)該盡量的利用這個工具，所謂集合運

5、算實際就是批量運算，就是盡量減少在客戶端進行大數(shù)據(jù)量的循環(huán)操作，而用SQL語句或者存儲過程代替三、只返回需要的數(shù)據(jù)返回數(shù)據(jù)到客戶端至少需要數(shù)據(jù)庫提取數(shù)據(jù)、網(wǎng)絡(luò)傳輸數(shù)據(jù)、客戶端接收數(shù)據(jù)以及客戶端處理數(shù)據(jù)等環(huán)節(jié)，如果返回不需要的數(shù)據(jù)，就會增加服務(wù)器、網(wǎng)絡(luò)和客戶端的無效勞動，其害處是顯而易見的，避免這類事件需要注意：A、橫向來看(1) 不要寫SELECT * 的語句，而是選擇你需要的字段。(2) 當(dāng)在SQL語句中連接多個表時, 請使用表的別名并把別名前綴于每個Column上。這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。?1如有表table1(ID,col1)和table

6、2(ID,col2)?123Select 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條件的用此替代。四、盡量少做重復(fù)的工作A、控制同一語句的多次執(zhí)行，特別是一些基礎(chǔ)數(shù)據(jù)的多次執(zhí)行是很多程序員很少注意的。B、減少多次的數(shù)據(jù)轉(zhuǎn)換，也許需要數(shù)據(jù)轉(zhuǎn)換是設(shè)計的問題，但是減少次數(shù)

7、是程序員可以做到的。C、杜絕不必要的子查詢和連接表，子查詢在執(zhí)行計劃一般解釋成外連接，多余的連接表帶來額外的開銷。D、合并對同一表同一條件的多次UPDATE，比如UPDATE EMPLOYEE SET FNAME='HAIWER' WHERE EMP_ID=' VPA30890F'UPDATE EMPLOYEE SET LNAME='YANG' WHERE EMP_ID=' VPA30890F' 這兩個語句應(yīng)該合并成以下一個語句UPDATE EMPLOYEE SET FNAME='HAIWER',LNAME=

8、9;YANG'WHERE EMP_ID=' VPA30890F'E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是性能差別是很大的。五、注意臨時表和表變量的用 在復(fù)雜系統(tǒng)中，臨時表和表變量很難避免，關(guān)于臨時表和表變量的用法，需要注意：A、如果語句很復(fù)雜，連接太多，可以考慮用臨時表和表變量分步完成。B、如果需要多次用到一個大表的同一部分?jǐn)?shù)據(jù)，考慮用臨時表和表變量暫存這部分?jǐn)?shù)據(jù)。C、如果需要綜合多個表的數(shù)據(jù)，形成一個結(jié)果，可以考慮用臨時表和表變量分步匯總這多個表的數(shù)據(jù)。D、其他情況下，應(yīng)該控制臨時表和表變量的使用。E、關(guān)于臨時表和表變量

9、的選擇，很多說法是表變量在內(nèi)存，速度快，應(yīng)該首選表變量，但是在實際使用中發(fā)現(xiàn)：(1) 主要考慮需要放在臨時表的數(shù)據(jù)量，在數(shù)據(jù)量較多的情況下，臨時表的速度反而更快。(2) 執(zhí)行時間段與預(yù)計執(zhí)行時間(多長)F、關(guān)于臨時表產(chǎn)生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，一般情況下：SELECT INTO會比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO會鎖定TEMPDB的系統(tǒng)表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用戶并發(fā)環(huán)境下，容易阻塞其他進程。所以我的建議是，在并發(fā)系統(tǒng)中，盡

10、量使用CREATE TABLE + INSERT INTO，而大數(shù)據(jù)量的單個語句使用中，使用SELECT INTO。六、子查詢的用法子查詢是一個 SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語句或其它子查詢中。任何允許使用表達(dá)式的地方都可以使用子查詢，子查詢可以使我們的編程靈活多樣，可以用來實現(xiàn)一些特殊的功能。但是在性能上，往往一個不合適的子查詢用法會形成一個性能瓶頸。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關(guān)子查詢。相關(guān)子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入。 關(guān)于相關(guān)子查詢，應(yīng)該注意：(1) NO

11、T IN、NOT EXISTS的相關(guān)子查詢可以改用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：SE

12、LECT TITLE FROM TITLES WHERE NOT EXISTS (SELECT TITLE_ID FROM SALES WHERE TITLE_ID = TITLES.TITLE_ID)?1可以改寫成：SELECT TITLE FROM TITLES LEFTJOIN SALES ON SALES.TITLE_ID = TITLES.TITLE_ID WHERE SALES.TITLE_ID ISNULL2）如果保證子查詢沒有重復(fù) ，IN、EXISTS的相關(guān)子查詢可以用INNER JOIN 代替。比如：SELECT PUB_NAME FROM PUBLISHERS WHERE

13、PUB_ID IN (SELECT PUB_ID FROM TITLES WHERE TYPE ='BUSINESS')?1可以改寫成：SELECT A.PUB_NAME -SELECT DISTINCT A.PUB_NAME FROM PUBLISHERS A INNERJOIN TITLES B ON B.TYPE ='BUSINESS'AND A.PUB_ID=B. PUB_ID(3) IN的相關(guān)子查詢用EXISTS代替，比如：SELECT PUB_NAME FROM PUBLISHERSWHERE PUB_ID IN(SELECT PUB_ID FROM

14、 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 JO

15、B_ID=JOBS.JOB_ID)=0應(yīng)該改成：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)0應(yīng)該改成：SELECT JOB_DESC FROM JOBSWHEREEXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOB

16、S.JOB_ID) 七：盡量使用索引建立索引后，并不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強制指定索引，索引的選擇和使用方法是SQLSERVER的優(yōu)化器自動作的選擇，而它選擇的根據(jù)是查詢語句的條件以及相關(guān)表的統(tǒng)計信息，這就要求我們在寫SQL語句的時候盡量使得優(yōu)化器可以使用索引。為了使得優(yōu)化器能高效使用索引，寫語句的時候應(yīng)該注意：（1）不要對索引字段進行運算，而要想辦法做變換，比如：SELECT ID FROM T WHERE NUM/2=100應(yīng)改為:SELECT ID FROM T WHERE NUM=100*2SELECT ID

17、 FROM T WHERE NUM/2=NUM1如果NUM有索引應(yīng)改為:SELECT ID FROM T WHERE NUM=NUM1*2如果NUM1有索引則不應(yīng)該改。（2）發(fā)現(xiàn)過這樣的語句：SELECT 年,月,金額 FROM 結(jié)余表 WHERE100*年+月=2010*100+10?1應(yīng)該改為：SELECT 年,月,金額 FROM 結(jié)余表 WHERE 年=2010 AND 月=10（3）不要對索引字段進行格式轉(zhuǎn)換日期字段的例子：WHERECONVERT(VARCHAR(10), 日期字段,120)='2010-07-15'應(yīng)該改為WHERE日期字段='2010-07

18、-15'AND 日期字段'2010-07-16'ISNULL轉(zhuǎn)換的例子：WHEREISNULL(字段,'')''應(yīng)改為:WHERE字段''WHEREISNULL(字段,'')=''不應(yīng)修改WHEREISNULL(字段,'F') ='T'應(yīng)改為: WHERE字段='T'WHEREISNULL(字段,'F')'T'不應(yīng)修改（4） 不要對索引字段進行格式轉(zhuǎn)換WHERELEFT(NAME, 3)='ABC'

19、; 或者WHERE SUBSTRING(NAME,1, 3)='ABC'應(yīng)改為: WHERE NAME LIKE'ABC%'日期查詢的例子：WHEREDATEDIFF(DAY, 日期,'2010-06-30')=0應(yīng)改為:WHERE 日期='2010-06-30' AND 日期 '2010-07-01'WHEREDATEDIFF(DAY, 日期,'2010-06-30')0應(yīng)改為:WHERE 日期 '2010-06-30'WHEREDATEDIFF(DAY, 日期,'2010

20、-06-30')=0應(yīng)改為:WHERE 日期 '2010-07-01'WHEREDATEDIFF(DAY, 日期,'2010-06-30')0應(yīng)改為:WHERE 日期='2010-07-01'WHEREDATEDIFF(DAY, 日期,'2010-06-30')=0應(yīng)改為:WHERE 日期='2010-06-30'（5）不要對索引字段使用函數(shù)WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'應(yīng)改為: WHE

21、RE NAME LIKE 'ABC%'日期查詢的例子：WHEREDATEDIFF(DAY, 日期,'2010-06-30')=0應(yīng)改為:WHERE 日期='2010-06-30'AND 日期 '2010-07-01'WHEREDATEDIFF(DAY, 日期,'2010-06-30')0應(yīng)改為:WHERE 日期 '2010-06-30'WHEREDATEDIFF(DAY, 日期,'2010-06-30')=0應(yīng)改為:WHERE 日期 '2010-07-01'WHERE

22、DATEDIFF(DAY, 日期,'2010-06-30')0應(yīng)改為:WHERE 日期='2010-07-01'WHEREDATEDIFF(DAY, 日期,'2010-06-30')=0應(yīng)改為:WHERE 日期='2010-06-30'（6）不要對索引字段進行多字段連接比如：WHERE FAME+'. '+LNAME='HAIWEI.YANG'應(yīng)改為:WHERE FNAME='HAIWEI' AND LNAME='YANG'八：多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的時候需要特別注意。A、多表連接的時候，連接條件必須寫全，寧可重復(fù)，不要缺漏。B、連接條件盡量使用聚集索引C、注意ON、WHERE和HAVING部分條件的區(qū)別ON是最先執(zhí)行， WHERE次之，HAVING最后，因為ON是先把不符合條件的記錄過濾后才進行統(tǒng)計，它就可以減少中間運算要處理的數(shù)據(jù)，按理說應(yīng)該速度是最快的，WHERE也應(yīng)該比HAVING快點的，因為它過濾數(shù)據(jù)后才進行SUM，在兩個表聯(lián)接時才用ON的，所以在一個表的時候，就剩下WHERE跟HAVING比較了。?1234考慮聯(lián)接優(yōu)先順序：(1) INNE