索引是什么？索引是提高查询性能的一个重要工具，索引就是把查询语句所需要的少量数据添加到索引分页中，这样访问数据时只要访问少数索引的分页就可以。但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建少了，用WHERE子句找数据效率低，不利于查找数据。索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER除了要更新数据表本身，还要连带地立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。因此要建得恰到好处，这就需要经验了。
一：索引的基本目的
索引的基本目的是在大量数据中找寻少量数据。你可以想像一下，若一本书有700页，就像数据表有700个数据页，而索引却有600个索引页，你会想用索引来查询书籍的内容吗？
索引字段的值重复性越低越好，假设书籍中如“的”“了”这些在文章中重复性极高的字，每页都有一大堆，你会先翻索引页某个位置有“的”，翻回该页读取了“的”之后，再索引看下一个“的”，结果是在先前同一页的不同位置，又翻回书籍原页查看下一个“的”。
那么怎么理解索引是从大量数据中寻找少量数据呢？下面我们举个例子来说明。
如果一个数据表的记录平均长度为400字节，则100万条记录需要5万个数据页，其计算公式如下：
1000000/（8060/400）=50000
如果该数据表建立聚集索引，键值为4个字节长度，而ID的数据长度为13个字节，因此索引结构每条记录为20个字节。
4（聚集索引键值）+13（ID键值）+3（管理信息）=20
以ID字段所建立的索引，100%填充率，则总分页数约为2482页，其计算方式如下：
1000000/(8060/20)
即使是使用80%的填充率来计算也只有3106页。其计算方式如下：
1000000/((8060*0.8)/20)
从上面可以看出如果是第一种情况，则索引页只占到总数据页的5%：
2482/50000=0.04964
即使考虑取每页只填充80%的索引数据，第二种情况，索引页也只是占总数据页的6%：
3106/50000=0.06212
再说如果查询条件中的字段建立索引，则由于索引键值数据都是以B-Tree有顺序的摆放，所以可采用二分查找找数据。也就是2的N次方大于记录数，就可以找到该条数据。而2的20次方大于100万，因此最多找寻20次就可以找到该条记录。由于比较次数少，数据结构也小，节省访问硬盘与内在的资源，索引将大幅提升找寻数据的效率。SQL SERVER为提高访问与查找对比的效率，用来作索引的数据域键值愈小愈好，也就是要让分页尽量存更多的键值记录。
注：
如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个 4 字节的 uniqueifier 列。必要时，数据库引擎将向行自动添加一个 uniqueifier 值以使每个键唯一。此列和列值供内部使用，用户不能查看或访问。
二：什么是索引
在 SQL Server 中，索引是按 B 树结构进行组织的。如下图。
您也可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，举例来说明一下聚集索引和非聚集索引的区别：
其实，新华字典的正文本身就是一个聚集索引。比如，我们要查“按”字，就会很自然地翻开字典的前几页，因为“按”的拼音是“an”，而按照拼音排序的新华字典是以英文字母“a”开头并以“z”结尾的，那么“按”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明新华字典中没有这个字；同样的，如果查“招”字，那也会将新华字典翻到最后部分，因为“招”的拼音是“zhao”。也就是说，新华字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您碰到一个不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63 页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后 再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下。
聚集索引
聚集索引指的是数据表本身就是索引的一部分，就是指数据表本身就是聚集索引的子叶层，整个数据表的摆放顺序是按照你选定的键值由小到大排序，SQL SERVER 2000 之后的版本可指定数据由大到小排序。
整个数据表按照键值字段由小到大排序，再搭配由键值字段加上指针的上层索引结构，也就是根节点和非子叶层级，形成整个聚集索引。因为数据表内实际摆放数据的方式只能遵循一种顺序，所以一个数据表只能有一个聚集索引。在指定聚集索引时，数据域本身并不需要唯一，或指定为唯一的聚集索引，SQL SERVER内部会自动为重复的键值建立4个字节的唯一标识。
如果你的数据表有一列常常用来排序，另一列常常用来 范围查询，还有一列重复性非常高，则该用哪一列来做聚集索引。正确答案是依据哪个查询最重要，最常被用户执行。例如：你的老板一小时内多次执行某个查询当然比一个月执行一两次的查询来得重要。
表（堆）创建聚集索引或删除和重新创建现有聚集索引时，要求数据库具有额外的可用工作区来容纳数据排序结果和原始表或现有聚集索引数据的临时副本。
当堆或聚集表具有多个分区时，每个分区都有一个堆或 B 树结构，其中包含该指定分区的行组。例如，如果一个聚集表有 4 个分区，那么将有 4 个 B 树，每个分区一个。
聚集索引（ Clustered Index）
· 聚集索引的叶节点就是实际的数据页
· 在数据页中数据按照索引顺序存储
· 行的物理位置和行在索引中的位置是相同的
· 每个表只能有一个聚集索引
· 聚集索引的平均大小大约为表大小的 5%左右
要使用索引来更有效地排序查询数据，最直接的方式就是在你要排序的字段上建立聚集索引。在建立聚集索引之后，SQL SERVER会重新组织数据页，让其中的数据行按照聚集索引中键值的顺序存储。SQL SERVER不需要在硬盘上的数据一定要实际按照聚集索引排序，但在建立聚集索引时，会尝试在逻辑上排序数据的同时，也会在物理上让数据尽可能地排序。在索引子叶层级中的每个数据页都有一个指针指向索引分页的前一页与后一页，形成双向链接串行，在内部的系统数据表包含了各索引子叶层第一个分页的地址，为了保证数据在逻辑上是依照聚集索引的顺序存放的，SQL SERVER 只需要由第一个分页开始，并依照其连接串行一个接着一个依序寻找数据即可。如下图。
注：聚集表是有聚集索引的表。
非聚集索引
非聚集索引是完全独立于数据表之外的结构，所以不会影响数据行的顺序，其子叶层包含索引行。每个索引行包含非聚集键值、行定位符和任意包含列或非键列。行定位符中存入的数据有两种类型：书签（BOOKMARK）或聚集索引的键值。如果数据表上建立了聚集索引，则行定位符中存入的数据就是聚集索引的键值。如果数据表没有建立聚集索引，则行定位符中存入的数据就是书签，即指向数据表中记录具体位置的ROWID，也就是文档编号、分页编号与页内记录编号（称之为SOLT编号）所组合成的值。通过该ROWID 在数据表内获取数据就称为书签查找 BOOKMARK LOOKUP。所以，一般通过非聚集索引查找到符合的键值后，还会搭配书签查找。
当非聚集索引从结构中找到符合的记录时，虽然在子叶层该键值是由小到大排序，因此可能在一个分页上就有全部符合查询条件的键值，但因为数据表中数据行的摆放是没有按顺序的（或是说没有按照该非聚集索引的键值顺序摆放），所以真正符合记录的数据是散布在文档各处的，而SQL SERVER每次读取数据都是以数据页为单位，因此，找到一条记录所在位置后，要先将存放该条记录的分页读到内存中，再从该页读出记录。
因为BOOKMARK LOOKUP是进行随机的I/O操作，当符合查询的记录很多时，通过非聚集索引访问将导致数据页读取非常频繁，就算两条记录在同一个分页，该分页也会被重复读两次，因此或符合的记录有N条，就需要读取数据表内的分页N页，虽然大部分的读取操作都是针对内存中的高速缓存，但记录数过多时一样没有效率，还不如数据表扫描，全部扫描一遍，把符合条件数据找出来。
虽然 SQL 2005 以后的版本中已经不在提 BOOKMARK LOOKUP了(但实际上却是换汤不换药)，我们的很多搜索都是使用如下的搜索过程：先在非聚集中找，然后再在聚集索引中找。如下图。
非聚集索引 （ Unclustered Index）
· 非聚集索引的页，不是数据，而是指向数据页的页。
· 若未指定索引类型，则默认为非聚集索引
· 叶节点页的次序和表的物理存储次序不同
· 每个表最多可以有 249个非聚集索引（一般认为每个表不应该超过10个索引）
· 在非聚集索引创建之前创建聚集索引（否则会引发索引重建）
聚集索引与非聚集索引使用的情况：
| 动作描述 | 使用聚集索引 | 使用非聚集索引 |
| 外键列 | 应 | 应 |
| 主键列 | 应 | 应 |
| 列经常被分组排序(order by) | 应 | 应 |
| 返回某范围内的数据 | 应 | 不应 |
| 小数目的不同值 | 应 | 不应 |
| 大数目的不同值 | 不应 | 应 |
| 频繁更新的列 | 不应 | 应 |
| 频繁修改索引列 | 不应 | 应 |
| 一个或极少不同值 | 不应 | 不应 |
创建索引的规则：
1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。 因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也 会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性 能，特别是对频繁更新的表来说，负面影响更大
三：索引的建立原则
一般来说，建立索引要看数据使用的场景，换句话来说哪些访问数据的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树中找到每一行的指针，再去其所在表上找数据，性能因此会大打折扣，有时甚至不如不加非聚集索引。
4294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
