溫馨提示×

                    如何使用系統SQL實體自動創建非聚集索引

                    發布時間:2021-11-23 17:56:23 來源:億速云 閱讀:98 作者:小新 欄目:編程語言

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

                    引言

                    一直以來,關于索引的常見問題是:判斷哪部分索引對保證數據庫的良好性能是必需的。在本文中,筆者將提供針對該問題的解決方案。本文用例中的所有代碼都基于名為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 
                    在這個函數中,有三個輸入參數:

                         @equality_columns
                         @equality_columns
                         @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

                    以上是“如何使用系統SQL實體自動創建非聚集索引”這篇文章的所有內容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內容對大家有所幫助,如果還想學習更多知識,歡迎關注億速云行業資訊頻道!

                    免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

                    sql
                    免費撥打  400 100 2938 免費撥打 400 100 2938
                    24小時售后技術支持 24小時售后技術支持
                    返回頂部 返回頂部
                    毛茸茸逼喷白浆_毛茸茸的BBB老妇人_毛茸茸的的肥婆阴户