SQL Server調優系列玩轉篇二（如何利用匯聚聯合提示（Hint）引導語句運行）

文章出處

前言

上一篇我們分析了查詢Hint的用法，作為調優系列的最后一個玩轉模塊的第一篇。有興趣的可以點擊查看：SQL Server調優系列玩轉篇（如何利用查詢提示（Hint）引導語句運行）

本篇繼續玩轉模塊的內容，同樣，還是希望扎實掌握前面一系列的內容，才進入本模塊的內容分析。

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

技術準備

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

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

誤區糾正

在開始本篇文章主題內容之前，先糾正一些關于新手對于數據庫調優的誤區。也希望在日常應用解決問題的時候，切記道聽途說，人云亦云，毛爺爺說過的：實踐是檢驗真理的唯一標準。

兩個誤區：

1、當在查詢計劃中發現了表掃描（Table Scan），就仿佛找到了病根一樣，就想搞掉它，因為很多過來人都說過這種方式是性能很爛，而搞掉它的方式就是上索引，而且認為有了索引的就會快很多。

2、SQL Server語句優化就是創建索引，而創建索引就更簡單了，找找查詢語句，看看Where條件后...有幾個篩選條件，創建幾個非聚集索引就可以。

來看第一個問題：關于表掃描（Table Scan）是否真像傳說的性能那么差勁！

首先，我們知道比較查詢語句性能的優越性，無非就幾個關鍵指標：運行IO、運行時間、消耗：CPU、Memory、編譯時間等。

來看，以下語句，完全相同的表結構、表數據，不同的是一張表是堆表，一張是加了聚集索引的表，我們來開啟兩個回話進行測試比較。

堆表查詢：

SET STATISTICS IO ON
--新建個測試表
SELECT * INTO NewOrders FROM Orders
--先清空緩存數據
DBCC DROPCLEANBUFFERS
GO
SELECT * FROM NewOrders
SET STATISTICS IO OFF

存在聚集索引的測試表查詢：

SET STATISTICS IO ON
--新建個測試表
SELECT * INTO NewCLOrders FROM Orders
--添加聚集索引
CREATE CLUSTERED INDEX CL_OrderID ON NewCLOrders (OrderID desc)
GO
--先清空緩存數據
DBCC DROPCLEANBUFFERS
GO
SELECT * FROM NewCLOrders
SET STATISTICS IO OFF

這樣對比的原因是：很多人認為數據庫優化的方式就是加上索引，并且認為查找（Seek）就比掃描好（Scan）。

這樣可以肯定的堆表采用的為表掃描（Table Scan），而后者則就是通過聚集索引掃描（Index Scan）

先來看IO的兩者對比：

先來看看堆表的IO信息

堆表的表現：邏輯讀取20次，預讀2次，這里預讀次數的多少其實是影響性能的重要指標，因為它是直接從磁盤中讀取，所以性能最差，當然SQL Server此處采用并行處理，而且第一次讀取數之后就緩存到內存中，防止再次的磁盤交互。

再來看聚集索引表的IO信息

采用聚集索引的表，邏輯IO為23次，預讀飆升至22次。

相比而言：

相同的查詢語句，堆表的查詢邏輯讀取次數為20次、預讀2次，沒有物理讀...，而用聚集索引的表邏輯讀為23次、預讀22次！還有統計信息的不準確導致的物理讀取1次！....所以相比堆表的SCAN是不是性能好很多。

當然，要再深入點分析，其實這兩者不同的原因很簡單：采用了聚集索引的表因為其存儲的結構（B-Tree）的方式，所以邏輯IO肯定要多3，因為從索引根節點至葉子節點，也就是需要經過三個索引頁，才能獲取到數據頁。

而預讀的差距這么大的原因也是同樣的原因：從堆表中獲取數據是一段連續的數據頁（確切的說是一次連續讀取64個數據頁<512KB>），而這時所有加上索引的表做不到的！通過索引只能依次讀取一個數據頁（8KB）,這也是索引的局限性。

關于查詢計劃的邏輯讀、預讀、物理讀等IO詳細邏輯信息，可以參照我前面的文章，分析的很詳細：SQL Server調優系列進階篇（查詢語句運行幾個指標值監測）

接著我們再對比下執行時間，相信這個也是更為關注的：

看明白了吧，獲取完全相同的數據量，堆表執行的時候耗時157毫秒，并且分析和編譯沒有占用時間，這個很簡單，因為它是堆表，根本不需要根據統計信息進行優化和選擇；而加上聚集索引的表就不一樣了，需要根據索引的統計信息對T-SQL語句進行優化和編譯，而這足足耗費了79毫秒，然后執行的時候還需要更多的預讀IO,還有如果優化器沒有優化到位的時候，還要造成額外的物理IO，所以它總耗費了298毫秒...

在我的測試表中只有八百多行的數據中就產生了如此的差距值..如果數據量多的話...性能就堪憂了....

關于CPU和內存值我就不截圖了......上面我們分析了加了聚集索引了，就產生了查詢優化器一系列的過程...而編譯就是需要CPU資源的.....

通過上面的結果，我要表達的是：

首先，在我們所看到的查詢計劃中，不要一看到表掃描，就感覺這個運算符是很慢的，或者是很耗時的。更有甚者看到了就感覺問題出現在這上面，并且為很多人所唾棄為“萬惡的表掃描”....

其次，請記住，在SQL Server中你所看到的任何一個運算符，都是在目前你所設定的環境中基本是最好的....更沒有那個運算符好與那個運算符爛一說...諸如偏執的認為哈希連接就比嵌套循環要快...索引查找就比索引掃描要好等問題.....我們要做的就是合適的場景運用合適的處理方式，最優的順應SQL Server性能。

再次，經過上述了問題的分析，也不要陷入另外一個極端的誤區：表掃描就要比聚集索引掃描好！后續的文章中我會給你展示聚集索引比表掃描好的用處...在SQL Server的世界中，只有你真正的觸摸的本質，才不會迷茫...才會看清一切所謂的教條調優都非絕對！

關于第二個問題的誤區，其實是很多人的誤區，誤認為了非聚集索引的強大性，誤以為在列中加上了索引就可以充分應用。本篇就不糾正了，可以參照我前面的文章，相信看完了基本也就懂了非聚集索引的利弊項，連接：SQL Server調優系列進階篇（如何索引調優）

一、GROUP 提示（Hints）

繼續咱們本篇文章的內容，上一篇我們分析了查詢的幾個重要的Hints,本篇文章我們來看分組提示，分組查詢也是我們在寫T-SQL語句經常用到的，關于分組的運算符也有兩個：Order Group和Hash Group。其實關于排序一直也是數據庫中最為頭疼的運算。這個運算符也是消耗比較大的，相當的耗內存，如果數據量較大的話，SQL Server處理的方式也是通過哈希算法進行優化。

當然，關于分組查詢運算符分解，看以參照基礎篇中的文章：SQL Server調優系列基礎篇（常用運算符總結）

來看個例子：

SELECT CustomerID,MAX(OrderDate) 
FROM Orders
GROUP BY CustomerID

上面的查詢語句，我們想獲取出每個訂單的最大訂單日期。

通過查詢計劃我們可以推測出肯定在CustomerID列存在索引，這樣SQL Serer能直接利用這個進行排序，但是即便如此消耗還是飆升到56%....然后通過再加上一個流聚合計算出最大訂單日期。

當然，此方式也是SQL Server認為的一種最優方式，但是如果數據量多的話，此種方式將會造成內存嚴重的消耗。

所以，我們可以采用GROUP Hint進行提示，將其更改為Hash 分組..代碼如下：

SELECT CustomerID,MAX(OrderDate) 
FROM Orders
GROUP BY CustomerID
OPTION(HASH GROUP)

當然，此處可能并不是一個最優的方式，只是為了演示，但是如果基礎數據量增大的話，我也相信SQL Server會自動的更改為哈希匹配的方式進行。

二、組合提示（Hints）

大部分情況下，我們所寫的T-SQL語句并不是簡單的，有很多的各種嵌套查詢進行，如果這種查詢語句，我們的提示（Hints）就可能不是單一的。

我們來看如此方式該如何進行指導。先來看個簡單的例子：

SELECT O.OrderID
FROM Customers C JOIN Orders O
JOIN Employees E
ON O.EmployeeID=E.EmployeeID
ON C.CustomerID=O.CustomerID
WHERE C.City=N'London' AND E.City=N'London'
OPTION(FORCE ORDER,HASH JOIN)