如何使 SQL Server高效 –T-SQL（ITPUT 討論匯總）

2、      您認為在T-SQL編寫（包括預存程序、函數和視圖）上，哪些因素會影響SQL Server效率?

討論匯總——索引使用

l  沒有索引或者沒有用到索引、I/O輸送量小、沒有建立計算資料行導致查詢不最佳化、鎖或者死結、查詢語句不夠最佳化等

l  對大表而言。那些不走索引的語句，錯誤scan的語句，還有那些強制使用HINT的語句，可能會因為時間的推移，影響了SQL Server對最優計劃的產生

l  避免無法使用索引的過濾條件。如 WHERE dbo.ufn.Test(ID)=1

l  確儲存在適合查詢的索引

l  根據查詢條件,建立索引,最佳化索引、最佳化訪問方式，限制結果集的資料量。注意填滿因數要適當（最好是使用預設值0）。

l  如果是使用like進行查詢的話，簡單的使用index是不行的，但是全文索引，耗空間。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查詢時，查詢耗時和欄位值總長度成正比,所以不能用CHAR類型，而是VARCHAR。對於欄位的值很長的建全文索引

討論匯總——遊標：

l  遊標的頻繁 "開 / 關", 既消耗資源, 同時會給相關的表加"鎖"

l  可以通過集合運算的方式去處理資料的情況下，避免使用遊標

l  預存程序通過遊標的方式，將函數的功能放在了遊標的計算中，這樣速度有了一些提高

l  考慮將遊標用其他方式改寫（需要驗證方法是否有效，無法保證效能的改寫，可能比直接使用遊標更糟糕），或確保遊標高效

l  個人補充：

a）  對於必須逐行處理的情況，建議直接使用遊標，如果要避免對構成遊標來來源資料的影響，可以考慮使用靜態資料指標。對於可以使用集合處理模式替代洲標的情況，也要考慮替代之後的查詢複雜性和涉及的資料量，如果這個修改產生的查詢非常複雜或涉及大量資料，那麼用遊標可能可以得到更佳或者更穩定的效果，建議通過測實驗證決定最終的方案

b）  注意遊標類型的選擇

c）  經常使用遊標片大量資料量，考慮適當調整cursor threshold選項（非同步產生控制）

討論匯總——暫存資料表與表變數

l  表變數和暫存資料表的使用要有規定，大小也要有個估計

l  暫存資料表和記憶體表要分別對待

l  個人補充：表變數不受事務控制，其資料寫入效率比暫存資料表高；但SQL Server不為表變數產生統計資訊，這意味著如果表變數參與查詢的話，查詢最佳化工具無法評估表變數的資料，從而導致無法有效評估查詢方案成本，所以，如果資料需要參與查詢的話，始終建議使用暫存資料表（尤其是在複雜查詢中，這個非常重要）。另外注意的一點，表變數並不確保只在記憶體中存在，當資料量大時，其資料仍然會寫入tempdb

討論匯總——綜合

l  預存程序、視圖、函數的一個好處是查詢計劃重用（預先編譯)），但還是要注意會導致重編譯的操作，例如預存程序中引用和暫存資料表、視圖結構更改等

l  使用不合適的資料類型, 如與表欄位不一致, 或發生隱式轉換 (字元轉數字)

l  SET NOCOUNT ON , 避免DML操作不必要的網路傳輸

l  最典型的函數加於欄位上再判斷。不正確的實現邏輯將導致效能問題。。當然，這本不屬於DB平台的事兒了，是實現者的事兒

l  編寫SQL語句時，在保證完成需要情況下盡量保證SQL簡單易讀，導致效率抵消的原因有：

a）  過大的事務操作導致事務之間嚴重阻塞

b）  返回過多無用資料引發磁碟和網路壓力

c）  過多的非集合化操作，如在返回列頻繁調用某個函數

d）  查詢中使用負邏輯

e）  不必要的排序,，查詢不需要的資料

f）  冗餘的判斷邏輯,，多層視圖的嵌套引用.

l  檢查不良的SQL，考慮其寫法是否還有可最佳化內容

a）  檢查子查詢。考慮SQL子查詢是否可以用簡單串連的方式進行重新書寫

b）  考慮資料庫的最佳化器

c）  盡量避免大事務操作, 提高系統並發能力

d）  盡量避免向用戶端返回大資料量, 如採用分頁

e）  盡量避免遊標使用進行大規模資料的串連和遍曆

l  資料庫引擎的本身的最佳化。每個資料庫的查詢引擎也是在不斷進化當中的，有些以前高效的語句現在顯得落伍了

l  .資料庫設計最佳化：在針對不同主題，不同分析方向的時候資料本身的設計結構也會使得SQL語句的寫法有所不同

l  70%效能問題出自不良的SQL語句。條件不符合SAR規範，遊標濫用，事務過長，複雜商務邏輯實現追求功能，不考慮效能等等

l  避免大表的distinct

l  T-SQL編寫要對複雜度進行規定，不能無限制的嵌套或者Union等等。T-SQL中檢索資料規模也需要估計

l  在查詢Select語句中用Where字句限制返回的行數,避免表掃描,如果返回不必要的資料，浪費了伺服器的I/O資源，加重了網路的負擔降低效能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯結訪問表,後果嚴重。

l  預存程序要設計合理，當然提示資料庫效率，主要在表的結構和使用合理的查詢方法

l  視圖的資料要放在記憶體中執行，這樣才能提升效率，但是要加記憶體

l  join的時候要命中index，少用多層嵌套，可以考慮用臨時表

l  設計SQL後，應使用explain命令檢查SQL，看是否使用到索引，是否存在filesort,重點檢查檢索的行數（rows）是否太大。一般來說.：

a）  rows<1000，是在可接受的範圍內的。

b）  ows在1000~1w之間，在密集訪問時可能導致效能問題，但如果不是太頻繁的訪問（頻率低於1分鐘一次），又難再最佳化的話，可以接受，但需要注意觀察

c）  rows大於1萬時，應謹慎考慮SQL的設計，最佳化SQL，最佳化db，一般來說不允許頻繁運行（頻率低於1小時一次）

d）  rows達到10w層級時，堅決不能做為即時啟動並執行SQL。但導資料場合除外，但導資料必須控制好時間，頻度

l  在設計SQL，尤其是稍微複雜的SQL時，一定要在測試環境甚至是實際環境上預先進行explain

l  ＳＱＬ語句避免不符合ＳＡＲＳ條件的

l  遊標和觸發器盡量避免使用

l  避免長事務，避免出現阻塞和死結　

l  複雜邏輯可以多次實現

l  動態拼接SQL語句是影響SQLServer效率的一個方面

l  預存程序中多表的串連查詢也是影響SQL Server效率的一個方面，當然是許多資料庫都具備的毛病，大概是CBO產生執行計畫不佳的一個問題，迫不得已多使用暫存資料表

l  應該採用函數或預存程序代替複雜的視圖，效能更好

個人補充：使用自訂函數的時候要注意評估。返回單個值的函數最好能夠保障是確定性函數；查詢最佳化工具不會為多語句資料表值函式建立統計資訊，所以如果多語句資料表值函式是用於關聯查詢的話，可能會產生較差的查詢方案

l  SQL Server沒有oracle的非阻塞讀，不光是寫/寫，讀/寫也會有衝突，應該勁量避免，比如：使用短小事務，合理設定並使用索引，如果可以的話做讀寫分離；影響SQL瓶頸的因素有很多，包括記憶體不足，硬體不行，或者沒有足夠的記憶體供SQL Server 使用，缺少有用的索引等，網路通訊不好，磁碟配置了，如tempdb 的配置，是否為查詢最佳化工具提供了最佳化複雜查詢的最有利條件

個人補充：讀寫衝突，可以通過控制交易隔離等級（或者相關的表提示）來控制

l  .資料的量級：在資料量低大的情況下高效率的SQL語句在資料量高的情況下就不能用了，反之亦然

個人補充：T-SQL執行包括編譯（查詢方案評估選擇）和執行兩個步驟，在查詢方案選擇的步驟，查詢最佳化工具可以根據統計資訊、對象結構評估出有效查詢方案，當然，如果查詢過於複雜，或者是查詢成本評估所需要的資訊不準確，評估的結果也會存在誤差，所以大部分情況下不會出現需要根據資料量隨時調整查詢的情況

l  T-SQL方面SQL Server缺乏Oracle完備的分析函數支援功能，也缺乏一些行列轉換、樹形目錄結構方面的函數，雖然可以通過遞迴cte、xml等函數解決

個人補充：內建函數的多少無法對比，每個資料庫都有自己的一些考慮，但常用的基本上是不缺的，對於需要但確實沒有的，可以考慮自己寫CLR函數。行列轉換有 PIVOT 和 UNPIVOT；階層有 hierarchyid 資料類型和對應的一些函數

個人補充

l  複雜查詢是需要特別注意控制的。越複雜的查詢，意味著可選擇的查詢方案越多，查詢評估所涉及的方面越廣，這意味著查詢最佳化工具評估查詢方案的成本越高，涉及的因素多，意味著評估的方案準確性越低。所以過於複雜的查詢，往意味著效能很難保障。瞭解SQL語句的處理過程，有助於理解這個問題（在線上說明中搜尋“SQL 陳述式處理”）可以得到這方面的說明。另外，要特別注意兩種複雜查詢：

a）  很多個CTE定義組成的查詢。單看每個CTE定義，都很簡單，但最終組合起來的，很可能是一個很複雜的查詢，可以通過查看執行計畫來確定。特別需要注意的是，通過CTE定義將執行步驟寫得再清晰和一目瞭然，最終執行也基本上不是按照寫的步驟去做的

b）  視圖（包括資料表值函式）嵌套導致的複雜查詢。查詢涉及的每個視圖可能很簡單，但一層層嵌套組合起來，可能最終也形成一個複雜查詢，這個也可以勇冠看執行計畫確定。（盡量避免在資料庫中使用模組化的設計思想）

l  注意實現方法的控制。比較常見的有下面這些：

a）  使用 JOIN 代替 EXISTS（包括IN）。這種很多時候容易出問題，特別是無法通過結構定義確定JOIN是否會產生一對多之類的情況下。EXISTS只需要考慮是否存在，JOIN還需要考慮資料是一對一，還是一對多或多對多，這兩者導致的查詢成本評估是不一樣的

b）  過多的 IN(值的列表)。這種基本上會被判斷為一堆的OR條件，而且會需要評估每個值，很多時候查詢成本是比較高的。可以考慮把值先放入暫存資料表，再IN 暫存資料表

c）  減少OR條件。不少的情況下，將OR條件改成多個 UNIONALL的結果會更高效一些

d）  注意條件和JOIN中的資料類型轉換。儘可能使用顯式的資料類型轉換，並將轉換的目標放在常量/變數/參數或者資料少的一方。隱式（自動）的資料類型轉換是根據類型優先順序確定轉換目標的，如果轉換操作發生在資料量大的一方，那查詢就會比較差了

  討論帖 之前的討論話題：1、 您認為在設計SQL Server對象時，主要會考慮哪些因素來避免出現效能問題?   後續討論話題：

3、 在設計資料庫操作程式上，您認為應該注意哪些事項，以確保能夠有效地使用資料庫?

4、 在您的SQL Server使用過程中，有哪些令您非常困惑的效能問題 ?