ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 翻译:聚集索引键的寻找方法

翻译:聚集索引键的寻找方法

原创 Linux操作系统 作者:bigholy 时间:2008-12-12 14:48:08 0 删除 编辑

原文地址:http://www.mssqltips.com/tip.asp?tip=1642
问题:

  对于刚创建的新表来说,要完全了解数据的访问模式是很困难的。因此对于聚集索引键的选择通常是使行唯一的ID,这或许是个好的选择,但是,在
应用程序使用了一段时间后,加上数据的访问统计数据,需要回过头来调整表的聚集索引键以提供更高的查询性能。本文向大家介绍一种如何寻找表中最佳的列
作为聚集索引的方法。
解决方法:
  为方便描述,这里采用AdventureWorks数据库为例,使用Person.Address表。
  首先,执行sp_helpindex 'Person.Address'来查看表的索引分布情况:
   

要收集索引使用统计信息,运行以下查询5次:

SELECT AddressLine1AddressLine2
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 

         
INNER JOIN sys.indexes AS 

           
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]
ALTER TABLE [Sales].[CustomerAddress] DROP CONSTRAINT 
[FK_CustomerAddress_Address_AddressID]
ALTER TABLE [Purchasing].[VendorAddress] DROP CONSTRAINT 
[FK_VendorAddress_Address_AddressID]
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT 
[FK_SalesOrderHeader_Address_ShipToAddressID]
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT 
[FK_SalesOrderHeader_Address_BillToAddressID]
ALTER TABLE Person.Address DROP CONSTRAINT 
PK_Address_AddressID

CREATE CLUSTERED INDEX IX_StateProvinceID ON Person.Address(StateProvinceID)


由于重建了聚集索引,现在重新运行先前的查询,再查看其新的执行计划:

SELECT AddressLine1AddressLine2
FROM 
Person.Address
WHERE StateProvinceID 1


以下是新的执行计划:


从图中不难发现,仅有“聚集索引查找”而不存在先前出现过的“键查找”。

下面我们来查看一下索引的使用统计情况:


小结:
  通过上述方法可以让我们判断表的聚集索引键的选择,希望本文对您遇到如何选择聚集索引有所帮助。

index.JPG

index2.JPG

index3.JPG

index4.JPG

index5.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-510178/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    83741