边肖将与您分享如何使用系统SQL实体来自动创建非聚集索引。相信大部分人都不太了解,所以分享这篇文章给大家参考。希望你看完这篇文章能有很多收获。我们一起来看看吧!
00-1010一直以来,关于索引的常见问题是:确定哪些部分的索引是保证数据库良好性能所必需的。在本文中,作者将提供一个解决这个问题的方法。这个用例中的所有代码都基于名为dm_db_missing_index_details的SQL Server系统视图。
00-1010在开始安装之前,多了解一些dm_db_missing_index_details会更有好处。
Dm_db_missing_index_details返回缺失索引的详细信息。在本文中,我们将更加关注以下几个专栏:
Index_handle:这是一个唯一的跨服务器标识符,它标记了一个特定的缺失索引。
Equality_columns:包含相等谓词的所有列。
不等式列3360包含用于其他比较的所有列。
包含在包含列索引中的查询需要列
Statement:补充了完整索引中缺少的表名。
引言
该系统的实施基于以下三个实体:
一个简单的函数,可以计算要创建的索引的名称
简化数据挖掘数据库缺失索引细节的用户视图
为每个索引创建声明的过程。
作者选择将这个系统分成三个进程,但事实上,将存储过程和视图结合起来是可行的。我没有选择后一种方法,因为我想在创建索引之前从业务逻辑中检查哪些索引存在。
00-1010函数fn_Index_CreateIndexName
在该功能中,有三个输入参数:
@equality_columns
@equality_columns
@index_handlE这个函数的目的是为每个需要的索引创建一个唯一的名称。
因此,首先拼接两个输入变量@equality_columns和@equality_columns。如果拼接后的结果超过120个字符,将被截断为第120个字符。
为什么是120个字符?
因为在SQL Server中,命名的最大长度是128个字符。该函数在名称@index_handlE的末尾添加一个字段,以确保唯一的索引名称。
CREATEFUNCTION[dbo]。[fn _ Index _ CreateIndexName](@ equality _ columns varchar(4000),_
@不等式_列VARCHAR(4000),@ index _ handlEINT)RETURNSVARCHAR(128)
如同
开始
DECLARE@IndexNameNVARCHAR(255)
SET @ index name=ISNULL(@ equality _columns,@不等式_ columns)
SET @ index name=LTRIM(REPLACE(@ index name,'[',' _ '))
SET @ index name=RTRIM(REPLACE(@ index name,']',' _ '))
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
以上是“如何使用系统SQL实体自动创建非聚集索引”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注行业资讯频道!
内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/113331.html