SQL語(yǔ)句優(yōu)化提升整體效能

針對(duì)性地對(duì)一些耗資源嚴(yán)重的具體應(yīng)用進(jìn)行優(yōu)化

出現(xiàn)效能問(wèn)題時(shí)，首先要做的是什么？這個(gè)問(wèn)題我問(wèn)過(guò)不少同事，有人說(shuō)憑經(jīng)驗(yàn)對(duì)出問(wèn)題的sql進(jìn)行優(yōu)化，如我們一般說(shuō)的要合理使用索引，盡量不要使用前面帶*號(hào)的Like語(yǔ)句，不要再比較操作符前邊進(jìn)行計(jì)算或使用函數(shù)等等，這些道路都是對(duì)的，但經(jīng)驗(yàn)有時(shí)候不一定能解決問(wèn)題。問(wèn)題出現(xiàn)時(shí)，首先要做的是確定問(wèn)題點(diǎn)是什么，只有正確的找到問(wèn)題后才能有針對(duì)性的解決問(wèn)題。下面簡(jiǎn)單介紹我們一般從哪些角度入手，來(lái)確定問(wèn)題所在。

1.首先從業(yè)務(wù)上理解該處功能，理解用戶的真正意圖，用戶真正關(guān)注的是什么，想要的是什么數(shù)據(jù)，是否有變通簡(jiǎn)潔的方法達(dá)到用戶要求。而非使用復(fù)雜sql查詢。其實(shí)有些時(shí)候進(jìn)行變通的修改，同樣能達(dá)到目的，但是采用的sql語(yǔ)句已經(jīng)極大地簡(jiǎn)化了。這是解決效能問(wèn)題的優(yōu)先要考慮的。

2.對(duì)固定的sql進(jìn)行優(yōu)化時(shí)，一定要關(guān)注查詢相關(guān)的數(shù)據(jù)量，關(guān)注數(shù)據(jù)量的大小，有些時(shí)候用戶進(jìn)行一個(gè)查詢，若沒(méi)有處理好查詢條件的話，返回的記錄集合太大，這對(duì)用戶來(lái)說(shuō)，其實(shí)意義不大，關(guān)鍵是這樣必然會(huì)導(dǎo)致較多的磁盤IO，效能問(wèn)題是必然的。除非是用戶真的需要這么多數(shù)據(jù)，但事實(shí)證明，多數(shù)都不是的，所以著眼點(diǎn)是怎樣限制返回的記錄集的大小或查詢中使用的臨時(shí)中間數(shù)據(jù)集合的大小。這樣才能使你的優(yōu)化達(dá)到效果，起到作用。

下面簡(jiǎn)單介紹幾種常用的檢查問(wèn)題sql的方法。

當(dāng)然其中是有些技巧的，如：

1. 使用 set statistics io on 檢查實(shí)際的磁盤IO信息，物理讀、邏輯讀等信息，這個(gè)是一個(gè)簡(jiǎn)單有效的參考數(shù)據(jù)，在筆者以往的經(jīng)驗(yàn)中，也是主要的參考數(shù)據(jù)。

在查詢分析器中貼出問(wèn)題sql，使用set statistics io  為on，也可以在空白處點(diǎn)擊右鍵，選擇<查詢選項(xiàng)>，

選擇<高級(jí)>

勾選Set Statistics Io 。

運(yùn)行查詢，除了得到結(jié)果集合以外，還可以得到本次查詢相關(guān)的IO信息，如下圖：

我們一般關(guān)注邏輯讀的次數(shù)，當(dāng)多個(gè)表聯(lián)合查詢時(shí)，這里會(huì)現(xiàn)時(shí)每一個(gè)表的IO信息，當(dāng)某個(gè)表的邏輯讀的次數(shù)很大時(shí)，你就要重點(diǎn)關(guān)注和分析這個(gè)表了，是不是查詢時(shí)涉及到這個(gè)表中的記錄條數(shù)過(guò)多，是不是沒(méi)有合理使用到Index,是不是可以增加其它的過(guò)濾條件來(lái)減少相關(guān)的記錄集合等等。下面是簡(jiǎn)單說(shuō)明：

輸出項(xiàng) 含義

Table       表的名稱。

Scan count     執(zhí)行的索引或表掃描數(shù)。

logical reads 從數(shù)據(jù)緩存讀取的頁(yè)數(shù)。

physical reads        從磁盤讀取的頁(yè)數(shù)。

read-ahead reads           為進(jìn)行查詢而放入緩存的頁(yè)數(shù)。

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

lob physical reads          從磁盤讀取的 text、ntext、image 或大值類型頁(yè)的數(shù)目。

lob read-ahead reads   為進(jìn)行查詢而放入緩存的 text、ntext、image 或大值類型頁(yè)的數(shù)目。

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

1. 使用 set statistics profile on 參考顯示當(dāng)前語(yǔ)句執(zhí)行的配置文件信息，執(zhí)行步驟等信息，使用方法同上。

執(zhí)行查詢后，除了顯示所執(zhí)行的結(jié)果集合外，還另外顯示本次sql語(yǔ)句執(zhí)行的相關(guān)配置信息，采用記錄樹的形式顯示，對(duì)應(yīng)執(zhí)行計(jì)劃中的各個(gè)步驟，比如某個(gè)步驟使用的索引類型，評(píng)估行數(shù)，IO信息，時(shí)間信息等。這些信息都可以用來(lái)參考，以確定該段sql語(yǔ)句的問(wèn)題在哪里。

參考當(dāng)前語(yǔ)句的估計(jì)的執(zhí)行計(jì)劃或?qū)嶋H的執(zhí)行計(jì)劃，分析當(dāng)前語(yǔ)句執(zhí)行時(shí)SQL Server 查詢優(yōu)化器所選擇的數(shù)據(jù)檢索方法。

實(shí)際的執(zhí)行計(jì)劃顯示了本次執(zhí)行所使用的執(zhí)行計(jì)劃。該圖應(yīng)該從右向左看，由下向上看，如果是多個(gè)表連接查詢的話，這里也會(huì)顯示多個(gè)執(zhí)行步驟，你可以檢查每一個(gè)步驟相關(guān)的操作相關(guān)信息，如IO開銷，CPU開銷，估計(jì)的行數(shù)，有沒(méi)有使用到Index，以及使用的何種Index等信息。行數(shù)過(guò)多則需要留意了。所使用的Indexl類型也是需要關(guān)注的信息之一。

下面是執(zhí)行計(jì)劃中一些概念的簡(jiǎn)單說(shuō)明：

工具提示項(xiàng)   說(shuō)明

Physical Operation        使用的物理運(yùn)算符，例如 Hash Join 或 Nested Loops。以紅色顯示的物理運(yùn)算符表示查詢優(yōu)化器已發(fā)出警告，例如丟失列統(tǒng)計(jì)信息或丟失聯(lián)接謂詞。這可能導(dǎo)致查詢優(yōu)化器選擇比預(yù)期的效率低的查詢計(jì)劃。有關(guān)列統(tǒng)計(jì)信息的詳細(xì)信息，請(qǐng)參閱使用統(tǒng)計(jì)信息提高查詢性能。

當(dāng)圖形執(zhí)行計(jì)劃建議創(chuàng)建統(tǒng)計(jì)信息、更新統(tǒng)計(jì)信息或創(chuàng)建索引時(shí)，使用 SQL Server Management Studio 對(duì)象資源管理器中的快捷菜單可以立即創(chuàng)建或更新丟失的列統(tǒng)計(jì)信息和索引。有關(guān)詳細(xì)信息，請(qǐng)參閱索引操作指南主題。

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

Estimated Row Size       操作符生成的行的估計(jì)大�。ㄗ止�(jié)）。

Estimated I/O Cost        用于執(zhí)行操作的所有 I/O 活動(dòng)的估計(jì)開銷。此值應(yīng)盡可能低。

Estimated CPU Cost       用于執(zhí)行操作的所有 CPU 活動(dòng)的估計(jì)開銷。

Estimated Operator Cost      用于執(zhí)行此操作的查詢優(yōu)化器的開銷。此操作的開銷以占查詢總開銷的百分比的形式顯示在括號(hào)中。由于查詢引擎選擇最高效的操作來(lái)執(zhí)行查詢或執(zhí)行語(yǔ)句，因此此值應(yīng)盡可能低。

Estimated Subtree Cost         查詢優(yōu)化器執(zhí)行此操作及同一子樹內(nèi)位于此操作之前的所有操作的總開銷。

Estimated Number of Rows           運(yùn)算符生成的行數(shù)。

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

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]