主页 / dba / 问题 / 160918
Accepted
SQLserving
Asked: 2017-01-13 12:58:11 +0800 CST

外键进程

  • 772

我试图从内部处理的角度了解外键的工作方式（我正在使用 SQLServer）。换句话说，添加外键会如何影响表？

假设我有两个表（PK 是非空表）：

表 A：PK Student_ID、 StudentName、StudentAddress
TableB：PK Books_ID、FK Student_ID、Books_IssueMonth

select Student_ID from TableB where Books_IssueMonth=January;

TableB当我在调用中运行 sql 语句时Student_ID，系统是否被告知首先在其中查找它TableA然后指向聚集索引 ( Books_ID) TableB？

会是双表查找吗？

还是在 TableA 中查找并在 TableB 中扫描？

database-design foreign-key

2 个回答

  1. Best Answer

    TableB 上的外键约束会影响三个级别（至少）。

    1. 当您INSERT INTO TableB查看 Student_ID 不为空的行时，将在 TableA 上查找 Student_ID 的值。如果它不存在，则插入将失败（该行将不会插入到数据库中），如果插入是较大事务的一部分，则事务本身将失败。涉及列UPDATE的 s 的行为方式类似（在 TableA 上查找 Student_ID 的新值）。TableBStudent_ID

    2. 当你UPDATE TableA SET Student_ID=<value>或DELETE FROM TableA任何一行时，该列的旧值Student_ID将在 TableB 上搜索。这会导致一些性能损失（特别是如果该列未在 TableB 上建立索引，但通常称为 a foreign key covering index）。如果找到该值，则：如果 FK 约束未指定对 UPDATE 或 DELETE 执行的操作，则 UPDATE 或 INSERT 将失败，因为它会违反约束。如果 FK 指定了对 UPDATE 和/或 DELETE 执行的操作，则将执行指定的操作。如果条件是，CASCADE这可能会递归地触发对引用表引用的表的更新/删除。

    3. 在 a 上SELECT，FK 约束根本不会有任何惩罚。它可以对数据库的规划者产生一些有益的影响。该条件TableB.Student_ID IS NULL OR TableB.Student_ID in (SELECT Student_ID FROM TableA)是计划者已知的事实，这可以用于某些优势（例如，如果 WHERE 子句中存在此类条件，则可以将其替换为常量 TRUE，并简化处理）。另一个优化是将 aTableB LEFT JOIN TableA USING (Student_ID)转换为 INNER JOIN，如果 Student_ID 已知为非空，因为规划器随后知道将在 TableA 上找到所有行。不同的规划者从这些知识中获得多少利润将在很大程度上取决于实施情况。

    从中选择行TableB（如果TableA在 FROM 子句中也未指定）将不会对Student_ID在TableA. 插入或更新数据时会强制执行外键约束，然后知道在选择数据时保留外键约束。

    注意：FK 约束可以指定当父Student_ID 更新或删除时要做什么。特定的语法可能会有细微的变化，但在类型上是ON {UPDATE|DELETE} {RESTRICT|NO ACTION|SET DEFAULT|SET NULL|CASCADE}. 检查它如何适用于PostgreSQL、MySQL、Oracle和SQL Server。

    • 4

  2. 对于您询问的特定 SELECT 语句，查询没有理由转到表 A，因为查询只提到表 B。至少对于我使用过的任何数据库产品都是如此。您没有说您使用的是哪种数据库产品。

    关于它将使用什么策略来查找 Books_IssueMonth = "January" 的所有行的问题，这将取决于表中存在的索引。如果有问题的列上有索引，查询优化器几乎肯定会使用该索引来定位少数磁盘访问中的相关行。如果相关列上没有索引，则查询必须对 TableB 进行全表扫描，如果 TableB 中有足够的行，这可能需要数百万次磁盘访问。

    当您询问连接 TableA 和 TableB 的选择时，问题变得更加有趣。你没有问这个。在这种情况下，您将需要 TableB 中的 StudentID 列，即使您没有将其声明为 FK。

    至于像 StudentID 这样的列用作外键，但没有用 FK 约束声明，这通常是一个坏主意，原因在 joanolo 的回答中概述。

    • 2

相关问题