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

2024-08-31 00:54:52

字體：大中小

來源：轉載

供稿：網友

前言

本篇繼續玩轉模塊的內容，關于索引在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 FORHint提示解決一個引入參數而導致的執行計劃評估不準的問題。

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

我們來回顧下：

--普通的查詢語句SELECT OrderID,OrderDateFROM OrdersWHERE ShipPostalCode=N'51100'--參數化后的查詢語句DECLARE  @ShipPostalCode NVARCHAR(50)SET @ShipPostalCode=N'51100'SELECT OrderID,OrderDateFROM OrdersWHERE ShipPostalCode=@ShipPostalCode

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

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

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

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

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

方法如下：

--參數化后的查詢語句DECLARE  @ShipPostalCode NVARCHAR(50)SET @ShipPostalCode=N'51100'SELECT OrderID,OrderDateFROM Orders WITH(INDEX(ShipPostalCode))WHERE ShipPostalCode=@ShipPostalCode