缺失索引自动创建语句

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

时间： 2024-09-20 00:25:43

缺失索引自动创建语句的相关文章

MS SQLSERVER 中如何得到表的创建语句

server|sqlserver|创建|语句 MS SQLSERVER 只能得到存储过程的创建语句,方法如下: sp_helptext procedureName 但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪些索引等等.下面我给出一个存储过程,供读者参考. 该存储过程可以得到你想得到的所有的表的创建语句,包括和表有关的索引的创建语句. SQLSERVER2000 下的代码

MS SQLSERVER中如何得到表的创建语句

MS SQLSERVER只能得到存储过程的创建语句,方法如下: sp_helptext procedureName 但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪些索引等等.下面我给出一个存储过程,供读者参考. 该存储过程可以得到你想得到的所有的表的创建语句,包括和表有关的索引的创建语句. SQLSERVER2000/2005 下的代码 create procedure SP

sqlserver2005自动创建数据表和自动添加某个字段索引_mssql2005

创建数据表的SQL语句如下: string tatlename = "T_useruid";//定义一个变量.用于自动创建数据表的名称,当前表名为:T_useruid string sql = "CREATE TABLE [dbo].[" + tatlename + "]([Cid] [int] IDENTITY(1,1) NOT NULL,[Uid] [nchar](32) COLLATE Chinese_PRC_CI_AS NULL,CONSTRAIN

创建索引对SQL语句执行的影响

一.创建索引对执行计划的影响 在SQL开始执行之前,Oracle会确定SQL语句的执行计划,并按照执行计划的步骤访问相应的表和索引. 一旦执行计划确定下来,Oracle会按照这个执行计划完成SQL语句的执行,在SQL语句执行开始之后建立的索引不会改变SQL语句的执行计划. 因此,创建索引不会对执行计划有任何的影响,也就不会对运行中的SQL语句有影响.下面通过一个例子简单验证一下: SQL> CREATE TABLE TEST (ID NUMBER, FID NUMBER, NAME VARCHA

如何识别真实和自动创建的索引？

创建|索引 问:我发现sysindexes索引表中的很多条目并不是我自己创建的.听说它们并不是真正的索引,而是SQL Server查询优化器自动创建的统计.怎样才能识别哪些是真正的索引,哪些是SQL Server自动创建的统计呢? 答:按照默认设置,如果表中的某列没有索引,则SQL Server会自动为该列创建统计.然后,查询优化器评估该列中数据分布范围的统计信息,以选择一个更为有效的查询处理方案.分辨自动创建的统计很简单,在SQL Server 7.0和SQL Server 2000中,自动创

SQL Server如何识别真实和自动创建的索引

问:最近我发现sysindexes索引表中的很多条目并不是我自己创建的.听同事说它们并不是真正的索引,而是SQL Server查询优化器自动创建的统计.怎样才能识别哪些是真正的索引,哪些是SQL Server自动创建的统计呢? 答:按照默认设置,如果表中的某列没有索引,则SQL Server会自动为该列创建统计.然后,查询优化器评估该列中数据分布范围的统计信息,以选择一个更为有效的查询处理方案.分辨自动创建的统计很简单,在SQL Server 7.0和SQL Server 2000中,自动创建的

如何识别真正的索引并可以自动创建索引

问:我发现sysindexes索引表中的很多条目并不是我自己创建的.听说它们并不是真正的索引,而是SQL Server查询优化器自动创建的统计.怎样才能识别哪些是真正的索引,哪些是SQL Server自动创建的统计呢? 答:按照默认设置,如果表中的某列没有索引,则SQL Server会自动为该列创建统计.然后,查询优化器评估该列中数据分布范围的统计信息,以选择一个更为有效的查询处理方案.分辨自动创建的统计很简单,在SQL Server 7.0和SQL Server 2000中,自动创建的统计的前

oracle中复合索引的创建剖析—包含in的三个条件SQL语句复合索引的创建

之前文章中提过复合索引的创建思路: 1 前导列尽可能让更多的核心业务SQL能够使用 2 单个SQL语句索引的前导列尽量选择等值条件做为索引的前导列 这里我们如果在对in的谓词.三个条件的SQL语句复合索引的创建做一些更深入的分析,详细的例子如下: SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle D

MySQL 创建索引、修改索引、删除索引的命令语句

mysql创建索引.修改索引.删除索引的命令语句 查看表中已经存在 index: show index from table_name; 创建和删除索引:索引的创建可以在 CREATE TABLE 语句中进行,也可以单独用 CREATE INDEX 或 ALTER TABLE 来给表增加索引.删除索引可以利用ALTER TABLE 或 DROP INDEX 语句来实现. PS: MySQL创建索引原则 http://blog.csdn.net/csdnones/article/details/5