為什麼SQL語句Where 1=1 and在SQL Server中不影響效能

標籤：style   blog   http   color   使用   sp   strong   on   資料   

    最近一個朋友和我探討關於Where 1=1 and這種形式的語句會不會影響效能。最後結論是不影響。

    雖然結論正確，但對問題的認識卻遠遠沒有解決問題的根本。實際上在T-SQL語句的書寫過程中經常犯得錯誤就是得出一個很窄的結論，然後教條式的奉若聖經，對於T-SQL領域來說，在網上經常可以看到所謂的最佳化規則，隨便在網上搜了一些摘錄如下：

• 不要有超過5個以上的表串連（JOIN）
• 考慮使用暫存資料表或表變數存放中間結果
• 少用子查詢
• 視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。
• 對出現在where子句中的欄位加索引
• 避免在索引列上使用函數或計算，在where子句中，如果索引是函數的一部分，最佳化器將不再使用索引而使用全表掃描
• 在insert和update維表時都加上一個條件來過濾維表中已經存在的記錄
• 如果使用了IN或者OR等時發現查詢沒有走索引，使用顯式申明指定索引
• EXISTS要遠比IN的效率高。

       ……….

 

問題出在哪了?

    雖然上述指導意見看上去沒什麼問題，也不能說完全不正確，但實際上有兩個重大問題：

    脫離上下文：很多道理只能在一個上下文範圍內生效，脫離了上下文範圍就毫無意義。舉個例子，平常有人對你說你有點腎虛，我想你的第一反應肯定是想辦法捍衛男人的尊嚴了，但如果你去醫院檢查醫生這麼說，那你可能就會一臉虔誠的求教如何補了：-），那舉上述摘錄的語句例子：1）少用子查詢，如果在SQL Server操作XML的XPATH按節點屬性篩選的時候，那轉換成子查詢一定會更快 2）如果使用了IN或者OR等時發現查詢沒有走索引，使用顯式申明指定索引，這種情況查詢分析器不走索引一定會有其原因，

 

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

說有一次兩個府吏一起來看病，一個叫倪尋，一個叫李延，兩人的癥狀也一樣，都是頭痛，身上發熱，也許都是感冒吧。而華佗卻說：“倪尋應當用下法來治，李延應當用汗法來治（尋當下之，延當發汗）。”旁人認為很奇怪，大家也一定認為很奇怪吧，為什麼同樣的一個病，同樣的癥狀，會有不同的治療法子呢？華佗解釋了，他說：“倪尋是外實，而立延是內實，所以用了不同的法子。”果然，第二天，他們兩的病都好了。

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

 

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指數退讓演算法不是本文的重點，因此也不多講了。

為什麼SQL語句Where 1=1 and在SQL Server中不影響效能