SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大最佳化運行）

標籤：另一個   過濾   也會   name   關注   很多   傳值   條件   有序   

前言

本篇繼續玩轉模組的內容，關於索引在SQL Server的位置無須多言，本篇將分析如何利用Hint引導語句充分利用索引進行運行，同樣，還是希望紮實掌握前面一系列的內容，才進入本模組的內容分析。

閑言少敘，進入本篇的內容。

技術準備

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

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

一、並行Hint提示 （MAXDOP N Hint）

在當前多核超執行緒的今天，並行運算已經不算什麼稀罕了，所以在SQL Server中也有它自己的並行運算子，來充分的利用現有硬體資源，最大限度的提升運行效率。

在本系列中有兩篇文章專門介紹關於SQL Server的並行運算，可以點擊查看：SQL Server並行運算總結、SQL Server並行運算總結篇二

所以，在Hint中也給出了關於並行運算的提示：MAXDOP N Hint，這個Hint還是經常用的，尤其索引操作的時候，為了縮短操作時間，我們常常會最大限度的利用並行運算。

另外，此Hint會優先於資料庫層級的配置選項。也就說儘管在資料庫中設定了MAXDOP 1(強制順序運行)，如果使用了此Hint也會忽略資料庫設定的。

當然，並行運算雖然大部分情況能提升運行效率，但是也非絕對，我們知道多線程的操作是需要維護線程之間的資料交換和執行順序等，所有有時候多線程的執行並不一定會單線程效率高。

來看個例子：

SELECT [KEY],[DATA]FROM TestMaxDopTableWHERE DATA<1000OPTION(MAXDOP 1)SELECT [KEY],[DATA]FROM TestMaxDopTableWHERE DATA<1000OPTION(MAXDOP 4)

上面為串列運算，下面為4線程的並行運算。

當然，此處幾線程運算可以自己設定，最大值推薦為當前系統配置的邏輯核心數，當然設定大了也可以只不過沒用罷了。

 二、索引Hint提示 （INDEX  Hint）

所謂的索引Hint提示，就是強制查詢最佳化工具為一個查詢語句執行掃描或者使用一個指定的索引。

此方式，是我們在調優中經常用到的一種方式，很多時候我們建立的索引是失效的，當然，大部分情況下失效的原因是建立索引不妥當導致的，但是有一些情況下，需要我們來指導下T-SQL的運行方式，這時候就是索引Hint的使用情境了。

當然，這裡能利用索引提示的前提就是當前表存在索引了，如果是堆表的情況，根本就談不上了索引提示了，只能通過表掃描擷取資料了。

來看看這個提示的用法：WITH(INDEX(N))

這裡的N就是索引的在該表中索引順序排序號了，來看一張表中的索引序號：

SELECT * FROM SYS.indexesWHERE OBJECT_NAME(object_id)=‘Orders‘

可以看到，該表中存在十個索引，依次排序之後，就是從1至10，第一個就是叢集索引（主鍵）了，然後是非叢集索引。

所以，我們上面的N的值就是這個數字了，指定幾就是要求用哪個索引了。

來看個指令碼：

SELECT OrderID,CustomerIDFROM Orders WITH(INDEX(1))WHERE ShipPostalCode=N‘99362‘SELECT OrderID,CustomerIDFROM Orders WITH(INDEX(9))WHERE ShipPostalCode=N‘99362‘

看到了，上面的例子中我們選了兩個索引：一個編號1的叢集索引PK_Orders，一個編號為9的非叢集索引了ShipPostalCode。當然，有興趣也可以玩玩其它幾個索引。

我們順便來分析下這個語句的索引用法：

首先從查詢條件來看，我們是根據ShipPostalCode進行查詢，所以最好在該列中被索引所覆蓋，這樣在資料量大的情況下，查詢最佳化工具就可以採用索引尋找（Index Seek）了，所以，這裡我們選擇了第9個非叢集索引，恰巧覆蓋該列值，從上面的查詢計劃也可以看出，採用該索引Hint提示後查詢開銷從69%提升至3%...但是由於這個非叢集索引沒有包含CustomerID列，所以不得不又引入書籤尋找（key Lookup）來擷取該列值，並且這個書籤尋找消耗還比較大：60%，所以最佳的方式就是將該索引Include進CustomerID列。

當然，此方式用起來可能很不爽，因為我們在使用的時候需要尋找當前表中的各個索引的排序號。

所以，我們最推薦也是最常用的方式是這樣：

WITH(INDEX(‘IndexName‘))

 

就是我們直接指定索引名稱既可以，來看個例子：

SELECT OrderID,CustomerIDFROM Orders WITH(INDEX(CustomersOrders))WHERE ShipPostalCode=N‘99362‘

看起來，簡單的多了，因為索引的名字我們直接能看到，來看看我們將這個查詢語句指定到這個非叢集索引CustomersOrders上的執行計畫。

來看看這個查詢計劃：丫的！.....查詢開銷直接飆升到100%......原因很簡單：這個非叢集索引和這個查詢一毛錢關係....但是我們卻強制的指定該語句利用索引執行....

首先非叢集索引包含的列為：[OrderID]，[CustomerID]

我們要擷取的值為按照ShipPostalCode進行篩選，所以要擷取結果就必須按照這個非叢集索引進行一次掃描（Index Scan），這個還可以，畢竟非叢集索引都是有序進行的，但是為了進行過濾，就必須引入書籤尋找（Key Lookup）進行過濾，我們知道書籤尋找為隨機IO，消耗巨大，所以這次過濾就好比在整張表中隨機的去尋找資料一樣，其實效率還不如來一次表掃描（Table Scan）的好，所以此開銷飆升到95%！

 

上面的例子，也是很多新手容易犯的錯誤。

我記得在玩轉模組的第一篇中，我們提到過一個利用OPTIMIZE FOR Hint提示 解決一個引入參數而導致的執行計畫評估不準的問題。

文章可以點擊此處看到：SQL Server調優系列玩轉篇（如何利用查詢提示（Hint）引導語句運行）

我們來回顧下：

--普通的查詢語句SELECT OrderID,OrderDateFROM OrdersWHERE ShipPostalCode=N‘51100‘--參數化後的查詢語句DECLARE  @ShipPostalCode NVARCHAR(50)SET @ShipPostalCode=N‘51100‘SELECT OrderID,OrderDateFROM OrdersWHERE [email protected]

完全相同邏輯的查詢語句，只是下面那個我們通過參數進行了傳值操作。

我們只是加了一個參數，SQL Server將相同的查詢語句，有以前的索引尋找變成了索引掃描了！

消耗一下子從46%提升到54%.....這也是我們寫的語句經常遇到的問題，因為很多情況下，我們都是通過傳參來實現該語句的重用性。

但是，為什麼加了參數使得查詢效能變差，顯然不是一個好的方式，在第一篇的玩轉篇中，我們的解決方式是通過OPTIMIZE FOR Hint提示解決。

這裡，我們再來看一個解決方式，也可以通過索引Hint來強制該語句指定按照給定的索引進行尋找。

方法如下：

--參數化後的查詢語句DECLARE  @ShipPostalCode NVARCHAR(50)SET @ShipPostalCode=N‘51100‘SELECT OrderID,OrderDateFROM Orders WITH(INDEX(ShipPostalCode))WHERE [email protected]

是不是一個很帥的方式。

希望你能理解這些個方式的好處，算作拋磚引玉了。 

 

結語

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

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

SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大最佳化運行）