AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

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

2013-06-17 1151
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介： 原文 http://www.cnblogs.com/BoyceYang/archive/2013/06/16/3139006.html 阅读导航 1. 概述 2. 什么是重复索引 3. 查找重复索引 4. 删除重复索引 5. 总结   1. 概述 很多人都知道索引在数据库上的是有利有弊的。

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

阅读导航

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

 

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

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

文章标签：
云数据库 RDS SQL Server 版
索引
SQL
数据库
数据格式
XML
Go
.NET
BI
开发框架
关键词：
SQL索引
SQL性能优化
云数据库RDS SQLServer版索引
SQL server
云数据库RDS SQLServer版性能优化
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
杰克.陈
目录
相关文章
猿java
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能？
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL，并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点，包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后，通过具体示例展示了索引如何提升查询速度，并提供了三层B+树的存储容量计算方法。通过这些技巧，可以帮助开发者有效提升数据库查询效率。
猿java
180 2
最好zzz
|
26天前
|
SQL 缓存 监控
SQL性能提升指南：五大优化策略与十个实战案例
在数据库性能优化的世界里，SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载，提高应用响应速度，甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤，并结合十个实战案例，为你提供一份详尽的性能提升指南。
最好zzz
45 0
云深知何处
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中，发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句，首先分析了 where 条件中各列的选择性，并创建了不同类型的索引，包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划，最终确定了使用复合索引（包含函数表达式）能够显著降低查询成本，提高执行效率。
云深知何处
36 3
1100237741946300
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引：详细技巧与方法
在数据库管理中，索引是提高SQL查询性能的重要手段
1100237741946300
605 5
1100237741946300
|
2月前
|
SQL 监控 Oracle
Oracle SQL性能优化全面指南
在数据库管理领域，Oracle SQL性能优化是确保数据库高效运行和数据查询速度的关键
1100237741946300
341 6
1100237741946300
|
2月前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据：技巧、方法与性能优化
id="">SQL查询功能详解 SQL（Structured Query Language，结构化查询语言）是一种专门用于与数据库进行沟通和操作的语言
1100237741946300
102 5
1100237741946300
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么：深入解析与技巧
在SQL数据库中，索引是一种用于提高查询性能的重要数据结构
1100237741946300
44 0
1100237741946300
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中，索引是一种用于提高查询性能的数据结构
1100237741946300
73 0
1100237741946300
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中，确认SQL查询是否使用了索引是一个至关重要的步骤
1100237741946300
190 0
1100237741946300
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引：详细步骤与技巧
在数据库管理和优化中，确认SQL查询是否有效利用了索引是提升性能的关键步骤
1100237741946300
350 0

热门文章

最新文章

  • 1
    PostgreSQL SQL HINT的使用
  • 2
    SQL0286N. DB2表空间的pagesize问题
  • 3
    SQL语句如何强制使用索引
  • 4
    SQL SERVER2005建Link Server
  • 5
    【代码分享-SQL】为DB中所有Table增加PK
  • 6
    SQL中的保留关键字
  • 7
    Sql Server在建好的表中，新增一列
  • 8
    直播预告 | PolarDB-X 动手实践系列——如何在 PolarDB-X 中优化慢 SQL
  • 9
    SQL中EXISTS的用法
  • 10
    SQL查询入门（下篇）
  • 1
    如何查看SQLSERVER的版本信息和SP补丁信息
    290
  • 2
    安装SQL Server 2005时出现对性能监视器计数器注册表值执行系统配置检查失败的解决办法...
    54
  • 3
    这篇文章教会你：从 SQL Server 移植到 DM（上）
    216
  • 4
    SQL Server语法基础：入门到精通
    208
  • 5
    SQL Server详细使用教程
    331
  • 6
    C#SQL Server数据库基本操作（增、删、改、查）
    408
  • 7
    数据库数据恢复—Sql Server数据库文件丢失的数据恢复案例
    82
  • 8
    【SQLserver】常见问题和解决方案
    117
  • 9
    【sqlserver】带分隔符字符串和多行互相转化
    139
  • 10
    SQL Server详细使用教程及常见问题解决
    147

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。