缺失索引自動(dòng)創(chuàng)建語(yǔ)句

iamyoung001 發(fā)布于2019-06-25 18:19 / 1695人閱讀

摘要：編者按本文主要介紹使用系統(tǒng)實(shí)體自動(dòng)創(chuàng)建非聚集索引。會(huì)返回缺失索引的細(xì)節(jié)信息。視圖該視圖基于和表，并使用函數(shù)來(lái)計(jì)算缺失的索引名。存儲(chǔ)過(guò)程該存儲(chǔ)過(guò)程基于，并且輸出索引創(chuàng)建語(yǔ)句。

【編者按】本文主要介紹使用系統(tǒng) SQL 實(shí)體自動(dòng)創(chuàng)建非聚集(non-clustered)索引。作者為意大利軟件工程師 GhostHost(筆名)。

本文系 OneAPM 工程師編譯呈現(xiàn)，以下為正文。

引言

一直以來(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í)體：

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

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

一個(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ù)：

    1.  @equality_columns
    2.  @equality_columns
    3.  @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

OneAPM 助您輕松鎖定 .NET 應(yīng)用性能瓶頸，通過(guò)強(qiáng)大的 Trace 記錄逐層分析，直至鎖定行級(jí)問(wèn)題代碼。以用戶角度展示系統(tǒng)響應(yīng)速度，以地域和瀏覽器維度統(tǒng)計(jì)用戶使用情況。想閱讀更多技術(shù)文章，請(qǐng)?jiān)L問(wèn) OneAPM 官方博客。

本文轉(zhuǎn)自 OneAPM 官方博客

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

GPU云服務(wù)器云服務(wù)器索引缺失自動(dòng)索引自動(dòng)索引推薦 mongodb創(chuàng)建索引

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/17518.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

iamyoung001

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

keras與tensorflow

閱讀 3597·2023-04-26 01:43
接口測(cè)試——接口測(cè)試流程

閱讀 2984·2021-10-14 09:42
新手如何使用MTR工具：Linux網(wǎng)絡(luò)診斷工具教程

閱讀 5478·2021-09-30 09:59
詳細(xì)的安裝Python教程（Windows）

閱讀 2183·2021-09-04 16:40
CSS 基本測(cè)試題

閱讀 1220·2019-08-30 15:52
wepy 滑動(dòng)刪除功能

閱讀 838·2019-08-29 17:09
JS對(duì)象序列化、對(duì)象的toString()與對(duì)象的valueOf()

閱讀 2006·2019-08-26 13:37
我的頁(yè)面我做主——瀏覽器去廣告正確姿勢(shì)

閱讀 3440·2019-08-26 10:20

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

缺失索引自動(dòng)創(chuàng)建語(yǔ)句

相關(guān)文章

【數(shù)據(jù)科學(xué)系統(tǒng)學(xué)習(xí)】Python # 數(shù)據(jù)分析基本操作[二] pandas

數(shù)據(jù)科學(xué)

發(fā)表評(píng)論

0條評(píng)論

iamyoung001

男|高級(jí)講師

TA的文章

keras與tensorflow

接口測(cè)試——接口測(cè)試流程

新手如何使用MTR工具：Linux網(wǎng)絡(luò)診斷工具教程

詳細(xì)的安裝Python教程（Windows）

CSS 基本測(cè)試題

wepy 滑動(dòng)刪除功能

JS對(duì)象序列化、對(duì)象的toString()與對(duì)象的valueOf()

我的頁(yè)面我做主——瀏覽器去廣告正確姿勢(shì)

最新活動(dòng)

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

缺失索引自動(dòng)創(chuàng)建語(yǔ)句

相關(guān)文章

發(fā)表評(píng)論

0條評(píng)論

男|高級(jí)講師

TA的文章

最新活動(dòng)

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！