聚集索引和非聚集索引的区别理解

    对于大多数的非专业的数据库操作人员来讲，比如我们这些软件开发人员，在很大程度上都搞不清楚数据库索引的一些基本知识，有些是知其一不知其二，或者是知其然不知其所以然。造成这种情况的主要原因我觉的是行业原因，有很多公司都有自己的数据库工程师，他们会帮助你优化SQL，开发人员即使不懂优化问题也不大，所以开发人员对这方面也就不会下太多功夫去了解SQL优化，但如果公司没有这样的DBA呢，就只能靠程序员自己了。其实了解这方面知识对自己还是有很大的帮助的。索引设计的好与坏对性能的影响是很大的。下面就来好好介绍下什么是聚集和非聚集索引吧。

　　大家可能认为这个问题难度不大，认为太熟悉了，也许不会感兴趣，但你真能说清楚吗？其实要想说明白这两者的差别也不是三两句就说的清的，那天我也是觉的这问题太泛了，就随便说了其中的两个区别：

　　1、聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个，这个跟没问题没差别，地球人都知道。

　　2、聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续，地球人也都知道。

但何为逻辑连续和物理存储联系呢。

其实上面的我们需要搞清楚以下几个问题：

　　第一：聚集索引的约束是唯一性，是否要求字段也是唯一的呢？

　　分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。

　　结论：聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。

　　第二：为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？


　　粗一看，这还真是和聚集索引的约束相背，但实际情况真可以创建聚集索引。


　　分析其原因是：如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎 将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。


　　第三：是不是聚集索引就一定要比非聚集索引性能优呢？


　　如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？


　　答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。


　　第四：在数据库中通过什么描述聚集索引与非聚集索引的？


　　索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。


　　第五：在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？


　　有了上面第四点的认识，我们分析这个问题就有把握了，在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况：聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行（索引的存储空间比实际数据要少），这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。

所以总结如下：

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