某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > 索引的作用

索引的作用

时间:2019-11-27 20:39

)深入浅出理解索引结构

我们做开发的人员,虽说自己不是专业从事数据库方面研究的(如DBA),但很多时候,公司没有专门的DBA,所以拿到具体的项目中,整体的数据库设计都是开发人员自己写的,随着时间的推移,加上开发经验的增长,越来越关心如何设计好的数据库,如何写出高效的sql语句。之所以非常关心数据库及sql语句的写法,主要是在程序逻辑代码上大家都有可能写出一样的效率的功能方法来,而sql语句呢,对于同样的结果集,一个初级的开发人员与一个资深的开发人员或者DBA写出的sql语句执行效率有着很大的差距。这里对数据库设计略过,主要说说正确建立索引,带来的性能提高。(还好,我们公司有DBA,自己写好的sql语句可以让他帮忙看看)

用索引这是用比家属开辟了一块空间
对UID即按一个索引,方便查询
相当于mysql在另外一块空间里用B+树的形式将UID组织起来,然后按顺序将UID组织起来
比如查询A的话,不用索引这种形式的话,就一行一行扫
用索引的情况下,就B+树,就可以直接找到结点

实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:

看sql 的性能,主要看执行计划,还有cpu成本,io成本等。这里就以一个简的表为例。

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

首先,创建一个简单的表,一般会先建个主键,系统自动以主键建聚集索引。语句如下:

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

 

通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

图片 1图片 2Code
 1图片 3CREATE TABLE [dbo].[Article](
 2图片 4    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 3图片 5    [MsId] [int] NOT NULL,
 4图片 6    [Title] [nvarchar](96) NOT NULL,
 5图片 7    [TitleBak] [nvarchar](96) NOT NULL,
 6图片 8    [Summary] [nvarchar](512) NOT NULL,
 7图片 9    [SummaryImageUrl] [nvarchar](256) NOT NULL,
 8图片 10    [Tag] [nvarchar](50) NOT NULL,
 9图片 11    [ArticleChannel_Id] [int] NOT NULL,
10图片 12    [ArticleCategory_Id] [int] NOT NULL,
11图片 13    [IsApproved] [bit] NOT NULL,
12图片 14    [Creator_Id] [int] NOT NULL,
13图片 15    [CreatedDateTime] [datetime] NOT NULL,
14图片 16    [ModifiedDateTime] [datetime] NOT NULL,
15图片 17    [ViewCount] [int] NOT NULL,
16图片 18    [ReplyCount] [int] NOT NULL,
17图片 19    [DiggCount] [int] NOT NULL,
18图片 20    [FavoriteCount] [int] NOT NULL,
19图片 21    [LastReplyUser_Id] [int] NOT NULL,
20图片 22    [LastReplyDateTime] [datetime] NOT NULL,
21图片 23    [RightType] [int] NOT NULL,
22图片 24    [IsDisplayContent] [bit] NOT NULL,
23图片 25    [IsSensitive] [bit] NOT NULL,
24图片 26    [Source] [int] NOT NULL,
25图片 27 CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED 
26图片 28(
27图片 29    [Id] ASC
28图片 30)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
29图片 31) ON [PRIMARY]
30图片 32

二、何时使用聚集索引或非聚集索引

 

下面的表总结了何时使用聚集索引或非聚集索引(很重要):

填充200000测试数据,

动作描述

使用聚集索引

使用非聚集索引

列经常被分组排序

返回某范围内的数据

不应

一个或极少不同值

不应

不应

小数目的不同值

不应

大数目的不同值

不应

频繁更新的列

不应

外键列

主键列

频繁修改索引列

不应

 

事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。

图片 33图片 34Code
 1图片 35DECLARE @number INT
 2图片 36SET @number = 200000
 3图片 37
 4图片 38WHILE @number > 0
 5图片 39BEGIN
 6图片 40    INSERT dbo.Article
 7图片 41      (
 8图片 42        MsId,
 9图片 43        Title,
10图片 44        TitleBak,
11图片 45        Summary,
12图片 46        SummaryImageUrl,
13图片 47        
14图片 48        Tag,
15图片 49        ArticleChannel_Id,
16图片 50        ArticleCategory_Id,
17图片 51        IsApproved,
18图片 52        Creator_Id,
19图片 53        
20图片 54        CreatedDateTime,
21图片 55        ModifiedDateTime,
22图片 56        ViewCount,
23图片 57        ReplyCount,
24图片 58        DiggCount,
25图片 59        
26图片 60        FavoriteCount,
27图片 61        LastReplyUser_Id,
28图片 62        LastReplyDateTime,
29图片 63        RightType,
30图片 64        IsDisplayContent,
31图片 65        
32图片 66        IsSensitive,
33图片 67        Source
34图片 68      )
35图片 69    VALUES
36图片 70      (
37图片 71          @number,
38图片 72        'Title'+cast(@number AS VARCHAR(20)),
39图片 73        'TitleBak'+cast(@number AS VARCHAR(20)),
40图片 74        'Summary'+cast(@number AS VARCHAR(20)),
41图片 75        'SummaryImageUrl'+cast(@number AS VARCHAR(20)),
42图片 76        
43图片 77        'Tag'+cast(@number AS VARCHAR(20)),
44图片 78        1,
45图片 79        2,
46图片 80        0,
47图片 81        @number,
48图片 82        
49图片 83        GETDATE(),
50图片 84       GETDATE(),
51图片 85        100,
52图片 86        29,
53图片 87        123,
54图片 88        
55图片 89        12,
56图片 90        @number,
57图片 91        GETDATE(),
58图片 92        1,
59图片 93        0,
60图片 94        
61图片 95        0,
62图片 96        2
63图片 97          
64图片 98      )
65图片 99      SET @number=@number-1
66图片 100END
67图片 101

三、结合实际,谈索引使用的误区

 

理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。

创建索引,什么时候创建,为哪个字段创建等等一系列的问题在这里统统的撂下,在这里进行一步步的带有试探的创建非聚集索引,看看建立索引前后以及不同的索引会有什么样的不同。

1、主键就是聚集索引

先说明一下,通过执行计划,判断是否需要优化sql的一个简单规则是:看执行计划中的操作是seek(搜索)还是scan(扫描)

这种想法笔者认为是极端错误的,是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。

长话短说,马上开始,

通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时,如果我们将这个列设为主键,SQL SERVER会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认为这样做意义不大。

 

显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。

图片 102WITH TEMP AS
图片 103(
图片 104SELECT ROW_NUMBER() OVER (ORDER BY CreatedDateTime) AS ROW,CreatedDateTime,ViewCount
图片 105FROM Article
图片 106WHERE Creator_Id=199996 
图片 107)
图片 108SELECT *
图片 109FROM TEMP
图片 110WHERE ROW BETWEEN 1 AND 5
图片 111

从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为ID号是自动生成的,我们并不知道每条记录的ID号,所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次,让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然,这种情况只是针对用户经常修改记录内容,特别是索引项的时候会负作用,但对于查询速度并没有影响。

 

在办公自动化系统中,无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。

通过执行计划,看到是操作是聚集索引扫描。我们刚才说了,seek操作性能更好一些,那如何优化这条语句呢,对Creator_Id建非聚集索引。

通常,办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况,但如果您的系统已建立了很长时间,并且数据量很大,那么,每次每个用户打开首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了,这样做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这个用户近3个月来未阅览的文件,通过“日期”这个字段来限制表扫描,提高查询速度。如果您的办公自动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。

图片 112

在这里之所以提到“理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):

 

(1)仅在主键上建立聚集索引,并且不划分时间段:

 创建Ix_article_creatorid 索引,

1.Select gid,fariqi,neibuyonghu,title from tgongwen

CREATE INDEX Ix_article_creatorid ON Article(Creator_Id)

用时:128470毫秒(即:128秒)

再看下执行计划,

(2)在主键上建立聚集索引,在fariq上建立非聚集索引:

 

1.select gid,fariqi,neibuyonghu,title from Tgongwen

图片 113

2.where fariqi> dateadd(day,-90,getdate())

 

用时:53763毫秒(54秒)

哦,加了Ix_article_creatorid索引后,聚集索引扫描操作改为索引查找和聚集索引查找,对于我们开发人员来说,一般的认为可以了。如果所开发的系统在正常运行一段时间后,需要优化,可以对此语句继续进行优化。

(3)将聚合索引建立在日期列(fariqi)上:

看完执行计划,想到了应该再看看cpu占用时间,IO资源等情况,主要用到命令

1.select gid,fariqi,neibuyonghu,title from Tgongwen

set statistics io 和 set statistics,这是性能调优时查看相关cpu占用时间,IO资源数据的两个比较重要的命令。

2.where fariqi> dateadd(day,-90,getdate())

        今天就先到了,下篇再介绍这两个命令吧。

用时:2423毫秒(2秒)

 

虽然每条语句提取出来的都是25万条数据,各种情况的差异却是巨大的,特别是将聚集索引建立在日期列时的差异。事实上,如果您的数据库真的有1000万容量的话,把主键建立在ID列上,就像以上的第1、2种情况,在网页上的表现就是超时,根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是:在各个select语句前加:

备注:

1.declare @d datetime

         其实没有详细介绍如何创建高效性能的索引,主要原因是根据不同的环境对待系统的要求不同,而优化也有所不同,当一个系统查询比较频繁,而新建,修改等操作比较少时,可以创建覆盖索引,将查询字段和where子句里的字段全部包含在内,这样查询的速度会比以前快很多,同时也带来弊端,就是新建或修改等操作时,比没有索引或没有建立覆盖索引时的要慢。总结一句话就是,具体问题具体分析。

2.set @d=getdate()

         数据库里的知识也是博大精深,并不是当初认为会写几条sq         l语句就以为非常精通了数据库什么的,真正要写出好的语句,得下功夫,了解数据库的底层,再经常问问DB牛人,慢慢积累后,也许你也能成为DB牛人呢。总结一句话就是,只要功夫深,铁杵磨成针。

并在select语句后加:

1.select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

2、只要建立索引就能显著提高查询速度

事实上,我们可以发现上面的例子中,第2、3条语句完全相同,且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别。所以,并非是在任何字段上简单地建立索引就能提高查询速度。

从建表的语句中,我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中,我们每天都会发几个文件,这几个文件的发文日期就相同,这完全符合建立聚集索引要求的:“既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看来,我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度

上面已经谈到:在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。

很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列):

1.(1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5''

查询速度:2513毫秒

1.(2)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5'' and neibuyonghu=''办公室''

查询速度:2516毫秒

1.(3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=''办公室''

查询速度:60280毫秒

从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

四、其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快

下面是实例语句:(都是提取25万条数据)

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''

使用时间:3326毫秒

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

使用时间:4470毫秒

这里,用聚合索引比用不是聚合索引的主键速度快了近1/4。

2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

用时:12936

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

用时:18843

这里,用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。

3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个:

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1''

用时:6343毫秒(提取100万条)

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-6-6''

用时:3170毫秒(提取50万条)

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''

用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)

1.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个不同的日期,日期精确到秒。

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1'' order by fariqi

用时:6390毫秒

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<''2004-1-1'' order by fariqi

用时:6453毫秒

五、其他注意事项

“水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。

所以说,我们要建立一个“适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使您的数据库能得到高性能的发挥。

当然,在实践中,作为一个尽职的数据库管理员,您还要多测试一些方案,找出哪种方案效率最高、最为有效。

(二)改善SQL语句

很多人不知道SQL语句在SQL SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解。比如:

1.select * from table1 where name=''zhangsan'' and tID > 10000和执行select * from table1 where tID > 10000 and name=''zhangsan''