备案控制台
探索云世界
开发者社区 数据库 文章 正文

缺失索引自动创建语句

2016-04-18 1902
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 一直以来，关于索引的常见问题是：判断哪部分索引对保证数据库的良好性能是必需的。在本文中，笔者将提供针对该问题的解决方案。本文用例中的所有代码都基于名为 dm_db_missing_index_details 的 SQL Server 系统视图。

【编者按】 本文主要介绍使用系统 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: 补充完整索引缺失的表名

实现

本系统的实现基于以下三个实体：

  1. 一个可以计算出待创建索引名称的简单函数
  2. 一个用于简化 dm_db_missing_index_details的用户视图
  3. 一个为每个索引创建声明的进程

笔者选择将这个系统分为三段进程，但实际上合并存储过程和视图也是可行的。笔者之所以没有选择后一种做法是因为想在创建索引之前先从业务逻辑检查一下存在哪些索引。

使用代码

函数 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 官方博客。

本文转自 OneAPM 官方博客

原文地址：http://www.codeproject.com/Tips/1079651/Automatic-Missing-Non-Clustered-Creation-Statement

文章标签：
索引
数据库
Go
SQL
存储
测试技术
oneapm_official
目录
相关文章
bbpp
|
9天前
|
存储 关系型数据库 索引
10. 在一个非主键字段上创建了索引, 想要根据该字段查询到数据, 需要查询几次 ?
在非主键字段上创建索引，查询数据通常需两次。对于MyISAM，先通过索引找到数据行指针，再获取数据；而InnoDB则先找主键ID，再从主键索引中查找数据。
bbpp
11 0
舒一笑
|
9天前
|
SQL 存储 关系型数据库
group by语句查询如何确保保留重复数据
group by语句查询如何确保保留重复数据
舒一笑
12 0
snowofsummer
|
29天前
|
存储 前端开发 Oracle
物化视图添加删除列测试
物化视图添加删除列测试
snowofsummer
12 2
快乐阿超
|
4月前
|
SQL 索引
加唯一索引时候发现已有重复数据删除
加唯一索引时候发现已有重复数据删除
快乐阿超
23 1
像素旅人
|
7月前
|
SQL 关系型数据库 MySQL
一个索引创建错误引发的思考
同事反馈说某个 MySQL 数据库创建索引提示错误，模拟报错如下： CREATE INDEX t_reg_code_idx USING BTREE ON t(reg_code) BLOB/TEXT column 'reg_code' used in key specification without a key length 从该提示可知，给 T 表的 reg_code 列创建一个 BTREE 索引，而这个 reg_code 列的字段类型是 BLOB 或 TEXT。 需要在键的说明中有长度定义，这是什么意思？
像素旅人
38 0
sunrr
|
8月前
|
关系型数据库 MySQL 索引
确保你的查询语句正确地使用了索引
确保你的查询语句正确地使用了索引
sunrr
30 1
游客5m6wdh6ebwgya
|
索引
测试关于索引的操作- 修改索引
测试关于索引的操作- 修改索引
游客5m6wdh6ebwgya
72 0
游客5m6wdh6ebwgya
|
索引
测试关于索引的操作-创建索引
测试关于索引的操作-创建索引
游客5m6wdh6ebwgya
106 0
q7s2kces74wvy
|
关系型数据库 MySQL 开发者
索引分类和建索引命令语句|学习笔记
快速学习索引分类和建索引命令语句
q7s2kces74wvy
75 0
金色海洋
|
数据库
检查两个数据库里的表名、字段是否一致的一种方法
不知道大家有没有遇到过这种情况。 程序已经给客户安装上了，并且客户已经录入了一些信息，然后程序还需要作比较大的变动（修改功能、增加模块等），数据库就不可避免要做一些改动。 但是这时候已经不能把客户的数据库删掉，换上新的数据库了。
金色海洋
971 0

热门文章

最新文章

  • 1
    阿里云各个地域节点测试IP（国内+海外）Ping值延迟测试（全解析）
  • 2
    基于Flink的实时日志分析系统实践
  • 3
    阿里云的SLB，植入cookie和重写cookie有什么区别？
  • 4
    uni-app 开源资源汇总
  • 5
    一文了解阿里云对象存储OSS
  • 6
    SQL SERVER中的OLEDB等待事件
  • 7
    kindle3使用技巧
  • 8
    sd卡无法写操作
  • 9
    普京顾问警告或征收科技税 谷歌苹果可能受创
  • 10
    CIO：传统企业如何做好转型？
  • 1
    手动给docusaurus添加一个搜索
    17
  • 2
    深入白盒测试：代码级透视与质量保证
    16
  • 3
    网络安全与信息安全：防御前线的关键技术与意识
    17
  • 4
    云端守卫：融合云计算与网络安全的未来之路
    17
  • 5
    nginx 配置代理ip访问https的域名配置
    17
  • 6
    基于Java的中国传统面食介绍网站的设计与实现(源码+lw+部署文档+讲解等)
    35
  • 7
    Python搭建代理IP池实现存储IP的方法
    27
  • 8
    外部中断的使用方法
    27
  • 9
    基于Java的快递信息管理系统的设计与实现(源码+lw+部署文档+讲解等)
    28
  • 10
    深入理解MySQL中的UPDATE JOIN语句
    46

    • 相关电子书

    更多
  • RowKey与索引设计：技巧与案例分析
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 通过函数计算实现网站文件自动处理
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句管理索引
  • 使用交互方式实现数据查询、插入、修改和删除操作
  • SQL进阶之约束、索引
    • 下一篇
    阿里云oss简介和使用流程