[转]SQL Server 从 【索引梳理】 到 【查询性能调优】

转自http://bbs.51cto.com/viewthread.php?tid=1144146&extra=&page=2

二：什么是索引

　　在 SQL Server 中，索引是按 B 树结构进行组织的。

　　您也可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，举例来说明一下聚集索引和非聚集索引的区别：

　　其实，新华字典的正文本身就是一个聚集索引。比如，我们要查“按”字，就会很自然地翻开字典的前几页，因为“按”的拼音是“an”，而按照拼音排序的新华字典是以英文字母“a”开头并以“z”结尾的，那么“按”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明新华字典中没有这个字；同样的，如果查“招”字，那也会将新华字典翻到最后部分，因为“招”的拼音是“zhao”。也就是说，新华字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

　　如果您碰到一个不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63 页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后 再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

 index.png (223.16 KB)

2015-2-11 11:52

通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下。


聚集索引

　　聚集索引指的是数据表本身就是索引的一部分，就是指数据表本身就是聚集索引的子叶层，整个数据表的摆放顺序是按照你选定的键值由小到大排序，SQL SERVER  2000 之后的版本可指定数据由大到小排序。

　　整个数据表按照键值字段由小到大排序，再搭配由键值字段加上指针的上层索引结构，也就是根节点和非子叶层级，形成整个聚集索引。因为数据表内实际摆放数据的方式只能遵循一种顺序，所以一个数据表只能有一个聚集索引。在指定聚集索引时，数据域本身并不需要唯一，或指定为唯一的聚集索引，SQL SERVER内部会自动为重复的键值建立4个字节的唯一标识。

　　如果你的数据表有一列常常用来排序，另一列常常用来 范围查询，还有一列重复性非常高，则该用哪一列来做聚集索引。正确答案是依据哪个查询最重要，最常被用户执行。例如：你的老板一小时内多次执行某个查询当然比一个月执行一两次的查询来得重要。

　　表（堆）创建聚集索引或删除和重新创建现有聚集索引时，要求数据库具有额外的可用工作区来容纳数据排序结果和原始表或现有聚集索引数据的临时副本。

　　当堆或聚集表具有多个分区时，每个分区都有一个堆或 B 树结构，其中包含该指定分区的行组。例如，如果一个聚集表有 4 个分区，那么将有 4 个 B 树，每个分区一个。



　　聚集索引（ Clustered Index）

　　·        聚集索引的叶节点就是实际的数据页

　　·        在数据页中数据按照索引顺序存储

　　·        行的物理位置和行在索引中的位置是相同的

　　·        每个表只能有一个聚集索引

　　·        聚集索引的平均大小大约为表大小的 5%左右



　　要使用索引来更有效地排序查询数据，最直接的方式就是在你要排序的字段上建立聚集索引。在建立聚集索引之后，SQL SERVER会重新组织数据页，让其中的数据行按照聚集索引中键值的顺序存储。SQL SERVER不需要在硬盘上的数据一定要实际按照聚集索引排序，但在建立聚集索引时，会尝试在逻辑上排序数据的同时，也会在物理上让数据尽可能地排序。在索引子叶层级中的每个数据页都有一个指针指向索引分页的前一页与后一页，形成双向链接串行，在内部的系统数据表包含了各索引子叶层第一个分页的地址，为了保证数据在逻辑上是依照聚集索引的顺序存放的，SQL SERVER 只需要由第一个分页开始，并依照其连接串行一个接着一个依序寻找数据即可。如下图。

注：聚集表是有聚集索引的表。

 Clustered_Index.png (203.09 KB)

2015-2-11 13:07

 

非聚集索引

　　非聚集索引是完全独立于数据表之外的结构，所以不会影响数据行的顺序，其子叶层包含索引行。每个索引行包含非聚集键值、行定位符和任意包含列或非键列。行定位符中存入的数据有两种类型：书签（BOOKMARK）或聚集索引的键值。如果数据表上建立了聚集索引，则行定位符中存入的数据就是聚集索引的键值。如果数据表没有建立聚集索引，则行定位符中存入的数据就是书签，即指向数据表中记录具体位置的ROWID，也就是文档编号、分页编号与页内记录编号（称之为SOLT编号）所组合成的值。通过该ROWID 在数据表内获取数据就称为书签查找 BOOKMARK LOOKUP。所以，一般通过非聚集索引查找到符合的键值后，还会搭配书签查找。

　　当非聚集索引从结构中找到符合的记录时，虽然在子叶层该键值是由小到大排序，因此可能在一个分页上就有全部符合查询条件的键值，但因为数据表中数据行的摆放是没有按顺序的（或是说没有按照该非聚集索引的键值顺序摆放），所以真正符合记录的数据是散布在文档各处的，而SQL SERVER每次读取数据都是以数据页为单位，因此，找到一条记录所在位置后，要先将存放该条记录的分页读到内存中，再从该页读出记录。

　　因为BOOKMARK LOOKUP是进行随机的I/O操作，当符合查询的记录很多时，通过非聚集索引访问将导致数据页读取非常频繁，就算两条记录在同一个分页，该分页也会被重复读两次，因此或符合的记录有N条，就需要读取数据表内的分页N页，虽然大部分的读取操作都是针对内存中的高速缓存，但记录数过多时一样没有效率，还不如数据表扫描，全部扫描一遍，把符合条件数据找出来。

　　虽然 SQL 2005 以后的版本中已经不在提 BOOKMARK LOOKUP了(但实际上却是换汤不换药)，我们的很多搜索都是使用如下的搜索过程：先在非聚集中找，然后再在聚集索引中找。如下图。



　　非聚集索引 （ Unclustered Index）  

　　·        非聚集索引的页，不是数据，而是指向数据页的页。

　　·        若未指定索引类型，则默认为非聚集索引

　　·        叶节点页的次序和表的物理存储次序不同

　　·        每个表最多可以有 249个非聚集索引（一般认为每个表不应该超过10个索引）

　　·        在非聚集索引创建之前创建聚集索引（否则会引发索引重建）

 Non-Clustered_Index.png (61.78 KB)

2015-2-11 13:59

 

聚集索引与非聚集索引使用的情况：

 IndexCompare.png (9.39 KB)

2015-2-11 14:03

 

索引的建立原则：

　　一般来说，建立索引要看数据使用的场景，换句话来说哪些访问数据的SQL语句是常用的，而这些语句是否因为缺少索引（也有可能是索引过多）变的效率低下。但绝不是所有的SQL语句都要建立索引，如果所有的SQL语句都建立索引，那么可能导致建立过多的索引。

　　我碰到过每秒钟新增记录超过千条的案例，虽然该数据表仅有聚集索引，但因为已存在的键值字段的值和新增数据键值字段的值并不是按顺序递增，每次新增记录时，肯定造成整体数据行的重新排列。在移掉聚集索引后，性能约提升20%。也曾经碰到过一个数据表上有20个索引，结果新增一条记录需要耗时4秒钟才能完成。

　　另一个案例中，POS系统中的销售数据变更，要同时更新多个数据表，每个数据表都有数十万条记录以上，但所使用的WHERE字段没有有效的索引，除查找缓慢外，导致更新时产生了大量的锁定。各数据表加上应有的索引后，原来要几十秒的更新，不到一秒钟便完成了。

　　另外，根据数据库的使用时机，也有可能先建立索引，用完后再删除。例如，当你做年报表、季报表时需要大量查询各种数据，可以考虑在生成报表之前建立相关索引。但在报表生成完毕后，为了保证平时新增、修改和删除操作的运行效率，再删除为了生成报表所建立的相关的索引。

　　而针对SQL语句或视图是否值得建立索引的问题，则有以下几个可以参考的方面。 



第一、选择性


　　选择性表示符合你查询条件的记录占总记录的百分比，也就是

　　选择性=符合查询条件的记录数量/总记录数量

　　这个值越小越好，越小代表选择越高，越适合采用索引。例如 ：

　　

复制内容到剪贴板

代码:

select * from WBK_Goods_Info where COP_G_NO='00078027'

　　在WBK_Goods_Info 表内符合这个条件的记录只有1条，而整个数据表有100000条记录，因此该查询的选择性是1/100000，这代表非常高的选择性，如此通过索引来查找数据才有效率。反过来说，或你的语句如下：

　　

复制内容到剪贴板

代码:

Select * from WBK_Goods_Info with(index(idx_cop_g_no)) where COP_G_NO>'00018000'

　　这时符合查询记录达82000条，选择性变为82000/100000，说明选择性非常低，除非以COP_G_NO字段为键值建立的索引是聚集索引，否则如果采用非聚集索引来访问，反而变成需要读取至少82000次数据页，因为SQL SERVER在读取每一条记录时，都是先将整个数据页读取（请记住，这是SQL SERVER读取数据的基本单位），再从中取出目标记录。就算两条记录存在同一数据页上，也要读该数据页两次。因此在选择性很低时，通过非聚集索引访问是非常没有效率的访问方式，还不如直接进行全表扫描。


第二、数据密度


　　数据密度是指键值惟一的记录条数分之一，也就是

　　数据密度=1/键值惟一的记录数量

　　通过以下语句进行测试：在WBK_PDE_LIST_ORG_HISTROY数据表的COP_G_NO字段建立索引，而后通过dbcc show_statistics语句查询存储系统内关于该索引的统计信息的记录，而后再应用上方的公式，以测试是否与存储在系统内的ALL Density字段值是否相同：

复制内容到剪贴板

代码:

--创建索引

CREATE NONCLUSTERED INDEX [idx_WBK_PDE_LIST_COP_G_NO] ON [dbo].[WBK_PDE_LIST_ORG_HISTROY] 

(

[COP_G_NO] ASC

)

INCLUDE ( [WBOOK_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]) 

--返回all desity字段的值

DBCC SHOW_STATISTICS ('WBK_PDE_LIST_ORG_HISTROY','idx_WBK_PDE_LIST_COP_G_NO')

--计算all desity字段的值

Select 1.0/(select count(distinct COP_G_NO)  from WBK_PDE_LIST_ORG_HISTROY) [All Density]

　　当数据密度越小，也就是惟一性越高时，代表该字段越适合建立索引，因为当总数据条数乘上该密度值，就是一般平均查询到的记录数字。


第三、数据分布


　　 数据分布代表多条数据记录组成的方式，与密度的概念有关。它代表数据记录是平均散布在一段范围内，还是集中在部分区块。


　　以我们的范例而言，每一种货物的货物编号都是自增且惟一的，也就是货物信息表(wbk_goods_info)中有100000种货物，以2000的倍数为值域的切分点，则各数据范围内的记录条数是相等的，此种分布称为平均分布。或数据类型如此，则要计算某个查询条件的选择性是否很高就相当的容易且精准。

　　如果数据是标准分布的，也就是说数据在有些范围内多，有些范围内少，以这个例子来说，就是有些货物的销售记录很多，有些货物可能基本上没有销售记录，则该索引就需要有更细致的统计数据，以记录一个范围的数据约略有多少条记录，在查询优化程序判断某个索引是否适用某项查询时，才可以精确判断出该字段的选择性是否很高，以决定使用的索引。

　　这也就是当你观察Dbcc show_statistics时(如上图)，如果呈现的分布记录有很多条，表示该键值在整个记录中是标准分布，所以需要各区段的记录数目，以较为精确地判断符合条件的记录数多少，若只有寥寥三四笔，表示接近平均分布，只需要描述平均分布的状态即可。


第四、索引的有效性


      在根据以上三原则建立相应的索引之后，我们再来看看如何观察在建立索引后，查询语句是否变得较有效率，索引的使用效率是否高。

复制内容到剪贴板

代码:

--没有索引的情况

Set statistics io on

Select [WBOOK_NO]      ,[COP_G_NO]      ,[G_NO]

      ,[CONTR_ITEM]      ,[CODE_S]      ,[CODE_T]

     ,[G_NAME]      ,[G_MODEL]   ,[G_QTY]      ,[G_UNIT]      ,[QTY_1]      ,[UNIT_1] ,[TRADE_CURR]      ,[DECL_PRICE]      ,[TRADE_TOTAL]     ,[GROSS_WT]      ,[NET_WT] from WBK_PDE_LIST_ORG_HISTROY c

Where c.WBOOK_NO='BE404942450001' and c.COP_G_NO='60196928' and QTY_1>15

复制内容到剪贴板

代码:

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中可以看到 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树中找到每一行的指针，再去其所在表上找数据，性能因此会大打折扣，有时甚至不如不加非聚集索引。

 图1，索引使用情况分布图.png (171.19 KB)

2015-2-11 14:15

 图2，索引名称.png (46.78 KB)

2015-2-11 14:15

IO的解读以及索引锁片的影响：



第一：先SET STATISTICS IO之后出现的关键信息如下

表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1，逻辑读 4825 次，物理读 6 次，预读 19672 次。

这句解释一下。



SQL SERVER 数据库引擎当遇到一个查询语句时，SQL SERVER数据库引擎会分别生成执行计划(占用CPU和内存资源)，同时存储引擎读取 IAM 以生成必须要读取的磁盘地址排序列表。这使 SQL Server 得以将其 I/O 优化为大型有序读取，根据它们在磁盘上的位置按顺序完成。磁盘中取得需要取的数据(占用I/O资源，这就是预读)，注 意，两个步骤是并行的，SQL SERVER通过这种方式可以让计算和 I/O 重叠进行，从而充分利用 CPU 和磁盘，从而提高性能。



扫描计数：查询数据时对涉及到的表被 访问次数或涉及到的索引的扫描次数。在我们的例子中，不管是表扫描（例一与例二）还是索引扫描（例三）其中的表或索引都只被访问了1次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。
　　一个循环外部的表的扫描计数值为1，但对于一个循环内的表而言，其值为循环的次数。可以想象得到，对于一个循环内的表而言，其 扫描计数值越小，它所使用的资源越少，查询的性能也就越高。因此在调节一个带连接的查询的性能时，需要关注扫描计数的值，在进行调节 时，注意观察它是增加还是减少了。

逻辑读： 这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的数据。我们知道，SQL Server在对任何数据进行操作前，必须首先从磁盘中读取数据所在的数据页或索引页，并把数据页或索引页存到数据缓冲区高速缓存中。
　　那么逻辑读的意义是什么呢？逻辑读是指SQL Server为得到查询中的结果而必须从数据缓冲区高速缓存读取的页数。在执行查询时，SQL Server不会读取比实际需求多或少的数据， 因此，当在相同的数据集上执行同一个查询，得到的逻辑读的数字总是相同的。
　　为什么说在调节查询性能中知道SQL Server执行查询时的逻辑读值是很重要的呢？因为在每次执行同一查询时，这个数值是不会变化的。因此，在进行查询性能的调节时，这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。
　　在对查询的性能进行调节时，如果逻辑读值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果逻辑读值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。


物理读：在这里我要说的的东西可能初听起来有点自相矛盾，但只要反复思考，就会明白其中的真正含意。
　　物理读指的是，在执行真正的查询操作前，SQL Server必须从磁盘上向数据缓冲区高速缓存中读取它所需要的数据。在SQL Server开始执行查询前，它要作的第一件事就是检查它所需要的数据是否在数据缓冲区高速缓存中，如果在，就从中读取，如果不在，SQL Server必须首先将它需要的数据从磁盘上读到数据缓冲区高速缓存中。
　　我们可以想象得到，SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此，在理想情况下，我们应当尽量避免物理读操作。
　　下面的这一部分听起来让人容易感到糊涂了。在对查询的性能进行调节时，可以忽略物理读而只专注于逻辑读。你一定会纳闷儿，刚才不是还说物理读比逻辑读需要更多的服务器资源吗？
　　情况确实是这样，SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作，但它涉及到整个服务器性能的调节，而不仅仅是查询性能的调节。在进行查询性能调节时，我们不能控制数据缓冲区高速缓存的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘上，唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。
　　因此，在查询性能的调节中，我们可以心安理得地不理会SET STATISTICS IO命令提供的物理读的值。（减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。）


预读：与 物理读一样，这个值在查询性能调节中也没有什么用处。预读表示SQL Server在执行预读机制时从磁盘上读取的数据页或索引页。为了优化其性能，SQL Server数据引擎首先预测执行查询执行计划所需的数据和索引页，然后在查询实际使用这些页之前将它们读入缓冲区高速缓存。根据SQL Server对数据 需求预测的准确程度，预读的数据页可能有用，也可能没用。

备注：一个缓冲区就是一个 8KB 大小的内存页




第二：关于碎片对于性能影响的结论：

SQL Server 中数据存储的基本单位是页，一页包含8KB数据。磁盘 I/O 操作在页级执行。也就是说，SQL Server 读取或写入的基本单位是数据页。连续的8个页面组成一个区（extent）。数据的insert和update操作可以引起页面分割产生碎片。如果分割后的两个页面在同一个区内，这种碎片称为内部碎片，如果分割后的两个页面处于不同的区内，这种碎片称为外部碎片。

[一、内部碎片和外部碎片对数据检索性能都有负面影响。

1. 内部碎片的产生使数据稀疏的分布在大量的页面中，这增加了读取页面到内存中所需的磁盘I/O操作，增加了从内存中检索数据的逻辑读取数量。

2. 外部碎片导致磁盘上的索引页面不连续，新的叶子页面和原始叶子页面离得很远，物理顺序与逻辑顺序不同。为了更好的性能，首选顺序I/O，因为这能在一个磁盘I/O读取整个区（8个8KB页面）。非连续的页面需要非顺序或者随机I/O操作来从磁盘读取数据，一个随机I/O只能读取一个页面（8KB）。



二、在堆表中，当删除数据链中间的记录行时，会出现空页,随着空页的累积，区的利用率也会下降，从而出现内部碎片与外部碎片。带索引的表也有可能出现外部碎片，如在现有的聚集索引中插入一行，这行正好导致现有的页空间无法满足容纳新的行，从而导致了分页，如果分页后的两个页面正好分布在两个区，就是外部碎片。当有外部碎片存在，会出现以下问题：对表进行处理时，常常出现死锁；利用较大的I／O操作或增加I/O缓冲区的大小也无法改变较慢的I／O速度；行操作的争用。



三、带有索引的表会由于插人记录而导致分页，但当删除记录后，页会获得释放．从而形成跨几个区的数据．而要访问该数据就必须遍历几个区，这将导致增加I/O操作，查询记录的时间大大延长，开始时数据库的性能虽然较高，但使用一段时间后就会发生性能下降等问题。实际上，数据在存储空间上排列得越紧密有序，SQL Server访问的速度就越抉，消除碎片有助于提高系统的性能和更有效地利用数据存储空间。(如例二，例二的访问速度就比例一要快）。



四、对于扫描部分或全部表的查询，这些表碎片会导致额外的页读取，这将防碍数据的并行扫描。

 

 索引的选择


第一步：应用正确的索引

　　假设我们有一个Products表，创建了一个聚集索引(根据表的主键自动创建的)，我们还需要在ProductName列上创建一个非聚集索引，创建非聚集索引时，数据库引擎会为非聚集索引自动创建一个索引树(就象故事中的“图书名称”目录一样)，产品名称会存储在索引页中，每个索引页包括一定范围的产品名称和它们对应的主键键值，当使用产品名称进行检索时，数据库引擎首先会根据产品名称查找非聚集索引树查出主键键值，然后使用主键键值查找聚集索引树找到最终的产品。

　　索引树结构叫做B+树(或平衡树)，中间节点包含值的范围，指引SQL引擎应该在哪里去查找特定的索引值，叶子节点包含真正的索引值，如果这是一个聚集索引树，叶子节点就是物理数据页，如果这是一个非聚集索引树，叶子节点包含索引值和聚集索引键(数据库引擎使用它在聚集索引树中查找对应的行)。

　　通常，在索引树中查找目标值，然后跳到真实的行，这个过程是花不了什么时间的，因此索引一般会提高数据检索速度。下面的步骤将有助于你正确应用索引。

　　确保每个表都有主键

　　这样可以确保每个表都有聚集索引(表在磁盘上的物理存储是按照主键顺序排列的)，使用主键检索表中的数据，或在主键字段上进行排序，或在where子句中指定任意范围的主键键值时，其速度都是非常快的。

　　在下面这些列上创建非聚集索引：

　　1)搜索时经常使用到的;

　　2)用于连接其它表的;

　　3)用于外键字段的;

　　4)高选中性的;

　　5)ORDER BY子句使用到的;

　　6)XML类型。


第二步：创建适当的覆盖索引

　　假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引，假设ProductID列是一个高选中性列，那么任何在where子句中使用索引列(ProductID)的select查询都会更快，如果在外键上没有创建索引，将会发生全部扫描，但还有办法可以进一步提升查询性能。

　　假设Sales表有10,000行记录，下面的SQL语句选中400行(总行数的4%)：　

复制内容到剪贴板

代码:

SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112

　　我们来看看这条SQL语句在SQL执行引擎中是如何执行的：

　　1)Sales表在ProductID列上有一个非聚集索引，因此它查找非聚集索引树找出ProductID=112的记录;

　　2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值，即SalesID);

　　3)针对每一个主键(这里是400)，SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

　　SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

　　在上面的步骤中，对ProductID = 112的每个主键记录(这里是400)，SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate，SalesPersonID)。

　　如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,，SalesPersonID)的值，SQL Server引擎可能不会执行上面的第3和4步，直接从非聚集索引树查找ProductID列速度还会快一些，直接从索引页读取这三列的数值。

　　幸运的是，有一种方法实现了这个功能，它被称为“覆盖索引”，在表列上创建覆盖索引时，需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子：　

复制内容到剪贴板

代码:

CREATE INDEX NCLIX_Sales_ProductID--Index name

　　ON dbo.Sales(ProductID)--Column on which index is to be created

　　INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

　　应该在那些select查询中常使用到的列上创建覆盖索引，但覆盖索引中包括过多的列也不行，因为覆盖索引列的值是存储在内存中的，这样会消耗过多内存，引发性能下降。

　　创建覆盖索引时使用数据库调整顾问

　　我们知道，当SQL出问题时，SQL Server引擎中的优化器根据下列因素自动生成不同的查询计划：

　　1)数据量

　　2)统计数据

　　3)索引变化

　　4)TSQL中的参数值

　　5)服务器负载

　　这就意味着，对于特定的SQL，即使表和索引结构是一样的，但在生产服务器和在测试服务器上产生的执行计划可能会不一样，这也意味着在测试服务器上创建的索引可以提高应用程序的性能，但在生产服务器上创建同样的索引却未必会提高应用程序的性能。因为测试环境中的执行计划利用了新创建的索引，但在生产环境中执行计划可能不会利用新创建的索引(例如，一个非聚集索引列在生产环境中不是一个高选中性列，但在测试环境中可能就不一样)。

　　因此我们在创建索引时，要知道执行计划是否会真正利用它，但我们怎么才能知道呢?答案就是在测试服务器上模拟生产环境负载，然后创建合适的索引并进行测试，如果这样测试发现索引可以提高性能，那么它在生产环境也就更可能提高应用程序的性能了。

　　虽然要模拟一个真实的负载比较困难，但目前已经有很多工具可以帮助我们。

　　使用SQL profiler跟踪生产服务器，尽管不建议在生产环境中使用SQL profiler，但有时没有办法，要诊断性能问题关键所在，必须得用，在http://msdn.microsoft.com/en-us/library/ms181091.aspx有SQL profiler的使用方法。

　　使用SQL profiler创建的跟踪文件，在测试服务器上利用数据库调整顾问创建一个类似的负载，大多数时候，调整顾问会给出一些可以立即使用的索引建议，在http://msdn.microsoft.com/en-us/library/ms166575.aspx有调整顾问的详细介绍。

 

SQL Server 创建和使用索引


创建索引：

（1）在SQL Server Management Studio中，选择并右击要创建索引的表，从弹出菜单中选择“设计”，打开表设计器。右键单击表设计器，从弹出菜单中选择“索引/键”命令，打开“索引/键”对话框。对话框中列出了已经存在的索引，如下图所示。

（2）单击“添加”按钮。在“选定的主/唯一键或索引”框显示系统分配给新索引的名称。

（3）在“列”属性下选择要创建索引的列。可以选择多达16列。为获得最佳性能，最好只选择一列或两列。对所选的每一列，可指出索引是按升序还是降序组织列值。

（4）如果要创建唯一索引，则在“是唯一的”属性中选择“是”。

（5）设置完成后，单击“确定”按钮。

（6）当保存表时，索引即创建在数据库中。


使用CREATE INDEX语句创建索引：

复制内容到剪贴板

代码:

CREATE[ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX索引名

    ON {表名|视图名} (列名[ ASC | DESC ] [ ,...n ] )

例：

在数据库HrSystem中为表Employees创建基于IDCard列的唯一索引IX_Employees，可以使用以下命令：

复制内容到剪贴板

代码:

USE HrSystem

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Employees]ONdbo.Employees(IdCard)

GO

例：

为表Employees创建基于列IDCard的唯一、聚集索引IX_Employees1，可以使用以下命令：

复制内容到剪贴板

代码:

USE HrSystem

GO

CREATE UNIQUE CLUSTERED INDEX [IX_Employees1] ON [dbo].[Employees](IdCard)

GO

需要注意的是，在一个表中只允许存在一个聚集索引。因此，如果表Employees中已经存在一个聚集索引，则执行上面的语句时将会提示下面的错误信息。

消息1902，级别16，状态3，第1行

无法对表'dbo.Employees'创建多个聚集索引。请在创建新聚集索引前删除现有的聚集索引'PK__Employee__263E2DD300551192'。
例：

对表Employees的列Emp_name按照降序创建索引，可以使用以下命令：

复制内容到剪贴板

代码:

USE HrSystem

GO

CREATENONCLUSTERED INDEX [IX_Employees2] ON [dbo].[Employees]

(

  [Emp_name] DESC

)

GO

在CREATE INDEX语句中使用INCLUDE子句，可以在创建索引时定义包含的非键列，其语法结构如下：

复制内容到剪贴板

代码:

CREATENONCLUSTERED  INDEX 索引名

    ON { 表名| 视图名 } ( 列名 [ ASC | DESC ] [ ,...n ] )

  INCLUDE (<列名1>, <列名2>, [,… n])

例：
在表Employees上创建非聚集索引IX_Wage，索引中的键列为Wage，非键列为Emp_name、Sex和Title，具体语句如下：

复制内容到剪贴板

代码:

USEHrSystem

GO

CREATENONCLUSTERED  INDEX IX_Wage

  ON Employees ( Wage )

  INCLUDE (Emp_name, Sex, Title)

GO

例：
在创建索引IX_Wage后，当表Employees中的数据量比较大时，执行下面的SELECT语句将会明显地改进查询效率。

复制内容到剪贴板

代码:

USEHrSystem

GO

SELECTEmp_name, Sex, Title, Wage

FROMEmployees

WHEREWage BETWEEN 1000 AND 3000

GO

修改索引：


        在SQL Server Management Studio中，选择并右击要创建索引的表，从弹出的菜单中选择“设计表”，打开表设计器。右键单击表设计器，从弹出菜单中选择“索引/键”命令，打开“索引/键”对话框，并查看已经存在的索引及修改索引的属性信息。

也可以使用ALTER INDEX语句修改索引，其基本语法如下：

复制内容到剪贴板

代码:

ALTER INDEX { 索引名| ALL }

    ON <表名|视图名>

    { REBUILD  | DISABLE  | REORGANIZE }[ ; ]

ALTER INDEX语句的参数比较复杂，这里只介绍它的基本使用情况。参数说明如下：

REBUILD指定重新生成索引。
DISABLE指定将索引标记为已禁用。
REORGANIZE指定将重新组织的索引叶级。

例：

要禁用索引IX_Employees，可以使用下面的语句：

复制内容到剪贴板

代码:

USE HrSystem

GO

ALTERINDEX IX_Employees ON Employees DISABLE

GO

删除索引：

        在SQL Server Management Studio中，选择并右击要创建索引的表，从弹出的菜单中选择“设计表”。打开表设计器。右键单击表设计器，从弹出菜单中选择“索引/键”命令，在打开的“索引/键”对话框中列出了已经存在的索引。单击“删除”按钮，即可删除索引信息。

复制内容到剪贴板

代码:

DROP INDEX 表名.索引名|视图名.索引名[ ,...n ]

 

 

总结：



o（1）索引并非越多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。

o（2）避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

o（3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

o（4）在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。

o（5）当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引能够确保定义的列的数据完整性，提高查询速度。

o（6）在频繁进行排序或分组（即进行GROUPBY或ORDERBY操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。