SQL Server的官方文档里共列了8行来说明它的索引种类,但其实SQL Server的索引主要就两个维度:是否是唯一性索引(Unique),是否是聚集索引(Cluster)。其它几种索引都是对特殊数据类型所做的索引。

创建索引的最基本的语法如下(详见MSDN):
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )

关于索引的第一个维度较好理解,唯一性索引会限制表的数据,使其每行中索引列的值都是唯一的,阻止重复值数据的插入。唯一性索引需在创建索引时显示地指明Unique。

关于索引的第二个维度,是否用聚簇(Clustered),这个几乎是SQL Server索引的核心内容,下面详细介绍下。

在一个表上的几个列上建立聚簇索引后,SQL Server将这个表按照B-树结构组织起来(未建立聚簇索引的表按照堆Heap的方式组织),下图取值MSDN的官方文档,很好的解释了聚簇索引的层级结构。

Levels of a Clustered Index

在SQL Server中最小的存储单元是页(Page),大小为8KB,这意味着每次SQL Server都是从硬盘中读取整数个页到内存中去处理的。如上图,在索引树中每个Page称为是索引节点(index node),一棵索引树从根节点(root node)开始,进入到下一层的索引节点,除叶子节点外的所有节点都储存了这样<值,指针>的键值对所组成的Index rows,其中的“值”代表的是索引列的值,指针则是指向下一层的node,而叶子节点(Leaf node)是实际储存数据的地方。

所以也就可以得到聚簇索引的几个性质:

1. 一个表上最多只能建立一个聚簇索引

2. 聚簇索引额外占用空间相对于数据储存空间而言是很小的

这些都是因为聚簇索引只是代表了表在底层的一种组织方式。

而非聚簇索引(Unclustered)则不一样,它是在建索引的表外又自己维护了一个表,这个系统自己维护的表也是采用B-树结构组织的,这树与聚簇索引的B-树的唯一区别在它的叶子节点上,前者的叶子节点只存了索引列的数据以及到该记录的指针,如下图所示:

Levels of a Nonclustered Index

(其实当表是以heap的方式组织时,此处叶子节点才存的是指针,当表上有建有聚簇索引是存的是聚簇索引对该条记录的索引唯一值,详见MSDN。)

自此可以看出“Clustered”意味着物理排序,而“Nonclustered”意味着逻辑排序。用网上流传的一个比方来说就是Clustered意味着新华字典里的拼音排序,而Nonclustered大致类似于部首索引。

一般将聚簇索引建在主键ID上一方面是因为ID是自增的,而聚簇索引作为一种有序的物理组织方式,所以不断增加的ID行就可以直接加在后面,否则将行插入中间的某个位置就很有可能要拆页,这样的行为多了就会产生碎片,浪费空间。另一个原因是非聚簇索引的叶子节点会存储聚簇索引的索引值,当聚簇索引用ID列时可以减少非聚簇索引的空间使用情况。

参考文章:

SQL Server的索引页可以参考MSDN这里

T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他

唯一性索引(unique index)和唯一性约束(unique  constraint)是一样的吗?

是的,你几乎可以认为它们是一样的。

那么它们不一样在什么地方?

就像我前面所说的那样,它们几乎是一样的,所以可以知道它们的共同点肯定会更多,而且也更重要。所以我们先来看看它们的共同点。

唯一性索引是一种实体结构来保证列值的唯一性,唯一性约束的实质也就是建立一个唯一性索引来实现唯一性约束的,所以唯一性约束就像是套上了“约束”外壳的唯一性索引。

至于它们的不同之处,可以详见这篇文章(啰嗦了一些),在此总结一下,唯一性约束(UC)和唯一性索引(UI)。

UC是个约束,但是却不比UI多什么特点,反倒是UI可以在创建索引时加入一些选项(如PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, 和STATISTICS_NORECOMPUTE)而更灵活,这些选项中除了FILLFACTOR,其它的均不能在UC中实现。

UC这种约束是不能被Disable的,所以就这样结束吧。