打破规则： SQL Server 中的主键

引言

在我 IT 行业多年的工作中，我见过许多数据库设计，但令我惊讶的是，有多少系统完全忽略了良好设计的根本。我说的不是从学术角度来看的设计糟糕；这些不合格的设计根本无法正常工作，因为它们在性能、编程、可伸缩性和维护方面都是一场噩梦。我经常看到的一个问题是在表中实现了不恰当的键设计。在本文中，我将讨论针对恰当键设计的每一种犯罪以及所打破的每一条规则。为保护无辜者，姓名将被更改。

第一定律：主键是表中每行的唯一标识符。

罪行：因缺失而有罪

最糟糕的键犯罪是根本没有为主键添加键。令人惊讶的是，有时这竟然能奏效，但我几乎可以 100% 确定它最终会在某个时候失败。虽然可能效率不高，但可以在没有定义键的情况下获取您想要的特定记录。但在尝试更新记录时，情况就不是这样了。在 OLEDB 和 ADO 的时代，当数据库驱动程序无法获取关于 Recordset 的任何键信息时，它会尝试创建自己的键。它是如何做到的？它会生成一个 WHERE 子句，其中包含针对 Recordset 中每个字段的条件。这样做是假设表中没有重复记录。基本上，在这种条件下，您不知道受影响的数据是什么。可能存在有效的重复记录。单行更新可能会更改多条记录。也许自上次读取数据集以来某个值已更改，因此 WHERE 子句会过滤掉所有记录，而没有记录会被更新。这是您可以设计的最高效、最容易出错且充满 bug 的方法。幸运的是，ADO.NET 中已修复了此“功能”。没有定义键，您就无法更新数据。

第二定律：主键应该是不可变的。

罪行：因更改而有罪

这是针对唯一主键问题的一个不推荐的快速修复方法。如果您想读取或写入特定行，该行需要一个唯一标识符，以确保您指向您想要的记录。SQL Server 有一个方便的数据类型，似乎很适合此目的。 TIMESTAMP 数据类型提供了一个唯一、顺序的数字。这种数据类型实际上并不代表日期或时间。它只是一个 8 字节的数字，随着时间的推移而增大。为什么不将 TIMESTAMP 字段放在表末尾，将其定义为主键，然后就完成了？这种方法存在几个严重问题。

TIMESTAMP 数据类型旨在控制数据读写的并发性。每次更新任何字段时，此字段的值都会增加到当前服务器时间戳值。如果表包含 TIMESTAMP 字段，ADO.NET 驱动程序可以检查此值，以查看自上次读取数据以来底层数据是否已更改。然后，数据库驱动程序可以在正在进行更新时引发错误，或者您可以使用此信息来确定数据是否已过时，而无需获取所有记录数据。不幸的是，这种行为是您最不希望主键做的事情。

如果您将 SQL Server 用作关系数据库（我希望您是），那么您会定义具有主键和外键的关系。您可能还会针对父表键值设置级联更新。在较大的系统中，定义几十个关系并不少见。如果使用 TIMESTAMP 作为主键，每次对任何字段进行任何数据更新时，TIMESTAMP 都会更改。这将导致任何相关表级联键更新。服务器可能必须更新许多不同的表和索引以维护数据完整性。这是服务器必须执行的大量额外工作，这些工作毫无意义且不必要。不难看出，这种方法如何会在中等流量下让服务器不堪重负。

主键在创建时就不应更改；它们应该是 不可变的。这消除了级联更新问题，还防止了其他几个问题。通过将聚集索引指定为主键，记录会按照索引的顺序物理存储。检索和更新数据速度更快。如果更改了键，表就会发生碎片化，从而降低性能。使用不可变键对于高性能数据库来说是一个重要的考虑因素。

第三定律——键是为了计算机的利益。

罪行：因关联而有罪

我理解，在糟糕的旧时代，字节很昂贵，人们试图在尽可能少的空间中存储尽可能多的信息（因此有了 2000 年问题）。有时旧习惯很难打破。我们都知道所有表都需要键，那么为什么不使用表中存储的一些数据作为键呢？人们试图使用基于某些数据元素唯一性的自然键。我从未见过这种方法能正常工作。

确定唯一键非常困难。在某个时候，您可能认为您有一个不错的键候选。稍后，随着系统的发展，您可能会意识到数据更改导致您的键不再唯一。由于键基于真实数据，它很可能会随着时间的推移而更改，如果键不是不可变的，则会导致前面提到的问题。找到一个单个字段值是唯一的也可能是一个问题，因此经常使用多列键。这会带来很多麻烦，因为您必须提供所有值才能检索记录，否则就必须执行多个联接才能从另一个相关表中获取数据。

关于使用自然键的争论是它们更容易阅读并具有一定的含义。这正是自然键不应被使用的原因。我见过各种奇怪的方案来尝试生成键。一种典型的生成键值的方案可能如下所示：

023409808 

其中 0234 是分支号，098 是顺序事务号，08 是年份。这里的问题显而易见。如果分支或年份发生变化怎么办？如果公司发展壮大，拥有超过 999 个分支机构怎么办？如果两个事务同时插入，它们都会生成相同的事务号怎么办？它是存储为数字值还是字符数据类型？这种类型的方案简直是在惹麻烦。如果您曾经想过使用这种方法，请狠狠地打自己一巴掌，然后迅速忘记它。如果您需要某种类型的系统生成的唯一参考号，请将其放在另一个列中，但不要将其用作键。您将为您节省大量的当前和未来的麻烦。

键不是供人类阅读的，它们是为了计算机和开发者的利益。如果您想提供人类可读的数据，请创建显示所需信息的视图，但不要以这种方式存储数据。

一个有趣的附注：该方案每年最多可支持 9,989,001 笔交易，持续 99 年，总计 988,911,099 笔交易。一个任意的 int 标识键可以支持总共 2,147,483,647 笔交易。通过一个巧妙的技巧，从标识种子值 -2,147,483,648 开始并每次增量一，您可以将此值加倍。计算机并不在意键是否为负数，它只是数字。假设您选择将自然键方案存储在 int 字段中，使用任意键可以让您在相同的存储空间中获得超过 4 倍的交易量。

第四定律——创建高效的数字键。

罪行：因过度而有罪

了解您的数据。使您的键大小正确。在上面那个奇怪的例子中，如果键存储在字符数据类型中（因为您希望前导零显示出来），它将占用 18 字节（如果存储在 nvarchar 字段中）。如果您稍微小心一点，这可以存储在 9 字节的 varchar 字段中。一个 int 数据类型可以轻松地将其存储在 4 字节中。int 数据类型是 SQL 中性能最佳的类型，因此是键列的绝佳选择。整数变体，bigint 和 tinyint 也是不错的选择。选择最能满足您数据需求的大小。由于我们将不再使用编码方法或实际数据来派生键值，因此让计算机完成所有工作，并将其声明为自动增量以创建唯一、顺序值的标识列。SQL Server 会负责确保唯一值，因此您无需担心。避免使用 GUID（uniqueidentifier）数据类型，因为它们难以使用，并且仅在某些复制场景下才需要。

键设计定律总结

• 始终为主键添加所有表：没有键的表不是表。
• 始终使用不可变键：设置一次，不要更改。
• 让 SQL Server 完成工作：使用标识列。
• 按数据大小调整 int 键 datatype：了解您的数据，按需调整大小。

历史

• 2008 年 3 月 13 日：初始发布