搜索
第1页2020-1-30深入浅出理解索引基于近期DB同学在学习数据库相关知识，我也给自己充电充电，方便的时候能显摆一下。索引结构实际上可以把索引理解为一种特殊的目录，好比图书馆中的卡片。可以以SQLSERVER为例。一般有两种索引结构：1.聚簇索引是按照数据库存放的物理位置为顺序的；能提高多行检索的速度。2.非聚簇索引与数据库物理排列顺序无关；对于单行的检索很快。但是两者都会减缓数据插入的速度。不过这个定义太抽象了。SQLServer中，索引是通过二叉树的数据结构来描述的。聚簇索引：索引的叶节点就是数据节点。非聚簇索引：叶节点仍然是索引节点，只不过有一个指针指向对应的数据库。如图：第2页2020-1-30以汉语字典为例，其实汉语字典的正文本身就是一个聚簇索引。比如，你要查“安”“张”，按照拼音排序汉字的字典是以“a”“z”结尾的，那么“安”自然就排在字典的前部，“张”自然排在字典的后部分。字典的正文部分本身就是一个目录，您不需要去查看其他目录来找到您需要的。如果您不认识的字，不知道它的发音，这个时候您就要根据“偏旁部首”查到您要找到的字，结合“部首目录”“检字表”查到的字排序不是真正的正文的排序方法，它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。何时使用聚簇索引或非聚簇索引动作描述聚簇索引非聚簇索引列经常被分组排序应应返回某范围内的数据应不应一个或极少不同值应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频繁修改索引列不应应例如：定义的某个表中有一个时间列，恰好把聚簇索引建立在该列，这时您查找的是某个时第3页2020-1-30间段之间的全部数据，那么这个速度就是非常的快，因为聚簇索引只要找到检索的所有数据中的开头和结尾数据即可以。而非聚簇索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码找到具体内容。索引块与数据块的区别例子：表中一个记录在磁盘上占用1000字节的话，对其中10个字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。SQLserver的最小空间分配单元是“Page”一个Page在磁盘上占用8K空间，那么一个Page可以存储上述记录8条，但是可以存储所以800条，现在要有一个8000条记录的表中检索符合某个条件的记录，如果没有索引，要遍历：8000*1000/8K=1000Page才能找到结果。如果在检索字段上有上述索引的话，8000*10/8k=10Page就能找到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多。索引优化技术1.并非用到索引速度就快,如：我们要检索上述表中的所有记录，不用索引需要1000Page，如果使用索引，首先检索索引10Page然后根据索引检索结果去对应数据页面，由于是检索所有数据，所需要再访问8000*1000/8K=1000Page一共访问了1010个页面。这显然不如不用索引快。2.SQLserver内部有一套完整的数据检索优化技术，在上述情况下，SQLserver的查询计划（SearchPlan）会自动使用表扫描的方式检索数据而不会使用任何索引，那么SQLServer是怎么知道什么时候用索引，什么时候不用索引的呢？SQLserver除了日常的维护数据信息外。还维护着数据统计信息，下图是数据库属性页面的一个截图第4页2020-1-303.从中可以看出SQLserver自动维护统计信息，这些统计信息包括数据密度信息以及数据分布信息，这些信息帮助SQLServer决定如何制定查询计划以及查询是是否使用索引以及使用什么样的索引CREATETABLEtabTest(IDintIDENTITY(1,1)NOTNULL,unqValueuniqueidentifierNOTNULL,intValueintNOTNULL)ALTERTABLEtabTestWITHNOCHECKADDCONSTRAINTPK_tabTestPRIMARYKEYCLUSTERED(ID)ALTERTABLEtabTestADDCONSTRAINTDF_tabTest_unqValueDEFAULT(newid())FORunqValueCREATEINDEXIX_tabTest_unqValueONtabTest(unqValue)declare@iintdeclare@vint第5页2020-1-30set@i=0while@i10000beginset@v=rand()*1000insertintotabTest(intValue)values(@v)set@i=@i+1end4.然后在打开查询执行计划“查询-显示执行计划”执行查询从执行计划上可以看出，在第一个查询中，使用了unqValue所以，根据箭头方向，计算机先在索引范围内找，找到后，使用BookmarkLookup将索引节点映射到数据节点上，最后给出select结果，在第二个查询中，系统直接遍历表给出结果，不过它使用了聚簇索引，为什么呢，不要忘记，聚簇索引的页节点就是数据节点，这样使用聚簇索引会更快一些，（不受数据删除，更新留下的存储空洞的影响，直接遍历数据是跳过这些空洞的）5.将ID字段的聚簇索引更改为非聚簇索引，然后执行select*fromtabTest，这样看执行计划第6页2020-1-30没有使用任何索引，而是直接执行了TableScan，因为只有这样，检索效率才是最高的。数据简单说明二者之间的区别聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引检点，并保留一个链接指向对应数据块。例如：假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节/8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数第7页2020-1-30据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。在执行插入操作时，非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复，那么SQLServer是怎么知道不出现重复的呢？唯一的方法就是检索。对于非聚簇索引，只需要检索20个页面中的16000个节点就知道是否有重复，因为所有主键键值在这16000个索引节点中都包含了。但对于聚簇索引，索引节点仅仅包含了8000个中间节点，至于会不会出现重复必须检索另外8000个页数据节点才知道，那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。看看数据检索的效率，如果对上述两表进行检索，在使用索引的情况下（有些时候SQLServer执行计划会选择不使用索引，不过我们这里姑且假设一定使用索引），对于聚簇索引检索，我们可能会访问10个索引页面外加1000个数据页面得到结果（实际情况要比这个好），而对于非聚簇索引，系统会从20个页面中找到符合条件的节点，再映射到1000个数据页面上（这也是最糟糕的情况），比较一下，一个访问了1010个页面而另一个访问了1020个页面，可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。实际应用中的误区理论的目的是应用。但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区。1.主键就是聚集索引SQLServer默认是在主键上建立聚簇索引的，通常我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。在我们的办公自动化实例中的列是ID就是，如果我们将这个列设为主键，数据库会将此列默认为聚簇索引，这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序。显而易见，聚簇索引的优势是很明显的，而每个表中只能有一个聚簇索引的规则，这使得聚簇索引变得更加珍贵。2.根据前面谈到的聚簇索引的定义，我们使用聚簇索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描，在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的Id号，所以我们很难在实践中用ID号来进行查询。这就是使让ID号这个主键作为聚簇索引成为一种资源的浪费。其次，让每个ID号都不同的字段作为聚簇索引也不符合“大数目的不同值下不应建立聚簇索引”的规则；当然，这种情况下只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。3.在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、计划、任务、需要用户进行文件查询等任何情况下进行数据查询都离不开字段是‘日期’还有用户本身的“用户名”4.通常，办公自动化的首页会显示每个用户尚未处理的任务等，虽然我们的where语句可以仅仅限制当前用户尚未处理的任务，但如果系统建立的时间长了，数据量大，那么每次用户打开首页的时候都要进行一次全表扫描，这样做意义是不大的，绝大多数的用户1一个月之前的文件等都已经浏览过了，这样做只能徒增数据的开销而已。事实上完全可以让用户打开系统首页时候，数据库仅仅查询这个用户近3个月来未处理的任务等，铜鼓日期这个字段来限制表扫描，提高查询速度。5.聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使第8页2020-1-30您在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）：（1）仅在主键上建立聚集索引，并且不划分时间段：Selectgid,fariqi,neibuyonghu,titlefromtgongwen用时：128470毫秒（即：128秒）（2）在主键上建立聚集索引，在fariq上建立非聚集索引：selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqidateadd(day,-90,getdate())用时：53763毫秒（54秒）（3）将聚合索引建立在日期列（fariqi）上：selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqidateadd(day,-90,getdate())用时：2423毫秒（2秒）虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是：在各个select语句前加：declare@ddatetimeset@d=getdate()并在select语句后加：select[语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())2、只要建立索引就能显著提高查询速度事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。第9页2020-1-30从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们