SQL優化：優化層次、途經、最佳實踐

一、優化的層次：

1、語句級别（包含索引）

2、系統級别：索引碎片、鎖定、統計資訊、系統參數配置、資料庫設計

3、底層級别：硬體、網絡

二、SQL Server查詢性能優化需要多種途徑：

 1、資料庫設計。這可能是影響查詢性能和資料完整性最重要的因素了，設計決策會影響讀取、修改的性能。

規範化的OLTP資料庫關注資料庫完整性、去除備援、建立多個實體之間的關系，這是一個适合快速事務處理的設計。通常在規範化的OLTP設計中看到很多的表，這意味着在查詢中要連接配接很多表。

另一方面，OLAP資料倉庫設計經常使用非規範化的星形結構、雪花狀結構，這些設計使用了中心事實表，它連接配接了2個或更多的描述次元表。對于雪花結構設計，次元表也可以存在于關聯它的關系表上，這種設計的重點在于查詢執行的速度而不是事務的快速更新。

2、适當的索引。

基于高優先級、頻繁執行的查詢來建立表索引。如果一個查詢一天執行上千次且每次2秒完成，則可以通過适當的索引時每次運作時間少于1秒，增加這個索引可以顯著減少SQL Server執行個體上的I/O壓力。

應該根據需要建立索引，删除不用的索引。

表上的每個索引都會給資料修改操作增加開銷，如果SQL Server使用低效率的索引甚至還會減慢查詢的速度。在最初設計資料庫的時候，索引最好少一些（當然，聚集索引、外鍵的非聚集索引還是要的）。在有需要的時候再增加索引，特别是在易變的、頻繁更新的資料庫中，索引需求是易變的，是以應該靈活、疊代的增加删除索引。

3、索引碎片。

由于長期的資料修改，索引會産生碎片。由于碎片的增多，資料會擴散到更多的資料頁中。查詢同樣多資料需要檢索的資料頁也就會更多，IO需求就會更高，查詢就會更慢。

4、系統參數配置。

包含了：資料庫、SQL Server執行個體、作業系統配置。不适當的配置（比如啟動了自動收縮、自動關閉資料庫）會影響應用程式的性能。

 5、最新的統計資訊。

AUTO_CREATE_STATISTICS資料庫選項激活了SQL Server自動産生關于列中值的分布的統計資訊。如果關閉此選項，統計資訊就會過期。由于SQL Server依賴統計資訊區決定如何執行查詢，如果SQL Server基于過期的統計資訊進行判斷，那麼可能會選擇一個不太好的查詢計劃。

6、硬體。

當通過索引、修改語句的寫法等都不能提高查詢速度時，原因可能是結果集中的行太多、IO的限制，這時應該考慮把資料庫遷移到更高配置的伺服器上。對處理器體系結構、可用記憶體、磁盤系統的選擇都會顯著影響查詢性能。

7、網絡吞吐量。

擷取查詢結果所花的時間會受到緩慢的、不穩定的網絡連接配接的影響，是以需要考慮這個潛在的影響因素。

 三、SQL Server查詢性能最佳實踐

1、在select查詢中，隻傳回需要的列。查詢中存在的列越少，則使用的IO和網絡帶寬就會越少。

2、在減少列的同時，也要考慮一下減少行。使用where子句來減少查詢傳回的行，不要讓應用程式在隻需顯示前10行時，卻傳回10000行。

3、減少join次數。連接配接到單個查詢中的每個表都會增加額外的開銷。雖然具體join多少個表會收到資料庫設計、容量大小以及用于關聯查詢的列的影響，但是短小查詢有短小查詢的好處。是以如果存儲過程中有一個執行很長時間的非常大的查詢，那麼可以把這個查詢分解為幾個更小的中間結果，這通常會顯著加快生成結果集的速度。

4、隻在需要有序結果時，采用order by。大結果集的排序操作會導緻額外開銷。如果排序不是必須的那就不要排序。

5、避免在from、where、having子句中隐式資料類型的轉換。當謂詞中底層資料類型不比對就會發生隐式資料類型轉換，并且是由SQL Server自動轉換的。比如，java應用程式發送unicode文本到非unicode列，對于每秒處理數百個事務的程式來說，隐式轉換會增加處理時間。另外，隐式資料類型的轉換可能會導緻不能引用索引，導緻查詢性能下降。

6、如果唯一行不是必須的，那麼就不要用distinct、union（而是用union all）。

7、當在遊标和基本集合的方法之間進行選擇時，最好選擇集合方法。如果必須用遊标，那麼使用完後一定要盡快關閉和釋放。

8、存儲過程可以提升查詢執行的穩定性（重用既有的查詢計劃），是以可以把一個查詢封裝到存儲過程中，這樣可能會提升性能。

9、應該避免使用嵌套視圖。如果在建立視圖時引用了其他視圖，而這個建立的視圖所引用的對象，在被調用的視圖中已經被引用過了，那麼由于查詢的複雜性，這個重複和重疊經常會造成未優化的查詢計劃。

10、在複雜的資料庫驅動的應用程式中，在某些情況下使用查詢提示是必要的。不過，當底層的資料量、分布情況發生變化時，查詢提示可能會覆寫SQL Server的決策處理，誤導SQL Server做出最佳計劃。

11、在開發資料庫中，将有代表性的資料集填充到表中是非常重要的。也就是說應該把與實際生産環境中相當數量的有代表性的行填充到表中，不要在開發資料庫内使用沒有代表性的資料。SQL Server的性能非常依賴索引和統計資訊，并且會基于表内實際的值做出決策。如果測試資料不是有代表性的較真實的資料，那麼生産環境中的查詢效果與測試中的效果會差異很大。

轉載于:https://www.cnblogs.com/momogua/p/8304332.html