溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

如何使用系統(tǒng)SQL實(shí)體自動(dòng)創(chuàng)建非聚集索引

發(fā)布時(shí)間：2021-11-23 17:56:23 來(lái)源：億速云 閱讀：186 作者：小新 欄目：編程語(yǔ)言

小編給大家分享一下如何使用系統(tǒng)SQL實(shí)體自動(dòng)創(chuàng)建非聚集索引，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

引言

一直以來(lái)，關(guān)于索引的常見(jiàn)問(wèn)題是：判斷哪部分索引對(duì)保證數(shù)據(jù)庫(kù)的良好性能是必需的。在本文中，筆者將提供針對(duì)該問(wèn)題的解決方案。本文用例中的所有代碼都基于名為dm_db_missing_index_details 的 SQL Server 系統(tǒng)視圖。

背景

在開(kāi)始安裝前，進(jìn)一步了解 dm_db_missing_index_details 會(huì)更有益處。

dm_db_missing_index_details 會(huì)返回缺失索引的細(xì)節(jié)信息。在本文中，我們將更關(guān)注以下幾列：

  • index_handle：它是一個(gè)獨(dú)特的跨服務(wù)器標(biāo)識(shí)符，并且標(biāo)志一個(gè)特定的缺失索引。

  • equality_columns：包含用于相等謂詞的所有列

  • inequality_columns:包含用于其他比較的所有列

  • included columns索引中所包含的查詢必要出現(xiàn)列

  • statement: 補(bǔ)充完整索引缺失的表名

實(shí)現(xiàn)

本系統(tǒng)的實(shí)現(xiàn)基于以下三個(gè)實(shí)體：

  1. 一個(gè)可以計(jì)算出待創(chuàng)建索引名稱的簡(jiǎn)單函數(shù)

  2. 一個(gè)用于簡(jiǎn)化 dm_db_missing_index_details的用戶視圖

  3. 一個(gè)為每個(gè)索引創(chuàng)建聲明的進(jìn)程

筆者選擇將這個(gè)系統(tǒng)分為三段進(jìn)程，但實(shí)際上合并存儲(chǔ)過(guò)程和視圖也是可行的。筆者之所以沒(méi)有選擇后一種做法是因?yàn)橄朐趧?chuàng)建索引之前先從業(yè)務(wù)邏輯檢查一下存在哪些索引。

使用代碼

函數(shù) fn_Index_CreateIndexName 
在這個(gè)函數(shù)中，有三個(gè)輸入?yún)?shù)：

     @equality_columns
     @equality_columns
     @index_handlE

該函數(shù)的目的是為每個(gè)期望創(chuàng)建的索引都創(chuàng)建一個(gè)唯一名稱。

因此，首先拼接@equality_columns和@equality_columns兩個(gè)輸入變量，如果拼接后所得結(jié)果超過(guò)120個(gè)字符，那就截取至第120個(gè)字符。

為什么是120個(gè)字符？

因?yàn)樵赟QL Server中，命名最大長(zhǎng)度為128個(gè)字符。這個(gè)函數(shù)在 @index_handlE 名字結(jié)尾添加字段以便保證唯一的索引名。

    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 函數(shù)來(lái)計(jì)算缺失的索引名。

    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

存儲(chǔ)過(guò)程 usp_Index_MissingIndexCreationStatements 
該存儲(chǔ)過(guò)程基于 vw_Index_MissingIndex，并且輸出索引創(chuàng)建語(yǔ)句。

    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

以上是“如何使用系統(tǒng)SQL實(shí)體自動(dòng)創(chuàng)建非聚集索引”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

sql
AI
助
手