SQL Server 效能調優3 之索引（Index）的維護

標籤：des   style   blog   http   io   ar   os   使用   sp   

SQL Server 效能調優3 之索引（Index）的維護

 

熱度1 評論 16

溪溪水草  

 

SQL Server 效能調優3 之索引（Index）的維護

前言

前一篇的文章介紹了通過建立索引來提高資料庫的查詢效能，這其實只是個開始。後續如果缺少適當的維護，你先前建立的索引甚至會成為拖累，成為資料庫效能的下降的幫凶。

尋找片段

消除片段可能是索引維護最常規的任務，微軟官方給出的建議是當片段等級為 5% - 30% 之間時採用 REORGANIZE 來“重整”索引，如果達到 30% 以上則使用 REBUILD 來“重建”索引。決定採用何種手段和操作時機可能需要考慮許多的因素，以下4條是你必須要考慮的：

 

• 備份的計劃
• 伺服器的負載
• 磁碟剩餘空間
• 回複(Recovery) 模型

 

PS：雖然片段與效能緊密相關，但某些特定情況下他可以被忽略。比如你有一張帶有叢集索引的表，幾乎所有針對該表的處理僅僅是根據主鍵取出一條資料。該場合下片段的影響可以忽略不計。

那麼怎樣確定某個索引的片段狀況呢？使用系統函數sys.dm_db_index_physical_stats 及系統目錄 sys.Indexes，樣本指令碼如下：

 

-- 擷取指定表（樣本：ordDemo）上所有索引的資訊SELECT  sysin.name as IndexName  ,sysIn.index_id  ,func.avg_fragmentation_in_percent  ,func.index_type_desc as IndexType  ,func.page_countFROM  sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N‘ordDemo‘), NULL, NULL, NULL) AS funcJOIN  sys.indexes AS sysInON  func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id-- 叢集索引的 Index_id 為 1-- 非叢集索引為 Index_id>1-- 以下指令碼用 WHERE 子句進行了篩選（剔除了沒有索引的表）-- 該指令碼返回資料庫所有的索引，可能花費較長時間！SELECT  sysin.name as IndexName  ,sysIn.index_id  ,func.avg_fragmentation_in_percent  ,func.index_type_desc as IndexType  ,func.page_countFROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS funcJOIN  sys.indexes AS sysInON  func.object_id = sysIn.object_id AND func.index_id = sysIn.index_idWHERE sysIn.index_id>0;

輸出如下

 

樣本資料庫的片段為0，這是因為片段是在執行增刪改時產生的，我們的資料庫還沒有做過類似操作。

填滿因數

前面提到過資料以8KB 資料頁的方式存放在資料庫中，假設你有一張建立了叢集索引的表，每當有資料插入時，資料庫會根據主鍵找到插入位置（資料頁）並寫入資訊。如果該資料頁已經滿了或者不夠空間存放新的資料，資料庫會建立一個新的8KB 資料頁，而這個建立的過程會造成I/O消耗。

填滿因數用來減少這種情況的發生，如果你設定填滿因數為10，那麼你的資料初始僅使用8KB 資料頁中的10%，當插入新紀錄時基本不用擔心會發生多餘的I/O消耗，因為資料頁中預留了90%的空間。

填滿因數也是把雙刃劍，他在增加寫操作效能的同時，降低了讀操作的效能。

【填滿因數僅當建立索引或重建（rebuildi）索引時起作用，對於一般的DML操作無效（資料頁總是填充到100%）】

以下指令碼協助你瞭解索引的填滿因數值：

 

SELECT  OBJECT_NAME(OBJECT_ID) AS TableName  ,Name as IndexName  ,Type_Desc  ,Fill_FactorFROM  sys.indexesWHERE  -- 這裡通過WHERE篩選來僅僅表示叢集索引和非叢集索引  type_desc<>‘HEAP‘

你還可以查看資料服務器上預設的填滿因數值：

 

 

SELECT  Description  ,Value_in_useFROM  sys.configurationsWHERE  Name =‘fill factor (%)‘

PS：0表示不保留任何預留空間。

通過以下指令碼來設定填滿因數的值：

 

 

ALTER INDEX [idx_refno] ON [ordDemo]REBUILD WITH (FILLFACTOR= 80)GO-- 如果要設定伺服器上的預設值，使用以下指令碼Sp_configure ‘show advanced options‘, 1GORECONFIGUREGOsp_configure ‘fill factor‘, 90GORECONFIGUREGO

在一張靜態表（偶然更新）的表上建議採用較大的填滿因數（90%以上），在讀寫頻繁的表上建議採用較低的填滿因數（70% - 80%）。特別的，當你的叢集索引建立在一個自增欄位上時，設定填滿因數為100%也沒有問題，因為新插入的資料總是在所有資料的最後，不會發生插入記錄與記錄之間的情況。

 

重建（REBUILD）索引來提高索引效率

重建索引的作用顧名思義，他帶來的好處包括消除片段，統計值(statistics)更新，資料頁中物理排序次序的對齊。另外他還會根據填滿因數來壓縮資料頁，（如果必要的話）新增資料頁。好處一籮筐，只是這個操作非常耗資源，會花費相當長的時間。如果你決定開始重建索引，你還需要知道他有兩種工作模式：

離線模式：這是預設的重建索引模式，它將鎖定表直到重建完成。如果表很大，會導致使用者（好幾個小時都）無法使用該表。相比線上模式來說離線模式工作更快，消耗的TempDb的空間更小。

線上模式：如果客觀條件不允許你鎖定表，你就只能選擇線上模式，這將耗費更多的時間和伺服器資源。值得一提的是如果你的表包含了varchar (max), nvarchar (max), text 類型欄位的話，將無法在該模式下進行重建索引。

【提示：該模式選擇僅在開發版/企業版中支援，其他版本預設使用離線模式！】

以下是重建索引的樣本指令碼：

 

-- 線上模式下重建索引 idx_refnoALTER INDEX [idx_refno] ON [ordDemo]REBUILD WITH (FILLFACTOR=80, ONLINE=ON)GO-- 離線模式下重建索引 idx_refnoALTER INDEX [idx_refno] ON [ordDemo]REBUILD WITH (FILLFACTOR=80, ONLINE=OFF)GO-- 重建 ordDemo 表上的所有索引ALTER INDEX ALL ON [ordDemo]REBUILD WITH (FILLFACTOR=80, ONLINE=OFF)GO-- 重建索引 idx_reno （DROP_EXISTING=ON）CREATE CLUSTERED INDEX [idx_refno] ON [ordDemo](refno)WITH(DROP_EXISTING = ON,FILLFACTOR = 70,ONLINE = ON)GO-- 使用 DBCC DBREINDEX 重建 ordDemo 表上的所有索引DBCC DBREINDEX (‘ordDemo‘)GO-- 使用 DBCC DBREINDEX 重建 ordDemo 表上的一個索引DBCC DBREINDEX (‘ordDemo‘,‘idx_refno‘,90)GO

 

【DBCC DBREINDEX 將在後續版本被廢棄】

基於作者的個人經驗，在一張大資料量的表上進行重建操作時，使用批量日誌恢複（bulk-logged recovery）或簡單恢複（simple recovery）比較好，這能防止記錄檔過大。不過需要提醒你的是，切換復原模式時會打斷資料庫的備份鏈，所以如果你之前是完全復原模式（full recovery），記得重建後再切換回來。

重建時一定要有耐心，長的可能花上1天，冒昧地打斷他是非常危險的（資料庫可能進入復原模式）。

執行該操作的使用者必須是該表的所有者，或是該伺服器的sysadmin一員，或是該資料庫的db_owner / db_ddladmin。

 

重整（REORGANIZE）索引來提高索引效率

 

重整不會鎖定任何對象，他是一個最佳化當前 B-Tree，組織資料頁的處理及磁碟重組。重整索引處理樣本指令碼如下：

 

-- 重整 "ordDemo" 表上的 "idx_refno" 索引ALTER INDEX [idx_refno] ON [ordDemo]REORGANIZEGO-- 重整 ordDemo 表上所有索引ALTER INDEX ALL ON [ordDemo]REORGANIZEGO-- 重整 AdventureWorks2012 資料庫中 ordDemo 表上所有索引DBCC INDEXDEFRAG (‘AdventureWorks2012‘,‘ordDemo‘)GO-- 重整 AdventureWorks2012 資料庫中 ordDemo 表上索引 idx_refnoDBCC INDEXDEFRAG (‘AdventureWorks2012‘,‘ordDemo‘,‘idx_refno‘)GO

 

注意：執行該操作的使用者必須是該表的所有者，或是該伺服器的sysadmin一員，或是該資料庫的db_owner / db_ddladmin。

 

發現缺失的索引

現在你已經瞭解索引帶來的效能提升，但實際情況下很難在一開始就建立好足夠正確及必要的索引，我們要怎樣才能判斷出哪些表需要索引，哪些索引建立得不對呢？

通常情況下，SQL Server 會利用既有的索引來執行查詢指令碼，如果沒有找到索引他會自動產生一個並存放在DMV（dynamic management view）中。每當SQL Server 服務重啟的時候這些資訊會被清除，所以在擷取缺失索引的過程中最好保持SQL Server 服務的運行，直到所有的商務邏輯跑完一遍。

可參照以下連結來擷取更多相關資訊：

 

• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_columns(Index_Handle)

 

提供一個現成的指令碼：

 

SELECT  avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)    AS PossibleImprovement  ,last_user_seek  ,last_user_scan  ,statement AS Object  ,‘CREATE INDEX [IDX_‘ + CONVERT(VARCHAR,GS.Group_Handle) + ‘_‘ +  CONVERT(VARCHAR,D.Index_Handle) + ‘_‘    + REPLACE(REPLACE(REPLACE([statement],‘]‘,‘‘),‘[‘,‘‘),‘.‘,‘‘) +    ‘]‘    +‘ ON ‘    + [statement]    + ‘ (‘ + ISNULL (equality_columns,‘‘)    + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS      NOT NULL THEN ‘,‘ ELSE ‘‘ END    + ISNULL (inequality_columns, ‘‘)    + ‘)‘    + ISNULL (‘ INCLUDE (‘ + included_columns + ‘)‘, ‘‘)  AS Create_Index_SyntaxFROM  sys.dm_db_missing_index_groups AS GINNER JOIN  sys.dm_db_missing_index_group_stats AS GSON  GS.group_handle = G.index_group_handleINNER JOIN  sys.dm_db_missing_index_details AS DON  G.index_handle = D.index_handleOrder By PossibleImprovement DESC

PS：你擷取到的資訊是一個提議列表，最終的決定權在你，另外DMV最多隻儲存500個索引。

 

 

發現未使用的索引我們建立了索引來改進效能，但如果建立的索引沒有被利用到，那反而成累贅了。

 

與前一個小節相同的原因，保持SQL Server 服務的運行，直到所有的商務邏輯跑完一遍。運行一下指令碼：

 

SELECT  ind.Index_id,  obj.Name as TableName,  ind.Name as IndexName,  ind.Type_Desc,  indUsage.user_seeks,  indUsage.user_scans,  indUsage.user_lookups,  indUsage.user_updates,  indUsage.last_user_seek,  indUsage.last_user_scan,  ‘drop index [‘ + ind.name + ‘] ON [‘ + obj.name + ‘]‘ as    DropIndexCommandFROM  Sys.Indexes as indJOIN  Sys.Objects as objON  ind.object_id=obj.Object_IDLEFT JOIN  sys.dm_db_index_usage_stats indUsageON  ind.object_id = indUsage.object_idAND  ind.Index_id=indUsage.Index_idWHERE  ind.type_desc<>‘HEAP‘ and obj.type<>‘S‘AND  objectproperty(obj.object_id,‘isusertable‘) = 1AND  (isnull(indUsage.user_seeks,0) = 0AND  isnull(indUsage.user_scans,0) = 0AND  isnull(indUsage.user_lookups,0) = 0)ORDER BY  obj.name,ind.NameGO

擷取這些資訊後，採取怎樣的行動由你決定。但是當你決定刪除某個索引時請注意以下兩點：

 

 

• 如果當前索引是個主鍵或唯一鍵，他能保證資料的完整性
• 唯一索引即使本身並沒有被使用，但能給最佳化器提供資訊，從而協助它產生更好的執行計畫

 

建立索引檢視表（indexed view）來改善效能

視圖是個儲存的查詢，表現得像表一樣。它有兩個主要好處：

 

• 限制使用者只能訪問某幾張表中特定欄位及特定資料
• 允許開發人員通過自訂的方式把原始資訊組織成面向使用者的邏輯視圖

 

索引檢視表在建立時就解析/最佳化好查詢語句，並把相關資訊以物理形式存放在資料庫中。再決定使用索引檢視表前請考慮以下建議：

 

• 視圖不應該參照其他視圖
• 試圖可以參照任何原始表
• 欄位名必須顯式明確的定義好合適的別名

 

另外如果針對該對象的處理查詢少更新多，又或者原始表是個經常更新的表，那麼使用索引檢視表並不是很合適。

如果你有個查詢包含較多的合計（aggregation）/聯合（join）而且表的資料量很大，那麼可以考慮使用索引檢視表。使用索引檢視表必須設定以下參數（NUMERIC_ROUNDABORT為OFF，其餘為ON）

 

• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER
• ANSI_WARNINGS
• ANSI_NULLS
• ANSI_PADDING
• NUMERIC_ROUNDABORT

 

樣本指令碼：

 

CREATE VIEW POViewWITH SCHEMABINDINGASSELECT  POH.PurchaseOrderID  ,POH.OrderDate  ,EMP.LoginID  ,V.Name AS VendorName  ,SUM(POD.OrderQty) AS OrderQty  ,SUM(POD.OrderQty*POD.UnitPrice) AS Amount  ,COUNT_BIG(*) AS CountFROM  [Purchasing].[PurchaseOrderHeader] AS POHJOIN  [Purchasing].[PurchaseOrderDetail] AS PODON  POH.PurchaseOrderID = POD.PurchaseOrderIDJOIN  [HumanResources].[Employee] AS EMPON  POH.EmployeeID=EMP.BusinessEntityIDJOIN  [Purchasing].[Vendor] AS VON  POH.VendorID=V.BusinessEntityIDGROUP BY  POH.PurchaseOrderID  ,POH.OrderDate  ,EMP.LoginID  ,V.NameGO-- 在視圖上建立一個叢集索引使得它成為使得它成為索引檢視表CREATE UNIQUE CLUSTERED INDEX IndexPOView ON POView(PurchaseOrderID)GO

你可以對比一下查詢語句與查詢索引檢視表的執行計畫，索引檢視表的方式提供了更好的查詢效能：

 

SQL Server 的查詢最佳化工具總是嘗試找到最佳的執行計畫，有時候雖然你建立了索引檢視表，但最佳化器依然使用了原始表上的索引，此時你可以使用 WITH NOEXPAND 來強制使用索引檢視表上的索引（而不是原始表上的索引）。

索引檢視表在 SQL Server 2012 的各個版本上都有支援，在開發版或企業版中查詢處理器甚至能以此來把匹配索引檢視表的查詢都最佳化了。

索引檢視表建立時必須帶上 WITH SCHEMABINDING，以此保證用到的欄位不會被修改掉。

如果索引檢視表包含了 GROUP BY 子句，則必須在 SELECT 子句中包含 COUNT_BIG (*)，並且不能指定 HAVING, CUBE, 以及 ROLLUP。

使用計算欄位（Computed Columns）上的索引來改善效能

首先來介紹一下計算欄位（Computed Columns），它通過一個運算式來引用同一張表的其他欄位，然後運算出一個結果。這個欄位的值會在每次被調用時都重新計算，除非你在建立時帶上 PERSISTED 標記。

在決定是否在計算欄位上建立索引前，需要考慮一下幾點：

 

• 計算欄位為 Image, Text, 或 ntext 的情況，它只能作為非叢集索引的非關鍵字段（non-key column）
• 計算欄位運算式不能是 REAL 或 FLOAT 類型
• 計算欄位應當是精確的（？）
• 計算欄位應當是確定的（輸入相同的值，輸出相同的結果）
• 計算欄位如果使用了函數（function），不管是使用者函數還是系統函數，表及函數的擁有者必須是同一個
• 針對多行記錄的函數（比如：SUM, AVG）不能在計算欄位中使用
• 增刪改會改變計算欄位上索引的值，所以必須設定以下6個參數。

 

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

下面我們來看一個完整的例子：

1. 設定系統變數，並建立我們的測試資料表

 

SET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONSET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET NUMERIC_ROUNDABORT OFFSELECT  [SalesOrderID]  ,[SalesOrderDetailID]  ,[CarrierTrackingNumber]  ,[OrderQty]  ,[ProductID]  ,[SpecialOfferID]  ,[UnitPrice]INTO  SalesOrderDetailDemoFROM  [AdventureWorks2012].[Sales].[SalesOrderDetail]GO

2. 建立一個使用者自訂函數，然後再建立一個計算欄位並使用這個函數

 

 

CREATE FUNCTION[dbo].[UDFTotalAmount] (@TotalPrice numeric(10,3), @FreightTINYINT)RETURNS Numeric(10,3)WITH SCHEMABINDINGASBEGINDECLARE @NetPrice Numeric(10,3)SET @NetPrice = @TotalPrice + (@TotalPrice*@Freight/100)RETURN @NetPriceENDGO--adding computed column SalesOrderDetailDemo tableALTER TABLE SalesOrderDetailDemoADD [NetPrice] AS [dbo].[UDFTotalAmount] ( OrderQty*UnitPrice,5)GO

3. 建立一個叢集索引，開啟效能指標開關，並執行一條查詢（注意此時我們還沒有在計算欄位上建立索引！）

 

 

CREATE Clustered Index idx_SalesOrderID_SalesOrderDetailID_SalesOrderDetailDemoON SalesOrderDetailDemo(SalesOrderID,SalesOrderDetailID)GO--checking SalesOrderDetailDemo with statistics option ON to--measure performanceSET STATISTICS IO ONSET STATISTICS TIME ONGO--checking SELECT statement without having Index on ComputedColumnSELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000GO

輸出的效能結果如下：

 

 

SQL Server parse and compile time:     CPU time = 650 ms, elapsed time = 650 ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. (3864 row(s) affected) Table ‘SalesOrderDetailDemo‘. Scan count 1, logical reads 757, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:     CPU time = 562 ms, elapsed time = 678 ms.

 

4. 在計算欄位上建立索引之前，可以用以下的指令碼確認是否滿足之前提到的建立要求：（傳回值：0不滿足，1滿足）

 

SELECTCOLUMNPROPERTY( OBJECT_ID(‘SalesOrderDetailDemo‘),‘NetPrice‘,‘IsIndexable‘) AS ‘Indexable?‘,COLUMNPROPERTY( OBJECT_ID(‘SalesOrderDetailDemo‘),‘NetPrice‘,‘IsDeterministic‘) AS ‘Deterministic?‘,OBJECTPROPERTY(OBJECT_ID(‘UDFTotalAmount‘),‘IsDeterministic‘)‘UDFDeterministic?‘,COLUMNPROPERTY(OBJECT_ID(‘SalesOrderDetailDemo‘),‘NetPrice‘,‘IsPrecise‘) AS ‘Precise?‘

5. 滿足要求的情況下建立索引，並再次執行先前的查詢語句

 

 

CREATE INDEX idx_SalesOrderDetailDemo_NetPriceON SalesOrderDetailDemo(NetPrice)GOSELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000GO

這次的效能結果如下：

 

 

SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. (3864 row(s) affected) Table ‘SalesOrderDetailDemo‘. Scan count 1, logical reads 757, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:     CPU time = 546 ms, elapsed time = 622 ms.

確認索引佔用的磁碟空間

 

 

SELECT  CASE index_id    WHEN 0 THEN ‘HEAP‘    WHEN 1 THEN ‘Clustered Index‘    ELSE ‘Non-Clustered Index‘  END AS Index_Type,  SUM(CASE    WHEN FilledPage > PageToDeduct THEN (FilledPage-PageToDeduct)    ELSE      0  END )* 8 Index_SizeFROM(  SELECT    partition_id,    index_id,    SUM (used_page_count) AS FilledPage,    SUM (      CASE        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)        ELSE          lob_used_page_count + row_overflow_used_page_count      END    ) AS PageToDeduct  FROM    sys.dm_db_partition_stats  GROUP BY    partition_id,index_id) AS InnerTableGROUP BY  index_idGO

PS: 輸出結果的單位為KB

SQL Server 效能調優3 之索引（Index）的維護