性能不理想的系統(tǒng)中除了一部分是因為應(yīng)用程序的負載確實超過了服務(wù)器的實際處理能力外,更多的是因為系統(tǒng)存在大量的SQL語句需要優(yōu)化绪励。為了獲得穩(wěn)定的執(zhí)行性能忧陪，SQL語句越簡單越好蔗包。對復(fù)雜的SQL語句暮蹂，要設(shè)法對之進行簡化杏瞻。
常見的簡化規(guī)則##
- 不要有超過5個以上的表連接（JOIN）；
- 考慮使用臨時表或表變量存放中間結(jié)果嫡霞；
- 少用子查詢瓶埋；
- 視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜；
- 連接的表越多诊沪，其編譯的時間和連接的開銷也越大养筒，性能越不好控制。最好是把連接拆開成較小的幾個部分逐個順序執(zhí)行端姚。優(yōu)先執(zhí)行那些能夠大量減少結(jié)果的連接晕粪。拆分的好處不僅僅是減少SQL Server優(yōu)化的時間，更使得SQL語句能夠以你可以預(yù)測的方式和順序執(zhí)行渐裸。如果一定需要連接很多表才能得到數(shù)據(jù)巫湘，那么很可能意味著設(shè)計上的缺陷；
- 連接是outer join昏鹃，非常不好尚氛。因為outer join意味著必須對左表或右表查詢所有行。如果表很大而沒有相應(yīng)的where語句洞渤，那么outer join很容易導(dǎo)致table scan或index scan阅嘶。要盡量使用inner join避免scan整個表。
優(yōu)化建議##
- **限制結(jié)果集 **要盡量減少返回的結(jié)果行载迄，包括行數(shù)和字段列數(shù)讯柔。返回的結(jié)果越大，意味著相應(yīng)的SQL語句的logical reads 就越大护昧，對服務(wù)器的性能影響就越甚魂迄。
一個很不好的設(shè)計就是返回表的所有數(shù)據(jù)：
Select * from tablename
即使表很小也會導(dǎo)致并發(fā)問題。更壞的情況是惋耙，如果表有上百萬行的話极祸，那后果將是災(zāi)難性的慈格。它不但可能帶來極重的磁盤IO，更有可能把數(shù)據(jù)庫緩沖區(qū)中的其他緩存數(shù)據(jù)擠出遥金，使得這些數(shù)據(jù)下次必須再從磁盤讀取浴捆。必須設(shè)計良好的SQL語句，使得其有where語句或TOP語句來限制結(jié)果集大小稿械。
- 合理的表設(shè)計 SQL Server 2005將支持表分區(qū)技術(shù)选泻。利用表分區(qū)技術(shù)可以實現(xiàn)數(shù)據(jù)表的流動窗口功能。在流動窗口中可以輕易的把歷史數(shù)據(jù)移出美莫，把新的數(shù)據(jù)加入页眯，從而使表的大小基本保持穩(wěn)定。 另外厢呵，表的設(shè)計未必需要非常范式化窝撵。有一定的字段冗余可以增加SQL語句的效率，減少JOIN的數(shù)目襟铭，提高語句的執(zhí)行速度碌奉。
處理百萬級以上的數(shù)據(jù)提高查詢速度的方法##
- 應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描寒砖。
- 對查詢進行優(yōu)化赐劣，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引哩都。
- 應(yīng)盡量避免在 where 子句中對字段進行 null 值判斷魁兼，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：
select id from t where num is null
可以在num上設(shè)置默認值0漠嵌，確保表中num列沒有null值咐汞，然后這樣查詢：
select id from t where num=0
- 應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描儒鹿，如：
select id from t where num=10 or num=20
可以這樣查詢：
select id from t where num=10 union all select id from t where num=20
- 下面的查詢也將導(dǎo)致全表掃描：(不能前置百分號)
select id from t where name like ‘%abc%’ 若要提高效率化撕，可以考慮全文檢索。
- in 和 not in 也要慎用挺身，否則會導(dǎo)致全表掃描，如：
select id from t where num in(1,2,3)
對于連續(xù)的數(shù)值锌仅，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
- 如果在 where 子句中使用參數(shù)章钾，也會導(dǎo)致全表掃描。因為SQL只有在運行時才會解析局部變量热芹，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時贱傀；它必須在編譯時進行選擇。然而伊脓，如果在編譯時建立訪問計劃府寒，變量的值還是未知的魁衙，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：
select id from t where num=@num
- 應(yīng)盡量避免在 where 子句中對字段進行表達式操作株搔，這將導(dǎo)致引擎放棄使用索引而進行全表掃描剖淀。如：
select id from t where num/2=100
應(yīng)改為:
select id from t where num=100*2
- 應(yīng)盡量避免在where子句中對字段進行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描纤房。如：
select id from t where substring(name,1,3)=’abc’ – name以abc開頭的id
select id from t where datediff(day,createdate,’2005-11-30′)=’2005-11-30′ 生成的id
應(yīng)改為:
select id from t where name like ‘a(chǎn)bc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
不要在 where 子句中的“=”左邊進行函數(shù)纵隔、算術(shù)運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引炮姨。
在使用索引字段作為條件時捌刮，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引舒岸，否則該索引將不會被使用绅作，并且應(yīng)盡可能的讓字段順序與索引順序相一致。
很多時候用 exists 代替 in 是一個好的選擇：
select num from a where num in(select num from b)
用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)
并不是所有索引對查詢都有效蛾派，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的俄认，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時，SQL查詢可能不會去利用索引碍脏，如一表中有字段 sex梭依，male、female幾乎各一半典尾，那么即使在sex上建了索引也對查詢效率起不了作用役拴。
索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率钾埂，但同時也降低了 insert 及 update 的效率河闰，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮褥紫，視具體情況而定姜性。一個表的索引數(shù)最好不要超過6個，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要髓考。 **
應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列部念，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整氨菇，會耗費相當(dāng)大的資源儡炼。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引查蓉。
盡量使用數(shù)字型字段乌询，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能豌研，并會增加存儲開銷妹田。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符唬党，而對于數(shù)字型而言只需要比較一次就夠了。
盡可能的使用 varchar/nvarchar 代替 char/nchar 鬼佣，因為首先變長字段存儲空間小驶拱，可以節(jié)省存儲空間，其次對于查詢來說沮趣，在一個相對較小的字段內(nèi)搜索效率顯然要高些屯烦。
任何地方都不要使用 select * from t ，用具體的字段列表代替“*”房铭，不要返回用不到的任何字段驻龟。
盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)缸匪，請注意索引非常有限（只有主鍵索引）翁狐。
避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗凌蔬。
臨時表并不是不可使用露懒，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行В缟靶模?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時懈词。但是，對于一次性事件辩诞，最好使用導(dǎo)出表坎弯。
在新建臨時表時，如果一次性插入數(shù)據(jù)量很大译暂，那么可以使用 select into 代替 create table抠忘，避免造成大量 log ，以提高速度外永；如果數(shù)據(jù)量不大崎脉，為了緩和系統(tǒng)表的資源，應(yīng)先create table伯顶，然后insert囚灼。
如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除祭衩，先 truncate table 灶体，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定汪厨。
盡量避免使用游標(biāo)赃春，因為游標(biāo)的效率較差愉择，如果游標(biāo)操作的數(shù)據(jù)超過1萬行劫乱，那么就應(yīng)該考慮改寫织中。
使用基于游標(biāo)的方法或臨時表方法之前，應(yīng)先尋找基于集的解決方案來解決問題衷戈，基于集的方法通常更有效狭吼。
與臨時表一樣，游標(biāo)并不是不可使用殖妇。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法刁笙，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時。在結(jié)果集中包括“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快谦趣。如果開發(fā)時 間允許疲吸，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好前鹅。
在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON 摘悴，在結(jié)束時設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息舰绘。
盡量避免向客戶端返回大數(shù)據(jù)量蹂喻，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理捂寿。
盡量避免大事務(wù)操作口四，提高系統(tǒng)并發(fā)能力。
