《SQLServer索引结构及其使用》由会员分享，可在线阅读，更多相关《SQLServer索引结构及其使用（16页珍藏版）》请在金锄头文库上搜索。
1、SQLServer索引结构及其使用一、深入浅出理解索引结构实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚 集索引（clustered index,也称聚类索引、簇集索引）和非聚集索引（nonclustered index,也称非 聚类索引、非簇集索引）。下血，我们举例來说明一下聚集索引和罪聚集索引的区别：其实，我们的汉语字典的正文木身就是一个聚集索引。比如，我们要查“安”字，就会很自然 地翻开字典的前儿页，因为“安”的拼昔是an-,而按照拼音排序汉字的字典是以英文字母“才开 头并以“才结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所冇以“旷开头
2、的部分仍 然找不到这个字，那么就说明您的字典中没冇这个字；同样的，如果查“张字，那您也会将您的 字典翻到最后部分，I大1为“张的拼音是uzhang-。也就是说，字典的正文部分木身就是一个目录, 您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容木身就是一种按照一定规 则排列的目录称为“聚集索引如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字, 不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首 查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部背0 录”和“检字表”而查到的字的排序并不是真
3、正的正文的排序方法，比如您查“张字，我们可以看 到在查部首之后的检字衣中“张的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63 页，“张”的下血是“弩”字，页血是390页。很显然，这些字并不是真止的分别位于“张”字的上下 方，现在您看到的连续的“驰、张、弩三字实际上就是他们在非聚集索引中的排序，是字典正文 中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程, 先找到H录中的结果，然后再翻到您所需要的页码。我们把这种H录纯粹是H录，止文纯粹是正 文的排序方式称为“非聚集索引”。通过以上例了，我们可以理解到什么是“聚集索弓I和“非聚集索引。进一步引申
4、一下，我们 可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。二、何时使用聚集索引或非聚集索引下面的表总结了何吋使用聚集索引或非聚集索引（很重要）：动作描述使用聚集索引使用非聚集索引列经常被分组挂序应应返回某范围內的数据应不应-个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频鑿更新的列不应应外键列应应主键列应应频蹩修改索引列不应应事实上 我们可以通过前面聚集索引和非聚集索引的定义的例子來理解上表。如：返回某范 围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询 2004年1月1|至2004年10月1日Z间的全部数据时
5、，这个速度就将是很快的，因为您的这 本字典正文是按口期进行排序的，聚类索引只需耍找到耍检索的所有数据中的开头和结尾数据即 而不像非聚集索引，必须先查到忖录中查到每一项数据対应的页码，然后再根据页码查到具 体内容。三、结合实际，谈索引使用的误区理论的目的是应丿U。虽然我们刚才列出了何时应使川聚集索引或非聚集索引，但在实践中以 上规则却很容易被忽视或不能根据实际情况进行综合分析。卜dii我们将根据在实践中遇到的实际 问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。1、主键就是聚集索引这种想法笔者认为是极端错谋的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在 主键上建立聚集索引
6、的。通常，我们会在每个表中都建立一个ID列，以区分每条数据，并H这个ID列是自动增大 的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个 列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在 数据库中按照ID进行物理排序，但笔者认为这样做意义不大。显而易见，聚集索引的优势是很明显的，而每个表中只能冇一个聚集索引的规则，这使得聚 集索引变得更加珍贵。从我们前-而谈到的聚集索引的处义我们可以看出，使用聚集索引的戢大好处就是能够根据杳 询要求，迅速缩小查询范囤，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并 不知道
7、每条记录的ID号，所以我们很难在实践中用ID号來进行查询。这就使让ID号这个主键 作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数 目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对川户经常修改记录内容， 特别是索引项的时候会负作用，但对于査询速度并没有影响。在办公白动化系统中，无论是系统首页显示的需要川户签收的文件、会议还是川八进行文件 査询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。通常，办公自动化的首贝会显示每个用户尚未签收的文件或会议。虽然我们的where语句可 以仅仅限制当前用户尚未签收的情况，但如果您的
8、系统已建立了很K时间，并且数据量很人，那 么，每次每个川户打开首页的时候都进行一次全表扌_1描，这样做意义是不大的，绝大多数的川户 1个刀前的文件都已经浏览过了，这样做只能徒增数据库的开销iftfLlo爭实上，我们完全可以让 用户打开系统首页时，数据库仅仅查询这个用户近3个刀來未阅览的文件，通过“口期”这个字段 来限制表扫描，提高查询速度。如果您的办公口动化系统已经建立的2年，那么您的首页显示速 度理论上将是原来速度8倍，甚至更快。在这里之所以提到“理论上”三字，是因为如杲您的聚集索引还是盲日地建在ID这个主键上 时,您的查询速度是没有这么高的,即使您在“日期这个字段上建立的索引（非聚合索引）
9、。下 面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万 条）：（1）仅在主键上建立聚集索引，并口不划分时间段：Select gid,fariqi,neibuyonghu,title from tgongwen用时：128470毫秒（即：128秒）（2）在主键上建立聚集索引，在fariq 建立非聚集索引：select gid,fariqi,neibuyonghu,title from Tgongwenwhere fariqi datcadd（day,-90,gctdatc（）用时：53763毫秒（54秒）（3）将聚合索引建立在日期列（fariqi）上：sel
10、ect gid,fariqi,neibuyonghu,title from Tgongwenwhere fariqi datcadd（day,-90,gctdatc（）用时：2423毫秒（2秒）虽然每条语句提取岀来的都是25万条数据，各种情况的差界却是巨大的，特别是将聚集索 引建立在I期列时的差异。爭实上，如果您的数据库真的有1000万容量的话，把主键建立在ID 列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃 ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是：在各个select语句前加：declare d datetimeset d=getdat
11、e（）并在select语句后加:select 语句执行花费时间(毫秒)=datediff(ms,d,getdate()2、只要建立索引就能显著提高查询速度事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同; 不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查 询速度却冇着天壤Z别。所以，并非是在任何字段上简单地建立索引就能提髙查询速度。从建表的语句中，我们可以看到这个冇着1000万数据的表中fariqi字段有5003个不同记录。 在此字段上建立聚介索引是再介适不过了。在现实中，我们每天都会发儿个文件，这儿个文件的 发文H期就
12、相同，这完全符合建立聚集索引耍求的：“既不能绝大多数都相同，又不能只有极少 数相同”的规则。由此看來，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。3、把所有需要提髙查询速度的字段都加进聚集索引，以提髙查询速度上面已经谈到：在进行数据查询时都离不开字段的是“口期还冇用户木身的“用户名”。既然 这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引(compound index)。很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复 合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询 速度(结果集都是25万条
13、数据):(日期列fariqi首先排在复合聚集索引的起始列，用户名 neibuyonghu 排在后列):(1) select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi2004-5-5M查询速度：2513毫秒(2) select gid,fariqi,neibuyonghu,title from Tgongwenwhere fariqiu2004-5-5H and neibuyonghu=u办公室”査询速度:2516毫秒(3) select gid,fariqi,neibuyonghu,title from Tgongwen w
14、here ncibuyonghu=办公窒查询速度：60280毫秒从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集 索引的全部列的查询速度是儿乎一样的，共至比用上全部的复合索引列述要略快(在查询结果集 数口一样的情况下)；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起 任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所育 列都川上，而且查询结果少的话，这样就会形成“索引覆盖，因而性能可以达到最优。同时，请 记住：无论您是否经常使用聚合索引的其他列，但其前导列一能要是使用最频繁的列。四、其他书上没有的索引使用经验总结
15、改善SQL语句很多人不知道SQL语句在SQL SERVER中是如何执行的，他们扌I!心自己所写的SQL语句会被SQLSERVER：吴解。比如：select * from table 1 where name=zhangsan and tID 10000和执行:select * from table 1 where tID 10000 and name=Hzhangsann一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个 语句的确是不一样，如果tID是一个聚合索引，那么片一句仅仅从表的10000条以片的记录中查 找就行了；而前一句则耍先从全表中查找看有儿个name=*zhangsan-的，而后再根据限制条件条 件tID 10000來提出杳询结果。事实上，这样的扌口心是不必耍的。SQL SERVER中有-个“查询分析优化器”，它可以计算 出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现口动 优化。虽然查询优化器可以根据where子句自动的进行查询优化，但人家仍然有必要了解一下“查 询优化器的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速査询。在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需耍扫描的数据址是否有丿IJ。 如果一个阶
