作者： riemann
riemann.blog.csdn.net/article/details/90324846
一、深入理解索引结构，实际上可以把索引理解为特殊的目录。 Microsoft SQL server提供了两种索引类型：群集索引、群集索引(也称为群集索引)和非群集索引(也称为非群集索引) 下面通过示例说明聚合索引和非聚合索引之间的区别。
其实，我们中文词典的正文本身就是集合索引。 例如，查一下“安”字吧。 “安”的拼音是“an”，但用拼音排序汉字的词典以字母“a”开始，以“z”结束，所以“安”字自然排列在词典的开头。 如果翻遍了以“a”开头的所有部分也找不到这个字，说明词典里没有这个字。 也就是说，词典的正文部分本身就是目录，所以不需要调查其他目录来找到应该找的内容。 这种正文内容本身按照一定规则排列的目录称为“集合索引”。
遇到不认识的字而不知道发音时，需要从“旁部首”中找到要找的字，然后从该字后面的页码中直接翻页找到要找的字。
但是，将“部首目录”和“检字表”组合起来查的字的排序并不是真正的正文排序方法。 例如，如果调查" htdxj字"，则在调查部首后的检查表中显示" htdxj的页号为672页，在检查表中显示" htdxj之上为"驰"字，但页号为63页，" htdxj的页号"。很明显，这是现在看到的连续的“驰、张、张”这三个单词，实际上是按非集合索引的排序，是词典正文单词在非集合索引的映射。
这样就可以找到所需的单词，但要找到目录中的结果并将其移动到所需的页码，需要两个过程。 这样的目录纯粹是目录，正文纯粹是正文的排序方式称为“非集约索引”。
通过以上示例，可以看出什么是聚合索引和非聚合索引。 在进一步的铺垫中，您会发现目录只能以一种方式排序，因此每个表只能有一个聚集索引。
二、区别和优缺点：
一个表中只能有一个聚合索引。 一个表中可以存在多个，而不是聚合索引中
聚合索引存储记录在物理上是连续的，在逻辑上是连续的而不是聚合索引，并且在物理上不是连续的
聚合索引：物理存储按索引排序； 聚合索引是对键值和逻辑顺序的索引的组织，这些索引决定表中数据行的物理存储顺序。
非聚集索引：物理存储未按索引排序； 非聚集索引是常规索引，它只为数据列创建适当的索引，而不影响整个表的物理存储顺序。
索引是用二叉树的数据结构记述的，聚类索引可以理解为如下。 索引的kqdjc点是数据节点。 非聚簇索引的kqdjc点仍然是索引节点，但有一个指向相应数据块的指针。
利弊：
当插入聚合索引的数据时，“物理保存的排序”会花费一些时间，必须首先找到位置，然后插入。 )中，数据查询比未聚合数据更快。
三.必须澄清的几个问题第一：聚集索引的约束是唯一性，是否要求字段也是唯一的呢？
分析：如果您认为可能受到了系统缺省设置的影响，则通常指定表的主键。 如果此表之前没有聚合索引，并且在创建主键时未强制指定使用非聚合索引，则缺省情况下，SQL会在此字段中创建聚合索引。 用于创建聚合索引的字段当然也必须是唯一的，因为所有主键都是唯一的。
结论：可以在任何列中想要创建的字段中创建集合索引。 这在理论上，实际情况不能随便指定。 不这样做的话，会成为表演上的噩梦。
第二：为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？
乍一看，这真的与聚合索引的约束相反，但实际上可以创建聚合索引。
这是因为如果没有使用UNIQUE属性创建聚合索引，数据库引擎会自动向表中添加4字节的uniqueifier列。 如有必要，数据库引擎会自动将uniqueifier值添加到行中，使每个键都是唯一的。 此列和列的值在内部使用，用户无法查看或访问。
第三：是不是聚集索引就一定要比非聚集索引性能优呢？
想查学分60-90的学生的学分和名字的话，给学分编制集合索引是最好的吗？
答：不。 因为只输出两列，所以可以为单位和学生名称创建联合非聚合索引。 此时的索引形成覆盖索引。 这意味着，存储在索引中的内容是最终输出的数据，与以单位为聚合索引构建查询相比，其性能更高。
第四：在数据库中通过什么描述聚集索引与非聚集索引的？
索引是以二叉树的形式记述的，可以这样区别集合索引和非集合索引的不同。 聚合索引上的kqdjc点是最终数据节点，而不是聚合索引的kqdjc仍然是索引节点，但指向最终数据
指针。索引超全总结：带你从头到尾捋一遍MySQL索引结构第五：在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？
有了上面第四点的认识，我们分析这个问题就有把握了，在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。
我们来比较下主键为聚集索引和非聚集索引的查找情况：聚集索引由于索引kqdjc点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行（索引的存储空间比实际数据要少），这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。
四、何时使用聚集索引或非聚集索引 五、结合实际，谈索引使用的误区理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。
1、主键就是聚集索引–错误想法的这种想法是极端错误的，是对聚集索引的一种浪费。虽然默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。如果我们将这个列设为主键，mysql会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照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毫秒
END 推荐好文强大，10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程！
为什么MySQL不推荐使用uuid或者雪花id作为主键？
为什么建议大家使用 Linux 开发？爽（外加七个感叹号）
IntelliJ IDEA 15款 神级超级牛逼插件推荐（自用，真的超级牛逼）
炫酷，SpringBoot+Echarts实现用户访问地图可视化（附源码）
记一次由Redis分布式锁造成的重大事故，避免以后踩坑！
十分钟学会使用 Elasticsearch 优雅搭建自己的搜索系统（附源码）