原文地址：
http://www.nowamagic.net/librarys/veda/detail/1502
我們做軟件開發(fā)的，大部分人都離不開跟數(shù)據(jù)庫打交道蒸播，特別是erp開發(fā)的毫炉，跟數(shù)據(jù)庫打交道更是頻繁，存儲(chǔ)過程動(dòng)不動(dòng)就是上千行果港，如果數(shù)據(jù)量大沦泌，人員流動(dòng)大，那么我們還能保證下一段時(shí)間系統(tǒng)還能流暢的運(yùn)行嗎辛掠？我們還能保證下一個(gè)人能看懂我們的存儲(chǔ)過程嗎谢谦？
要知道sql語句，我想我們有必要知道sqlserver查詢分析器怎么執(zhí)行我么sql語句的萝衩，我么很多人會(huì)看執(zhí)行計(jì)劃回挽，或者用profile來監(jiān)視和調(diào)優(yōu)查詢語句或者存儲(chǔ)過程慢的原因，但是如果我們知道查詢分析器的執(zhí)行邏輯順序猩谊，下手的時(shí)候就胸有成竹千劈，那么下手是不是有把握點(diǎn)呢？
查詢的邏輯執(zhí)行順序
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>
標(biāo)準(zhǔn)的SQL 的解析順序?yàn)?
.FROM 子句 組裝來自不同數(shù)據(jù)源的數(shù)據(jù)
.WHERE 子句 基于指定的條件對記錄進(jìn)行篩選
.GROUP BY 子句 將數(shù)據(jù)劃分為多個(gè)分組
.使用聚合函數(shù)進(jìn)行計(jì)算
.使用HAVING子句篩選分組
.計(jì)算所有的表達(dá)式
.使用ORDER BY對結(jié)果集進(jìn)行排序
執(zhí)行順序
FROM：對FROM子句中前兩個(gè)表執(zhí)行笛卡爾積生成虛擬表vt1
ON:對vt1表應(yīng)用ON篩選器只有滿足< join_condition> 為真的行才被插入vt2
OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行將行作為外部行添加到vt2 生成t3如果from包含兩個(gè)以上表則對上一個(gè)聯(lián)結(jié)生成的結(jié)果表和下一個(gè)表重復(fù)執(zhí)行步驟和步驟直接結(jié)束
WHERE：對vt3應(yīng)用 WHERE 篩選器只有使< where_condition> 為true的行才被插入vt4
GROUP BY：按GROUP BY子句中的列列表對vt4中的行分組生成vt5
CUBE|ROLLUP：把超組(supergroups)插入vt6 生成vt6
HAVING：對vt6應(yīng)用HAVING篩選器只有使< having_condition> 為true的組才插入vt7
SELECT：處理select列表產(chǎn)生vt8
DISTINCT：將重復(fù)的行從vt8中去除產(chǎn)生vt9
ORDER BY：將vt9的行按order by子句中的列列表排序生成一個(gè)游標(biāo)vc10
TOP：從vc10的開始處選擇指定數(shù)量或比例的行生成vt11 并返回調(diào)用者
看到這里牌捷，那么用過linqtosql的語法有點(diǎn)相似扒脚啤？如果我們我們了解了sqlserver執(zhí)行順序暗甥，那么我們就接下來進(jìn)一步養(yǎng)成日常sql好習(xí)慣喜滨，也就是在實(shí)現(xiàn)功能同時(shí)有考慮性能的思想，數(shù)據(jù)庫是能進(jìn)行集合運(yùn)算的工具撤防，我們應(yīng)該盡量的利用這個(gè)工具虽风，所謂集合運(yùn)算實(shí)際就是批量運(yùn)算，就是盡量減少在客戶端進(jìn)行大數(shù)據(jù)量的循環(huán)操作，而用SQL語句或者存儲(chǔ)過程代替辜膝。
只返回需要的數(shù)據(jù)
返回?cái)?shù)據(jù)到客戶端至少需要數(shù)據(jù)庫提取數(shù)據(jù)陌凳、網(wǎng)絡(luò)傳輸數(shù)據(jù)、客戶端接收數(shù)據(jù)以及客戶端處理數(shù)據(jù)等環(huán)節(jié)内舟，如果返回不需要的數(shù)據(jù)合敦，就會(huì)增加服務(wù)器、網(wǎng)絡(luò)和客戶端的無效勞動(dòng)验游，其害處是顯而易見的充岛，避免這類事件需要注意：
- 橫向來看：
不要寫SELECT *的語句，而是選擇你需要的字段耕蝉。
當(dāng)在SQL語句中連接多個(gè)表時(shí), 請使用表的別名并把別名前綴于每個(gè)Column上.這樣一來,就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤崔梗。
如有表table1（ID,col1）和table2 （ID,col2）
Select A.ID, A.col1, B.col2
-- Select A.ID, col1, col2 –不要這么寫，不利于將來程序擴(kuò)展
from table1 A inner join table2 B on A.ID=B.ID Where …
- 縱向來看：
合理寫WHERE子句垒在，不要寫沒有WHERE的SQL語句蒜魄。
SELECT TOP N * --沒有WHERE條件的用此替代
盡量少做重復(fù)的工作
控制同一語句的多次執(zhí)行，特別是一些基礎(chǔ)數(shù)據(jù)的多次執(zhí)行是很多程序員很少注意的场躯。
減少多次的數(shù)據(jù)轉(zhuǎn)換谈为，也許需要數(shù)據(jù)轉(zhuǎn)換是設(shè)計(jì)的問題，但是減少次數(shù)是程序員可以做到的踢关。
杜絕不必要的子查詢和連接表伞鲫，子查詢在執(zhí)行計(jì)劃一般解釋成外連接，多余的連接表帶來額外的開銷签舞。
合并對同一表同一條件的多次UPDATE秕脓，比如：
UPDATE EMPLOYEE SET FNAME='HAIWER'
WHERE EMP_ID=' VPA30890F' UPDATE EMPLOYEE SET LNAME='YANG'
WHERE EMP_ID=' VPA30890F'
這兩個(gè)語句應(yīng)該合并成以下一個(gè)語句
UPDATE EMPLOYEE SET FNAME='HAIWER',LNAME='YANG' WHERE EMP_ID=' VPA30890F'
UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同儒搭，但是性能差別是很大的吠架。
注意臨時(shí)表和表變量的用法
在復(fù)雜系統(tǒng)中，臨時(shí)表和表變量很難避免搂鲫，關(guān)于臨時(shí)表和表變量的用法傍药，需要注意：
如果語句很復(fù)雜，連接太多默穴，可以考慮用臨時(shí)表和表變量分步完成怔檩。
如果需要多次用到一個(gè)大表的同一部分?jǐn)?shù)據(jù)，考慮用臨時(shí)表和表變量暫存這部分?jǐn)?shù)據(jù)蓄诽。
如果需要綜合多個(gè)表的數(shù)據(jù)薛训，形成一個(gè)結(jié)果，可以考慮用臨時(shí)表和表變量分步匯總這多個(gè)表的數(shù)據(jù)仑氛。
其他情況下乙埃，應(yīng)該控制臨時(shí)表和表變量的使用闸英。
關(guān)于臨時(shí)表和表變量的選擇，很多說法是表變量在內(nèi)存介袜，速度快甫何，應(yīng)該首選表變量，但是在實(shí)際使用中發(fā)現(xiàn)遇伞，主要考慮需要放在臨時(shí)表的數(shù)據(jù)量辙喂，在數(shù)據(jù)量較多的情況下，臨時(shí)表的速度反而更快鸠珠。執(zhí)行時(shí)間段與預(yù)計(jì)執(zhí)行時(shí)間(多長)巍耗。
關(guān)于臨時(shí)表產(chǎn)生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，一般情況下渐排，SELECT INTO會(huì)比CREATE TABLE + INSERT INTO的方法快很多炬太，但是SELECT INTO會(huì)鎖定TEMPDB的系統(tǒng)表SYSOBJECTS、SYSINDEXES驯耻、SYSCOLUMNS亲族，在多用戶并發(fā)環(huán)境下，容易阻塞其他進(jìn)程可缚，所以我的建議是霎迫，在并發(fā)系統(tǒng)中，盡量使用CREATE TABLE + INSERT INTO城看，而大數(shù)據(jù)量的單個(gè)語句使用中女气，使用SELECT INTO。
子查詢的用法
子查詢是一個(gè) SELECT 查詢测柠，它嵌套在 SELECT、INSERT缘滥、UPDATE轰胁、DELETE 語句或其它子查詢中。任何允許使用表達(dá)式的地方都可以使用子查詢朝扼，子查詢可以使我們的編程靈活多樣赃阀，可以用來實(shí)現(xiàn)一些特殊的功能。但是在性能上擎颖，往往一個(gè)不合適的子查詢用法會(huì)形成一個(gè)性能瓶頸榛斯。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關(guān)子查詢搂捧。相關(guān)子查詢可以用IN驮俗、NOT IN、EXISTS允跑、NOT EXISTS引入王凑。 關(guān)于相關(guān)子查詢搪柑，應(yīng)該注意：
- NOT IN、NOT EXISTS的相關(guān)子查詢可以改用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
- 如果保證子查詢沒有重復(fù) 工碾，IN、EXISTS的相關(guān)子查詢可以用INNER JOIN 代替百姓。比如：
SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
(SELECT PUB_ID
FROM TITLES
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
- IN的相關(guān)子查詢用EXISTS代替渊额，比如
SELECT PUB_NAME FROM PUBLISHERS
WHERE PUB_ID IN
(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)
- 不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFT JOIN或者EXISTS垒拢，比如有人寫這樣的語句：
SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0
應(yīng)該寫成：
SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID
WHERE EMPLOYEE.EMP_ID IS NULL
還有
SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0
應(yīng)該寫成：
SELECT JOB_DESC FROM JOBS
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)
盡量使用索引
建立索引后旬迹，并不是每個(gè)查詢都會(huì)使用索引，在使用索引的情況下子库，索引的使用效率也會(huì)有很大的差別舱权。只要我們在查詢語句中沒有強(qiáng)制指定索引，索引的選擇和使用方法是SQLSERVER的優(yōu)化器自動(dòng)作的選擇仑嗅，而它選擇的根據(jù)是查詢語句的條件以及相關(guān)表的統(tǒng)計(jì)信息宴倍，這就要求我們在寫SQL。
語句的時(shí)候盡量使得優(yōu)化器可以使用索引仓技。為了使得優(yōu)化器能高效使用索引鸵贬，寫語句的時(shí)候應(yīng)該注意：
A、不要對索引字段進(jìn)行運(yùn)算脖捻，而要想辦法做變換阔逼，比如
SELECT ID FROM T WHERE NUM/2=100
應(yīng)改為:
SELECT ID FROM T WHERE NUM=100*2
SELECT ID FROM T WHERE NUM/2=NUM1
如果NUM有索引應(yīng)改為:
SELECT ID FROM T WHERE NUM=NUM1*2
如果NUM1有索引則不應(yīng)該改。
發(fā)現(xiàn)過這樣的語句：
SELECT 年,月,金額 FROM 結(jié)余表 WHERE 100年+月=2010100+10
應(yīng)該改為：
SELECT 年,月,金額 FROM 結(jié)余表 WHERE 年=2010 AND月=10
B地沮、 不要對索引字段進(jìn)行格式轉(zhuǎn)換
日期字段的例子：
WHERE CONVERT(VARCHAR(10), 日期字段,120)='2010-07-15'
應(yīng)該改為
WHERE日期字段〉='2010-07-15' AND 日期字段<'2010-07-16'
ISNULL轉(zhuǎn)換的例子：
WHERE ISNULL(字段,'')<>''應(yīng)改為:WHERE字段<>''
WHERE ISNULL(字段,'')=''不應(yīng)修改
WHERE ISNULL(字段,'F') ='T'應(yīng)改為: WHERE字段='T'
WHERE ISNULL(字段,'F')<>'T'不應(yīng)修改
C嗜浮、 不要對索引字段使用函數(shù)
WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'
應(yīng)改為: WHERE NAME LIKE 'ABC%'
日期查詢的例子：
WHERE DATEDIFF(DAY, 日期,'2010-06-30')=0
應(yīng)改為:WHERE 日期>='2010-06-30' AND 日期 <'2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')>0
應(yīng)改為:WHERE 日期 <'2010-06-30'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')>=0
應(yīng)改為:WHERE 日期 <'2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')<0
應(yīng)改為:WHERE 日期>='2010-07-01'
WHERE DATEDIFF(DAY, 日期,'2010-06-30')<=0
應(yīng)改為:WHERE 日期>='2010-06-30'
- 不要對索引字段進(jìn)行多字段連接
比如：
WHERE FAME+ '. '+LNAME='HAIWEI.YANG'
應(yīng)改為:
WHERE FNAME='HAIWEI' AND LNAME='YANG'
多表連接的連接條件
多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的時(shí)候需要特別注意摩疑。
多表連接的時(shí)候危融，連接條件必須寫全，寧可重復(fù)雷袋，不要缺漏吉殃。
連接條件盡量使用聚集索引
注意ON、WHERE和HAVING部分條件的區(qū)別
ON是最先執(zhí)行楷怒， WHERE次之蛋勺，HAVING最后，因?yàn)镺N是先把不符合條件的記錄過濾后才進(jìn)行統(tǒng)計(jì)鸠删，它就可以減少中間運(yùn)算要處理的數(shù)據(jù)抱完，按理說應(yīng)該速度是最快的，WHERE也應(yīng)該比 HAVING快點(diǎn)的冶共，因?yàn)樗^濾數(shù)據(jù)后才進(jìn)行SUM乾蛤，在兩個(gè)表聯(lián)接時(shí)才用ON的每界，所以在一個(gè)表的時(shí)候，就剩下WHERE跟HAVING比較了
考慮聯(lián)接優(yōu)先順序：
INNER JOIN
LEFT JOIN (注：RIGHT JOIN 用 LEFT JOIN 替代)
CROSS JOIN
其它注意和了解的地方有：
在IN后面值的列表中家卖，將出現(xiàn)最頻繁的值放在最前面眨层，出現(xiàn)得最少的放在最后面，減少判斷的次數(shù)
注意UNION和UNION ALL的區(qū)別上荡。--允許重復(fù)數(shù)據(jù)用UNION ALL好
注意使用DISTINCT趴樱，在沒有必要時(shí)不要用
TRUNCATE TABLE 與 DELETE 區(qū)別
減少訪問數(shù)據(jù)庫的次數(shù)
還有就是我們寫存儲(chǔ)過程，如果比較長的話酪捡，最后用標(biāo)記符標(biāo)開叁征，因?yàn)檫@樣可讀性很好，即使語句寫的不怎么樣但是語句工整逛薇，C# 有region捺疼，sql我比較喜歡用的就是：
--startof 查詢在職人數(shù)
sql語句
--end of
正式機(jī)器上我們一般不能隨便調(diào)試程序，但是很多時(shí)候程序在我們本機(jī)上沒問題永罚，但是進(jìn)正式系統(tǒng)就有問題啤呼，但是我們又不能隨便在正式機(jī)器上操作，那么怎么辦呢呢袱？我們可以用回滾來調(diào)試我們的存儲(chǔ)過程或者是sql語句官扣，從而排錯(cuò)。
BEGIN TRAN
UPDATE a SET 字段=''
ROLLBACK
作業(yè)存儲(chǔ)過程我一般會(huì)加上下面這段羞福，這樣檢查錯(cuò)誤可以放在存儲(chǔ)過程惕蹄，如果執(zhí)行錯(cuò)誤回滾操作，但是如果程序里面已經(jīng)有了事務(wù)回滾治专，那么存儲(chǔ)過程就不要寫事務(wù)了卖陵，這樣會(huì)導(dǎo)致事務(wù)回滾嵌套降低執(zhí)行效率，但是我們很多時(shí)候可以把檢查放在存儲(chǔ)過程里张峰，這樣有利于我們解讀這個(gè)存儲(chǔ)過程赶促，和排錯(cuò)。
BEGIN TRANSACTION
--事務(wù)回滾開始
--檢查報(bào)錯(cuò)
IF ( @@ERROR > 0 )
BEGIN
--回滾操作
ROLLBACK TRANSACTION
RAISERROR('刪除工作報(bào)告錯(cuò)誤', 16, 3)
RETURN
END
--結(jié)束事務(wù)
COMMIT TRANSACTION
大概就寫這么多了挟炬，有錯(cuò)誤的地方歡迎大家拍磚,希望交流和共享。
