SQLSERVER索引优化二

一、简介 自从你和你的团队成功的开发和部署了一个INTERNET网站,已经过去数月了,这个网站在很短的时间内吸引了数千用户前来注册和使用,因此你有了一个非常满意的客户。包括你和你的团队、管理层、客户，每个人都非常高兴。 生活并不总是一帆风顺的。当站点的

一、简介

自从你和你的团队成功的开发和部署了一个INTERNET网站,已经过去数月了,这个网站在很短的时间内吸引了数千用户前来注册和使用,因此你有了一个非常满意的客户。包括你和你的团队、管理层、客户，每个人都非常高兴。

生活并不总是一帆风顺的。当站点的用户开始日均高速增长的时候，问题随即出现了，客户发来邮件开始抱怨网站性能太慢，同时称网站正在丢失客户。

你开始调查这个系统，很快你发现当系统访问或更新数据的时候，速度非常慢。打开数据库一看，数据库的记录增加的很快，有些表的记录达到了成千上万行，测试团队在产品数据库上做了一个测试，结果发现在测试服务器上仅2/3秒就能完成的一个处理过程，现在需要5分钟。”

这个古老的故事发生在全球范围内的数以千计的系统身上。包括我在内，几乎每个开发人员在他或她的开发过程中会碰到同样的事情。我知道为什么这样的情形会发生，同时我也知道如何去克服它。

二、阅读范围

请注意本一系列文章讨论的主要的焦点是“事务性的SQLServer数据库数据访问性能优化”，但大部分优化技术同样适用于其他的数据库。

我将要讨论的优化技术仅仅适用于软件开发人员。作为一个开发者，你需要跟随我关注的问题，确认你已经作了所有能做的事情，去优化你已经写的或将要写的数据访问代码。数据库管理人员(DBA)同样在优化和提高性能上扮演了很重要的角色，但是DBA领域的优化将不属于这篇文章讨论的范围。

三、开始优化一个数据库

当基于数据库的应用系统放慢的时候，99%的可能是系统的数据访问过程没有优化，或者没有使用最好的方式。所以你需要回顾和优化你的数据访问/操作过程，提高系统的全局性能。接下来我们通过一步一步的方式开始我们的优化任务。

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

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

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

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

如果你发现有当前的数据库没有很好的处理索引，你建了索引，结果就是性能的快速提升。然而，如果索引已经处理了，我们进入下面的步骤。

什么是索引

我相信你已经明白了什么是索引，但是，我仍旧看到很多人对索引不太清楚。让我们再一次弄明白什么是索引，请看下面的小故事。

很久以前，在一个古城市里有一个很大的图书馆，里面有数以千计的图书，图书凌乱的存放在书架上。因此，一旦有读者向图书员索要一本图书，图书员除了一本一本的检查图书,看是否匹配读者索要的图书，其它没有更好的办法。发现一本渴望的图书往往需要花费图书员数个小时。同时读者也不得不等很长的时间。

[这看起来象一个没有主键的表，当在表里进行搜索数据的时候，数据库引擎需要遍历全部的数据来查找相关的记录，所以运行起来非常慢。]

当读者和图书每天都在大量增加的时候，图书员的工作越来越繁重。有一天，有一个智者来到图书馆，看到图书员的繁重的工作，建议他给每一本书编号，同时按顺序码放在书架上。“我可以从中得得什么好处?”图书员问，那个智者回答到:“如果有读者通过给你一个书号来索要图书，你很快就能发现在哪个书架上存放了包含该书号的图书，然后在这个书架上，你同样能很快的找到需要的图书”

[给书编号就象在数据表里创建一个主键,当你在一个表里创建了一个主健后,系统就创建了一个聚集索引树,所有的包含记录的数据页按照主键的值在文件系统中进行排序.每一个数据页内部也同样按照主键的值进行排序.所以,当你向数据库请求任何一个数据行的时候,首先数据库服务器使用聚焦索引找到合适的页 (象首先发现书架一样),接着在页里查找包含主键值的记录(象在书架发现一本书)]

“这正是我所需要的”,兴奋的图书员开始给书编号,接着把它们排列在不同的书架上,他花费了一天的时间来排序.在那天快结束的时候,他做了测试,结果发现几乎不用花费时间就能找到一本书.图书员高兴极了.

[这正是你创建了主键后所发生的事情.首先,创建了聚焦索引,接着数据页在物理文件里按照主键的值被排序.有一点我想你应该很容易理解,因为数据仅仅只能使用一列的值作为凭证来排序,所以一个表只能创建一个聚焦索引.就象图书只能使用一个标准即书号来排序一样.]

等一等,问题还没有被完全解决,在接下来的时间里,有个读者没有图书的编号,只有图书的名字,他想通过书名索要图书,如何办呢?可怜的图书员只能按照从1到N来查遍所有已经编号的图书.如果图书存放在67号书架上,他可能需要20分钟,相比早间图书没有被排序的时候,他所花费的2-3个小时.这确实有一个进步.但是和花费30秒通过书号查找一本书比较起来,,20分钟仍旧是一个不短的时间.还有没有更好的办法呢?他问那个智者。

[假设你有一个产品表,如果你只有一个ProductID主键而没有其它的索引,上述的情况同样会发生,所以,当使用产品名字来搜索的时候,数据引擎只能遍历文件里所有物理排序的数据页,没有其它的办法.]

那个智者告诉图书员:因为你已经按照书号对图书做了排序,你不能使用其它的凭证重新排序,所以,较好的方法是创建一个包含书名和与之对应的编号的目录或索引,在这个目录上,按照图书的字母顺序排序,并使用阿拉伯字母进行分组,例如,当有人想查找DatabaseManagementSystem这本书的时候,你使用下列的规则就能发现这本书

1.在书名目录里跳到D章,找到包含你的书名的图书.

2.得到这本书的书号,然后用书号去查找这本书

“你真是一个天才”,图书员喊到,他立即花费了一些时间创建了书名的目录,通过一个快速的测试,他发现使用书名来查询仅仅需要1分钟,其中30秒查找书的编号,30秒用编号来找书.

图书员想到,读者还可能使用其它的凭证来查找图书,例如作者的名字,所以他为作者创建了同样的目录.在创建了这些目录后,图书员可以使用这些凭证在 1分钟内找到图书.图书员的繁重的工作终于结束了,许多读者也因为很快的查找到图书而聚集在图书馆,图书馆变的非常热闹起来.

图书员随后开始过着他的快乐的生活,故事结束了.

到这里，现在我确信你已经明白了什么是索引,为什么它们如此重要以及它们的内部工作原理,,例如,我们有一个已创建聚焦索引的产品表 Products,因为当创建了主键的时候,随即就创建了聚焦索引。我门应当在Productname列创建一个非聚焦索引,一旦我们这样作了,数据库引擎就为非聚焦索引创建一个索引树,象故事里的书名目录，按照产品的名字在索引页里排序。每个索引页包含一定范围的产品名字和与之对应的 ProductID，所以当使用产品名字作为凭证搜索的时候，数据库引擎首先查询产品名字的非聚焦索引树来发现这本书的主键productID，一旦发现，数据库引擎就使用主键ProductID来搜索聚焦索引树，从而并得到正确的结果。

索引树的工作原理如下图：

上图被称做为B+树，中间的节点包含一定数量的值，指示数据库引擎当从跟节点搜索一个索引值的时候如何遍历.如果这是一个聚焦索引树，页节点是物理数据页.如果是非聚焦索引树，页节点包含包含索引值和与之对应的聚焦索引值.

通常，在索引树里发现需要的值并且转到目标数据记录，对于数据库引擎来说花费的时间是很短的，所以，在数据库应用索引极大的提高了数据的检索操作.

请跟随下列的步骤确保正确的索引包含在你的数据库里。

确保数据库的每个表有一个主健

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

在这些列上创建非聚焦索引

经常被作为搜索凭证的列

用来联合其它表的列

用来作为外健的列

用来排序的列

高选择性列

Xml类型

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

CREATEINDEX<p>NCLIX_OrderDetails_ProductIDON
</p><p>dbo.OrderDetails(ProductID)</p>

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

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

现在，你是否已经在数据库创建了所有的适合的索引?假设，在一个Sales表 (SelesID,SalesDate,SalesPersonID,ProductID,Qty)，你已经在外键(ProductID)创建了索引，如果ProductID是一个高选择性列，任何在where语句里使用索引列(ProductID)的检索数据的SELECT查询都会运行的非常快吗?

对，相对没有在外键创建索引的情况(这需要全部数据页的遍历)来说，这是非常快的，但是，还有进一步提升的空间.

让我们假设：Sales表包含10,000行数据，下面的SQL语句选择400行。

SELECTSalesDate,SalesPersonIDFROMSalesWHEREProductID=112

首先让我们弄明白在数据库引擎怎么执行SQL语句的:

1.Sales表有在ProductID列一个非聚焦索引，所以，首先查询非聚焦索引树，发现包含ProductID=112的入口。

2.包含ProductID=112入口的索引页同样同样也包含了聚焦索引的值(所有的主健的值，即SalesID)

3.对于每一个主健(共400个)，数据库引擎进入聚焦索引树来发现正确的行的位置

4.对于每一个主健，一旦发现正确的行的位置，数据库引擎会从匹配的行得到SalesDate和SalesPersonID的列的值。

请注意，在上述的步骤中，对于每一个ProductID=112的主键入口(共400个)，数据库引擎必须搜索聚焦索引树400次，来检索附加的列 (SalesDate,SalesPersonID)。

让我们猜想一下,如果非聚焦索引不但包含了聚焦索引的值(主健)，同时还包含查询里标注的其他的2个列 (SalesDate,SalesPersonID)的值，数据库引擎就不用执行上述的第3步和第4步，只须进入ProductID的列的非聚焦索引树，从索引页上读取3个列的值，这样运行的速度不是更快吗?

幸运的是，有一种办法来实施这种特点，这就是复合索引。你可以在表的列上创建复合索引，标明哪些列是和聚焦索引一起的应该附加存储的列。下面是一个在表Sales表的列ProductID创建复合索引的例子。

CREATEINDEXNCLIX_Sales_ProductID--Indexname<p>ONdbo.Sales(ProductID)--Columnonwhichindexistobecreated
</p><p>INCLUDE(SalesDate,SalesPersonID)--Additionalcolumnvaluestoinclude</p>

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

当创建复合索引的时候，尽量使用DatabaseTuningAdvisor(数据库优化顾问)的帮助。

我们知道，一旦一个SQL开始运行，SQLSERVER引擎优化器基于以下几点动态的产生不同的检索计划。

数据量

统计

索引变化

TSQL的参数值

服务器的负载

这意味着：对于一个特殊的SQL语句，在产品服务器上的执行计划可能和在测试服务器上的执行计划不近相同，甚至表和索引结构一样。这同样也表明，一个在测试服务器上创建的索引可能会加速测试服务器上的性能，但是在产品服务器上的同样的索引可能不会带给你任何益处。为什么?因为在测试环境下的 SQLSEVVER执行计划可能使用创建的索引，因此给你很好的性能，但是，在产品服务器上的执行计划可能出于下列的原因而根本不使用新创建的索引。例如：一个非聚焦索引列在产品服务器上不是高选择性列，而在测试服务器上是高选择性列.

所以，当创建索引的时候，我们需要弄明白这一点：索引是执行引擎用来提高速度的。但是我们该如何去做呢?

答案是我们必须在测试服务器上模拟产品服务器的负载，接着创建索引，以及测试他们。只有这样，在测试服务器上能提高性能的索引，才能更有可能在产品服务器上提高性能。

这么做应该很困难，但幸运的是，我们有一些好用的工具去实现它，请跟随下面的指导：

1：使用SQLprofiler捕获产品服务器上的痕迹。使用Tuningtemplate(我知道，有人建议不要在产品服务器上使用 SQLprofiler,但有些时候，你不得不在产品服务器上诊断性能问题的时候使用它)，如果你不熟悉这个工具，或者你想了解更多的关于 SQLprofiler的知识，请阅读http://msdn.microsoft.com/en-us/library/ms181091.aspx

2.利用上一步产生的跟踪文件，用数据库优化顾问在测试数据库创建相似的负载,从优化顾问得到一些建议，特别是创建索引的建议，你很可能从优化顾问那里获得比较实际的建议。因为优化顾问使用产品服务器产生的跟踪文件来装载测试服务器，所以能产生最可能好的索引建议。如果你不熟悉优化顾问工具，或者你想了解更多的关于使用优化顾问的的资料，请阅读：http://msdn.microsoft.com/en-us/library /ms166575.aspx.

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

到了这里，如果你已经在表里创建了所有正确的索引，但是，你可能还没有获得所希望的良好的性能。什么原因呢?有一种可能是出现了索引碎片。

1、什么是索引碎片

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

有2种类型的碎片：

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

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

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

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

SELECTobject_name(dt.object_id)Tablename,si.name<p>IndexName,dt.avg_fragmentation_in_percentAS
</p><p>ExternalFragmentation,dt.avg_page_space_used_in_percentAS
</p><p>InternalFragmentation
</p><p>FROM
</p><p>(
</p><p>SELECTobject_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
</p><p>FROMsys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED'
</p><p>)
</p><p>WHEREindex_id0)ASdtINNERJOINsys.indexessiONsi.object_id=dt.object_id
</p><p>ANDsi.index_id=dt.index_idANDdt.avg_fragmentation_in_percent>10
</p><p>ANDdt.avg_page_space_used_in_percent</p>

上面的查询显示的AdventureWorks’数据库的索引碎片信息如下：

分析上面的结果，你就能发现在哪里出现了索引碎片，应用下面的规则：

ExternalFragmentation的值>10，预示对应的索引出现外部碎片。InternalFragmentation的值

3、怎样重新整理索引碎片

有2种方式：

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

ALTERINDEXALLONTableNameRECOGNIZE

索引重建：

ALTERINDEXALLONTableNameREBUILDWITH(FILLFACTOR=90,ONLINE=ON)

通过使用具体索引的名字代替ALL,你能重组或重建单个的索引。你也可以使用数据库控制台来重建/重组索引

4、什么时候重组和重建索引?

当外部碎片的值在10-15，内部碎片的值在60-75，对于这样的索引，你应该重组索引。否则，你应该重建索引。

关于索引重建的一个重要的事情是：一旦在一个特定的表上重建索引，表就会被锁定(重组的时候不会发生)。所以，对于一个产品数据库的一个大的表，因为在一个大表上的索引重建往往需要花费数个小时，我们不希望这种锁定。幸运的是，在SQL2005有一个解决方法，你可以在重建一个表的索引的时候，把 ONLINE选项的值设为ON，这样会使重建索引和表上的数据事务同样进行。

四、实现数据访问结束语

在数据表里的所有适合创建索引的字段上创建索引，这是非常诱惑人的。但是如果你正在从事一个事务数据库工作，在每个字段上创建索引并不是每次都是需要的。事实上，在一个OLTP系统上创建大量的索引可能会降低数据库的性能。(因为当很多操作是更新操作的时候，更新数据意味着更新索引)

一个首要的规则建议如下：

如果你在从事一个事务性数据库，平均不要在一个表上创建超过5个索引，另外，如果你在从事数据仓库，平均最高可在一个表上创建10个索引。