SQL Server調優系列玩轉篇（如何利用查詢提示（Hint）引導語句執行）

發表於2015-01-21
SQLServer

—————–以下進階篇——————-

 

前言

前面幾篇我們分析了關於SQL Server關於效能調優的一系列內容，我把它分為兩個模組。

第一個模組注重基礎內容的掌握，共分6篇文章完成，內容涵蓋一系列基礎運算演算法，詳細分析瞭如何檢視執行計劃、掌握執行計劃優化點，並一一列舉了日常我們平常所寫的T-SQL語句所會應用的運算子。我相信你平常所寫的T-SQL語句在這幾篇文章中都能找到相應的分解運算子。

第二個模組注重SQL Server執行T-SQL語句的時候一些內幕解析，共分為5篇文章完成，其中包括：查詢優化器的執行方式、執行時幾個優化指標值檢測，統計資訊、利用索引等一系列內容。通過這塊內容讓我們瞭解SQL Server為我們所寫的T-SQL語句如何進行優化及執行的。

從本篇進入第三個模組的內容，該篇為第一篇，該模組主要讓我們來指導SQL Server進行定向調整，達到優化的目的。本模組的內容是以前面一系列內容為前提的，希望充分掌握了前面基礎內容，方能進入本模組內容。

技術準備

資料庫版本為SQL Server2012，利用微軟的以前的案例庫（Northwind）進行分析，部分內容也會應用微軟的另一個案例庫AdventureWorks。

相信瞭解SQL Server的朋友，對這兩個庫都不會太陌生。

概念理解

談到hint，其實概念很簡單，正如詞義理解：提示，也就是說讓我們通過給予SQL Server提示（hint）讓資料庫執行時按照我們的思路進行，我估計很多不怎麼了解SQL Server的童鞋都不怎麼知道，因為一般應用的不多。

其實，SQL Server本身的查詢優化器已經做到很好了，所以大部分情況下不需要我們人工干預，自己就能執行的很好，並且最大限度的優化執行項。但是，俗話說：老虎也有打盹的時候，所以，在有些場景下，就需要我們來給資料庫指導一個方向，讓其執行的更流暢。

但是，記住了：你所應用的hint是在現在的場景中給予現有的環境下，相對是一個好的方式，不能確保你所給予的提示（Hint）永久有效，並且隨著時間推移，資料量的變更，你所發出的提示（Hint）有可能會成為資料庫優化的絆腳石。所以沒有充分的把握不要輕易使用Hint，並且最好採用目標導向Hint。

Hint主要分為三類應用：查詢Hint、表Hint、連線Hint。查詢Hint影響整個查詢，主要應用於查詢語句優化，本篇主要分析查詢Hint。

表Hint影響查詢引用的單個表，而連線Hint影響一個單獨的連線。

Hint應用方式分為兩類：目標導向Hint和物理運算子Hint。

目標導向Hint傳遞邏輯的目標給優化器，而不會具體指定優化器應該如何達到這個目標，應該使用什麼物理運算子，或者如何排列這些運算子。所以這種運算子使我們所推薦的，原因很簡單：我告訴丫按照這個思路執行就可以，至於怎麼達到，自己想辦法！這種方式從長期看對於資料庫的影響會小很多。

另外一個就是物理運算子，此方式就更直接了：直接告訴丫的步驟，你按照這個去做就行。這種方式不推薦，原因很簡單：你的思路暫時會是好的，但是過段時間就不好了。

 

一、查詢提示（Hint）

首先，查詢提示（Hint）是我們在調優中應用最廣泛的，因為大部分時間我們是在調整查詢的效能。

關於查詢中的優化選項就是在指導SQL Server的連線型別、聚合型別、聯合型別等物理連線運算子。關於此塊的詳細解析，可以參照我調優系列中前幾篇文章，分析的相當的詳細。

a、FAST N Hint提示

關於此方式的提示，我在前面的文章中已經有使用到，在介紹索引那篇文章中，可以點選這裡檢視。

首先，這個Hint是一個目標導向hint。提示目標很簡單：告訴資料庫給我速度出前N行資料就可以，而其它的資料你愛咋地咋地。

這個提示最優的應用環境就是：應用系統中的分頁查詢，當然其它環境可以用。有點類似於SELECT  TOP  N….

其次，在我們的應用環境中，尤其資料量多的情況下，如果這時候我們的場景是：我想速度的看到前面的部分資料，其它的資料你可以稍後再顯示，但是在執行T-SQL的時候，SQL Server會多方面的考慮耗費（cost），然後再平衡各種利弊選擇出它認為相對好的執行計劃去執行，顯然這種方式獲取資料的方式是很浪費的，並且速度就會相對慢很多。

所以，我們利用FAST N Hint提示，這樣，SQL Server會阻止優化器使用雜湊連線、雜湊聚合、排序、甚至是並行這些大消耗的動作，而轉變成為這N條資料做快速的優化並輸出。這在大資料量的情況下，是一種非常高明的方式。

來個例子：

 

簡單的查詢，並且按照OrderDate排序，不看執行計劃，我們就已經推測出這個執行計劃中最耗損的就是這個OrderDate了，排序永遠是高耗損，這也是為什麼各種型別的索引都要提前排序的原因。

然後，我們再來看一下加上這個FAST N Hint提示的執行

為了快速獲取這一行資料，利用HINT後，改為了索引掃描+書籤查詢，因為這是獲取一條資料的最優的一種方式。

因為資料量的關係，所以我上述演示沒能很好的表現出FAST 提示的優越性來，其實在實際生產中，在面臨龐大的資料量的時候，一般利用FAST N提示獲取出部分資料之後，就不再繼續執行了，因為我們關注的就是這一部分資料。

當然，此HINT也有弊端：在快速獲取前N行結果之後，可能會延遲整個查詢的總體相應時間。也就是說，儘管FAST N HINT可能會使優化器快速產生前N個輸出計劃。但是它會使優化器產生一個在結束最後一行前花費更多時間，消耗更多CPU，甚至於更多IO。

 

b、OPTIMIZE FOR Hint提示

此HINT是一種非常有用的提示，也是我們在日常中經常使用的。

這個HINT目標很簡單：告訴優化器目標以Hint值進行分配或者執行。此Hint提示是從SQL Server2005版本以上開始支援，能夠根據指定的引數值產生一個計劃，尤其適用於非對稱資料集中，因為這種資料集中資料分佈不均勻，不同的引數值可能導致不同的基數評估和不同的查詢計劃，我們可以從不同的引數中選擇一個最優的執行計劃，作為後續不同引數的執行計劃，避免了SQL  Server的重新評估和重編譯的耗費的動作。

來個例子：

此語句很簡單，就是通過查詢郵政編碼（ShipPostalCode），獲取出訂單ID和訂單日期。

來看這個查詢語句，最理想的情況就是直接通過索引查詢（index seek）動作獲取出資料。其實最好的方式也是通過INCLUDE將兩列值包含進去。

我們來看一下實際的執行計劃：

SQL Server通過了索引查詢+書籤查詢方式獲取，這種方式也湊合吧，其實我們還可以繼續優化。

但是，這不是問題重點，問題重點是該段T-SQL一般我們會利用引數進行查詢或者包裝成儲存過程通過傳參呼叫。是吧？？不會你永遠只查詢一個固定值吧….來看語句

 

是吧，這種方式才能做到重用嘛，不過包裝成一個儲存過程或者一個函式等，估計核心程式碼肯定就這樣子了。

來看看生成的執行計劃：

 

本來很爽的非聚集索引查詢（Seek)，通過我加了一個引數之後變成了聚集索引掃描（Scan）了，聚集索引掃描的效能跟表掃描基本一樣，沒有啥質的提高！

如果該表資料量特別大的話，我們為該語句設計的非聚集索引就失效了。只能通過依次掃描獲取資料了。有意思嗎？？？沒意思！！！

怎麼解決呢？這就是我們此處提到Hint出場的時候了，告訴資料庫：丫就按照執行 “51100” 的查詢一樣去執行我傳過來的引數。

看到了，這裡又迴歸了快速的非聚集索引查詢（Seek）狀態，並且不受限制於傳過來的引數是啥。

這個提示只是告訴SQL Server查詢按照這個目標值進行操作，並不會實際影響結果值。

當然上面的問題，如果封裝成儲存過程的時候，可以採用重編譯的方式解決，但是相比利用Hint的方式，重編譯帶來的消耗遠大的多。尤其高併發的環境下重編譯所帶來CPU消耗是非常高的。

 

c、物理連線提示（Hint)

關於物理連線我們在前面的文章中已經詳細的分析了，在SQL Server中共分為三種物理連線方式：巢狀迴圈、合併、雜湊連線。

詳細的內容可以參照我的基礎篇中的連結：SQL Server調優系列基礎篇（常用運算子總結——三種物理連線方式剖析）

文章中對三種連線的利弊進行了詳細的對比，並且對三種連線的使用環境進行了詳細的介紹。但是，有時候SQL Server為我們評估的連線並不是最優的，或者說並不是符合我們的要求，這時候，就要利用我們的物理連線提示進行指導。

總共分為三種查詢級別的連線Hint，正好對應三種物理連線運算子，依次是:LOOP JOIN、MERGE JOIN 和 HASH JOIN

在應用時候，可以指定一個或者多個，如果指定一個，那麼查詢計劃中的全部連線使用指定的連線型別，如果指定兩個，SQL Server會在這兩個連線型別中選擇最好的一個，也就是斃掉了第三個。

應用場景蠻多的，根據三種連線的特性，我們可以有選擇的進行提示，比如我們想一個查詢不消耗記憶體，那麼就可以指定OPTION(LOOP JOIN，MERGER JOIN)，這樣就去掉消耗記憶體的雜湊連線，當然這是減小記憶體消耗但會增加執行時間。如果採用了合併連線（MERGER JOIN）方式不會消耗記憶體，但是合併連線需要提前排序（sort），排序會消耗大量的記憶體。

當然，有時候巢狀迴圈連線執行的時間不理想，就可以指定為雜湊連線（hash join）進行連線。

來看個例子：

上面的查詢計劃採用了巢狀迴圈的連線方式，兩張表依次進行迴圈巢狀執行。

如果，經過測試這裡發現採用合併連線的方式更好一點，我們可以採用如下Hint進行提示操作

經過調整之後，這時候該語句就利用到了我們設計的非聚集索引，並且由原來的索引SCAN變成了索引Seek運算。

通過如下方式，可以指導SQL Server在雜湊連線和合並連線之間做出選擇，但是一定要放棄巢狀迴圈連線。

看以看到，經過評估SQL Server還是依然的選擇了合併連線

其實，這個很正常，首先資料量不大，其次是在City列上存在非聚集索引，所以要充分利用，並且在兩張表的CustomerID是都為索引所覆蓋，這就保證了兩張表在這列上都是預先排序(sort)了，這完全滿足了合併連線的條件。當然，預設選擇巢狀迴圈連線的原因，我估計的原因就一個：兩張表資料量不大。

當然，出來上面的HINT方式可以指定連線的物理連線方式，還有另外更為粗暴的一種方式，強制執行。如下：

當然，這種方式也手動的達到了指定採用合併連線的方式。

但是，此種方式有嚴重的弊端：

1、通過採用這種方式貌似暫時解決問題了，但是經過一段時間，此連線方式可能會嚴重阻礙資料庫的優化，而要解決此問題就不得不更改程式碼。

2、只能粗暴的指定一種物理連線方式，不能順應SQL Server本身自己的優化策略。

上述的方式是非常不推薦的一種，大部分新手會選擇這種方式。

當然，利用Hint的方式是並非一種萬全之策，但在當前基本能解決問題，當執行到一段週期之後，如果當前的HINT干預了SQL Server資料庫的正常執行，我們也可以採用適當的方式予以停用Hint。使資料庫得到完美的平穩的正常執行。後續文章我們依次介紹。

關於Hint這塊的使用，內容還是挺多的，其中一部分還包含鎖提示等，後續文章我們依次介紹，有興趣的童鞋提前關注。

其實Hint是平常我們調優時候一種重要的工具。但是，這個工具的正確的使用則要依靠牢靠的基礎知識掌握和經驗累積。正所謂：厚積薄發！ 不要輕易的看到了使用場景就妄自的進行盲目的使用。如果使用不當，還會擾亂SQL Server資料庫本身正常的生態環境，得不償失，越調越亂。

所以：施主，三思而行呀……

 

參考文獻

結語

此篇文章先到此吧，關於SQL Server調優工具Hint的使用還有很多內容，後續依次介紹，有興趣的童鞋可以提前關注。

有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。

相關文章