【編者按】 本文主要介紹使用系統(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
