缺失索引自动创建语句
【编者按】 本文主要介绍使用系统 SQL 实体自动创建非聚集(non-clustered)索引。作者为意大利软件工程师 GhostHost(笔名)。
本文系OneAPM 工程师编译呈现，以下为正文。
引言
一直以来，关于索引的常见问题是：判断哪部分索引对保证数据库的良好性能是必需的。在本文中，笔者将提供针对该问题的解决方案。本文用例中的所有代码都基于名为 dm_db_missing_index_details 的 SQL Server 系统视图。
背景
在开始安装前，进一步了解 dm_db_missing_index_details 会更有益处。
dm_db_missing_index_details 会返回缺失索引的细节信息。在本文中，我们将更关注以下几列：
-
index_handle：它是一个独特的跨服务器标识符，并且标志一个特定的缺失索引。 -
equality_columns：包含用于相等谓词的所有列 -
inequality_columns:包含用于其他比较的所有列 -
included columns索引中所包含的查询必要出现列 -
statement: 补充完整索引缺失的表名
实现
本系统的实现基于以下三个实体：
-
一个可以计算出待创建索引名称的简单函数
-
一个用于简化
dm_db_missing_index_details的用户视图 -
一个为每个索引创建声明的进程
笔者选择将这个系统分为三段进程，但实际上合并存储过程和视图也是可行的。笔者之所以没有选择后一种做法是因为想在创建索引之前先从业务逻辑检查一下存在哪些索引。
使用代码
函数 fn_Index_CreateIndexName
在这个函数中，有三个输入参数：
1. @equality_columns 2. @equality_columns 3. @index_handlE
该函数的目的是为每个期望创建的索引都创建一个唯一名称。
因此，首先拼接 @equality_columns 和 @equality_columns 两个输入变量，如果拼接后所得结果超过120个字符，那就截取至第120个字符。
为什么是120个字符？
因为在SQL Server中，命名最大长度为128个字符。这个函数在 @index_handlE 名字结尾添加字段以便保证唯一的索引名。
CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _ @Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128) AS BEGIN DECLARE @IndexName NVARCHAR(255) SET @IndexName = ISNULL(@equality_columns,@Inequality_columns) SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_')) SET @IndexName = RTRIM(REPLACE(@IndexName,']','_')) SET @IndexName = REPLACE(@IndexName,',','') SET @IndexName = REPLACE(@IndexName,'_ _','_') IF LEN(@IndexName) > 120 BEGIN SET @IndexName = SUBSTRING(@IndexName,0,120) END SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15)) RETURN @IndexName END
视图 vw_Index_MissingIndex
该视图基于 dm_db_missing_index_details 和 sys.databases 表，并使用 fn_Index_CreateIndexName 函数来计算缺失的索引名。
CREATE VIEW [dbo].[vw_Index_MissingIndex] AS SELECT '[' + d.name + ']' as DBName, [dbo].[fn_Index_CreateIndexName]_ (mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID, REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns, REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns, mid.Included_columns, mid.[statement] FROM sys.dm_db_missing_index_details mid INNER JOIN sys.databases d on d.database_id = mid.database_id
存储过程 usp_Index_MissingIndexCreationStatements
该存储过程基于 vw_Index_MissingIndex ，并且输出索引创建语句。
CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements] AS DECLARE @IndexCreationPlaceholder_Start AS NVARCHAR(MAX) DECLARE @IndexCreationPlaceholder_End AS NVARCHAR(MAX) -- PREPARE PLACEHOLDER SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS(SELECT * _ FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' ) BEGIN CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}' SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, _ STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, _ ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END;' + char(13) + char(10) -- STATEMENT CREATION SELECT DBName, CASE WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _ mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.equality_columns,'') + ' ASC,' + COALESCE(mid.Inequality_columns,'') + ' ASC )' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,_ '{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.Inequality_columns,'') + ' ASC ) ' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _ mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.equality_columns,'') + ' ASC ) ' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End ELSE NULL END AS Index_Creation_Statement, ' DROP INDEX [IX_' + mid.ID + '] ON ' + mid.[statement] _ + + char(13) + char(10) AS Index_Drop_Statement FROM [dbo].[vw_Index_MissingIndex] AS mid 完整代码
-- CREATE FUNCTION fn_Index_CreateIndexName CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _ @Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128) AS BEGIN DECLAR E @IndexName NVARCHAR(MAX) SET @IndexName = ISNULL(@equali ty_columns,@Inequality_columns) SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_')) SET @IndexName = RTRIM(REPLACE(@IndexName,']','_')) SET @IndexName = REPLACE(@IndexName,',','') SET @IndexName = REPLACE(@IndexName,'_ _','_') IF LEN(@IndexName) > 120 BEGIN SET @IndexName = SUBSTRING(@IndexName,0,120) END SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15)) RETURN @IndexName END GO -- CREATE FUNCTION vw_Index_MissingIndex CREATE VIEW [dbo].[vw_Index_MissingIndex] AS SELECT '[' + d.name + ']' as DBName, [dbo].[fn_Index_CreateIndexName]_ (mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID, REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns, REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns, mid.Included_columns, mid.[statement] FROM sys.dm_db_missing_index_details mid INNER JOIN sys.databases d on d.database_id = mid.database_id GO CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements] AS DECLARE @IndexCreationPlaceholder_Start AS NVARCHAR(MAX) DECLARE @IndexCreationPlaceholder_End AS NVARCHAR(MAX) -- PREPARE PLACEHOLDER SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS_ (SELECT * FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' ) BEGIN CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}' SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, _ STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, _ ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END;' + char(13) + char(10) -- STATEMENT CREATION SELECT DBName, CASE WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _ mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.equality_columns,'') + ' ASC,' + COALESCE(mid.Inequality_columns,'') + ' ASC )' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _ mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.Inequality_columns,'') + ' ASC ) ' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _ mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.equality_columns,'') + ' ASC ) ' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End ELSE NULL END AS Index_Creation_Statement, ' DROP INDEX [IX_' + mid.ID + '] ON ' + mid.[statement] _ + + char(13) + char(10) AS Index_Drop_Statement FROM [dbo].[vw_Index_MissingIndex] AS mid GO OneAPM 助您轻松锁定.NET 应用性能瓶颈，通过强大的 Trace 记录逐层分析，直至锁定行级问题代码。以用户角度展示系统响应速度，以地域和浏览器维度统计用户使用情况。想阅读更多技术文章，请访问OneAPM 官方博客。
原文地址： http://www.codeproject.com/Tips/1079651/Automatic-Missing-Non-Clustered-Creation-Statement
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
