聚集索引与非聚集索引的总结.docx

《聚集索引与非聚集索引的总结.docx》由会员分享，可在线阅读，更多相关《聚集索引与非聚集索引的总结.docx（6页珍藏版）》请在第壹文秘上搜索。

1、聚集索引与非聚集索引的总结一,索引简介众所周知，索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及HaSh结构，聚集索引以及非聚集索引用的是B+树索引。这篇文章会总结SQLServer以及MySQL的InnoDB和MyISAM两种SQL的索引。SQLSeVer索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。MySQL索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。二.聚集索引聚集（ClUStered）索引，也叫聚簇索引。定义：数据行的物理挨次与列值（一般是主键的那一列）的规律挨次相同，一个表中只能拥有一个聚集索引。单单从定义来看

2、是不是显得有点抽象，打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音名目，而每个字存放的页码就是我们的数据物理地址，我们假如要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音名目对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音名目对应的A-Z的字挨次，和新华字典实际存储的字的挨次A-Z也是一样的，假如我们中文新出了一个字，拼音开头第一个是B,那么他插入的时候也要依据拼音名目挨次插入到A字的后面，现在用一个简洁的示意图来也许说明一下在数据库中的样子：地址idusernameOxOl1小明0x022小红0x033小华Oxff256小英注：第一列的地址表示该行数据在磁

3、盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。结合上面的表格就可以理解这句话了吧：数据行的物理挨次与列值的挨次相同，假如我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的挨次相同，所以也就只能建立一个聚集索引了。FindingRowsinaClusteredIndexsysindexesIidIindid.rootClusteredIndexJ,235625Page 140-RootAkhtarMartinSmithPageI45nioleseses GaHalJ0nJ0nJ0n3478343

4、43476802459267878347834Smith 1434Smith 5778Smith 7978White 2234White 1634Page 100Page 110Page 120Page 130聚集索引实际存放的示意图从上图可以看出聚集索引的好处了，索引的叶子节点就是对应的数据节点（MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区分），可以直接猎取到对应的全部列的数据，而非聚集索引在索引没有掩盖到对应的列的时候需要进行二次查询，后面会具体讲。因此在查询方面，聚集索引的速度往往会更占优势。创建聚集索引假如不创建索引，系统会自动创建一个隐

5、含列作为表的聚集索引。1 .创建表的时候指定主键（留意：SQLSeVer默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引）createtabletl(idintprimarykey,namenvarchar(255)2 .创建表后添加聚集索引SQLServercreateclusteredindexClusteredJndexontable_name(colum_name)MySQLaltertabletable.nameaddprimarykey(colum,name)值得留意的是，最好还是在创建表的时候添加聚集索引，由于聚集索引的物理挨次上的特别性，因此假如再在上

6、面创建索引的时候会依据索引列的排序移动全部数据行上面的挨次，会特别地耗费时间以及性能。三.非聚集索引非聚集(unclustered)索弓I。定义：该索引中索引的规律挨次与磁盘上行的物理存储挨次不同，一个表中可以拥有多个非聚集索引。其实依据定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成一般索引，唯一索引，全文索引。假如非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构挨次与实际存放挨次不肯定全都。FindingRowsinaHeapwithaNonclusteredIndexsysindexesid I indid = 2 roo

7、t Page 37AkhtarGanioPage 41Akhtar 4:706:01Barr 4:70503Con 470401Funk 4:706:02FUnk 4704021fHHeapPage 7049i0203Page 705-Rudd WhiteBarrAhte 4705:02age 71S储 4:70603Srrilh 4:708:04 mdh 4:707:01Page 51 Ganio 4:703:01Hall 4:709:04 Jones 4:709:02 Jones 4:703:03 JAS-6 47C7CMiIKi I4:79；.URiirkl 14795 11Page 7

8、001 .02 .03 .04 .f-Page 707Non-Leaf LevelNon clustered IndexLeafLeveI(Key Value)Page 808Page 709非聚集索引实际存放的示意图聚合索引的二次查询问题非聚集索引叶节点仍旧是索引节点，只是有一个指针指向对应的数据块，此假如使用非聚集索引查询，而查询列中包含了其他该索引没有掩盖的列，那么他还要进行其次次的查询，查询节点上对应的数据行的数据。如有以下表tl：id12username小明小红小华256小英score90809270以及聚集索引clusteredindex(id),非聚集索引index(userna

9、me)。使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以猎取到查询列的数据。selectid,usernamefromtlwhereusername=小明selectusernamefromtlwhereusername=小明但是使用以下语句进行查询，就需要二次的查询去猎取原数据行的score：selectusername,scorefromtlwhereusername=小明在SQLServer里面杳询效率如下所示，IndexSeek就是索引所花费的时间，KeyLookup就是二次查询所花费的时间。可以看的出二次查询所花费的查询开销占比很大，达到50%。SEL

10、ECT开俏：0 士Nested Loops(Inner Join)开楂：0 %AIndexSeek(NonClustered)tl.ix_test2开俏二SO%燧KeyLookt(Clustered)tl.PK_tl_3213E83F8D0SDD5Bl一丽50在SQLSerVer里面会对查询自动优化，选择适合的索引，因此假如在数据量不大的状况下，SQLSerVer很有可能不会使用非聚集索引进行查询，而是使用聚集索引进行查询，即便需要扫描整个聚集索引，效率也比使用非聚集索引效率要高。seIectscorefromtlwherenameSELECT开俏：0 &ClusteredIndexScan(

11、Clustered)tl.PK_jtl3213E83F8D0SDDSB一开清：工00本人试过在含有30W行表上建立非聚集索引，查询非聚集索引掩盖以外的列就会变成聚集索引的全索引扫描(indexscan)查询来避开二次查询，而在此外一张200W行表才会用到非聚集索引Seek对应的列再进行keklookup,有关于SQLServer的有Indexseek,indexscan,tablescan,keyLookUp这几个概念，可以查看这个hhg,描写比较具体。但在MySQL里面就算表里数据量少且查询了非键列，也不会使用聚集索引去全索引扫描，但假如强制使用聚集索引去查询，性能反而比非聚集索引查询要差，

12、这就是两种SQL的不同之处。还有一点要留意的是非聚集索引其实叶子节点除了会存储索引掩盖列的数据，也会存放聚集索引所掩盖的列数据。如何解决非聚集索引的二次查询问题复合索引(掩盖索引)建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，11index(coll,col2),执行下面的语句selectcoll,col2fromtlwherecoll=213;要留意使用复合索引需要满意最左侧索引的原则，也就是查询的时候假如where条件里面没有最左边的一到多列，索引就不会起作用。在SQLServer中还有include的用法，可以把非聚集索引里包含的列包含进来，而不肯定需要建立复合索引。四.总结与使专心得1 .使用聚集索引的查询效率要比非聚集索引的效率要高，但是假如需要频繁去转变聚集索引的值，写入性能并不高，由于需要移动对应数据的物理位置。2 .非聚集索引在查询的时候可以的话就避开二次查询，这样性能会大幅提升。3 .不是全部的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。