本主题介绍了 Spanner 中的外键,以及如何在数据库解决方案中使用它们来强制执行引用完整性。
概览
外键允许您定义表之间的关系。Spanner 可确保这些关系的数据完整性。
假设您是电子商务企业的首席开发者。您正在设计一个数据库来处理客户订单。数据库需要存储每个订单、客户和产品的相关信息。图 1 展示了应用的基本数据库结构。
图 1:订单处理数据库示意图
您可以定义一个存储客户信息的 Customers
表、一个跟踪所下全部订单的 Orders
表以及一个存储客户可以订购的每件商品的相关信息的 Products
表。
图 1 还展示了映射到以下现实世界关系的表之间的关联:
客户下了订单
下了某商品的订单
您决定数据库应实施以下规则,以确保系统中的订单有效。
您无法为不存在的客户创建订单。
客户无法对您没有的商品下订单。
实施这些规则(即“限制条件”),即表示我们正在维护数据的参照完整性。当数据库保持参照完整性时,所有尝试添加无效数据的操作都将失败,这将导致数据之间出现无效链接或引用。参照完整性可防止用户出错。Spanner 通过外键强制执行引用完整性。
使用外键强制执行引用完整性
我们再次看一下订单处理示例,如图 2 所示,在设计中添加了更多细节。
图 2:采用外键的数据库架构示意图
现在设计为每个表中都显示了列名称和类型。Orders
表还定义了两个外键关系。FK_CustomerOrder
可确保 Orders
中所有行都具有有效的 CustomerID
。FK_ProductOrder
外键可确保 Orders
表中的所有 ProductID
值都有效。下表将这些限制条件与我们希望强制执行的实际规则一一对应。
外键名称 | 限制 | 实际说明 |
---|---|---|
FK_CustomerOrder | 确保 Orders 中所有行都具有有效 CustomerID |
有效客户下了订单 |
FK_ProductOrder | 确保 Orders 中所有行都具有有效 ProductID |
对有效商品下了订单 |
如果事务尝试插入或更新 Orders
表中在 Customers
和 Products
表中找不到 CustomerID
或 ProductID
的行,Spanner 会失败。此外,它也无法执行在 Customers
和 Products
表中尝试更新或删除行的事实,从而导致 Orders
表中的数据失效。如需详细了解 Spanner 如何验证限制条件,请参阅下面的事务限制条件验证。
定义外键
使用 DDL 在 Spanner 数据库中创建和移除外键。使用 CREATE
TABLE
语句将外键添加到新表中。同样,您可以使用 ALTER TABLE
语句向现有表添加外键或从现有表中移除外键。以下是使用外键创建新表的示例。
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
如需查看如何创建和管理外键的更多示例,请参阅创建和管理外键关系。
以下是 Spanner 中外键的特征列表。
定义外键的表是引用表,而外键列是引用列。
外键引用“被引用”表的的“被引用”列。
在上面的示例中,您可以为每个外键限制条件指定名称。如果您未指定名称,Spanner 会自动生成一个名称。可通过 Spanner 的 INFORMATION_SCHEMA 查询生成的名称。例如,限制条件名称的范围限定为架构以及表和索引的名称,并且在架构内必须具有唯一性。
引用列数和被引用列数必须相同。顺序很重要。第一个引用列引用第一个被引用列,第二个引用列引用第二个被引用列,以此类推。
引用列及其被引用的对应项目的类型必须相同。这些列还必须可编入索引。
无法使用
allow_commit_timestamp=true
选项在列上创建外键。数组列不受支持。
不支持 JSON 列。
外键可以引用同一表的列(“自引用”外键)。例如,一个员工表具有一个引用该表的 EmployeeId 列的 ManagerId 列。
外键还可以在表之间形成循环关系,其中两个表直接或间接地相互引用。由于必须先有被引用表,然后才能创建外键,因此必须使用
ALTER TABLE
语句添加至少其中一个外键。被引用键必须是唯一的。如果外键的引用列与引用表的主键列匹配,则 Spanner 使用引用表的
PRIMARY KEY
。如果 Spanner 无法使用引用表的主键,则会对引用的列创建一个UNIQUE NULL_FILTERED INDEX
。Spanner 或许也可以使用引用表的主键,但这种情况不太常见。否则,Spanner 会在引用列上创建一个
NULL_FILTERED INDEX
。外键不使用您创建的二级索引,而是创建自己的支持性索引。这些支持性索引可用于查询评估,包括明确的 force_index 指令。可以通过 Spanner 的 INFORMATION_SCHEMA 查询后备索引的名称。如需了解详情,请参阅支持性索引。
外键操作
通过外键操作,您可以控制在删除或更新受限制列时它所引用的列。Spanner 支持使用 ON DELETE CASCADE 操作。使用 ON DELETE CASCADE 外键操作删除包含所引用外键的行时,引用该键的所有行也会在同一事务中删除。
使用 DDL 创建数据库时,您可以添加带有操作的外键。使用 CREATE TABLE
语句将带有操作的外键添加到新表。同样,您可以使用 ALTER TABLE
语句向现有表添加外键操作或移除外键操作。以下示例演示了如何使用外键操作创建新表。
GoogleSQL
CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);
CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);
PostgreSQL
CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);
CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);
以下是 Spanner 中外键操作的特征列表。
外键操作为 ON DELETE CASCADE 或 ON DELETE NO ACTION。
您可以查询 INFORMATION_SCHEMA 以查找具有操作的外键约束条件。
不支持对现有外键限制条件添加外键操作。您需要添加带有操作的新外键约束。
长时间运行架构更改
将外键添加到现有表或使用外键创建新表可能导致产生长时间运行的操作。对于新表,只有在长时间运行的操作完成之后,才能写入该表。
对于带有外键的新表,Spanner 需要针对每个外键根据需要回填引用的索引。
对于具有外键的现有表,Spanner 需要根据需要回填引用的索引。此外,Spanner 还会验证表中的现有数据,以确保其符合外键的引用完整性限制条件。如果任何数据无效,架构更改将失败。
不支持对现有限制条件添加外键操作。我们建议您执行以下操作:
- 添加带有操作的新约束条件。
- 丢弃旧约束条件,不执行任何操作。
这样可以避免 Long-running Alter Constraint Operation
问题。使用 ON DELETE CASCADE 操作创建新外键后,这两个限制条件的实际结果都是 DELETE CASCADE。如果其他外键限制条件未使用外键支持索引,则丢弃限制条件可能会导致丢弃这些索引。之后,如果用户在操作中添加相同的外键限制条件,则可能需要长时间运行的操作,包括回填索引、验证唯一索引限制条件以及验证外键参照限制条件。
如果由于违反 UNIQUE
限制条件而无法创建被引用索引,则上述架构更改都可能会失败。
您可以查询 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE 以检查外键创建状态。
事务的限制条件验证
Spanner 会在提交事务时或写入的效果对事务中的后续操作可见时验证外键约束。
插入到引用列中的值将与被引用表和被引用列的值进行匹配。系统不会检查具有 NULL
引用值的行,也就是说,您可以将它们添加到引用表中。
在尝试通过 DML 语句或 API 更新数据时,Spanner 会验证所有适用的外键参照限制条件。如果有任何限制条件无效,则所有待更改项都将回滚。
每个 DML 语句完成之后立即进行验证。例如,您必须在插入引用行之前先插入被引用的行。使用 Mutation API 时,Mutation 会进行缓冲,直到提交事务为止。 外键验证将推迟到提交事务之后进行。在这种情况下,可以先插入引用行。
将评估每个事务的修改是否会影响外键限制条件。此类评估可能需要向服务器发送额外请求。 支持性索引还需要额外的处理时间来评估事务修改并维护索引。此外,每个索引都需要额外的存储空间。
支持性索引
外键不使用用户创建的索引。它们会创建自己的支持性索引。
Spanner 最多可以为每个外键创建两个二级后备索引,一个用于引用列,另一个用于引用列。但是,外键通常会引用被引用表的主键,因此通常不需要被引用表上的第二个索引。
引用的表的支持性索引是 UNIQUE NULL_FILTERED
索引;如果任何现有数据违反了索引的唯一性限制条件,则无法创建外键。引用表的支持性索引为 NULL_FILTERED
。
如果两个或多个外键需要相同的支持性索引,Spanner 将为所有外键创建一个索引。丢弃使用该索引的外键,将同时丢弃支持性索引。用户无法更改或丢弃支持性索引。
Spanner 使用每个数据库的信息架构来存储有关后备索引的元数据。INFORMATION_SCHEMA.INDEXES
中 SPANNER_IS_MANAGED
值为 true
的行描述了后备索引。
在直接调用信息架构的 SQL 查询之外,Google Cloud 控制台不会显示有关数据库后备索引的任何信息。
长时间运行的删除级联操作
当您从引用的表中删除行时,Spanner 需要删除引用表中引用已删除行的所有行。这可能会导致出现级联效应,即一次删除操作可能导致数以千计的其他删除操作。如果向表添加具有删除级联操作的外键约束条件,或者创建具有删除级联操作的外键约束条件的表,则可能会降低删除操作速度。
超出外键删除级联的变更限制
使用外键删除级联删除大量记录可能会影响性能。这是因为删除每条记录都会触发删除与其相关的所有使用外键的记录。如果事务中的变更数量超过 80,000,则事务将失败。
如果您需要使用外键删除级联删除大量记录,则应先明确删除子表中的行,然后再从父表中删除该行。这样可以防止事务因变更限制而失败。
外键和表交错的比较
Spanner 的表交错非常适合许多父子关系,其中子表的主键包含父表的主键列。子行及其父行共用位置可以显著提高性能。
外键是一种较通用的父子解决方案,并解决了其他用例。外键不限于主键列,而表可以具有多个外键关系,二者在某些关系中可以作为父键,而在其他关系中可以作为子键。但是,外键关系不会隐含表在存储层中共处同一位置的关系。
我们来看一个示例,使用之前在本主题中讨论过的订单处理架构。回想一下,我们的 Orders
表的定义如下:
图 3:采用外键的数据库架构示意图
图 3 中的设计存在一些限制。例如,每个订单目前只能包含一个订购商品。
假设我们的客户希望每笔订单能够订购多件商品。我们可以通过引入 OrderItems
表来增强设计,该表包含客户订购的每件商品。我们可以引入另一个外键来表示 Orders
和 OrderItems
之间新的一对多关系。但是,我们也知道,对各个订单及其各自的订购商品运行查询需要很长时间。此类数据的共用位置可以提高性能,因此我们将使用 Spanner 的表交错功能创建父子关系。
下面是我们对 OrderItems
表的定义(与 Orders
交错)。
GoogleSQL
CREATE TABLE OrderItems (
OrderID INT64 NOT NULL,
ProductID INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
) PRIMARY KEY (OrderID, ProductID),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
PostgreSQL
CREATE TABLE OrderItems (
OrderID BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
PRIMARY KEY (OrderID, ProductID)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
图 4 直观展示了更新后的数据库架构,因为引入了这个新表 OrderItems
与 Orders
交错。在这里,您还可以看到这两个表之间的一对多关系。
图 4:添加交错的 OrderItems 表
在此配置中,我们可以在每个订单中拥有多个 OrderItems
条目,每个订单的 OrderItems
条目是交错的,因此与这些订单位于同一位置。通过这种方式进行物理交错 Orders
和 OrderItems
可以提高性能,有效地预连接表并允许您一起访问相关的行,同时最小化磁盘访问。例如,Spanner 可以在本地通过主键执行联接,从而最大限度地减少磁盘访问和网络流量。
如果事务中的变更数量超过 80,000,则事务将失败。这种大型级联删除适用于具有“父级交错”关系的表,但不适用于具有外键关系的表。如果您具有外键关系并且需要删除大量行,则应先从子表中明确删除这些行。
如果您的某个用户表与另一个表之间存在外键关系,并且从引用的表删除一行会触发删除数百万行的操作,那么您应该设计具有“交错在父项”的删除级联操作。
对照表
下表汇总了外键与表交错的比较方式。您可以根据这些信息来确定什么方式适合您的设计。
父子关系类型 | 表交错 | 外键 |
---|---|---|
可以使用主键 | 是 | 是 |
可以使用非主键列 | 否 | 是 |
支持的父数据数量 | 0 .. 1 | 0 .. N |
同时存储父数据和子数据 | 是 | 否 |
支持级联删除 | 是 | 是 |
Null 匹配模式 | 如果所有引用值与被引用值没有区别,则传递。 Null 值与 Null 值没有区别,但 Null 值与非 Null 值有区别。 |
如果有任何引用值为 Null,则传递。 如果所有引用值均为非 Null 值,且被引用表中有值等于引用值的行,则传递。 如果找不到匹配的行,则失败。 |
强制执行时间 | 使用 Mutation API 时的操作。 使用 DML 时的每个语句。 |
使用变更 API 时的每项事务。 使用 DML 时的每个语句。 |
可以轻松移除 | 不可以。除非删除整个子表,否则一旦创建表交错就无法移除。 | 是 |