以下的文章主要描述的是 SQL Server 使用索引来实现数据访问优化的实际操作步骤,我前两天在相关网站看见SQL Server 使用索引来实现数据访问优化的实际操作步骤的资料,觉得挺好,就拿出来供大家分享。
第一步:在列上采用正确的索引
有些人可能争论实施正确的索引是否是数据库优化过程的第一步。但是我认为在数据库应用正确的索引是第一位的。原因有两点:
1.在一个产品系统里,它将使你在很快的时间内提高尽可能大的性能。
2.创建数据库索引不需要你做任何的系统修改,因此不需要任何重新编译和部署
假如你发现有当前的数据库没有很好的处理索引,你建了索引,结果就是性能的快速提升。
(1 )首先,创建了聚焦索引(即主键),接着数据页在物理文件里按照主键的值被排序。
(2 )其次,在非主键列上创建一个非聚焦索引,将该列和主键绑定在一起,但是要按照该列进行排序。
策略:
(1 )确保数据库的每个表有一个主健,这么做会确保每个表有一个聚焦索引,通过主健的值,表的数据页通按物理顺序排列在磁盘上。所以任何使用主健的数据检索操作 , 任何在主健字段的排序操作都能非常迅速的检索数据。
(2 )在这些列上创建非聚焦索引: A : 经常被作为搜索凭证的列; B :用来联合其它表的列; C :用来作为外健的列; D :用来排序的列; E :高选择性列; F : Xml 类型
下面是一个创建索引的命令的例子:
1 CREATE INDEX NCLIX_OrderDetails_ProductID ON dbo.OrderDetails(ProductID)
你也可以使用SQL Server 控制台在需要的列上创建索引。
第二步:创建正确的复合索引
下面是创建复合索引的例子:
2 CREATE INDEX NCLIX_Sales_ProductID ON dbo.Sales(ProductID) INCLUDE(SalesDate,SalesPersonID)
请注重,创建复合索引应当包含少数几个列,并且这些列经常在select 查询里使用。在复合索引里包含太多的列不仅不会给你带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值,其后果是内存溢出和性能降低。
第三步:假如有碎片发生,重新整理它
1、什么是索引碎片
索引碎片是这样一种情形:由于在表里大量的插入、修改、删除操作而使索引页分裂。假如索引有了高的碎片,有两种情况,一种情况是扫描索引需要花费很多的时间,另一种情况是在查询的时候索引根本不SQL Server 使用索引 , 都会导致性能降低。
2、有两种类型的碎片:
内部破碎:由于索引页里的数据插入或修改操作而发生,以数据作为稀疏矩阵的形式的分布而结束,这将导致数据页的增加,从而增加查询时间。
外部破碎:由于索引/ 数据页的数据插入或修改而发生,以页码分离和在文件系统里不连贯的新的索引页的分配而结束,数据库服务器不能利用预读操作的优点,因为:下一个相关联的数据页不临近,而且这些相关连的下面的页码可能在数据文件的任何地方。
3、如何知道索引破碎是否已经发生 ?
在数据库执行下面的SQL 语句 ( 下面的语句在 SQLserver2005 及以后的版本运行正常,以你的目标数据库的名字取代 AdventureWorks)
3 SELECT object_name(dt.object_id) Tablename,si.name IndexName,dt.avg_fragmentation_in_percent AS ExternalFragmentation,
4 dt.avg_page_space_used_in_percent AS InternalFragmentation
5 FROM
6 (
7 SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
8 FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED')
9 WHERE index_id<>0
10 )
11 AS dt INNER JOIN sys.indexes as si ON si.object_id=dt.object_id
12 AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
13 AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC
分析查询结果,你就能发现在哪里出现了索引碎片,应用下面的规则:A : ExternalFragmentation 的值 >10 ,预示对应的索引出现外部碎片。 B : InternalFragmentation 的值 <75 ,预示对应的索引出现内部碎片。
4、什么时候重组和重建索引 ?
当外部碎片的值在10-15 ,内部碎片的值在 60-75 ,对于这样的索引,你应该重组索引。否则,你应该重建索引。
关于索引重建的一个重要的事情是:一旦在一个特定的表上重建索引,表就会被锁定( 重组的时候不会发生 ) 。所以,对于一个产品数据库的一个大的表,因 为在一个大表上的索引重建往往需要花费数个小时,我们不希望这种锁定。幸运的是,在 SQL2005 有一个解决方法,你可以在重建一个表的索引的时候,把 ONLINE 选项的值设为 ON ,这样会使重建索引和表上的数据事务同样进行。
5、怎样重新整理索引碎片。有两种方式:
索引重组:执行下面的命令:
14 ALTER INDEX ALL ON TableName RECOGNIZE
索引重建:
15 ALTER INDEX ALL ON TableName REBUILD WITH(FILLFACTOR=90,ONONLINE=ON)
通过使用具体索引的名字代替ALL, 你能重组或重建单个的索引。你也可以使用数据库控制台来重建 / 重组索引。以上的相关内容就是对 SQL Server 使用索引实现数据访问优化的介绍,望你能有所收获。