SQL Server索引应遵循哪些规则

索引的建立原则

 　　一般来说，建立索引要看数据使用的场景，换句话来说哪些访问数据的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，索引使用情况分布图　　　　

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

但是，不是在任何时候使用索引都能够达到这种效果。若在不恰当的场合下，使用索引反而会事与愿违。所以，在SQL Server数据库中使用索引的话，还是需要遵守一定的规则。笔者觉得，主要是需要遵守六大铁律。

　　铁律一：天下没有免费的午餐，使用索引是需要付出代价的

　　索引的优点有目共睹，但是，却很少有人关心过采用索引所需要付出的成本。若数据库管理员能够对索引所需要付出的代价有一个充分的认识，也就不会那么随意到处建立索引了。

　　仔细数数，其实建立索引的代价还是蛮大的。如创建索引和维护索引都需要花费时间与精力。特别是在数据库设计的时候，数据库管理员为表中的哪些字段需要建立索引，要调研、要协调。如当建有索引的表中的纪录又增加、删除、修改操作时，数据库要对索引进行重新调整。虽然这个工作数据库自动会完成，但是，需要消耗服务器的资源。当表中的数据越多，这个消耗的资源也就越多。如索引是数据库中实际存在的对象，所以，每个索引都会占用一定的物理空间。若索引多了，不但会占用大量的物理空间，而且，也会影响到整个数据库的运行性能。

　　可见，数据库管理员若要采用索引来提高系统的性能，自身仍然需要付出不少的代价。数据库管理员现在要考虑的就是如何在这两个之间取得一个均衡。或者说，找到一个回报与投入的临界点。

　　铁律二：对于查询中很少涉及的列或者重复值比较多的列，不要建立索引

　　在查询的时候，如果我们不按某个字段去查询，则在这个字段上建立索引也是浪费。如现在有一张员工信息表，我们可能按员工编号、员工姓名、或者出身地去查询员工信息。但是，我们往往不会按照身份证号码去查询。虽然这个身份证号码是唯一的。此时，即使在这个字段上建立索引，也不能够提高查询的速度。相反，增加了系统维护时间和占用了系统空间。这简直就是搬起石头砸自己的脚呀。

　　另外，如上面的员工信息表，有些字段重复值比较多。如性别字段主要就是“男”、“女”;职位字段中也是有限的几个内容。此时，在这些字段上添加索引也不会显著的增加查询速度，减少用户响应时间。相反，因为需要占用空间，反而会降低数据库的整体性能。

　　数据库索引管理中的第二条铁律就是，对于查询中很少涉及的列或者重复值比较多的列，不要建立索引。

　　铁律三：对于按范围查询的列，最好建立索引

　　在信息化管理系统中，很多时候需要按范围来查询某些交易记录。如在ERP系统中，经常需要查询当月的销售订单与销售出货情况，这就需要按日期范围来查询交易记录。如有时候发现库存不对时，也需要某段时期的库存进出情况，如5月1日到12月3日的库存交易情况等等。此时，也是根据日期来进行查询。

　　对于这些需要在指定范围内快速或者频繁查询的数据列，需要为其建立索引。因为索引已经排序，其保存的时候指定的范围是连续的，查询可以利用索引的排序，加快查询时间，减少用户等待时间。

　　不过，若虽然可能需要按范围来进行查询，但是，若这个范围查询条件利用的不多的情况下，最好不好采用索引。如在员工信息表中，可能需要查询 2008年3月份以前入职的员工明细，要为他们增加福利。但是，由于表中记录不多，而且，也很少进行类似的查询。若维这个字段建立索引，虽然无伤大雅，但是很明显，索引所获得的收益要低于其成本支出。对数据库管理员来说，是得不偿失的。

　　再者，若采用范围查询的话，最好能利用TOP关键字来限制一次查询的结果。如第一次按顺序只显示前面的500条记录等等。把TOP关键字跟范围一起使用，可以大大的提高查询的效率。

　　铁律四：表中若有主键或者外键，一定要为其建立索引

　　定义有主键的索引列，一定要为其建立索引。因为主键可以加速定位到表中的某一行。结合索引的作用，可以使得查询的速度加倍。如在员工信息表中，我们往往把员工编号设置为主键。因为这不但可以提高查询的速度，而且因为主键要求记录的唯一，还可以保证员工编号的唯一性。此时，若再把这个员工编号字段设置为索引，则通过员工编号来查询员工信息，其效率要比没有建立索引高出许多。

　　另外，若要使得某个字段的值唯一，可以通过两种索引方式实现。一种就是上面所讲的主键索引。还有一种就是唯一索引，利用UNIQUE关键字指定字段内容的唯一性。这两种方式都会在表中的指定列上自动创建唯一索引。这两种方式的结果没有明显的区别。查询优化器不会区分到底是哪种方式建立的唯一性索引，而且他们进行数据查询的方式也是相同的。

　　若某张表中的数据列定义有外键，则最好也要为这个字段建立索引。因为外键的主要作用就在于表与表之间的连接查询。若在外键上建立索引，可以加速表与表之间的连接查询。如在员工基本信息表中，有一个字段为员工职位。由于员工职位经常在变化，在这里，存储的其实只是一个员工职位的代码。在另外一张职位信息表中详细记录着该职位的相关信息。此时，这个员工职位字段就是外键。若在这个字段上建立外键，则可以显著提高两张表的连接速度。而且，记录越多，其效果越加明显。

　　所以，当表中有外键或者主键的时候，就最好为其建立索引。通过索引，可以强化主键与外键的作用，提高数据库的性能。

　　铁律五：对于一些特殊的数据类型，不要建立索引

　　在表中，有些字段比较特殊。如文本字段(TXT)、图像类型字段(IMAGE)等等。如果表中的字段属于这些数据类型，则最好不要为其建立索引。因为这些字段有一些共同的特点。如长度不确定，要么很长，几个字符;要么就是空字符串。如文本数据类型常在应用系统的数据库表中用来做备注的数据类型。有时候备注很长，但有时候又没有数据。若这种类型的字段上建立索引，那根本起不了作用。相反，还增加了系统的负担。

　　所以，在一些比较特殊的数据类型上，建立索引要谨慎。在通常情况下，没有必要为其建立索引。但是，也有特殊的情况。如有时候，在ERP系统中，有产品信息这个表，其中有个产品规格这个字段。有时候，其长度可能长达5000个字符。此时，只有文本型的数据类型可以容纳这么大的数据量。而且，在查询的时候，用户又喜欢通过规格这个参数来查询产品信息。此时，若不为这个字段建立索引的话，则查询的速度会很慢。遇到这种情况时，数据库管理员只有牺牲一点系统资源，为其建立索引。

　　从这里也可以看出，虽然以上几条说的时铁律，但是，是否需要遵循，还是需要数据库管理员根据企业的实际情况，做出合理的选择。

　　铁律六：索引可以跟Where语句的集合融为一体

　　用户在查询信息的时候，有时会经常会用到一些限制语句。如在查询销售订单的时候，经常会用到客户以及下单日期的条件集合;如在查询某个产品的库存交易情况时，就会利用产品编号与交易日期起止日期的条件集合。

　　对于这些经常用在Where子句中的数据列，将索引建立在 Where子句的集合过程中，对于需要加速或者频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，以加快查询的时间。