浅谈聚集索引 & 非聚集索引

聚集索引

聚集索引表记录的排列顺序和索引的排列顺序一致，数据行的物理顺序与列值（一般是主键那一列）的逻辑顺序相同，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。

一个表中只能拥有一个聚集索引。

优点是查询快。

缺点是修改慢。因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

-- 添加聚集索引
create clustered index CLU_ABC on abc(A);

-- 删除聚集索引
drop index abc.CLU_ABC;

• 其中，CLU_ABC 为聚集索引的名字，abc(A) 为表 abc 的 A 列

非聚集索引

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

非聚集索引层次多，不会造成数据重排。

非聚集索引又可以分为普通索引、唯一索引、全文索引。

-- 添加非聚集索引
create nonclustered index NONCLU_ABC on abc(A);

二者区别

二者的各根本区别是：表记录的排列顺序和索引的排列顺序是否一致。

用一个例子来形容聚集索引和非聚集索引的区别就是：聚集索引就好像用新华字典的汉字的拼音首字母去查询汉字所在页，而非聚集索引就好像你用汉字的偏旁部首去查询一个汉字（偏旁部首的顺序跟汉字实际存储的顺序不一致）。

• 聚集索引的叶子节点就是对应的数据节点，可以直接获取到某一行对应的全部列的信息；
• 非聚集索引的叶子节点仍然是索引节点，只是有一个指针指向对应的数据块。在索引没有覆盖到对应的列时候需要进行二次查询。
• 因此在查询方面，聚集索引的速度更优。

总结：

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

有以下几个问题：

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

   不要求唯一！

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

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

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

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

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

   在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。

   聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，

   而非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗。

测试

假如现在有这么一张表：

children

	age	name
1	1	hory
2	5	casia
3	7	amili
4	9	ferry

这个时候插入一条数据：

insert into children values('6','kiven');

此时再次查询为下表所示：

	age	name
1	1	hory
2	5	casia
3	7	amili
4	9	ferry
5	6	kiven

添加聚集索引：

create clustered index CLU_CHIL on children(age);

再查询数据显示为如下表，此时发现表的顺序发生了变化，此时的排序按age字段的递增排序：

	age	name
1	1	hory
2	5	casia
3	6	kiven
4	7	amili
5	9	ferry

删除聚集索引，会发现表的顺序不会发生改变：

drop index children.CLU_CHIL;

接着添加非聚集索引，添加新的记录：

create nonclustered index NONCLU_CHIL on children(age);
insert into children values('4','mari');

查看表顺序，如下表，并没有影响表的顺序：

	age	name
1	1	hory
2	5	casia
3	6	kiven
4	7	amili
5	9	ferry
6	4	mari