当前位置：首页 > 系统运维 > 正文内容

SQL Server 使用索引来对数据访问进行优化

a8116255316年前 (2010-07-22)系统运维7

以下的文章主要描述的是SQL Server 使用索引来实现数据访问优化的实际操作步骤，我前两天在相关网站看见SQL Server 使用索引来实现数据访问优化的实际操作步骤的资料，觉得挺好，就拿出来供大家分享。

之一步：在列上采用正确的索引

有些人可能争论实施正确的索引是否是数据库优化过程的之一步。但是我认为在数据库应用正确的索引是之一位的。原因有两点：

1.在一个产品系统里，它将使你在很快的时间内提高尽可能大的性能。

2.创建数据库索引不需要你做任何的系统修改，因此不需要任何重新编译和部署

假如你发现有当前的数据库没有很好的处理索引，你建了索引，结果就是性能的快速提升。

（1）首先，创建了聚焦索引（即主键），接着数据页在物理文件里按照主键的值被排序。

（2）其次，在非主键列上创建一个非聚焦索引，将该列和主键绑定在一起，但是要按照该列进行排序。

策略：

（1）确保数据库的每个表有一个主健，这么做会确保每个表有一个聚焦索引，通过主健的值，表的数据页通按物理顺序排列在磁盘上。所以任何使用主健的数据检索操作,任何在主健字段的排序操作都能非常迅速的检索数据。

（2）在这些列上创建非聚焦索引：A：经常被作为搜索凭证的列； B：用来联合其它表的列； C：用来作为外健的列；D：用来排序的列；E：高选择性列； F：Xml类型

下面是一个创建索引的命令的例子：

CREATE INDEX NCLIX_OrderDetails_ProductID ON dbo.OrderDetails(ProductID)

你也可以使用SQL Server控制台在需要的列上创建索引。

第二步：创建正确的复合索引

下面是创建复合索引的例子：

CREATE INDEX NCLIX_Sales_ProductID ON dbo.Sales(ProductID) INCLUDE(SalesDate,SalesPersonID)

请注重，创建复合索引应当包含少数几个列，并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给你带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。

第三步：假如有碎片发生，重新整理它

1、什么是索引碎片

索引碎片是这样一种情形：由于在表里大量的插入、修改、删除操作而使索引页分裂。假如索引有了高的碎片，有两种情况，一种情况是扫描索引需要花费很多的时间，另一种情况是在查询的时候索引根本不SQL Server 使用索引,都会导致性能降低。

2、有两种类型的碎片：

内部破碎：由于索引页里的数据插入或修改操作而发生，以数据作为稀疏矩阵的形式的分布而结束，这将导致数据页的增加，从而增加查询时间。

外部破碎：由于索引/数据页的数据插入或修改而发生，以页码分离和在文件系统里不连贯的新的索引页的分配而结束，数据库服务器不能利用预读操作的优点，因为：下一个相关联的数据页不临近，而且这些相关连的下面的页码可能在数据文件的任何地方。

3、如何知道索引破碎是否已经发生

在数据库执行下面的SQL语句(下面的语句在SQLserver2005及以后的版本运行正常，以你的目标数据库的名字取代AdventureWorks)

SELECT object_name(dt.object_id) Tablename,si.name IndexName,dt.avg_fragmentation_in_percent AS ExternalFragmentation, dt.avg_page_space_used_in_percent AS InternalFragmentation FROM ( SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED') WHERE index_id<>0 ) AS dt INNER JOIN sys.indexes as si ON si.object_id=dt.object_id AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10 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、怎样重新整理索引碎片。有两种方式：

索引重组：执行下面的命令：

ALTER INDEX ALL ON TableName RECOGNIZE

索引重建：

ALTER INDEX ALL ON TableName REBUILD WITH(FILLFACTOR=90,ONONLINE=ON)

通过使用具体索引的名字代替ALL,你能重组或重建单个的索引。你也可以使用数据库控制台来重建/重组索引。以上的相关内容就是对SQL Server 使用索引实现数据访问优化的介绍，望你能有所收获。