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 DROPCLEANBUFFERSGOSELECT * FROM NewOrdersSET STATISTICS IO OFF

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

SET STATISTICS IO ON--建立個測試表SELECT * INTO NewCLOrders FROM Orders--添加叢集索引CREATE CLUSTERED INDEX CL_OrderID ON NewCLOrders (OrderID desc)GO--先清空快取資料DBCC DROPCLEANBUFFERSGOSELECT * FROM NewCLOrdersSET 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 OrdersGROUP BY CustomerID

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

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

當然，此方式也是SQL Server認為的一種最優方式，但是如果資料量多的話，此種方式將會造成記憶體嚴重的消耗。

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

SELECT CustomerID,MAX(OrderDate) FROM OrdersGROUP BY CustomerIDOPTION(HASH GROUP)

當然，此處可能並不是一個最優的方式，只是為了示範，但是如果基礎資料量增大的話，我也相信SQL Server會自動的更改為雜湊匹配的方式進行。

 

 二、組合提示 （Hints）

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

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

SELECT O.OrderIDFROM Customers C JOIN Orders OJOIN Employees EON O.EmployeeID=E.EmployeeIDON C.CustomerID=O.CustomerIDWHERE C.City=N‘London‘ AND E.City=N‘London‘OPTION(FORCE ORDER,HASH JOIN)

不僅僅如此，我們還可以手動給查詢語句寫查詢計劃。

也就是我們自己寫的XML查詢計劃，讓T-SQL語句就按照我們自訂的查詢計劃去進行，當然，這是大招了，我們留在最後使用。

 

參考文獻

• 微軟聯機叢書邏輯運算子和物理運算子引用
• 參照書籍《SQL.Server.2005.技術內幕》系列

結語

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

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

 

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

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

SQL Server調優系列基礎篇

SQL Server調優系列基礎篇（常用運算子總結）

SQL Server調優系列基礎篇（聯合運算子總結）

SQL Server調優系列基礎篇（並行運算總結）

SQL Server調優系列基礎篇（並行運算總結篇二）

SQL Server調優系列基礎篇（索引運算總結）

SQL Server調優系列基礎篇（子查詢運算總結）

 

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

SQL Server調優系列進階篇（查詢最佳化工具的運行方式）

SQL Server調優系列進階篇（查詢語句運行幾個指標值監測）

SQL Server調優系列進階篇（深入剖析統計資訊）

SQL Server調優系列進階篇（如何索引調優）

SQL Server調優系列進階篇（如何維護資料庫索引）

 

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

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

 

如果您看了本篇部落格,覺得對您有所收穫，請不要吝嗇您的“推薦”。

 

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