SQL Server查询性能优化――创建索引原则（二）(二)

Select * from sys.dm_db_missing_index_groups

Select * from sys.dm_db_missing_index_group_stats

Select * from sys.dm_db_missing_index_details

Select mig.*,statement as table_name,column_id,column_name,column_usage

From sys.dm_db_missing_index_details as mid

Cross apply sys.dm_db_missing_index_columns (mid.index_handle)

Inner join sys.dm_db_missing_index_groups as mig on mig.index_handle=mid.index_handle

Order by mig.index_group_handle,mig.index_handle,column_id

---在建立索引之后，再次执行以上语句。

接下来通过sys_dm_db_index_usage_stats可观察是否生成了过多的索引。

--插入数据会影响到索引

insert WBK_PDE_LIST_ORG_HISTROY

Select 'BE404942451001','60196928','11427','305','92','52083200'

,null ,'布料',null,'215',25,'011',25,'011',null,null,null,10.82,270.5,null,null,
null,5,3.8

表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数0，逻辑读取17 次，物理读取5 次，预读0 次，lob 逻辑
读取0 次，lob 物理读取0 次，lob 预读0 次。

--更新数据会影响到索引

--通过PK_WBK_PDE_LIST_ORG_HISTROY

--idx_WBK_PDE_LIST_QTY1

--idx_WBK_PDE_LIST_COP_G_NO索引扫描WBOOK_NO='BE404942451001'的记录

update WBK_PDE_LIST_ORG_HISTROY set QTY_1=50000

where WBOOK_NO='BE404942451001'

--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取9 次，物理读取0 次，预读0 次，lob
逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

Select * from sys.dm_db_index_usage_stats where object_id=object_id('WBK_PDE_LIST_ORG_
HISTROY')

图1，索引使用情况分布图　　　　

图2，索引名称

　　从上图1中可以看到 sys.dm_db_index_usage_stats系统视图是指某个查询利用索引所进行的
查找、扫描、查找或更新操作都被计为对该索引的一次使用，每次使用都会对视图中的相应计数器累加1。
它针对用户提交的查询所导致的操作，以及由系统内部产生的查询所导致的操作（例如，扫描以收集
统计数据）分开累积信息。而由于前述的insert语句会影响到之前建立的所有索引，所以index_id等于
1、6、10的记录行的user_updates字段为是1 （见图1中2）。update 语句会更新数据表中的QTY_1字段，
但是没有更新COP_G_NO字段，所以只影响index_id等于1与6的记录行，这两行的user_updates字段是2
（见图1中3）。update语句的where条件则会利用index_id等于1的索引，见user_seeks的值为1
（见图1中3）。

　　User_updates字段是指由于基础数据表或视图的插入、更新或删除操作导致的更新次数。利用这个
数据可判断应用程序是否很少用到某个索引。如果该索引的更新次数(user_updates)值很大，那么说明
产生的维护量比较大，再参见搜索次数(user_seeks)与书签查找操作的次数(user_lookups)，如是这两
个值很小，则可以考虑删除索引。

　　重新启动SQL SERVER服务时，sys.dm_db_index_usage_stats系统视图内的各种计数器会初始化为
空值。此外，每当分离或关闭数据时（例如，由于 AUTO_CLOSE 设置为 ON），就会删除所有与该数据库
关联的数据行。初次使用某个索引后，才会加入到系统的统计信息中，sys.dm_db_index_usage_stats
随后才看得到代表该索引的数据行，此时各项计数器的初始设置值为零。

　　最后再次重申一下，“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或
不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索
引甚至会导致索引碎片。

　　所以说，我们要建立一个“适当”的索引体系，特别是对聚集索引的创建，更应精益求精，以使您
的数据库能得到高性能的发挥。

　　因为非聚集索引需要在非聚集索引的B树中找到每一行的指针，再去其所在表上找数据，性能因此
会大打折扣，有时甚至不如不加非聚集索引。