摘要:編者按本文主要介紹使用系統實體自動創建非聚集索引。會返回缺失索引的細節信息。視圖該視圖基于和表,并使用函數來計算缺失的索引名。存儲過程該存儲過程基于,并且輸出索引創建語句。
【編者按】 本文主要介紹使用系統 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
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/17518.html
摘要:中面向行和面向列的操作基本是平衡的。用層次化索引,將其表示為更高維度的數據。使用浮點值表示浮點和非浮點數組中的缺失數據。索引的的格式化輸出形式選取數據子集在內層中進行選取層次化索引在數據重塑和基于分組的操作中很重要。 我們在上一篇介紹了 NumPy,本篇介紹 pandas。 pandas入門 Pandas 是基于Numpy構建的,讓以NumPy為中心的應用變的更加簡單。 pandas...
閱讀 3585·2023-04-26 01:43
閱讀 2972·2021-10-14 09:42
閱讀 5404·2021-09-30 09:59
閱讀 2172·2021-09-04 16:40
閱讀 1208·2019-08-30 15:52
閱讀 822·2019-08-29 17:09
閱讀 1993·2019-08-26 13:37
閱讀 3432·2019-08-26 10:20