SQL語句優化提升整體效能

針對性地對一些耗資源嚴重的具體應用進行優化

出現效能問題時，首先要做的是什么？這個問題我問過不少同事，有人說憑經驗對出問題的sql進行優化，如我們一般說的要合理使用索引，盡量不要使用前面帶*號的Like語句，不要再比較操作符前邊進行計算或使用函數等等，這些道路都是對的，但經驗有時候不一定能解決問題。問題出現時，首先要做的是確定問題點是什么，只有正確的找到問題后才能有針對性的解決問題。下面簡單介紹我們一般從哪些角度入手，來確定問題所在。

1.首先從業務上理解該處功能，理解用戶的真正意圖，用戶真正關注的是什么，想要的是什么數據，是否有變通簡潔的方法達到用戶要求。而非使用復雜sql查詢。其實有些時候進行變通的修改，同樣能達到目的，但是采用的sql語句已經極大地簡化了。這是解決效能問題的優先要考慮的。

2.對固定的sql進行優化時，一定要關注查詢相關的數據量，關注數據量的大小，有些時候用戶進行一個查詢，若沒有處理好查詢條件的話，返回的記錄集合太大，這對用戶來說，其實意義不大，關鍵是這樣必然會導致較多的磁盤IO，效能問題是必然的。除非是用戶真的需要這么多數據，但事實證明，多數都不是的，所以著眼點是怎樣限制返回的記錄集的大小或查詢中使用的臨時中間數據集合的大小。這樣才能使你的優化達到效果，起到作用。

下面簡單介紹幾種常用的檢查問題sql的方法。

當然其中是有些技巧的，如：

1. 使用 set statistics io on 檢查實際的磁盤IO信息，物理讀、邏輯讀等信息，這個是一個簡單有效的參考數據，在筆者以往的經驗中，也是主要的參考數據。

在查詢分析器中貼出問題sql，使用set statistics io  為on，也可以在空白處點擊右鍵，選擇<查詢選項>，

選擇<高級>

勾選Set Statistics Io 。

運行查詢，除了得到結果集合以外，還可以得到本次查詢相關的IO信息，如下圖：

我們一般關注邏輯讀的次數，當多個表聯合查詢時，這里會現時每一個表的IO信息，當某個表的邏輯讀的次數很大時，你就要重點關注和分析這個表了，是不是查詢時涉及到這個表中的記錄條數過多，是不是沒有合理使用到Index,是不是可以增加其它的過濾條件來減少相關的記錄集合等等。下面是簡單說明：

輸出項 含義

Table       表的名稱。

Scan count     執行的索引或表掃描數。

logical reads 從數據緩存讀取的頁數。

physical reads        從磁盤讀取的頁數。

read-ahead reads           為進行查詢而放入緩存的頁數。

lob logical reads    從數據緩存讀取的 text、ntext、image 或大值類型 (varchar(max)、nvarchar(max)、varbinary(max)) 頁的數目。

lob physical reads          從磁盤讀取的 text、ntext、image 或大值類型頁的數目。

lob read-ahead reads   為進行查詢而放入緩存的 text、ntext、image 或大值類型頁的數目。

磁盤IO相關信息先介紹到這里，另外一個參考數據是使用 set statistics time on 參考顯示分析、編譯和執行語句所需的毫秒數。具體的使用方法同set statistics io on 基本相同，只不過顯示的是本次查詢所使用的分析編譯、執行等的時間信息。聰明的你一定一看就明白了。在此不再贅述。

1. 使用 set statistics profile on 參考顯示當前語句執行的配置文件信息，執行步驟等信息，使用方法同上。

執行查詢后，除了顯示所執行的結果集合外，還另外顯示本次sql語句執行的相關配置信息，采用記錄樹的形式顯示，對應執行計劃中的各個步驟，比如某個步驟使用的索引類型，評估行數，IO信息，時間信息等。這些信息都可以用來參考，以確定該段sql語句的問題在哪里。

參考當前語句的估計的執行計劃或實際的執行計劃，分析當前語句執行時SQL Server 查詢優化器所選擇的數據檢索方法。

實際的執行計劃顯示了本次執行所使用的執行計劃。該圖應該從右向左看，由下向上看，如果是多個表連接查詢的話，這里也會顯示多個執行步驟，你可以檢查每一個步驟相關的操作相關信息，如IO開銷，CPU開銷，估計的行數，有沒有使用到Index，以及使用的何種Index等信息。行數過多則需要留意了。所使用的Indexl類型也是需要關注的信息之一。

下面是執行計劃中一些概念的簡單說明：

工具提示項   說明

Physical Operation        使用的物理運算符，例如 Hash Join 或 Nested Loops。以紅色顯示的物理運算符表示查詢優化器已發出警告，例如丟失列統計信息或丟失聯接謂詞。這可能導致查詢優化器選擇比預期的效率低的查詢計劃。有關列統計信息的詳細信息，請參閱使用統計信息提高查詢性能。

當圖形執行計劃建議創建統計信息、更新統計信息或創建索引時，使用 SQL Server Management Studio 對象資源管理器中的快捷菜單可以立即創建或更新丟失的列統計信息和索引。有關詳細信息，請參閱索引操作指南主題。

Logical Operation           與物理運算符匹配的邏輯運算符，如 Inner Join 運算符。邏輯運算符列在物理運算符之后，兩者均位于工具提示的頂部。

Estimated Row Size       操作符生成的行的估計大小（字節）。

Estimated I/O Cost        用于執行操作的所有 I/O 活動的估計開銷。此值應盡可能低。

Estimated CPU Cost       用于執行操作的所有 CPU 活動的估計開銷。

Estimated Operator Cost      用于執行此操作的查詢優化器的開銷。此操作的開銷以占查詢總開銷的百分比的形式顯示在括號中。由于查詢引擎選擇最高效的操作來執行查詢或執行語句，因此此值應盡可能低。

Estimated Subtree Cost         查詢優化器執行此操作及同一子樹內位于此操作之前的所有操作的總開銷。

Estimated Number of Rows           運算符生成的行數。

綜合以上介紹的幾種參考信息的方法，一般都可以確定問題sql的問題所在，然后對癥下藥，剩下的就是進行針對性的修改了，這里只是拋磚引玉，聰明的你一定會有方法解決的。

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]