SQL索引学习-聚集索引

这篇接着我们的索引学习系列，这次主要来分享一些有关聚集索引的问题。上一篇SQL索引学习-索引结构主要是从一些基础概念上给大家分享了我的理解，没有实例，有朋友就提到了聚集索引的问题，这里列出来一下： 其实，我想知道的就是对于一个大数据量的表，我应

这篇接着我们的索引学习系列，，这次主要来分享一些有关聚集索引的问题。上一篇SQL索引学习-索引结构主要是从一些基础概念上给大家分享了我的理解，没有实例，有朋友就提到了聚集索引的问题，这里列出来一下：

其实，我想知道的就是对于一个大数据量的表，我应该用哪种索引，各有什么优缺点。如果能带一两个实例，就更perfect了。

看过很多这样文章，但具体还是不知道如何设计表和优化，比如：聚集和非聚集， 唯一与主键, 设计表事该如何取舍。应该有示例说明,这更容易理解，只是概念即使理解了也不容易消化。

上面两位朋友的问题有一个共同特点，就是希望有示例，因为这样容易让他们更加容易理解。但从我的角度来讲，有示例只能给你提供一个参考而已，够不成是否容易消化的关键因素，最好的办法是，通过自己的理解，自己有能力去做相应的实验，这样效果才是最好的，你也会发现更多的问题，每个项目都有自己的特点，所以性能优化这块也是需要因地制宜的。

聚集索引的存储结构

聚集索引的特点 B+树的结构

它的结构完全符合聚集索引的存储结构，所以我们说聚集索引的存储结构为B+树。

聚集索引的重要性

聚集索引的选择是数据库设计的基石，不好的聚集索引设计不光是增加查询的执行时间，而且是一个瀑布性的影响：

如何选择表的聚集索引

一般可以优先参考如下因素：

窄列

创建如下表，为了测试只包含两个字段，一个在类型为Int的Id上创建聚集索引，一个在类型为uniqueindentifier的Code上创建聚集索引，且为唯一聚集索引。

CREATE TABLE dbo.NarrowStudent ( Id INT IDENTITY(1, 1) , -- unique Code UNIQUEIDENTIFIER -- unique ) ; CREATE UNIQUE CLUSTERED INDEX PK_NarrowStudent_Id ON NarrowStudent(Id) CREATE TABLE dbo.Student ( Id INT IDENTITY(1, 1) , -- unique Code UNIQUEIDENTIFIER -- unique ) ; CREATE UNIQUE CLUSTERED INDEX PK_Student_Code ON Student(Code)

再分别插入一条数据：

INSERT INTO dbo.NarrowStudent ( Code ) VALUES ( NEWID() -- Code - uniqueidentifier ) INSERT INTO dbo.Student ( Code ) VALUES ( NEWID() -- Code - uniqueidentifier )

 看下行大小：

注意为什么宽度为27而不是20呢(Id类型为int,占用4字节，Code为Guid占用16字节)，这是SQL Sever内部为了维护可空值或者是可变长值而预留7位空间。

我们再多插入些数据来做对比,插入的脚本就贴了，然后我们看下两表所占用的空间对比：采用了int做为主键的表数据占用为320K,选用Guid为主键的表占用为464K,明显较int要费磁盘空间。

索引健康情况

下图中红线部分有一个非常重要的参数：扫描密度，明显可以看出在连续对表进行数据插入后，int自增性为主键的索引密度比Guid为主键的索引密度要大的多。这说明前者产生的索引碎片更低。

聚集索引对非聚集索引的影响

两者最大的区别在于聚集索引的叶级存储了数据本身，但非聚集索引叶结点不存在数据记录，只是一个指向聚集索引的指针，这就意味着在非聚集索引的所有级别中都包含了聚集索引的指针，聚集索引的大小会直接影响非聚集索引的大小。

为上面两个表，增加一个AddressInfo的字段，且创建非聚集索引，这里为了测试的有效性，不要使用如下语句添加列之后做测试，因为后期表结构的变更会引起比较明显的数据分页情况，建议创建新表来测试，下面对比在两个表中，字段类型以及值者相同以及表数据条数一样的情况下非聚集索引的大小情况,结论是在其它条件都相同的情况下，谁的主键大谁占用的索引空间就更大。

主键为int的非聚集索引

主键为Guid的非聚集索引

唯一性

上面提到过聚集索引可以选择具有重复值的列，但在内部会维护一个类型为uniqueifier的字段，长度为4字节,同时还会需要维护可变长列，同样会占用4字节，所以SQL Server会使每行的大小增加8字节，数据类似如下表格：

Id First Name uniqueifier

1 Tom NULL

2 Tom 1

3 Andy Null

 

 

 

 

 

 

关键字第一次出现时，uniqueifier赋值为NULL,当第二次出现时，就开始计数累加。赋值为NULL时占用0字节，可从如一图得到结果：

再插入一条重复数据之后再查看行大小，由11字节变成19字节了，这多出来的8字节，就是当uniqueifier值不等于空之后的结果。