第22章 伪键洁癖

确实有这么一种人，他们会为一系列数据的断档而抓狂。

一方面，bug_id 3这一行确实发生过一些事情。为什么这个查询不返回这个Bug？这条记录丢失了吗？那个Bug到底是什么？这个Bug是我们的一个重要客户提交的吗？我要为这条数据的丢失负责吗？

对于具有伪键洁癖这个反模式的那些人来说，他们的目的就是要解决这些麻烦的问题。这些人对数据的完整性问题负责，但通常来说，他们对数据库不够了解，或者不怎么相信数据库所生成的报表。

大多数人对于断档的第一反应就是想要填补其中的空缺。对此，可能会有两种做法。

你可能想要在插入新行时，通过遍历表，将找到的第一个未分配的主键编号分配给新行，来代替原来自动分配的伪主键机制。随着你不断地插入新行，断档就被填补起来了。

然而，为了找到第一个未被使用的值，你不得不执行一个不必要的自联合查询：

Neat-Freak/anti/lowest-value.sql
SELECT b1.bug_id + 1 FROM Bugs b1 LEFT OUTER JOIN Bugs AS b2 ON (b1.bug_id + 1 = b2.bug_id) WHERE b2.bug_id IS NULL ORDER BY b1.bug_id LIMIT 1;

在本书的前几章中，我们解释过在创建唯一标识的主键时，如果使用SELECT MAX(bug_id)+1这种查询语句，则会出现并发访问的问题。如果有两个程序同时想要找到最小的未使用值时，也会出现同样的问题，结果就是一个成功，另一个失败。况且，这个方法既低效，也容易导致问题。

在某些情况下，你可能急着想要让所有的主键变得连续，而等着新行来填补空缺不够快。你可能会考虑更新现有行的主键值，让其变得连续，从而消除断档。通常这样的做法是找到主键最大的行，然后用最小的未被使用的值来更新它。比如，你可以将4更新为3：

Neat-Freak/anti/renumber.sql
UPDATE Bugs SET bug_id = 3 WHERE bug_id = 4;

要完成这一步，你需要使用和前面一种方法中插入新行类似的方法。先找到一个未被使用的值，随后执行UPDATE语句来重新分配主键值。这两步都会引起并发访问的问题。而且，你需要重复执行很多次这样的操作，才能将较大的断档填满。

你必须同时更新所有引用了你重新分配了主键的行的子记录。如果你在定义外键时，使用了ON UPDATE CASCADE选项，这一步会变得很方便，但如果你没这么做，就必须先禁用约束，手动更新所有的子记录，然后恢复约束。这是一个费时费力且容易出错的操作，并且可能会影响到数据库的服务，正常人都会避免这么做的。

即使你完成了清理操作，“整洁”也是个“短命鬼”。当数据库生成一个新的伪键时，这个值是根据上次生成的值来计算的（即使上次生成的这条记录已经被删除了，也不会有任何影响），并不是按照现有记录中的最大值来计算的，这和一些数据库开发人员的假设相违背。假设你将最大的bug_id 4更新为最小的未被使用的值，然后消除了一个断档，下一次你使用默认的伪键生成器插入新行时，生成的伪键是5，这就会在4的地方产生一个新的断档。

Mitch Ratcliffe说：“计算机是人类历史中最容易让你犯更多错误的发明……除了手枪和龙舌兰之外。”1

本章最开始的故事描述了重编号主键所存在的一些隐患。如果别的外部系统依赖于数据库中的主键来定义数据，那么你的更新操作就会使那个系统中的引用失效。

重用主键并不是一个好主意，因为断档往往是由于一些合理的删除或者回滚数据所造成的。比如，假设一个account_id为789的用户由于发送带有人身攻击性的邮件而被封号。产品策略要求你删除这个账号，但如果你重用了主键，就会在某一个时间点将789分配给另一个用户。由于收件人可能在删除后的某一个时间点才打开这些邮件，他们还会投诉789这个账号的用户。尽管这个用户本身没有做错任何事情，但是他被分配了一个需要为此负责的编号。

别因为这些伪键看上去像是没用的而重新分配它们。

如果你发现团队成员问了如下几个问题，那么他们可能使用了“伪键洁癖”这个反模式。

没有理由改变伪键的值，因为它的值本身并没有什么重要的意义。如果这个主键列有实际的意义，那么这就是一个自然键，而不是伪键。改变自然键的值并不奇怪。

主键的值必须是唯一且非空的，因而你才能使用主键来唯一确定一行记录，但这是主键的唯一约束——它们不一定非得是连续值才能用来标记行。

大多数伪键返回的数字看起来就像行号一样，因为它们就是依次递增的（每一个新返回的值都比前一个值大1），但这只是由于伪键实现机制所造成的巧合而已。按照这样的方式生成主键值能比较方便地确保唯一性。

别把主键值和行号混为一谈。主键是用来标识表中记录的，而行号是用来标识查询结果集中记录的。查询结果集中的行号和主键没有一丁点关系，尤其是当你使用了JOIN、GROUP BY或者ORDER BY这些操作符的时候。

有很多使用行号的好理由，比如，返回一个查询结果集的子集。通常我们称之为分页，就像在网络搜索时的一页。要选择一个子集，你需要使用到实际的连续增长的行号，但和查询的形式无关。

SQL:2003定义了包括ROW_NUMBER()在内的一些窗口函数，返回一个查询结果集中一段连续的行。通常使用行号的作用是将查询结果限制在一个特定的范围内。

Neat-Freak/soln/row_number.sql
SELECT t1.* FROM (SELECT a.account_name, b.bug_id, b.summary, ROW_NUMBER() OVER (ORDER BY a.account_name, b.date_reported) AS rn FROM Accounts a JOIN Bugs b ON (a.account_id = b.reported_by)) AS t1 WHERE t1.rn BETWEEN 51 AND 100;

这些函数目前已经被很多主流数据库所支持，包括Oracle、Microsoft SQL Server 2005、IBM DB2、PostgreSQL 8.4和Apache Derby。

MySQL、SQLite、Firebird和Informix还不支持SQL:2003的窗口函数，但它们有一些独有的语法能用来处理本节所描述的问题。MySQL和SQLite提供了一个LIMIT子句，Firebird和Informix提供了使用FIRST和SKIP关键字的查询选项。

当然我们还可以生成随机伪键值，只要你不会重复使用任何数字。有些数据库提供全局唯一标识符（GUID）来达到这个目的。

GUID是一个128位的伪随机数（通常使用32个十六进制字符表示）。由于GUID的定义及其所要实现的目的，它被设计成具有唯一性，因此你可以用其作为伪键。

下面这个例子用的是Microsoft SQL Server 2005的语法：

Neat-Freak/soln/uniqueidentifier-sql2005.sql CREATE TABLE Bugs (
   bug_id UNIQUEIDENTIFIER DEFAULT NEWID(),  -- . . .
 );
  INSERT INTO Bugs (bug_id, summary) VALUES (DEFAULT, 'crashes when I save');

整数是不可再生资源吗？

和伪键洁癖反模式有关的误解是认为定量递增的伪键生成方式最终会用完整数集，未雨绸缪，不能浪费任何一个整数值。

乍看之下，这么说似乎有点道理。在数学中，整数是一个无限集合，但在数据库中，任何数据类型都只有有限数量的可选值，32位整型最多只能表示232个不同的值。每生成了一个新的主键，离最后一个值就更近一步了。

我们可以算一下：如果你每天24小时不停地插入新的数据，平均每秒产生1000行记录，用完32位整型所能表示的这些数字总共需要136年。

如果这还不能满足你的需求，那就使用64位整型。这样，就算你每秒产生一百万条记录，也需要584 542年才能消耗完所有的整型数字。

所以，要把所有的整型用完几乎是不可能的！

这会生成如下形式的一行记录：

GUID相比传统的伪键生成方法来说，至少有如下两个优势。

下面这几点是GUID所带来的不便。

虽然你已经清楚对伪键重新编号和一些相关方案可能会造成的问题，但你还有一个大问题没解决：怎么抵挡一个希望清理数据库中伪键断档的老板的请求？这是一个沟通方面的问题，而不是技术问题。无论如何，你需要让经理理解保护数据库中数据完整性的重要性。

将伪键当做行的唯一性标识，但它们不是行号。