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

發表於2015-03-19
SQLServer索引優化

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

—————–以下玩轉篇——————-

 

前言

本篇繼續玩轉模組的內容，關於索引在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也會忽略資料庫設定的。

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

來看個例子：

上面為序列運算，下面為4執行緒的並行運算。

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

二、索引Hint提示 （INDEX  Hint）

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

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

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

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

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

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

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

來看個指令碼：

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

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

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

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

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

WITH(INDEX(‘IndexName’))

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

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

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

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

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

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

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

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

我們來回顧下：

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

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

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

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

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

方法如下：

是不是一個很帥的方式。

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

 

結語

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

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

 

文章最後給出前面幾篇的連線，以下內容基本涵蓋我們日常中所寫的查詢運算的分解以及調優內容項，皆為原創……..

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

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

第三個玩轉模組重點跟進特定的問題進行特定的提示（Hints)，基於前兩個模組進行的分析。

相關文章