• 1
  • 2
  • 3
  • 4
  • 5
mssql數據庫問題 首 頁  ?  幫助中心  »  數據庫  »  mssql數據庫問題
SQL 語句 Where 1=1 and 在 SQL Server 中不影響性能的原因
發布日期:2016-5-2 21:5:12

  最近,一個朋友與我探討關于Where 1=1 and這種形式的語句會不會影響性能。我們得出的結論是最后結論是不影響。

  盡管結論正確,但是對問題的認識卻遠遠沒有解決問題的根本。事實上在T-SQL語句的書寫過程中經常犯得錯誤就是得出一個很窄的結論,然后教條式的奉若圣經,對T-SQL領域來說,在網上經??梢钥吹剿^的優化守則,隨便在網上搜了一些摘錄如下所示:

  1.不要有超過5個以上的表連接(JOIN)

  2.考慮使用臨時表或表變量存放中間結果

  3.少用子查詢

  4.視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。

  5.對出現在where子句中的字段加索引

  6.避免在索引列上使用函數或計算,在where子句中,如果索引是函數的一部分,優化器將不再使用索引而使用全表掃描

  7.在insert和update維表時都加上一個條件來過濾維表中已經存在的記錄

  8.如果使用了IN或者OR等時發現查詢沒有走索引,使用顯式申明指定索引

  9.EXISTS要遠比IN的效率高。

  ……….

  一、問題出在哪了?

  盡管上述指導意見看上去沒什么問題,但也不能說完全不正確,但實際上有兩個重大問題,如下所示:

  問題1--脫離上下文:很多道理只能在一個上下文范圍內生效,脫離了上下文范圍就毫無意義。例如,平常有人對你說你有點腎虛,我想你的第一反應肯定是想辦法捍衛男人的尊嚴了,但是如果你去醫院檢查醫生這么說,那你可能就會一臉虔誠的求教如何補了:-)

  那舉上述摘錄的語句例子:

  (1)少用子查詢,如果在SQL Server操作XML的XPATH按節點屬性篩選的時候,那轉換成子查詢一定會更快

  ( 2)如果使用了IN或者OR等時發現查詢沒有走索引,使用顯式申明指定索引,這種情況查詢分析器不走索引一定會有其原因,

  問題2--不解釋本質原因:佛語有云“凡所有相,皆是虛妄,若見諸相非相,即見如來”。請看下面故事:

  說有一次兩個府吏一起來看病,一個叫倪尋,一個叫李延,兩人的癥狀也一樣,都是頭痛,身上發熱,也許都是感冒吧。而華佗卻說:“倪尋應當用下法來治,李延應當用汗法來治(尋當下之,延當發汗)?!迸匀苏J為很奇怪,大家也一定認為很奇怪吧,為什么同樣的一個病,同樣的癥狀,會有不同的治療法子呢?華佗解釋了,他說:“倪尋是外實,而立延是內實,所以用了不同的法子?!惫?,第二天,他們兩的病都好了。

  其實我們可以看出,完全同樣的癥狀,可以是完全不同的原因,反之,同樣的原因,也可以形成完全不同的“相”。如果僅僅是看到“相”而采取應激處理措施,往往結果會不盡人意。

  二、Think Like Query Optimizer

  在每一個領域都有其領域內的規則,簡單來說,拖你不符合C#規范去編程,例如錯誤的使用關鍵字,那么編譯就會報錯。當然,每一個領域內有一些隱藏的規則,也有人會說是所謂的“潛規則”,這類規則往往不在明面上,例如說你不符合最佳實踐編寫一段程序,編譯不會報錯,但因此而引起的性能或是安全性問題就是你需要遵循最佳實踐這個“潛規則”才能避免。

  但是在SQL Server領域,T-SQL語句到查詢結果返回需要經歷一個完整的周期,如圖1所示:


  圖1:T-SQL生命周期

  所以,在關系數據庫領域,SQL語句的寫法只是一個抽象的邏輯,而不是像編程語言那樣直接的實現。比如說訪問一行數據,若是編程語言實現,就需要指定連接數據的方式,打開數據,按某個方式取出數據,最后還要關閉連接,而在SQL Server中,T-SQL僅僅是定義如何去獲取所需的數據,而無需考慮實現細節。

  圖1所示的是從T-SQL到具體返回數據經歷了多個步驟,每一個步驟又存在大量的規則。所示在本文提到Where 1=1 and引起的性能問題就需要按照查詢分析器的規則去考慮為什么,這也是Think like query optimizer。

  在SQL Server中,T-SQL需要編譯為執行計劃才能去執行,在編譯過程中,Query Optimizer需要考慮很多元數據,例如說表上的索引、估計行數、數據分布、一些參數配置、硬件環境等,在這其中,最重要的就是估計行數,SQL Server需要估計行數來估計成本。

  三、Where 1=1 and寫法為什么不會變慢?

  由于查詢分析器在代數樹優化階段就把1=1 直接給過濾掉了。這個功能就是查詢優化器中所謂的“Constant Folding”。

  在這里我們假設查詢分析器在代數樹優化階段沒有把where 1=1這種情況直接過濾掉。

  比如語句select * from table where a=1 and b=2 這個語句,SQL Server估計的行數會是:

  a列的選擇率*b列的選擇率*表中采樣的總行數

  所以,當Where 1=1 and a=1時,結果就變為

  1*a列的選擇率 *表中采樣的總行數=a列的選擇率 *表中采樣的總行數

  所以無論是否有1=1 and,查詢分析器都會估計相同的行數,從而擁有同樣的執行計劃,所以不影響性能。

  當我們明白了查詢分析器對A and B這種寫法是如何估計行數之后,那么我們就可以推算出什么情況A and B可能引起執行計劃不準確。從公式來看,SQL Server認為A列和B列是無關聯的,如果A和B關聯很大,那么估計的行數一定會非常不準。

  例如,假如表中有100萬行數據,where a=1的數據有1萬條,where b=1的數據有1萬條,則A與B的選擇性都是1/100=0.01,在Where中A And B聯合的估計行數則變為0.01*0.01=0.0001*100萬=100行,假設where a=1 和b=1所篩選的數據為同樣的1萬行數據,則估計行數為100而實際行數為1萬,則可能引起執行計劃的不準確,從而引起性能問題。當然,這種情況的確是少數,但是發生后往往對性能有一定影響,因此SQL Server 2014新的行數估計采用了指數退讓算法,在這種情況下就會估計為1000行,從而引起性能問題的可能性會變小,2014指數退讓算法不是本文的重點,因此也不多講了。

    后面我們會更新一些關于mssql的相關文章,關注mssql的童鞋敬請期待。

什么行业的讲师最赚钱 上海电气股票趋势 买秒速赛车有什么规律 浙江快乐彩走势图 6个平码怎推算下期 如何打好上海敲麻麻 北京11选5走势图top10 管家婆资料 湖南牵手红中麻将下载 捕鱼大师现金 南粤风采26选5历史开奖结果