自动缺失索引创建语句

于 2024-07-15 17:06:36 发布
阅读量1 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库 sql
原文链接：https://www.codeproject.com/Tips/1079651/Automatic-Missing-Indexes-Creation-Statements
版权

目录

介绍

背景

实现

使用代码

1. 函数——fn_Index_CreateIndexName

2. 查看——vw_Index_MissingIndex

3. 存储过程——usp_Index_MissingIndexCreationStatements

完整代码

介绍

有关索引的一个常见问题是选择在数据库上创建哪些索引以保证良好的性能。在此提示中，我想介绍有关此问题的解决方案，在本例中，我的所有代码都基于名为dm_db_missing_index_details。

背景

在我们继续实现之前，也许了解更多有关dm_db_missing_index_details。

dm_db_missing_index_details返回有关缺失索引的详细信息。在这个提示中，我们最感兴趣的是以下列：

  • index_handle：它是整个服务器的唯一标识符，用于标识特定的缺失索引。
  • equality_columns：包含用于相等谓词的所有列
  • inequality_columns：包含用于其他比较的所有列
  • included columns：索引中必须包含的列，作为查询的覆盖列。
  • statement：缺少索引的完整表名。

实现

该系统的实施基于三个实体：

  1. 一个简单的函数，用于计算我们想要创建的索引名称
  2. 要简化dm_db_missing_index_details的用户视图
  3. 为每个索引创建语句的过程

我选择将这个系统划分为三个过程，但老实说，可以合并存储过程和视图。我个人没有选择这个解决方案，因为我想在创建索引之前从我的业务逻辑中检查它们有什么样的索引。

使用代码

1. 函数——fn_Index_CreateIndexName

在此函数中，有三个输入参数：

  1. @equality_columns
  2. @equality_columns
  3. <a>@index_handlE</a>

该函数的目标是为要创建的每个索引创建一个唯一的名称。
所以首先，我们连接@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

2. 查看——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

3. 存储过程——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
	
	DECLARE @IndexName NVARCHAR(MAX)

	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

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

https://www.codeproject.com/Tips/1079651/Automatic-Missing-Indexes-Creation-Statements

寒冰屋
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
缺失索引自动创建语句
wangpeng198688的专栏
04-18 2441
一直以来，关于索引的常见问题是：判断哪部分索引对保证数据库的良好性能是必需的。在本文中，笔者将提供针对该问题的解决方案。本文用例中的所有代码都基于名为 dm_db_missing_index_details 的 SQL Server 系统视图。
SQL语句实现查询并自动创建Missing Index
09-10
通过运行这个SQL脚本，数据库管理员可以快速识别出那些最需要创建的缺失索引，并直接复制生成的`CREATE INDEX`语句到实际的SQL执行环境中，以优化查询性能。 总结来说，使用SQL语句查询并自动创建Missing Index是一...
Sqlserver自动查询缺失索引及拼出建立索引的语句的脚本
lusklusklusk的博客
03-03 849
官方文档 https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/s
Sql缺失索引查询，自动创建执行语句
大新软件老杨
04-10 338
创建索引，直接打印为日志，复制日志直接执行。创建索引语句,按查询表方式返回。
mysql missing index_使用缺失索引信息编写 CREATE INDEX 语句
weixin_42511602的博客
01-27 331
排列 CREATE INDEX 语句中的列缺失索引功能的组件在其输出中列出相等列、不等列以及包含列。例如，XML 显示计划 MissingIndexes 元素指示索引键列在 Transact-SQL 语句谓词中是用于相等 (=) 或不等( 等)，还是只被包含在其中以包含查询。它将针对 ColumnGroup 子元素的 Usage 属性，将此信息显示为下列值之一：动态管理对象 sys.dm_db_m...
mysql 创建索引权限_MYSQL索引和权限管理
weixin_36448083的博客
01-19 1446
索引可以理解为: 搜索引导,索引是一个特殊的数据结构,其存储的是数据的关键信息与详细信息的位置对应关系例如:书本的目录创建索引的语法:​create index 索引的名字 on 表名称(字段名)联合索引​create index 索引的名字 on 表名称(字段名,字段名)​create index union_index on usr(id,email,name,gender);删除索引...
缺失索引
wacthamu的专栏
05-31 1823
当大家发现数据库查询性能很慢的时候，大家都会想到加索引来优化数据库查询性能，但是面对一个复杂的SQL语句，找到一个优化的索引组合对人脑来讲，真的不是一件很简单的事。 好在SQLSERVER提供了两种“自动”功能，给你建议，该怎么调整索引 第一种是使用DMV 第二种是使用DTA (database engine tuning advisor) 数据库引擎优化顾问 这篇文章主要讲第一种
Pandas 索引对齐、缺失值
Cdccc_daye的博客
04-12 2268
开篇 当在进行Series、DataFrame这两个对象的二元运算时，有稍不同于Numpy的处理方式。 本篇将以组合不同来源的数据为起点，探究其中发生的索引对齐，过程中产生的缺失值的特性和处理手段，以及更进一步的数据集合并方法。 引子——Pandas的NumPy-based特性 因为Pandas是建立在NumPy的基础之上的，所以NumPy的通用函数仍适用于Series对象和DataFrame对象。你可以在NumPy的通用函数里传入一个Series对象，会返回一个保留索引的Pandas对象。*下面代
MongoDB索引详解
持续学习，坚持原创，分享技术笔记及经验。
01-06 1605
MongoDB索引详解
SQL Server 索引优化——无用索引和索引缺失
三空道人的博客
01-02 2863
SQLServer索引优化 ——无用索引和索引缺失 我们知道，合理的索引能大幅提升性能，但冗余的索引也会降低数据库性能。随着我们业务的发展，数据库的中的表、表结构、查询的内容都有可能发生变化。这样，有的索引就可能不再使用了，需要删除（因为维护索引即浪费存储，又耗费性能）；而有的表则需要修改或者增加索引。本文主要给出快速确定不再使用的索引的查找方式之一，动态视图（DMV）查询。 无用索引 ...
mysql如何查缺索引_MySQL查缺补漏系列（1）：索引失效问题
weixin_34570507的博客
02-18 431
MySQL查缺补漏系列(1)：索引失效问题1、背景知识必备1.1、key(键)等于约束字段的行为+索引primary key(主键)约束作用：唯一标识数据库表的数据；主键必须唯一则不能为NULL索引作用：建立了一个主键索引unique key(唯一约束)约束作用：唯一标识数据库表中的每条记录索引作用：建立了一个主键索引foreign key (外键)约束作用：引用完整性索引作用：建立了一个主键索引...
Sql Server 索引使用情况及优化的相关Sql语句分享
09-11
缺失索引是指数据库可能受益但目前尚未创建的索引。以下语句可帮助识别可能缺失的索引： ```sql SELECT d.*, s.avg_total_user_cost, s.avg_user_impact, s.last_user_seek, s.unique_compiles FROM sys.dm_db_...
sql语句优化之SQL Server(详细整理)
09-10
3. **计算列缺失**：创建计算列有助于数据库进行更有效的查询优化。 4. **内存不足**：内存对数据库性能至关重要，不足的内存可能导致数据缓存不足，从而影响查询速度。 5. **网络延迟**：网络速度直接影响数据传输...
pycharm 代码自动补全的实现方法(图文)
12-16
例如，在创建新的`.py`文件时，你可以设定模板，使得每次新建文件都会自动添加包含时间、日期和作者信息的注释。 在PyCharm中，通过`Code` -> `Reformat Code` 可以快速对选定的代码段进行格式化，遵循PEP8或其他...
JavaFx+MySql学生管理系统
2301_81253185的博客
07-11 1105
上个月学习了javafx和mysql数据库,于是写了一个学生管理系统,因为上个月在复习并且有一些事情,比较忙,所以没有更新博客了,这个项目页面虽然看着有点简陋了,但是大致内容还是比较简单的,于是现在跟大家分享一下我的学生管理系统,希望对这方面有兴趣的同学提供一些帮助。
set user & password for database 设置数据库 用户和密码
DavidsonGong的博客
07-12 151
USE sbms;
7.10飞书一面面经
东篱君的博客
07-10 1207
B+树的⾮叶⼦节点不存放实际的记录数据，仅存放索引，所以数据量相同的情况下，相⽐存储即存索引⼜存记录的 B 树，B+树的⾮叶⼦节点可以存放更多的索引，因此 B+ 树可以⽐ B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。主从复制：单节点Redis的并发能力是有上限的，要进一步提高Redis的并发能力，可以搭建主从集群，实现读写分离。B+ 树有⼤量的冗余节点（所有⾮叶⼦节点都是冗余索引），这些冗余索引让 B+ 树在插⼊、删除的效率都更⾼，⽐如删除根节点的时候，不会像 B 树那样会发⽣复杂的树的变化；
Redis集群和高可用
最新发布
xiaogengtongxu的博客
07-13 412
主从架构无法实现master和slave角色的自动切换，即当master出现redis服务异常、主机断电、磁盘损坏等问题导致master无法使用，而redis主从复制无法实现自动的故障转移(将slave 自动提升为新master)，需要手动修改环境配置,才能切换到slave redis服务器，另外当单台Redis服务器性能无法满足业务写入需求的时候,也无法横向扩展Redis服务的并行写入性能。master和slave角色的无缝切换，让业务无感知从而不影响业务使用。
mysql笔记1
m0_62975692的博客
07-11 380
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
kibana关闭自动创建索引dsl语句
07-28
要在Kibana中关闭自动创建索引，您可以使用以下的DSL语句： ``` PUT _cluster/settings { "persistent": { "action.auto_create_index": "false" } } ``` 这个DSL语句将设置`action.auto_create_index`参数为`false`，从而禁止Kibana自动创建索引。请确保您有适当的权限来执行此操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值