原文地址:http://www.mssqltips.com/tip.asp?tip=1642
问题:
对于刚创建的新表来说,要完全了解数据的访问模式是很困难的。因此对于聚集索引键的选择通常是使行唯一的ID,这或许是个好的选择,但是,在
应用程序使用了一段时间后,加上数据的访问统计数据,需要回过头来调整表的聚集索引键以提供更高的查询性能。本文向大家介绍一种如何寻找表中最佳的列
作为聚集索引的方法。
解决方法:
为方便描述,这里采用AdventureWorks数据库为例,使用Person.Address表。
首先,执行sp_helpindex 'Person.Address'来查看表的索引分布情况:
要收集索引使用统计信息,运行以下查询5次:
SELECT AddressLine1, AddressLine2 FROM Person.Address WHERE StateProvinceID = 1 |
此时,我们查看执行计划会发现在IX_Address_StateProvinceID上做的是“索引查找”,而在PK_Address_AddressID上使用的聚集索引上的“键查找”。
说明:
(1)“索引查找”所做的操作是扫描非聚集索引中匹配提供值的记录
(2)“键查找”所做的操作是查询聚集索引中的数据页
下面我们来查看一下索引的使用统计,执行下面的查询:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM sys.dm_db_index_usage_stats AS S INNER JOIN sys.indexes AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECT_NAME(S.[OBJECT_ID]) = 'Address' |
在执行上面的查询之前,重新启动SQL Server实例,查询到的数字应该与先前运行5次查询保持一致。
从“USER_SEEKS”列可以知道,IX_Address_StateProvinceID (非聚集索引)查找了5次,在PK_Address_AddressID (聚集索引)上
查找了5次。
假如在真实的环境中用户是采用上述的数据访问模式,将IX_Address_StateProvinceID 作为聚集索引键为最佳选择,同时聚集索引键可以
覆盖查询中的所有列,这样可以消除上面“执行计划”中96%的“键查找”。
既然知道了选用StateProvinceID作为聚集索引键,则需要一些工作来完成。
首先,需要删除现有的主键/聚集索引键,由于该表也受一些外键约束,因此也需要将外键一并删除。下面的查询说明了如何删除外键、主键
和创建新的聚集索引键。
在真实的环境下,你需要做的就是重建主键约束,并生成这些外键约束的脚本以备重建它们。
ALTER TABLE [HumanResources].[EmployeeAddress] DROP CONSTRAINT [FK_EmployeeAddress_Address_AddressID] |
由于重建了聚集索引,现在重新运行先前的查询,再查看其新的执行计划:
SELECT AddressLine1, AddressLine2 FROM Person.Address WHERE StateProvinceID = 1 |
以下是新的执行计划:
从图中不难发现,仅有“聚集索引查找”而不存在先前出现过的“键查找”。
下面我们来查看一下索引的使用统计情况:
小结:
通过上述方法可以让我们判断表的聚集索引键的选择,希望本文对您遇到如何选择聚集索引有所帮助。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-510178/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/355374/viewspace-510178/