SQL Server 性能优化之——重复索引

2021-08-11 22:18:14

原文 http://www.cnblogs.com/BoyceYang/archive/2013/06/16/3139006.html

阅读导航

1. 概述

很多人都知道索引在数据库上的是有利有弊的。像其他主流商业数据库一样SQL Server允许在一个列上重复创建索引。因为SQL Server没有限制创建重复索引的数量，只是限制数据库的一个表上最多可以创建999重复索引，所以这就增加了数据库中存在重复索引的可能性。表的列上存在重复索引的话，可能会明显的损害数据库性能，因为SQL Server必须分别维护每一个重复索引。此外，SQL Server优化查询语句时，查询优化器也会考虑这个问题，这就导致一系列性能问题。要理解什么事实重复索引、怎么样找到它们、怎么样移除它们。

2. 什么是重复索引

首先假设有一个表Test_Table有四个列（Col, Col2, Col3, Col4）

CREATE 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)

有一种是列出所有索引在哪个表上面，它们被扫描多少次，被更新多少次，在内存中的大小，这些对我们有用的信息

SELECT

	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 SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s	WITH (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 JOIN		sys.tables					AS t	WITH (NOLOCK) ON i.object_id = t.object_id
INNER JOIN		sys.schemas					AS sch	WITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT OUTER JOIN sys.dm_db_partition_stats	AS p	WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (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]

还有一种是基于列查找重复索引

/* 执行这个脚本后，索引将会以三个报表的实行展现出来

请看下面:

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
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 9) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],9) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 10) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],10) END  AS [AllColName],

 (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])   AS [ColName1],
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 AS [ColName2],
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 AS [ColName3],
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 AS [ColName4],
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 AS [ColName5],
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 AS [ColName6],
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 AS [ColName7],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id],8) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) END AS [ColName8],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 9) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],9) END AS [ColName9],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 10) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],10) END AS [ColName10]
FROM [sys].[objects] AS o WITH (NOLOCK)
LEFT OUTER JOIN [sys].[indexes] AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
JOIN [sys].[schemas] AS u WITH (NOLOCK)
ON o.[schema_id] = u.[schema_id]
WHERE o.[type] = 'U' --AND i.[index_id] < 255
AND o.[name] NOT IN ('dtproperties')
AND i.[name] NOT LIKE '_WA_Sys_%'

-----------
SELECT 'Listing All Indexes' AS [Comments]

SELECT I.*
FROM @AllIndexes AS I
ORDER BY [Table Name]

-----------
SELECT 'Listing Possible Redundant Index keys' AS [Comments]

SELECT DISTINCT I.[Table Name], I.[Index Name] ,I.[Index Type],  I.[Constraint Type], I.[AllColName]
FROM @AllIndexes AS I
JOIN @AllIndexes AS I2
ON I.[Table ID] = I2.[Table ID]
AND I.[ColName1] = I2.[ColName1]
AND I.[Index Name] <> I2.[Index Name]
AND I.[Index Type] <> 'XML'
ORDER BY I.[Table Name], I.[AllColName]

----------
SELECT 'Listing Possible Reverse Index keys' AS [Comments]

SELECT DISTINCT I.[Table Name], I.[Index Name], I.[Index Type],  I.[Constraint Type], I.[AllColName]
FROM @AllIndexes AS I
JOIN @AllIndexes AS I2
ON I.[Table ID] = I2.[Table ID]
AND I.[ColName1] = I2.[ColName2]
AND I.[ColName2] = I2.[ColName1]
AND I.[Index Name] <> I2.[Index Name]
AND I.[Index Type] <> 'XML'

4. 删除重复索引

把它们暴露出来，剩下的事情就很简单了，删除。

   1: USE test_table;

   2: GO

   3: --从表Test_Tabler删除索引 IX2

   4: DROP IX2

   5: ON Test_Tabler

   6: GO

5. 总结

设计数据库查询语句时，需要相当的留意重复索引可能引起DML操作的性能降低。设计新数据库之前最好检查一下已有数据库的索引。在自己的数据库发现重复索引，明智的选择就是果断删除它，删除之前最好还是先做数据库备份，这样可以避免删除后对数据库造成重大影响。其实，删除重复索引不仅能提高性能而且可以给数据库瘦身，同时备份文件也会变小。

在此谢谢读完这篇博客，有什么写的不对的地方请指正

有帮助就推荐下，有感想就下下来，不满意就留言，有问题就更正。

码农公寓

相关文章