聚集索引与非聚集索引区别

1. 聚集索引

聚集（clustered）索引，也叫聚簇索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引确定了表中数据的物理顺序，即数据块是根据聚集索引进行存储的，所以每张表中聚集索引只能建立一个，这就显得聚集索引更加的重要。聚集索引的挑选可以说是 查询优化 和 高效分页 的最关键因素。

例举聚集索引

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

聚集索引实际存放示意图

聚集索引有两个最大的优势

• 最快的速度缩小查询范围 – 多用于查询优化
• 最快的速度进行字段排序 – 多用于分页时的数据排序

聚集索引字段的选择

1. 最频繁使用的、用以缩小查询范围的字段上
2. 最频繁使用的、用以进行排序的字段上

注意

最好在创建表的时候就定义好聚集索引，由于聚集索引在物理顺序上的特殊性，因此如果在已经创建好的表中建立聚集索引的时候会根据索引字段的排序重新移动所有数据，会非常的耗费时间及性能。

2. 非聚集索引

非聚集（unclustered）索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，非聚集索引又细分为普通索引，唯一索引，全文索引。

索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

例举非聚集索引

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

非聚集索引实际存放示意图

非聚集索引二次查询问题

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据，也称为回表。

如何解决非聚合索引二次查询问题

复合索引（覆盖索引）：建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询。

注意

使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

3. 何时使用聚集索引或非聚集索引

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

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

4. 使用误区

1. 主键就是聚集索引

   纠正：详见 主键与聚集索引的区别

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

   纠正：并非是在任何字段上简单地建立索引就能提高查询速度，索引的建立会影响数据的插入速度，建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

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

   纠正：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列

5. 使用经验

1. 用聚合索引比用非聚合索引的主键速度快
2. 用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下
3. 使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
4. 日期列不会因为有分秒的输入而减慢查询速度

6. 总结

1. 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
2. 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
3. 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

---

参考

• https://blog.csdn.net/ykdrj/article/details/8201697

• https://www.cnblogs.com/s-b-b/p/8334593.html

• https://www.cnblogs.com/aspnethot/articles/1504082.html