在选择主键时通常也会选择聚集键。这两者经常被混淆,但您必须理解它们的区别。
主键是逻辑上的 业务 元素。应用程序使用主键来识别实体,并且关于主键的讨论主要是是否使用自然键或代理键。这些链接提供了更详细的信息,但基本思想是自然键源自现有实体属性,如 ssn
或 电话号码
,而代理键对于业务实体没有任何意义,比如 id
或 rowid
,它们通常是 IDENTITY
类型或某种 uuid。我的个人观点是,代理键优于自然键,选择本地应用程序始终使用标识值,选择分布式数据使用 guid。主键在实体的生命周期内永远不会改变。
聚集键是定义表中行的物理存储的键。大多数情况下,它们与主键(逻辑实体标识符)重叠,但实际上并不强制执行或要求这两者相同。当二者不同时,这意味着表上有一个非聚集唯一索引实现了主键。聚集键的值实际上可以在行的生命周期内更改,导致将该行在表中移动到新位置。如果必须将主键与聚集键分开(有时确实需要这样做),则选择好的聚集键比选择主键要困难得多。主要驱动聚集键设计的因素有两个:
- 普遍的数据访问模式。
- 存储考虑因素。
数据访问模式。我的理解是指查询和更新表格的方式。请记住,聚集键确定了表中行的实际顺序。对于特定的访问模式,某些布局在查询速度或更新并发性方面起到了非常重要的作用:
使用FIFO队列风格进行处理。在这种情况下,表格有两个热点:插入发生的尾部(enqueue)和删除发生的头部(dequeue)。集群键必须考虑到这一点,并组织表格以在物理上分离磁盘上的尾部和头部位置,以允许enqueue和dequeue之间的并发性,例如使用enqueue顺序键。在纯粹的队列中,这个集群键是唯一的键,因为表格上没有主键(它包含消息,而不是实体)。但是大多数时候,队列不是纯粹的,它也作为实体的存储,队列和表格之间的界限变得模糊。在这种情况下,还有一个主键,它不能是集群键:实体可以重新入队,从而更改enqueue顺序集群键值,但它们不能更改主键值。没有看到这种分离是用户表格支持的队列非常难以正确处理且容易出现死锁的主要原因:因为enqueue和dequeue会交错地发生在表格中,而不是局部地发生在队列的尾部和头部。
相关处理。当应用程序设计良好时,它将在其工作线程之间分区处理相关项目。例如,处理器被设计为具有8个工作线程(比如与服务器上的8个CPU匹配),因此处理器将数据在它们之间进行分区,例如,工作线程1只选择名为A到E的帐户,工作线程2选择F到J等。在这种情况下,表格应该实际上按帐户名称(或由左侧位置是帐户名称第一个字母的复合键)进行聚集,以便工作线程在表格中定位它们的查询和更新。这样的表格将有8个不同的热点,每个工作线程都集中在此时此刻的区域周围,但重要的是它们不重叠(不会阻塞)。这种设计在高吞吐量的OLTP设计和TPCC基准负载中很普遍,在这种分区也反映在缓冲池加载的页面的内存位置上(NUMA本地性),但我离题了。
存储考虑事项。集群键的宽度在表的存储方面具有巨大的影响。首先,这个键占用了B树的每个非叶页的空间,因此一个大的键将占用更多的空间。其次,而且通常更为重要的是,聚簇键被每个非聚簇键用作查找键,因此每个非聚簇键都必须为每一行存储聚簇键的完整宽度。这就是为什么像varchar(256)和guids这样的大聚簇键不适合用作聚簇索引键的原因。
此外,选择键对聚簇索引碎片化也有影响,有时会严重影响性能。
这两个力量有时会相互对立,数据访问模式需要某个大的聚簇键,但这会导致存储问题。在这种情况下,当然需要平衡考虑,但没有魔法公式。您需要进行测量和测试才能找到最佳点。
那么我们应该怎么做呢? 始终首先考虑聚簇键,它也是形式为entity_id IDENTITY(1,1) NOT NULL
的主键。在适当的情况下,将其与表分开并进行组织(例如按日期进行分区)。