你的位置:首页 > 数据库

[数据库]第6周 聚集索引


上个星期我向你介绍了堆表(heap tables)。我们说过,在SQL Server表可以是堆表(Heap Table)或者聚集表(Clustered Table)——一个在它上面有聚集索引(Clustered Index)定义的表。今天我们来谈论聚集索引(Clustered Index)的更多细节,还有如何选择正确的聚集键(Clustered Key)。

每次你在SQL Server创建一个主键约束(Primary Key constraint),这个约束(默认情况)是通过唯一聚集索引(Unique Clustered Index)来执行的。这意味着你选择的那列(或多列,当你定义复合主键(composite primary key)时)必须是唯一值。作为一种副作用,你的表数据是按那列(或那些列)物理排序的。让我们一起看下在SQL Server里聚集索引(Clustered Index)的优点和缺点。

优点

聚集表最大的优点是,数据是在你的存储子系统里是按聚集键(Clustered Key)物理排序的。你可以拿传统电话本与聚集表(Clustered Table)做比较:电话本是按姓来聚合排序的,这意味着Aschenbrenner排在Bauer之前,Bauer排在Meyer之前。因此聚集表(Clustered Table)和堆表(heap tables)完全不一样,堆表没有物理上的排序顺序。

你可以从聚集表(Clustered Table)获得真正的巨大受益。想象下你在便利查找一条具体的记录,在WHERE语句里那列是你用来限制你数据的主键(Clustered Key)。在那个情况下,SQL Server在执行计划里会选择聚集索引(Clustered Index Seek)查找运算符。查找运算符会非常,非常高效,因为SQL Server使用B-tree结构来找相关的数据。这个查找运算符的复杂度总是O(log N)。如果你想学习更多关于B+tree在内部是如何使用的,你可以观看我关于这个话题的SQL Server Quickie。在过去的2010年里,我也写了关于这个话题的很多博客帖子。

当你在电话本找名为Aschenbrenner的号码是一样的,你知道那个名只能在电话本的开头部分找到,因为电话本是按这个数据(名)排序的。因此你可以避免整个电话本的扫描,而SQL Server可以避免在叶子节点聚集索引(Clustered Index)的完全扫描。

只要在你的聚集索引(Clustered Index)里没有索引碎片(index fragmentation),当你使用扫描运算符访问聚集索引时,你会使用循序存取(sqquential I/O)。索引碎片(index fragmentation)指的是你在叶节点里的页,逻辑上和物理上的排列顺序是不一样的。你可以通过Index RebuildIndex Reorganized操作来修复索引碎片(index fragmentation)。在第24周,当我们涉及数据库维护时,我们会谈到这2个操作间的区别。

是否有索引碎片取决于你选择的聚集键(Clustered Key)列。只要你使用自增长值(像 INT IDENTIY,或订单日期(OrderDate)列),记录插在聚集索引(Clustered Index)的末端。这意味着在你索引里,碎片不会被引入。因为SQL Server只在你聚集索引(Clustered Index)末端追加数据。但在一些极少的情况下,也会产生索引碎片(index fragmentation)。因此我们现在会谈到聚集索引(Clustered Index)拥有的缺点,还有聚集键(Clustered Key)的错误选择。

缺点

数据只插在聚集索引(Clustered Index)的末端会引入被称为最后页插入加锁竞争(Last Page Insert Latch Contention)的问题,因为在你的聚集索引(Clustered Index)的末端你只有一个热区(hotspot),那里各个查询在遍历(traversing through)B-tree结构时互相竞争。下图演示了这个现象。

Last Page Insert

 

为了克服这个问题,你可以选择随机聚集键(random Clustered Key)作为你的聚集索引(Clustered Index),那样的话,你就可以把插入的数据散步到聚集索引(Clustered Index)里各个不同地方。但是随机聚集键(random Clustered Key)会引入被称为硬页分裂(Hard Page Splits)的问题,因为SQL Server需要把新数据页分配到在聚集索引(Clustered Index)叶子级别之内的一些地方。硬页分裂(Hard Page Splits)同样也有在事务日志(transaction log)性能上的负面影响,因为相比在你聚集索引(Clustered Index)末端记录一个普通的INSERT(被称为软页分裂(Soft Page Splits)),记录一个硬页分裂(Hard Page Splits)需要更多的工作。

作为一个副作用,随机聚集键(random Clustered Key)会引入索引碎片(index fragementation),因为你的逻辑和物理排列顺序已经不再一样。随机存取(random I/O) 会扼杀你在传统的旋转存储的扫描操作性能,因为当读取各个数据页的时候,磁头必须在硬盘的盘片上前后移动。

小结

聚集索引(Clustered Index)伸缩性(scale)很好,因为它内部采用了B-tree数据结构。当在你表进行索引查找(index seek)运算符时,SQL Server可以很高效的利用这个结构。但是选择一个正确并合适的聚集键(Clustered Key)是个很耗时的工作,因为你要考虑每个情况下所有优点和缺点(什么时候用增值型(increasing value),什么时候用随机值型(random value))。

(作者广告时间,推销自己额外详细介绍如何选择正确和合适的列作为聚集键超长1个多小时视频,清仓打折出售,买2得3,不翻译。)

下星期我会谈论SQL Server里非聚集索引(Non-Clustered Indexes)的更多信息。你会学到什么是非聚集索引(Non-Clustered Indexes),还在聚集索引(Clustered Indexe)里定义的聚集键(Clustered Key),非聚集索引(Non-Clustered Indexes)与它有怎样的从属关系。好好享受接下来的7天,到时候我们会再次见面。