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

jopen 9年前發布 | 16K 次閱讀 SQL Server 數據庫服務器

前言

上一篇我們研究了如何利用索引在數據庫里面調優，簡要的介紹了索引的原理，更重要的分析了如何選擇索引以及索引的利弊項，有興趣的可以點擊查看。

本篇延續上一篇的內容，繼續分析索引這塊，側重索引項的日常維護以及一些注意事項等。

閑言少敘，進入本篇的主題。

技術準備

數據庫版本為SQL Server2012，前幾篇文章用的是SQL Server2008RT，內容區別不大，利用微軟的以前的案例庫（Northwind）進行分析，部分內容也會應用微軟的另一個案例庫AdventureWorks。

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

一、創建索引

當我們要開始對表進行索引的創建的時候，首先明確的是，一張表內只能創建一個聚集索引，最多可以創建最多249個非聚集索引(SQL Server2005)，在SQL Server2008以后聚集索引數提升至999個，上一篇文章我們知道對于聚集索引項一般要創建上，而非聚集索引項要根據日常的T-SQL語句進行選擇。

關于索引的選擇是一個很考驗調優能力的事情，大部分的情況下優質的索引新建全靠經驗而論，有興趣的可以點擊查閱我前面的一系列關于分析查詢計劃的文章，掌握住里面的精髓才能有的放矢。

當然，小白級別的也可以參照如下方法嘗試進行創建：

由于SQL Server有著自己的一套調優技巧，所以在我們每次運行的T-SQL語句應該怎樣優化，SQL Server是了如指掌的，所以它會將缺失的索引項進行記錄，用于提示使用者，嘗試去建立這些索引。

主要記錄在以下幾個DMV中

sys.dm_db_missing_index_details

sys.dm_db_missing_index_groups

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_columns(index_handle)

sys.dm_db_missing_index_details

關于這些個DMV的使用，來舉一個例子：

--新建表，建立主鍵，形成聚集索引
CREATE TABLE BigTable
(
   [KEY] INT,
   DATA INT,
   PAD CHAR(200),
   CONSTRAINT [PK1] PRIMARY KEY ([KEY])
)
GO
--批量插入測試數據250000行
SET NOCOUNT ON 
DECLARE @i INT
BEGIN TRAN
    SET @i=0
    WHILE @i<250000
    BEGIN
       INSERT BigTable VALUES(@i,@i,NULL)
       SET @i=@i+1
       IF @i%1000=0
       BEGIN
          COMMIT TRAN
          BEGIN TRAN
       END
END    
COMMIT TRAN
GO

利用這個測試腳本，我們新建了一張測試表，并且插入了一些測試數據，運行一個查詢

SELECT [KEY],[DATA]
FROM BigTable
WHERE DATA<1000
GO

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

在這個簡單的查詢腳本中，SQL Server已經提示了我們需要創建的索引項。我們可以右鍵，直接生成創建腳本

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

SQL Server已經提示我們要創建的索引項內容了，穿件一個非聚集索引在列DATA上，并且INCLUDE列KEY，并且經創建完這個索引后的提升值都給計算出來了。

以上這種方式，在我們調優的時候是經常使用的，在我們拿到需要優化的語句后，直接執行就可以看到一部分需要調整的信息了。

但是，大部分的T-SQL語句不允許我們進行這樣的優化流程，甚至有時候是已經存在的系統。所以，我們下手的方式只能繞道了，幸好SQL Server為我們記錄下了這些缺失索引項的信息，就存在我上面提到的幾個DMV中。我們來查看下：

SELECT migs.group_handle, mid.* 
FROM sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON (migs.group_handle = mig.index_group_handle) 
INNER JOIN sys.dm_db_missing_index_details AS mid 
ON (mig.index_handle = mid.index_handle) 
WHERE migs.group_handle = 2

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

所以，大部分情況下，通過查看以上語句基本能確認到需要創建的索引項有哪些。

提示：但是，這里的DMV信息只是記錄自上次SQL Server啟動以后的信息項，也就是說每次重啟之后這部分信息就丟失了，所以對于生產系統，建議確保運行了一段周期之后再進行查看。

知道了應該創建什么樣的索引，下一步就是創建索引了，來看創建索引的腳本

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

創建腳本很簡單，指定索引類型、索引名稱、所屬表、包含列、篩選項、所屬文件組以及操作項就可以創建了。

我相信基本搞過SQL Server數據的這塊腳本一般不會陌生。

當然，如果不熟悉腳本的方式，SQL Server也默認給提供了圖形化操作界面，傻瓜式操作

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

這里我們重點分析幾點注意事項。

UNIQUE：

該關鍵字指定索引項為唯一值，也就是非重復值，在實際應用中非常的有用，應為唯一就意味著這個索引的高選擇性，也就意味著當前索引的可用性高低。

前面文章已經分析了SQL Server會默認的在主鍵列上創建聚集索引，也是利用了主鍵的非空和唯一性特點。

當然，這里也提示下聚集索引要求的就是唯一性，如果當前列確實存在重復值，那在創建聚集索引的時候SQL Server會默認的在當前列上加上一個唯一標識符（uniqueifiter）在內部來保證索引的唯一性。但這個時候就不需要顯式的指定UNIQUE 了，否則會報如下錯誤：

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

CLUSTERED|NONCLUSTERED：

這個就是指定創建的索引為聚集還是非聚集索引。

關于它，這里有幾點需要注意，因為非聚集索引的葉子節點存儲的就是聚集索引鍵值，所以在創建順序上要保證優先創建聚集索引，而后再創建非聚集索引，保證有足夠的存儲空間來存放非聚集索引。

在我們重新創建聚集索引的時候，SQL Server會默認的重新生成全部非聚集索引，如果表數據量特別大，這個過程會很漫長，如果不指定ONLINE的話，這個過程會是鎖定索引B-Teee 的，這就意味著是阻塞的，業務就要停下來等待完成操作，切記不要將此事發生在生產機上。

當然，以上問題是可以避免的。

index_name：索引的名字。
column ：

創建索引所選的列了，提示下：不能將大型對象 (LOB) 數據類型 ntext、text、varchar(max)、 nvarchar(max)、varbinary(max)、xml 或 image 的列指定為索引的鍵列。另外，即使 CREATE INDEX 語句中并未引用 ntext、text 或 image 列。如果想用這些類型的列可以存放于INCLUDE里面。

INCLUDE：

索引包含列，這個關鍵字非常有用，尤其在應對T-SQL的隨機IO問題上，具體內容可參照我前面的一系列的文章介紹。

還有前面提到的那些大型對象（LOB）數據類型，也可以包含進去，不過這里有一點需要提示下，如果包含了大型對象，則創建索引不支持在線（ONLINE）操作，這就意味著必須選擇非業務器進行操作。

PAD_INDEX = { ON | OFF }|FILLFACTOR =fillfactor

這個兩個選項是為了設置填充因子使用的，也是我們在創建索引的時候最常用的。

關于填充因子的作用簡單點講就是為了減少分頁而在索引空間中提前先預留空間。我們知道對于聚集索引在葉級別就包含了數據，所以用戶在這里可以指定每個葉子保留的空間的大小，通過預留空間，就可以避免用戶新的數據填充而產生分頁現象，產生索引碎片影響性能。

當然，關于填充因子的內容支撐，是需要一部分基礎知識的，有興趣的可以點擊此參照聯機叢書的官方介紹。

索引默認的的選項是OFF，也就是說基本不會預留太多空間。

關于這里填充因子設置的數值大小問題，其實沒有一個固定的值，純粹是一個經驗值，來自于系統的場景和長期運行的總結。當然，如果非要給出的話，可以參照如下進行設置：

1.當讀寫比例大于100：1時，不要設置填充因子，100%填充

2.當寫的次數大于讀的次數時，設置50%-70%填充

3.當讀寫比例位于兩者之間時80%-90%填充

但是，這個值并不是被SQL Server所維護的，也就是說在這部分預留空間填滿之后，后者改數據頁刪除部分數據之后，還是會產生索引碎片，所以在系統運行過一段周期之后，我們需要手動的去重新整理索引，來維護好索引的秩序，維護方式也就是：重新創建，重新組織等。文章后面的會介紹。

SORT_IN_TEMPDB = { ON | OFF }

這個就是指定當前索引排序是否要借助TempDB庫，默認值為OFF。如果想快速的生成索引請將此選項指定為ON，當然弊端就是會擴大TempDB的大小，如果原表數據量特別多的話，這可能會是一個很大的空間值。

STATISTICS_NORECOMPUTE = { ON | OFF}

這個指定是否同時更新統計信息。默認是開啟的。我知道統計信息的重要性，所以在創建的時候不要更改此值。

DROP_EXISTING = { ON | OFF }

刪除或重建的時候是否重新生成已經命名先前存在的聚集或非聚集索引。默認是OFF。

這個選項非常的有用。刪除或者重建索引的時候整個流程是作為一個事務來處理的。所以，通常情況下，如果打算重建一個聚集索引的時候，需要先刪除聚集索引，而后再新建立一個，但是這個流程中，在刪除的時候SQL Server必須重建每一個非聚集索引將每一個非聚集索引的葉子節點有聚集索引鍵改成RID,然后新建過程，在重復的將所有的每一個非聚集索引的葉子節點由RID鍵更改成新的聚集索引鍵值。

這就是需要重建非聚集索引兩次，如果表數據量特別大的話，這個時間消耗就會很長很長…而且是阻塞的….

但是如果指定DROP_EXISTING選項為ON的話，就可以在創建或者刪除的時候只需要一次更改所有非聚集索引就可以。當然此方式也可以通過ALTER INDEX做到，后面分析。

ONLINE = { ON | OFF }

是否在線提供索引創建，此方式也是數據庫的在05版本以后新添加的一大亮點，提供了在線狀態下索引的創建，但是僅限于Enterprise版本。

如果在生產系統中，業務并發時期可以采用這個選項進行索引的創建及維護，但相對離線創建的時間周期要明顯長很多，但是不會造成業務停機。

如果深入研究此方式的底層原理，其實就是數據的快照隔離機制，簡單點將就是在創建索引的時候，將相應的數據行提供了版本控制，避免了和正常業務系統的鎖爭用從而避免了阻塞，屬于樂觀鎖機制原理。

MAXDOP = max_degree_of_parallelism

設置并行計劃的數量值。這個選項也很有用，如果是非業務高發期，可以適當調高此值來并行進行索引的創建，加快索引的創建速度。

當然，也受限于物理的CPU核數。還有就是此功能也只有Enterprise版提供。

ALLOW_ROW_LOCKS = { ON | OFF }|ALLOW_PAGE_LOCKS = { ON | OFF }

此方式指定是否行鎖或者頁鎖，當然，只所以索引的創建和修改大部分情況下需要離線操作，就是因為在索引創建的時候加鎖了。為了加快索引的生成就必須添加相應的鎖。

如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，則訪問索引時允許行級、頁級和表級鎖。數據庫引擎將選擇相應的鎖，并且可以將鎖從行鎖或頁鎖升級到表鎖。

如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF，則訪問索引時僅允許使用表級鎖。

一個有用的索引的創建需要耐心的創建出來，切勿草率的魯莽進行，如果操作不當有可能還會產生更多意外的情況。所以要充分把握好數據的特性，合理的創建好每一個有用的索引。

二、索引管理

經過上面一步的索引的創建，其實在日常的大部分時間就需要維護好索引。關于索引的維護基本就集中在以下幾個方面

a、索引的重建

當我們發現索引索引覆蓋范圍不夠或者存在大量索引鎖片，影響性能的時候，我們就需要對索引進行重建。

索引范圍的問題其實大部分來源于對于T-SQL語句性能的把握，也就是我們前面幾篇文章中分析的需要調優的內容項。

而關于索引碎片的形成，也是源于數據庫長時間的運行，大量的增刪該查造成了B-Tree結構的不準確，確切的說是不能正確的提供平衡查詢的性能，或者大量的數據分頁造成索引碎片，進而增大了IO，影響了性能。

關于索引碎片的查看，可以通過以下DMV語句進行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
INTO #TempFragmentation 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempFragmentation 
SELECT TOP 20 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND i.name IS NOT NULL 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
ORDER BY [Fragmentation %] DESC' 
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC 
DROP TABLE #TempFragmentation

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

看到了，這部分索引的碎片到大了99%…這就需要我們重建進行維護了，否則將嚴重拖垮數據的性能。

維護的方式也就主要集中在以下幾種：

1、重建索引

這種方式簡單高效也就是我們上面分析的CREATE INDEX 命令后面加上DROP_EXISTING方式。當然可以聯機操作，操作方式參考文章前面

2、修改索引

這種方式是05版本以后才提供的，簡單點將就是ALTER INDEX命令進行。其實底層的運行方式同索引重建，只不過這種方式更改的選項多一些。

3、索引重組

這種方式就是重新填充索引里面的數據，對于解決索引碎片的方式不如前面兩種來的直接。不過也是一種推薦的方式，因為此方式在運行的時候，也是隨時停止。

不像前面兩種方式為原子性操作，并且業務阻塞。

b、索引的禁用

關于索引的禁用，這個功能也是SQL Server2005版本以后才出現的新功能，這個功能一般應用的不多。

因為大部分情況下將索引禁用了，還倒不如直接將索引刪除掉來的直接。

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

但是，記住了既然SQL Server設計了它就是有它的用武之地的。

很多情況下，數據庫在運行很長一段時間之后，會發生壞頁的情況。而如果通過命令查找，發現損壞也處于索引項上，那么你所做的操作就是禁用這個索引（記住只能是禁用）

然后重新建立一個新索引就可以了。

在這種情況下我們可選的最快處理方式就是禁用該索引，因為一旦發生壞頁的情況，該索引項是不允許刪除的。

很多朋友就好奇了，索引來了個禁用，那我什么時候啟用呢？…….

.嘿嘿…一旦問出了此問題，就說明了你對數據庫的理解還很淺…基本上還算沒有入門了……一旦索引禁用就意味著這個所以不再維護更新了….不再維護更新了那它里面的數據就是過時的或者說不準確的…那還啟用它干嘛…與其啟用還不如重新維護一個呢…

關于數據庫壞頁的情況，可以參照我前面寫的一篇文章，點擊此。

c、索引的刪除

關于索引的刪除，就不需要太多的介紹了，原因很簡單，索引的存在會影響數據插入數據的速度，并且在查詢的時候需要維護等多的鎖，進而影響并發。

所以，一旦索引存在著一點優化的作用沒有，我們就要及時的刪除掉，因為百害而無一利嘛。

查看未使用的索引DMV腳本如下：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempNeverUsedIndexes 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.NAME AS IndexName 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id 
AND I.index_id = S.index_id 
AND DATABASE_ID = DB_ID() 
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 
AND I.name IS NOT NULL 
AND S.object_id IS NULL' 
SELECT * FROM #TempNeverUsedIndexes 
ORDER BY DatbaseName, SchemaName, TableName, IndexName 
DROP TABLE #TempNeverUsedIndexes

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

當然，這些記錄都是自動SQL Server啟動以來未曾使用的索引，所以在生產系統中，一定要確保已經運行了一段周期了。

索引腳本的刪除，很簡單和表刪除類似，直接drop掉就可以了。

當然，最后再贈送一個DMV，查看那些經常被大量更新，但是卻基本不適用的索引項

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
INTO #TempUnusedIndexes 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempUnusedIndexes 
SELECT TOP 20 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
AND s.user_seeks = 0 
AND s.user_scans = 0 
AND s.user_lookups = 0 
AND i.name IS NOT NULL 
ORDER BY s.user_updates DESC' 
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC 
DROP TABLE #TempUnusedIndexes

關于這些腳本，就要自己酌情考慮是否刪除了，不能一概而論。

..此篇先到此吧…文章寫的有點糙….

關于調優內容太廣泛，我們放在以后的篇幅中介紹，有興趣的可以提前關注。

來源：指尖流淌

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1421393473187.html

SQL Server 數據庫服務器

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

前言

技術準備

相關經驗

相關資訊

相關文檔

目錄