缺失索引自动创建语句

silvertheo 发布于2019-08-27 11:03 / 1993人阅读

摘要：编者按本文主要介绍使用系统实体自动创建非聚集索引。会返回缺失索引的细节信息。视图该视图基于和表，并使用函数来计算缺失的索引名。存储过程该存储过程基于，并且输出索引创建语句。

【编者按】本文主要介绍使用系统 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 官方博客。

本文转自 OneAPM 官方博客

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

idc机房托管私有云索引缺失自动索引自动索引推荐 mongodb创建索引

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/110437.html

缺失索引自动创建语句

摘要：编者按本文主要介绍使用系统实体自动创建非聚集索引。会返回缺失索引的细节信息。视图该视图基于和表，并使用函数来计算缺失的索引名。存储过程该存储过程基于，并且输出索引创建语句。【编者按】本文主要介绍使用系统 SQL 实体自动创建非聚集(non-clustered)索引。作者为意大利软件工程师 GhostHost(笔名)。本文系 OneAPM 工程师编译呈现，以下为正文。引言一直以...

kviccn 2019-08-06 13:57 评论0 收藏0
缺失索引自动创建语句

摘要：编者按本文主要介绍使用系统实体自动创建非聚集索引。会返回缺失索引的细节信息。视图该视图基于和表，并使用函数来计算缺失的索引名。存储过程该存储过程基于，并且输出索引创建语句。【编者按】本文主要介绍使用系统 SQL 实体自动创建非聚集(non-clustered)索引。作者为意大利软件工程师 GhostHost(笔名)。本文系 OneAPM 工程师编译呈现，以下为正文。引言一直以...

iamyoung001 2019-06-25 18:19 评论0 收藏0
【数据科学系统学习】Python # 数据分析基本操作[二] pandas

摘要：中面向行和面向列的操作基本是平衡的。用层次化索引，将其表示为更高维度的数据。使用浮点值表示浮点和非浮点数组中的缺失数据。索引的的格式化输出形式选取数据子集在内层中进行选取层次化索引在数据重塑和基于分组的操作中很重要。我们在上一篇介绍了 NumPy，本篇介绍 pandas。 pandas入门 Pandas 是基于Numpy构建的，让以NumPy为中心的应用变的更加简单。 pandas...

jayzou 2019-07-31 11:11 评论0 收藏0
数据科学

摘要：资料分析资料筛选侦测缺失值补齐缺失值资料转换处理时间格式数据重塑资料学习正规运算式处理资料格式，提供高效能，简易使用的数据格式让用户可以快速操作及分析资料。使用平均数，中位数，众数等叙述性统计补齐缺失值。有90%的有用数据，都在数据库中。数据数据类型定性数据：叙述特征或种类，例如：种族，区域。定量数据：可以被计数或测量，例如：身高，消费金额。定量数据离散数据只能...

anquan 2019-07-30 17:52 评论0 收藏0
【SqlServer】统计索引使用情况解决DB的CPU高和IO高的问题

摘要：但是堆不会在索引重建或重新组织期间被重新生成，所以会脱离控制的增长，占用的数据页比必要的多很多。使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询，因而使用的操作更少，从而提高性能。查看索引情况 sp_helpindex 表名; 显示索引使用情况 user_seeks和user_scans字段都为0的，考虑是否为垃圾索引另外last_user_seek,last_use...

zhangyucha0 2019-07-01 16:08 评论0 收藏0