Requirement
今天遇到一个SQL Performance Tuning需求:
对于用户表的索引进行优化,剔出那些重复的索引,添加必要的索引。
那么,我们必须先把表中的索引给找出来,SQL Server数据库和其他数据库一样,都有强大的元数据表,所以我们也可以从元数据表中得到索引(INDEX)的信息。 要完成这个查询需求,会涉及到master数据库中的几个视图(VIEW):
- sys.[indexes]
- sys.[index_columns]
- sys.[all_columns]
- sys.[tables]
- sys.[schemas]
Resolution
SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH('')
), 2, 8000) AS KeyColumns,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR XML PATH('')
), 2, 8000) AS IncludedColumns,
Ind.type_desc AS IndexType,
CASE Ind.is_primary_key
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END AS IsPrimaryKey,
CASE Ind.is_unique
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END AS IsUnique
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
WHERE Ind.[name] is not null
ORDER BY TableName DESC
Explanation
上边这段代码的难点可能就是FOR XML PATH的用法。可以参考下面这篇博文:
Examples
在此我们拿Northwind数据库来演示代码执行后的结果。
TableName | IndexName | KeyColumns | IncludeColumns | IndexType | IsPrimaryKey | IsUnique |
[dbo].[Territories] | PK_Territories | TerritoryID | NULL | NONCLUSTERED | Y | Y |
[dbo].[Suppliers] | PK_Suppliers | SupplierID | NULL | CLUSTERED | Y | Y |
[dbo].[Suppliers] | CompanyName | CompanyName | NULL | NONCLUSTERED | N | N |
[dbo].[Suppliers] | PostalCode | PostalCode | NULL | NONCLUSTERED | N | N |
[dbo].[Shippers] | PK_Shippers | ShipperID | NULL | CLUSTERED | Y | Y |
[dbo].[Region] | PK_Region | RegionID | NULL | NONCLUSTERED | Y | Y |
[dbo].[Products] | PK_Products | ProductID | NULL | CLUSTERED | Y | Y |
[dbo].[Products] | CategoriesProducts | CategoryID | NULL | NONCLUSTERED | N | N |