SQL Server 效能最佳化之——重複索引

閱讀導航

1. 概述

2. 什麼是重複索引

3. 尋找重複索引

4. 重複資料刪除索引

5. 總結

 

1. 概述

很多人都知道索引在資料庫上的是有利有弊的。像其他主流商務資料庫一樣SQL Server允許在一個列上重複建立索引。因為SQL Server沒有限制建立重複索引的數量，只是限制資料庫的一個表上最多可以建立999重複索引，所以這就增加了資料庫中存在重複索引的可能性。表的列上存在重複索引的話，可能會明顯的損害資料庫效能，因為SQL Server必須分別維護每一個重複索引。此外，SQL Server最佳化查詢語句時，查詢最佳化工具也會考慮這個問題，這就導致一系列效能問題。要理解什麼事實重複索引、怎麼樣找到它們、怎麼樣移除它們。

 

2. 什麼是重複索引

首先假設有一個表Test_Table有四個列（Col1, Col2, Col3, Col4）

CREATE TABLE Test_TableCREATE TABLE Test_Table(    Col1 int NOT NULL PRIMARY KEY,    Col2 varchr(30) NOT NULL,    Col3 varchr(30) NOT NULL,    Col4 varchr(30) NOT NULL,)

 

1) 在主鍵列上建立不同類型的索引

   1: CREATE UNIQUE CLUSTERED INDEX IX1 ON Test_Table(Col1); 

   2:  

   3: CREATE INDEX IX2 ON Test_Table(Col1); 

2) 在非主鍵列上建立不同順序的內含資料行索引

   1: CREATE INDEX IX3 ON Test_Table (Col4) 

   2: INCLUDE (Col2, Col3); 

   3:  

   4: CREATE INDEX IX4 ON Test_Table (Col4) 

   5: INCLUDE (Col3, Col2); 

3) 在非主鍵列上建立相同順序內含資料行索引

   1: CREATE INDEX IX5 ON Test_Table (Col4) 

   2: INCLUDE (Col2, Col3); 

   3:  

   4: 

CREATE UNIQUE INDEX IX6 ON Test_Table (Col4)

   5: INCLUDE (Col2, Col3); 

4) 在不同非主鍵列建立不同順序的索引

   1: CREATE INDEX IX7 ON Test_Table (Col3, Col2); 

   2:  

   3: 

CREATE INDEX IX8 ON Test_Table (Col3, Col2);

這樣重複的索引，在執行DML操作（插入、更新、刪除）的時候需要更新索引。

 

3. 尋找重複索引

一般不會有人特意建立重複索引。有時候，神不知鬼不覺的建立了，有時候 是因為建立新的索引是沒有檢查當前列是否已經存在索引。那麼怎麼樣才能它們暴露來呢？

1) 使用SQL Server Management Studio (SSMS，但是在SQL Server有很多資料庫，資料庫中又有大量表和索引的情況下，使用SSMS並不是一個快捷的方式。

2) 使用sp_helpindex尋找重複索引

3) 使用SQL Server系統目錄，可以在SQL Server資料庫上使用和開發指令碼尋找重複索引，這是一個比較方便並靈活的方式。

SQL系統目錄：

a. sys.indexes：包括表格對象（例如，表、視圖或資料表值函式）的索引或堆的每一行

b. sys.objects：在資料庫中建立的每個使用者定義的架構範圍內的對象在該表中均對應一行。

c. sys.index_columns：屬於 sys.indexes 索引或未排序的表（堆）的每個列都對應一行。

d. sys.columns：返回包含列對象（如視圖或表）的列的每一行

下面是包含列物件類型的表：

a) 表值程式集合函式 (FT)

b) 內聯表值 SQL 函數 (IF)

c) 內部表 (IT)

d) 系統資料表 (S)

e) 表值 SQL 函數 (TF)

f) 使用者表 (U)

g) 視圖 (V)

有一種是列出所有索引在哪個表上面，它們被掃描多少次，被更新多少次，在記憶體中的大小， 這些對我們有用的資訊

ViewSELECT sch.name + '.' + t.name AS [Table Name], i.name AS [Index Name], i.type_desc,  ISNULL(user_updates,0) AS [Total Writes], ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],s.last_user_seek, s.last_user_scan ,s.last_user_lookup,ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],  p.reserved_page_count * 8.0 / 1024 as SpaceInMBFROM sys.indexes AS i WITH (NOLOCK)  LEFT OUTER JOIN sys.dm_db_index_usage_stats AS sWITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,'IsUserTable') = 1  INNER JOINsys.tablesAS tWITH (NOLOCK) ON i.object_id = t.object_id  INNER JOINsys.schemasAS schWITH (NOLOCK) ON t.schema_id = sch.schema_id  LEFT OUTER JOIN sys.dm_db_partition_statsAS pWITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_idWHERE (1=1)--AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --顯示包含沒有使用的約束在內的所有約束--AND ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --僅僅顯示那些已經使用的索引--AND i.index_id > 1-- 非第一索引--AND i.is_primary_key<>1-- 不是作為主鍵被定義的--AND i.is_unique_constraint<>1         -- 不是UniqueConstraints  ORDER BY [Table Name], [index name]

 

還有一種是基於列尋找重複索引

View/* 執行這個指令碼後，索引將會以三個報表的實行展現出來
請看下面:
1.  列出所有索引和約束的關鍵資訊
2.  列出表潛在的冗餘索引
3.  列出表潛在的反向索引
*/
-- 建立一個存放索引資訊的表
DECLARE @AllIndexes TABLE (
 [Table ID] [int] NOT NULL,
 [Schema] [sysname] NOT NULL,
 [Table Name] [sysname] NOT NULL,
 [Index ID] [int] NULL,
 [Index Name] [nvarchar](128) NULL,
 [Index Type] [varchar](12) NOT NULL,
 [Constraint Type] [varchar](11) NOT NULL,
 [Object Type] [varchar](10) NOT NULL,
 [AllColName] [nvarchar](2078) NULL,
 [ColName1] [nvarchar](128) NULL,
 [ColName2] [nvarchar](128) NULL,
 [ColName3] [nvarchar](128) NULL,
 [ColName4] [nvarchar](128) NULL,
 [ColName5] [nvarchar](128) NULL,
 [ColName6] [nvarchar](128) NULL,
 [ColName7] [nvarchar](128) NULL,
 [ColName8] [nvarchar](128) NULL,
 [ColName9] [nvarchar](128) NULL,
 [ColName10] [nvarchar](128) NULL
)
--  載入索引資訊到下面語句
INSERT INTO @AllIndexes
 ([Table ID],[Schema],[Table Name],[Index ID],[Index Name],[Index Type],[Constraint Type],[Object Type]
 ,[AllColName],[ColName1],[ColName2],[ColName3],[ColName4],[ColName5],[ColName6],[ColName7],[ColName8],
 [ColName9],[ColName10])
SELECT o.[object_id] AS [Table ID] ,u.[name] AS [Schema],o.[name] AS [Table Name],
 i.[index_id] AS [Index ID]
 , CASE i.[name]
 WHEN o.[name] THEN '** Same as Table Name **'
 ELSE i.[name] END AS [Index Name],
 CASE i.[type]
 WHEN 1 THEN 'CLUSTERED'
 WHEN 0 THEN 'HEAP'
 WHEN 2 THEN 'NONCLUSTERED'
 WHEN 3 THEN 'XML'
 ELSE 'UNKNOWN' END AS [Index Type],
 CASE
 WHEN (i.[is_primary_key]) = 1 THEN 'PRIMARY KEY'
 WHEN (i.[is_unique]) = 1 THEN 'UNIQUE'
 ELSE '' END AS [Constraint Type],
 CASE
 WHEN (i.[is_unique_constraint]) = 1
 OR (i.[is_primary_key]) = 1
 THEN 'CONSTRAINT'
 WHEN i.[type] = 0 THEN 'HEAP'
 WHEN i.[type] = 3 THEN 'XML INDEX'
 ELSE 'INDEX' END AS [Object Type],
 (SELECT COALESCE(c1.[name],'') FROM [sys].[columns] AS c1 INNER JOIN [sys].[index_columns] AS ic1
 ON c1.[object_id] = ic1.[object_id] AND c1.[column_id] = ic1.[column_id] AND ic1.[key_ordinal] = 1
 WHERE ic1.[object_id] = i.[object_id] AND ic1.[index_id] = i.[index_id]) +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 2) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id],2) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],3) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 4) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],4) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 5) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],5) END  +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 6) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],6) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) END +
 CASE