(转)聚合索引(clustered index) / 非聚合索引(nonclustered index)

以下我面试经常问的2道题..尤其针对觉得自己SQL SERVER 还不错的同志.. 呵呵 很难有人答得好..
各位在我收集每个人擅长的东西时,大部分都把SQL SERVER 标为Expert,看看是否答的上来..
1. 什么是聚合索引(clustered index) / 什么是非聚合索引(nonclustered index)?
2. 聚合索引和非聚合索引有什么区别?
深入浅出理解索引结构     
实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:     
其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查”安”字,就会很自然地翻开字典的前几页,因为”安”的拼音是”an”,而按照拼音排序汉字的字典是以英文字母”a”开头并以”z”结尾的,那么”安”字就自然地排在字典的前部。如果您翻完了所有以”a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查”张”字,那您也会将您的字典翻到最后部分,因为”张”的拼音是”zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。     
我们把这种正文内容本身就是一种按照一定规则排列的目录称为”聚集索引”。     
如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据”偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合”部首目录”和”检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查”张”字,我们可以看到在查部首之后的检字表中”张”的页码是672页,检字表中”张”的上面是”驰”字,但页码却是63页,”张”的下面是”弩”字,页面是390页。很显然,这些字并不是真正的分别位于”张”字的上下方,现在您看到的连续的”驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。     
我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为”非聚集索引”。     
通过以上例子,我们可以理解到什么是”聚集索引”和”非聚集索引”。     
进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引 ,因为目录只能按照一种方法进行排序。     
(二)何时使用聚集索引或非聚集索引     
下面的表总结了何时使用聚集索引或非聚集索引(很重要)。       
动作描述                        使用聚集索引                       使用非聚集索引    
列经常被分组排序              应                                       应    
返回某范围内的数据          应                                       不应       
一个或极少不同值           不应                                     不应    
小数目的不同值                应                                       不应    
大数目的不同值              不应                                      应    
频繁更新的列                 不应                                      应    
外键列                            应                                        应    
主键列                            应                                        应    
频繁修改索引列             不应                                       应    
事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。     
(三)结合实际,谈索引使用的误区     
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。     
1、主键就是聚集索引     
这种想法是极端错误的,是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。     
通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时,如果我们将这个列设为主键,SQL SERVER会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认为这样做意义不大。     
显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。     
从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为ID号是自动生成的,我们并不知道每条记录的ID号,所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次,让每个ID号都不同的字段作为聚集索引也不符合”大数目的不同值情况下不应建立聚合索引”规则;当然,这种情况只是针对用户经常修改记录内容,特别是索引项的时候会负作用,但对于查询速度并没有影响。     
在办公自动化系统中,无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是”日期”还有用户本身的”用户名”。     
通常,办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况,但如果您的系统已建立了很长时间,并且数据量很大,那么,每次每个用户打开首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了,这样做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这个用户近3个月来未阅览的文件,通过”日期”这个字段来限制表扫描,提高查询速度。如果您的办公自动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。     
在这里之所以提到”理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在”日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):     
(1)仅在主键上建立聚集索引,并且不划分时间段:     
Select gid,fariqi,neibuyonghu,title from tgongwen  用时:128470毫秒(即:128秒)     
(2)在主键上建立聚集索引,在fariq上建立非聚集索引:    
select gid,fariqi,neibuyonghu,title from Tgongwen where  fariqi> dateadd(day,-90,getdate())   用时:53763毫秒(54秒)     
(3)将聚合索引建立在日期列(fariqi)上:    
select gid,fariqi,neibuyonghu,title from Tgongwen where  fariqi> dateadd(day,-90,getdate()) 用时:2423毫秒(2秒)     
虽然每条语句提取出来的都是25万条数据,各种情况的差异却是巨大的,特别是将聚集索引建立在日期列时的差异。事实上,如果您的数据库真的有1000万容量的话,把主键建立在ID列上,就像以上的第1、2种情况,在网页上的表现就是超时,根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。    
得出以上速度的方法是:在各个select语句前加:declare @d datetime set @d=getdate()     
并在select语句后加:    
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())    
2、只要建立索引就能显著提高查询速度     
事实上,我们可以发现上面的例子中,第2、3条语句完全相同,且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别。所以,并非是在任何字段上简单地建立索引就能提高查询速度。     
从建表的语句中,我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中,我们每天都会发几个文件,这几个文件的发文日期就相同,这完全符合建立聚集索引要求的:”既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看来,我们建立”适当”的聚合索引对于我们提高查询速度是非常重要的。     
3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度     
上面已经谈到:在进行数据查询时都离不开字段的是”日期”还有用户本身的”用户名”。既然这两个字段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。     
很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列)     
(1)select gid,fariqi,neibuyonghu,title from Tgongwen where  fariqi>’2004-5-5′  查询速度:2513毫秒     
(2)select gid,fariqi,neibuyonghu,title from Tgongwen where  fariqi>’2004-5-5′ and neibuyonghu=’办公室’   查询速度:2516毫秒     
(3)select gid,fariqi,neibuyonghu,title from Tgongwen where  neibuyonghu=’办公室’   查询速度:60280毫秒     
从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成”索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。     
(四)其他书上没有的索引使用经验总结     
1、用聚合索引比用不是聚合索引的主键速度快     
下面是实例语句:(都是提取25万条数据)     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where fariqi=’2004-9-16′ 使用时间:3326毫秒     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where gid<=250000 使用时间:4470毫秒     
这里,用聚合索引比用不是聚合索引的主键速度快了近1/4。     
2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下    
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  order by fariqi 用时:12936     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  order by gid  用时:18843    
这里,用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。     
3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where fariqi>’2004-1-1′ 用时:6343毫秒(提取100万条)    
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where fariqi>’2004-6-6′ 用时:3170毫秒(提取50万条)     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where fariqi=’2004-9-16′    
用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where fariqi>’2004-1-1′ and fariqi<‘2004-6-6’ 用时:3280毫秒     
4 、日期列不会因为有分秒的输入而减慢查询速度     
下面的例子中,共有100万条数据,2004年1月1日以后的数据有50万条,但只有两个不同的日期,日期精确到日;之前有数据50万条,有5000个不同的日期,日期精确到秒。     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where fariqi>’2004-1-1′ order by fariqi 用时:6390毫秒     
select gid,fariqi,neibuyonghu,reader,title from Tgongwen  where fariqi<‘2004-1-1’ order by fariqi 用时:6453毫秒    
(五)其他注意事项     
“水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。     
所以说,我们要建立一个”适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使您的数据库能得到高性能的发挥。     
当然,在实践中,作为一个尽职的数据库管理员,您还要多测试一些方案,找出哪种方案效率最高、最为有效。

SQL Server 的优化方法(转)

千辛万苦,终于把数据库服务器的CPU从超过50%(开5个程序线程)乃至100%(开10个程序线程)降低到了5%。摸索到了一些门道,总结一下

1、SQL SERVER 2005的性能工具中有SQL Server Profiler和数据库引擎优化顾问,极好的东东,必须熟练使用。
2、查询SQL语句时打开“显示估计的执行计划”,分析每个步骤的情况
3、初级做法,在CPU占用率高的时候,打开SQL Server Profiler运行,将跑下来的数据存到文件中,然后打开数据库引擎优化顾问调用那个文件进行分析,由SQL SERVER提供索引优化建议。采纳它的INDEX索引优化部分。
4、但上面的做法经常不会跑出你所需要的,在最近的优化过程中CPU占用率极高,但根本提不出我需要的优化建议,特别是有些语句是在存储过程中并且多表联立。这时就需要用中级做法来定位占用CPU高的语句。
5、还是运行SQL Server Profiler,将运行结果保存到某个库的新表中(随便起个名字系统会自己建)。让它运行一段时间,然后可以用
select top 100 * from test where textdata is not null order by duration desc
这个可以选出运行时间长的语句,在ORDER BY 中可以替换成CPU、READS,来选出CPU占用时间长和读数据过多的语句。
定位出问题的语句之后就可以具体分析了。有些语句在执行计划中很明显可以看出问题所在。
常见的有没有建索引或索引建立不合理,会出现table scan或index scan,凡是看到SCAN,就意味着会做全表或全索引扫描,这是带来的必然是读次数过多。我们期望看到的是seek或键查找。
6、怎么看SQL语句执行的计划很有讲究,初学者会过于关注里面显示的开销比例,而实际上这个有时会误导。我在实际优化过程中就被发现,一个index scan的执行项开销只占25%,另一个键查找的开销占50%,而键查找部分根本没有可优化的,SEEK谓词就是ID=XXX这个建立在主键上的查找。而仔细分析可以看到,后者CPU开销0.00015,I/O开销0.0013。而前者呢,CPU开销1.4xxxx,I/O开销也远大于后者。因此,优化重点应该放在前者。
7、如何优化单个部分,一个复杂的SQL语句,SQL SERVER会很聪明地重组WHERE后的语句,试图匹配索引。选中带优化的步骤,选择旁边的‘属性”,再选择其中的“谓词”,将其中部分复制下来,这部分就是分解后的WHERE 语句,然后在查询界面中select * from 表 where 刚才复制下来的“谓词”。这个就是需要优化的部分,既然已经走到这一步了,大部分人应该能手动建立索引了,因为这里的WHERE语句比之前的肯定简单不少。(在我项目中原始SELECT语句的WHERE部分有10个条件组合,涉及6个字段,提取出来要优化的部分就4个条件,涉及到3个字段。新的索引建立后,CPU占用率一下子就降低了,而且新建立的索引涉及的字段属于不常UPDATE的部分,频繁的读写操作不会影响UPDATE的效率)
8、以上就是优化的思路,最后提一些优化过程或是系统设计时中需要注意的问题。
A、尽量避免用select * from xxx where abc like ‘%xxx’类型的模糊查询,因为%在前面的话是无法利用到索引,必然会引起全量SCAN操作。应该找寻替代方式或用前置条件语句把like查找之前的行数减到最低。
B、尽量避免对大表数据进行select top n * from xxx where xxxx order by newid()的取随机记录的操作。newid()操作会读全量数据后再排序。也会占用大量CPU和读操作。可以考虑用RAND()函数来实现,这方面我还在研究中,对于整表操作比较好弄,比如id>=(select max(id) from table)*rand()。但如果取局部数据的随机记录还需要思量。
C、在SQL Server Profiler记录中会看到Audit Logout会占用大量CPU和读写等操作。查了一些资料称是某个链接在某次连接过程中执行SQL语句产生的总数,不用过于担心。看下来的确似乎这样,很多Audit Logout的CPU和IO消耗量和之前优化的语句基本一致。所以在第5点我提的SQL语句用textdata is not null条件把Audit Logout给隐去。
D、两个不同字段OR语句会导致全表扫描。例如 where m=1 or n=1。如果建立一个索引是m和n,同样会引起scan,解决方法是给m和n分别建立索引。测试12万条数据的表,索引建立错误的情况下IO开销高达 10.xxx,分别建立索引后,全部变成0.003,这个反差是非常巨大的。虽然会引起INSERT操作的性能问题,但毕竟大部分瓶颈在SELECT的读操作上。
E、索引查找(Index Seek)和索引扫描(Index Scan),我们需要的是前者,而引起后者的原因通常是某个索引里的字段多余要查找的,例如索引建立在A和B两个字段,而我们只要查找A,则会导致 INDEX SCAN。建议针对单独的A建立索引,以形成索引查找。
F、对于小表不建议建立索引,特别是几百的数据量,只有上千上万级别的数据建立索引才有效果。
数据库优化是很深的学问,在数据库设计时就应该注意,特别是最后提到的A、B两点,尽可能在设计初期避免。