结构化主键

# 结构化主键 来源：https://modern-sql.com/blog/2026-06/structured-primary-keys 有时我在优化客户查询时会碰壁。多年来，我意识到大多数情况可分为两类：(1) 基于云账单而非基础设施能力的不合理期望；(2) 主键设计。本文解释了主键如何将表置于围城之中，导致数据库模式分崩离析成互不连通的碎片，从而有效禁用 SQL 的基于集合的能力。我还提出了一种替代的主键设计方法，并讨论了其优缺点。我使用术语**结构化主键**来强调它与自然键和代理键的不同之处。 目录： 1. 客户 (https://modern-sql.com/blog/2026-06/structured-primary-keys#customers) 2. 订单 (https://modern-sql.com/blog/2026-06/structured-primary-keys#orders) 3. 订单行 (https://modern-sql.com/blog/2026-06/structured-primary-keys#order_lines) 4. 这难道不是反范式化吗？(https://modern-sql.com/blog/2026-06/structured-primary-keys#denormalization) 5. 空间效率 (https://modern-sql.com/blog/2026-06/structured-primary-keys#space-efficiency) 6. 按父序列 (https://modern-sql.com/blog/2026-06/structured-primary-keys#per-parent-sequences) 7. 循环一致性 (https://modern-sql.com/blog/2026-06/structured-primary-keys#cyclic-consistency) 8. 底线 (https://modern-sql.com/blog/2026-06/structured-primary-keys#recipe) ## 客户 我将用一个简单的网上商店的小模式来介绍本文。让我们从 `customers` 表的核心开始： ```sql CREATE TABLE customers ( name VARCHAR NOT NULL, email VARCHAR NOT NULL, /* 更多属性 */ /* TODO: 主键 */ ) ``` 我保持简短，因为本文并非关于业务属性。0 (https://modern-sql.com/blog/2026-06/structured-primary-keys#footnote-0) 展示的两列仅用于提出一个基本问题：这个表的好的主键是什么？ 乍一看，这是自然键与代理键的讨论。但由于这仅是本文的一个侧面，我会简要说明：如表所示，没有一列可以被考虑纳入主键。1 (https://modern-sql.com/blog/2026-06/structured-primary-keys#footnote-1) 原因是这两列都受外部定义的语义支配。这意味着我们不知道它们遵循的规则。特别是，我们不知道它们是否或何时是唯一的。当然，我们知道名字*不*是唯一的，所以可以排除 `name` 列。虽然不太明显，但电子邮件地址以及更普遍的任何由外部定义唯一性规则的内容也同样如此。 注意，我并没有使用“主键*值*应该不可变”这个论点。我也没有说这是一个不好的论点。当主键值改变时，将其应用于数据库可能很困难，因为它可能影响许多表中的许多行。2 (https://modern-sql.com/blog/2026-06/structured-primary-keys#footnote-2) 此外，旧的主键值可能已在数据库之外留下痕迹（API、日志文件、打印输出等），因此更改主键值会使这些痕迹变得毫无意义。虽然这两个论点都是正确的，但从关系理论的角度来看，它们都是不相关的。我更喜欢一个更强的论点，这是关系模型正常工作所严格要求的。 这个论点是，*唯一性规则的不可变性*是必需的。注意强调：*不可变的唯一性规则*是严格要求的，而不是不可变的值！我们以电子邮件地址为例。问题是电子邮件地址的唯一性语义取决于目标邮件服务器。特别是，“@”之前的部分理应是大小写敏感的，但通常不是 (https://en.wikipedia.org/wiki/Email_address#:~:text=Although,jane%2Esmith%2E)，子地址（“+”寻址）可能支持也可能不支持 (https://en.wikipedia.org/wiki/Email_address#Sub-addressing)，点可能被忽略 (https://support.google.com/mail/answer/10313?topic=14822#zippy=%2Cgetting-messages-sent-to-a-dotted-version-of-my-address)。好好想想。虽然 `[email protected]` 和 `[email protected]` 表示同一个邮箱，但在其他域名下它们可能指向不同的邮箱。 外部定义的唯一性语义是个问题。我们可能不完全理解它们，而且它们将来可能会改变。这种误解和更改会破坏主键的唯一性。因此，我们绝对不能在主键中使用这样的值。到目前为止，这与“始终使用代理键”的口头禅一致，但故事还在继续。因此，本文剩余部分基于以下 `customers` 表定义： ```sql CREATE TABLE customers ( name VARCHAR NOT NULL, email VARCHAR NOT NULL, /* 更多属性 */ id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, PRIMARY KEY (id) ) ``` ## 订单 商店中的第二个表是订单： ```sql CREATE TABLE orders ( customer_id BIGINT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (id), placed TIMESTAMP(6) NOT NULL, /* 更多属性 */ /* TODO: 主键 */ ) ``` 同样，我只展示相关部分。它从对客户的引用开始，包括外键定义。接着是 `placed` 列，用于存储订单下单时间。显然，这个表会有更多列，但它们对于重要问题无关紧要：这个表的好主键是什么？ 情况与之前略有不同。有人可能会认为 `(customer_id, placed)` 的组合值得考虑。我实际上同意这一点。虽然时间的唯一性语义 (https://en.wikipedia.org/wiki/Relativity_of_simultaneity) 也是外部定义的——由我们的宇宙以令人困惑的方式定义——但在网上商店的上下文中，我们可以认为时间戳足够被理解且不可变。过去事件的时间戳甚至具有不可变值的良好属性，并由宇宙本身支持。如果时间戳具有足够高的分辨率，我们也可以认为它是唯一的。结合 `customer_id` 和 `placed` 列的主键的结果是，单个客户不能在同一个微秒内下两个订单。或者换句话说：主键为商店的容量设定了每秒每个客户一百万订单的硬性限制。这个限制是否可以接受是个人决定，但在后续会变得无关紧要。如果我们接受这些限制，就没有强有力的论据反对使用 `(customer_id, placed)` 作为主键。甚至不可变值的优点也得以保留。反对的理由是时间戳在磁盘上占用相当大的空间 ↓ (https://modern-sql.com/blog/2026-06/structured-primary-keys#space-efficiency)，并且对人类来说处理起来很尴尬。想象一下在电话中询问：“您想取消哪个订单？” 我们也考虑一下替代方案。如果我们不接受 `placed` 列作为主键的一部分，那么该表就没有候选键。所以我们需要像之前一样创建一个候选键。再次，“始终使用代理键”的口头禅似乎适用——但这次它导致了一个有问题的主键。看看通常用于 `orders` 表的主键： ```sql CREATE TABLE orders ( customer_id BIGINT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (id), placed TIMESTAMP(6) NOT NULL, /* 更多属性 */ id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, PRIMARY KEY (id) ) ``` 你能看出问题吗？让我展示这个是如何将模式拆成碎片的…… ## 订单行 完成商店初始设计的表是订单中的商品： ```sql CREATE TABLE order_lines ( order_id BIGINT NOT NULL, FOREIGN KEY (order_id) REFERENCES orders (id), product_id INTEGER NOT NULL, qty INTEGER NOT NULL CHECK (qty > 0), /* 更多属性 */ /* TODO: 主键 */ ) ``` 这个表实际上就是购物车。每个放入订单的商品对应一行。该表有一个指向 `orders` 表的外键，但还没有主键。`order_lines` 的主键甚至与本讨论无关。 来自亚马逊的截图：您上次购买此商品是在 2026 年 6 月 1 日 要理解这个主键导致的问题，我们必须考虑像这样的查询：特定客户上次订购特定商品是什么时候？这是对应的查询： ```sql SELECT placed FROM order_lines ol JOIN orders o ON o.id = ol.order_id WHERE customer_id = ? AND product_id = ? ORDER BY placed DESC FETCH FIRST 1 ROW ONLY ``` 这是一个使用标准 SQL 语法的 top-n 查询。如果你不熟悉 `fetch first` 子句，它在这里的作用就像 `limit 1` 或 `select top 1`。由于查询需要来自两个不同表的列，需要连接也就不奇怪了——除非你记得 `(customer_id, placed)` 可以被用作 `orders` 表主键的想法。 如果 `orders` 表的主键是 `(customer_id, placed)`，那么 `order_lines` 表的定义也会改变： ```sql CREATE TABLE order_lines ( customer_id BIGINT NOT NULL, order_placed TIMESTAMP(6) NOT NULL, FOREIGN KEY (customer_id, order_placed) REFERENCES orders (customer_id, placed), product_id INTEGER NOT NULL, qty INTEGER NOT NULL CHECK (qty > 0), /* 更多属性 */ /* TODO: 主键 */ ) ``` 由于它有一个指向 `orders` 的外键，`orders` 的主键列必须出现在 `order_lines` 表中。因此，之前的查询不再需要连接： ```sql SELECT order_placed FROM order_lines ol WHERE customer_id = ? AND product_id = ? ORDER BY order_placed DESC FETCH FIRST 1 ROW ONLY ``` `customer_id` 以及订单下单时间现在与 `product_id` 一起直接可用。下图显示了第一个查询的响应时间作为基准（100%）。下面两个条显示了使用无连接查询的相对响应时间。对于其中一个引擎（E1），它快了 10 倍，对于另一个也快了 4 倍。但这并不是因为去掉了连接！ (id) 100% (customer_id, placed) 无连接 - E1 -90% (customer_id, placed) 无连接 - E2 -77% 接下来的查询仍然执行连接，以证明我的观点。它使用来自 `orders` 表的 `customer_id` 和 `placed` 列，就好像它们在 `order_lines` 中不可用一样。对 `order_lines` 表的唯一引用是连接条件和 `product_id` 搜索。特别地，`select` 子句以及 `order by` 子句都引用了 `orders` 表中的列。 ```sql SELECT o.placed FROM order_lines ol JOIN orders o ON o.customer_id = ol.customer_id AND o.placed = ol.order_placed WHERE o.customer_id = ? AND ol.product_id = ? ORDER BY o.placed DESC FETCH FIRST 1 ROW ONLY ``` 连接仅将响应时间增加了几个百分点。即使有连接，该查询的速度也比使用 `orders.id` 单列主键的模式快数倍。 (customer_id, placed) - 带连接 E1 -88% (customer_id, placed) - 带连接 E2 -70% 连接并不是大问题。主键构建的围墙才是。下面的图使其可见。首先是 `orders` 表使用单列主键的变体。箭头表示外键。它们的端点位于约束的相应列。 ``` customers 🔑 id name email dob orders 🔑 id customer_id placed order_lines 🔑 order_id 🔑 product_id qty ``` `orders` 表将 `customers` 和 `order_lines` 分隔开，因为“入站”和“出站”外键命中该表的不同列。每当查询需要 `customers` 和 `order_lines` 时，就需要通过 `orders` 表在 `customer_id` 和 `order_id` 之间进行逐行映射。 当 `orders` 表使用结构化主键 `(customer_id, placed)` 时，这幅图景发生了变化： ``` customers 🔑 id name email dob orders 🔑 customer_id 🔑 placed order_lines 🔑 customer_id 🔑 order_placed 🔑 product_id qty ``` 这个模式维护了属于同一客户的所有行之间的内聚性。这对于性能以及一致性都具有巨大价值。 ## 这难道不是反范式化吗？ 虽然反范式化可能带来相同的性能提升，但重要的是要理解结构化主键不会破坏范式化。下图展示了一个为了性能而破坏第二范式的模式。 ``` customers 🔑 id name email dob orders 🔑 id customer_id placed order_lines 🔑 order_id 🔑 product_id qty customer_id ``` 对于分析的查询，这个模式提供了与结构化主键相同的性能优势。但这个模式不保证 `order_lines` 表中的 `customer_id` 列指向与相应 `orders` 行相同的客户。迟早会出现不一致。许多人相信他们的应用程序可以防止这种情况，但墨菲定律 (https://en.wikipedia.org/wiki/Murphy%27s_law) 却相反。 ``` orders id customer_id 1 10 2 20 order_lines order_id customer_id 1 20 2 20 2 10 ``` 给定这些示例行，“谁下了订单 1？”在不同表中有不同答案。更糟糕的是，`order_lines` 表对于订单 2 自相矛盾。 当然，我希望同时拥有反范式化模式的性能以及范式化模式的正确性。这就是结构化主键所提供的。 ## 空间效率 我想现在是讨论结构化主键空间效率的好时机。可以预见，更多的表会导致主键具有更多列。这也需要更多的输入，更重要的是，增加了在 `on` 子句中出错的可能性。事实就是如此。目前我必须接受这个缺点——尽管我知道有些 SQL 方言可以沿着外键进行连接，而无需显式连接条件。4 (https://modern-sql.com/blog/2026-06/structured-primary-keys#footnote-4) 能够生成正确查询的模式感知工具可以在一定程度上缓解这个缺点。 然而，让我们看一下结构化主键的内存使用情况。通常，对于哪种主键设计需要更多内存，没有明确的答案。对于结构化主键，`orders` 表根本不需要 `id` 列。这通常也省去了一个索引。5 (https://modern-sql.com/blog/2026-06/structured-primary-keys#footnote-5) 另一方面，`order_lines` 表有更多的列。在使用堆表的系统 (https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key) 中，这要计算两次：一次用于堆表，一次用于支持主键的索引。哪个因素占主导——某些表的节省还是其他表的损失——也取决于数据分布。如果每个订单只有一个 `order_lines` 条目，那么节省可能超过损失。如上所述，没有一般性的答案。但存在一些普遍适用的方法，可以使结构化主键设计的可能性更大。 在商店模式中，`orders` 表的主键所占用的空间是关键因素，因为它的值被复制到所有具有指向它外键的表中。因此，这个主键需要在保持其结构化性质的同时进行空间优化。这是反对将 `placed` 列放入主键的强烈论据。毕竟，单个……