如何使用系统SQL实体自动创建非聚集索引
发表于：2024-10-21 作者：千家信息网编辑
千家信息网最后更新 2024年10月21日，小编给大家分享一下如何使用系统SQL实体自动创建非聚集索引，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！引言一直以来，关于
千家信息网最后更新 2024年10月21日如何使用系统SQL实体自动创建非聚集索引
小编给大家分享一下如何使用系统SQL实体自动创建非聚集索引，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
引言
一直以来，关于索引的常见问题是：判断哪部分索引对保证数据库的良好性能是必需的。在本文中，笔者将提供针对该问题的解决方案。本文用例中的所有代码都基于名为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
在这个函数中，有三个输入参数：
@equality_columns @equality_columns @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以上是"如何使用系统SQL实体自动创建非聚集索引"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！
索引
函数
系统
视图
字符
缺失
实体
代码
笔者
篇文章
过程
存储
三个
内容
名称
进程
问题
保证
输入
选择
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
百度云 服务器异常
软件开发培训班有什么内容
18大以来网络安全重大成就
深圳电脑软件开发价格
淘宝上买东西提示服务器忙
网络安全必须从
网络安全工程师证照片
巨杉数据库公司 北京
无线网络技术类型的优缺点
sq数据库怎么建一亿个表
java 数据库 中文乱码
数据库原理查询选修课程
浦东新区自主可控网络技术内容
网络安全的命脉行业
桓台微信小程序软件开发公司
宿迁百川网络技术
浪潮服务器最高温度
渭南软件开发学费
http服务器价格
企业网络技术安全管理
大学生软件开发项目个人总结
orecle数据库打开方式
长亮科技互联网金融
软件开发的本质简答题
浙大 网络安全 机考
cs1.5刷新服务器
明瑞互联网科技
安卓移动软件开发教学
南京三商电脑软件开发
黄浦区推广软件开发收费标准
