唯一约束 vs 插入前检查

Question

我有一个 SQL 服务器表格 RealEstate，它包含 Id、Property 和 Property_Value 列。该表格大约有 5-10 百万行数据量，未来可能还会增加。我想要插入一行数据，前提是该行数据的 Id、Property 和 Property_Value 的组合在该表格中不存在。

例如表格：

1,Rooms,5
1,Bath,2
1,Address,New York
2,Rooms,2
2,Bath,1
2,Address,Miami

插入2,Address,Miami不应该被允许。但是，2,Price,2billion可以。我很好奇哪种方法是最好的，以及为什么。检查的两种方式是 -

1. 在应用程序级别进行检查--应用程序在插入行之前应该检查该行是否存在。
2. 在数据库级别进行检查--对所有3列设置唯一约束，并让数据库而不是人员/应用程序进行检查。

是否存在某种情况，其中一种方法比另一种更好？

谢谢。

PS：我知道已经有一个类似的问题了，但它并没有回答我的问题 - Unique constraint vs pre checking。此外，我认为UNIQUE适用于所有数据库，因此我不认为我应该删除mysql和oracle标签。

- Steam

---

永远选择第二个选项。 - Wagner DosAnjos

第二种方法更好，因为这是两种方法中唯一保证可行的方法。如果你使用“先检查后插入”的方法，存在竞态条件的可能性。在检查和插入之间，无论时间有多短，就会有时间差，在这个时间差中，另一个线程可能已经插入了记录。我认为避免这种情况的唯一方法是使用 MERGE 和 HOLDLOCK（特定于 SQL Server）。即使如此，也没有理由不设置约束。你可以同时执行这两种方法。 - GarethD

@GarethD - 只有当多个应用程序访问/修改数据库时，竞态条件才是一个问题。在我的情况下，可能会有一个或多个，但我不确定。您能否将您的评论作为答案，并加上一些更多的解释或代码？谢谢。 - Steam

没有太多可以说的了，如果你想要一个不特定于DBMS的答案，那么我不能再添加更多了。无论如何进行插入操作，请使用约束条件，这样就永远不会破坏表的完整性。如果您想要更多关于避免SQL Server（2008+）中竞争条件的信息，那么请阅读有关使用MERGE和HOLDLOCK的此文章。 - GarethD

在应用程序级别使用并发事务是无法做到的。如果在数据库级别没有进行防止重复值的操作，两个并发事务仍然可以插入重复的值。在应用程序中进行检查并不比数据库为唯一约束执行的检查昂贵，但在多用户环境中它将不正确。 - user330315

Answer 1

我认为在大多数情况下，这两种方法的区别都不会太大，所以选择哪种实现方式应该主要考虑哪种更容易让第一次看代码的人理解。

但是，我认为异常处理有几个小优点：

• 异常处理避免了潜在的竞态条件。如果另一个进程在检查和插入之间插入了记录，则“检查，然后插入”方法可能会失败。因此，即使您正在进行“检查然后插入”，您仍需要在插入时使用异常处理，如果您已经执行异常处理，则最好取消初始检查。

• 如果您的代码不是存储过程，并且必须通过网络与数据库交互（即应用程序和数据库不在同一台服务器上），则希望避免进行两个单独的网络调用（检查和插入）。使用异常处理提供了一种处理整个过程的简单方法。现在，有很多方法可以在避免第二个网络调用的同时执行“检查然后插入”的方法，但简单地捕获异常可能是最简单的方法。

另一方面，异常处理需要一个唯一的约束（实际上是唯一索引），这会带来性能上的折衷：

• 在非常大的表上创建唯一约束将会很慢，并且每次插入到该表中都会导致性能损失。在真正大型的数据库上，您还必须预算用于执行约束的唯一索引所需的额外磁盘空间。
• 另一方面，如果您的查询可以利用该索引，则从表中选择可能会更快。

我还要注意的是，如果您处于需要执行“更新或插入”的情况下（即，如果具有唯一值的记录已经存在，则要更新该记录，否则要插入新记录），则您实际上应该使用特定数据库的UPSERT方法（如果有的话）。对于SQL Server和Oracle，这将是MERGE语句。

- ivanatpr

---

谢谢。我不确定使用“预检查”的UNIQUE和INSERT是否会有相同的性能惩罚。它们之间会有区别吗？如果有，差异会有多大？ - Steam

在某些情况下（使用SAN进行存储的集群SQL Server），我的经验表明检查-插入性能可能会显着变差。多次跳跃似乎会影响重复调用的整体时间。我们有一个喜欢聊天的应用程序，对于一些使用该配置的客户端，应用程序中某些部分的墙时运行时间显着变差。（铜线与光纤、慢或配置不良的NIC，谁知道呢） - DaveE

我不认为这些差异是“微小”的。实际上，这种差异非常大：如果你不对表进行完全排他性的写入锁定，你就无法保证应用程序中的唯一性。这意味着，应用程序内部的解决方案要么是不可扩展的，要么根本无法工作。这两个选项都不是很好。 - user330315

@DaveE 我们有独立于数据库服务器的应用服务器（也在SAN上，并且非常适合处理多个并发数据库请求），我的经验与您相似。如果您正在处理一个慢速应用程序，每次页面加载都会执行多个单独的数据库查询，那么您应该尝试的第一件事情是改变它，使其并行执行所有操作或作为单个jdbc批处理执行。当您串行执行所有操作时，网络跳跃的单个开销真的开始累加。 - ivanatpr

唯一列出现“重复条目...”错误，如何避免无需预检查？ - Hector

Answer 2

Depending on the feasibility of #1 (performing a lookup) at a reasonable cost, I would suggest implementing both unique and primary keys. This recommendation is based on my experience with Oracle, which is the database system that I am most familiar with.

Here are the reasons why:

- Unique/primary keys should be an integral part of your data model design. There is no good reason not to implement them, even if maintaining the unique index affects performance due to a large amount of data. If this is the case, consider partitioning or archiving the data away from your OLTP work. - The more constraints you have, the safer your data is against application logic errors. - By checking if a row exists first, you can easily extract other relevant information from that row to use as part of an error message or adjust the application logic accordingly to handle duplication. - In Oracle, rolling back DML statements is relatively expensive because Oracle expects changes to succeed by default and commit them.

- Ben

---

"取决于#1（进行查找）的成本是否合理" - 我该如何判断成本是否合理？我的表格有500万到1000万行，并且每月增长约10K左右。考虑到行数巨大，我不确定是否应该同时进行预插入检查和唯一约束。 - Steam

两种检查的成本可能相似，因为它们从根本上来说是在执行同样的操作。 - Ben

谢谢Ben。有没有一种比较这两种方法成本的方式？如果我们漏掉了什么，或者关系型数据库管理系统有什么奇怪的地方，那么这两种方法的成本可能会不同。 - Steam

我认为唯一真正比较的方法是运行基准测试并计时结果。可以估算SELECT语句的成本 - Oracle提供了Explain Plan功能来实现此目的，但我不确定它是否会包括检查唯一索引的成本。它可能会。 - Ben

SQL Server的版本解释计划包括索引查找的相关代价，并且会明确告诉您是否涉及表扫描。 - DaveE

Answer 3

这并没有直接回答问题，但我认为将它发布在这里可能会有所帮助，因为它比维基百科更好，并且链接可能会在某一天失效。

链接 - http://www.celticwolf.com/blog/2010/04/27/what-is-a-race-condition/ 维基百科对竞争条件有很好的描述，但如果您不了解编程的基础知识，就很难理解。我将尝试用较少的技术术语来解释它，使用上述生成标识符的示例。我还将使用人类活动的比喻来传达思想。

竞争条件是指两个或多个程序（或单个程序的独立部分）同时尝试获取某些资源，导致答案不正确或冲突。这种资源可以是信息，例如下一个可用的预约时间，也可以是对某些东西的独占访问权限，例如电子表格。如果您曾经使用Microsoft Excel在共享驱动器上编辑文档，您可能会被告知Excel已经有其他人正在编辑该电子表格。此错误消息是Excel处理潜在的竞争条件的方式，可以优雅地防止错误。

一个常见的编程任务是识别某种类型的下一个可用值，然后进行分配。这种技术用于发票号码、学生ID等。这是一个旧问题，在以前已经得到了解决。最常见的解决方案之一是允许存储数据的数据库生成编号。还有其他解决方案，它们都有各自的优缺点。

不幸的是，对于这个领域无知或者只是糟糕的程序员通常会试图自己解决这个问题。聪明的程序员很快就会发现这个问题比看起来更加复杂，并寻找现有的解决方案。而糟糕的程序员则从未看到这个问题，或者一旦看到，就坚持使他们不可行的解决方案变得更加复杂，而不修复错误。让我们以学生ID为例。新手程序员说：“要知道下一个学生编号应该是什么，我们只需要获取上一个学生编号并递增它。”在幕后发生的事情如下：

1. 贝蒂是招生办公室的一名行政助理，她启动了学生管理程序。请注意，这实际上只是运行在她个人电脑上的程序副本。它通过学校的网络与数据库服务器通信，但无法与其他运行在不同电脑上的程序副本通信。
2. 贝蒂为鲍勃·史密斯创建了一个新的学生记录，并输入了所有信息。
3. 当贝蒂正在输入数据时，另一位行政助理乔治在他的电脑上启动了学生管理程序，并开始为吉娜·维尔德创建记录。
4. 乔治打字速度更快，所以他和贝蒂同时完成了记录的创建。他们同时点击“保存”按钮。
5. 贝蒂的程序连接到数据库服务器并获取正在使用的最高学生编号5012。
6. 同时，乔治的程序也得到了相同的答案。
7. 两个程序决定将他们要保存的记录的新学生ID设置为5013。然后将该信息添加到记录中，最后保存在数据库中。
8. 现在，鲍勃·史密斯（贝蒂的学生）和吉娜·维尔德（乔治的学生）具有相同的学生ID。

这个学生ID将与各种其他记录相关联，从成绩到餐厅的就餐卡。最终，这个问题将浮出水面，有人将不得不花费大量时间为其中一个分配新的ID并整理混乱的记录。

当我向人们描述这个问题时，通常的反应是“但在实践中会经常发生吗？从未吧？”。错了。首先，当您的员工进行数据输入时，它通常是在相对短的时间内由所有人完成的。这增加了重叠的机会。如果涉及的应用程序是向公众开放的Web应用程序，则两个人同时点击“保存”按钮的机会甚至更高。我最近在生产系统中看到了这种情况。这是一个公共测试版的Web应用程序。使用率相当低，每天只有几个人注册。尽管如此，在几个月的时间里，有六对人管理获得了相同的ID。如果你想知道，不，我和我的团队没有编写那个代码。然而，我们很惊讶，这个问题发生了多少次。事后看来，我们不应该感到惊讶。这真的是墨菲定律的一个简单应用。

如何避免这个问题？最简单的方法是使用已经经过充分测试的解决方案来解决这个问题。所有主要的数据库（MS SQL Server、Oracle、MySQL、PostgreSQL等）都有一种递增数字而不创建重复项的方法。MS SQL服务器称其为“identity”列，而MySQL称其为“自动编号”列，但功能相同。每当插入一个新记录时，一个新的标识符会自动创建并保证唯一性。这将改变上述情况如下：

1. 贝蒂是招生办公室的一名行政助理，她启动了学生管理程序。请注意，这实际上只是运行在她个人电脑上的程序副本。它通过学校网络与数据库服务器通信，但无法与其他运行在其他个人电脑上的程序副本通信。
2. 贝蒂为鲍勃·史密斯创建了一个新的学生记录，并输入了所有信息。
3. 当贝蒂正在进行数据录入时，另一位行政助理乔治在他的个人电脑上启动了学生管理程序，并开始为吉娜·维尔德创建记录。
4. 由于乔治打字更快，所以他和贝蒂同时完成了记录创建。他们同时点击了“保存”按钮。
5. 贝蒂的程序连接到数据库服务器并将要保存的记录交给它。
6. 与此同时，乔治的程序也将另一条记录交给了数据库服务器。
7. 数据库服务器将两条记录放入队列中，并逐个保存它们，为它们分配下一个可用编号。
8. 现在，鲍勃·史密斯（贝蒂的学生）获得ID 5013，而吉娜·维尔德（乔治的学生）获得ID 5014。

使用这种解决方案，不会出现重复的问题。每个数据库服务器执行此操作的代码已经在制造商和用户的多年测试中得到了反复验证。全球数百万应用程序依赖它，并继续每天进行压力测试。有人能对他们自己开发的解决方案说同样的话吗？

至少有一种经过充分测试的方法可以在软件中创建标识符，而不是在数据库中创建：uuid（通用唯一标识符）。然而，uuid采用xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx的形式，其中“x”代表十六进制数字（0-9和a-f）。你想将其用于发票号码、学生ID或其他公众可见的标识符吗？可能不是。

总之，当两个程序或程序的两个独立部分尝试同时访问某些信息或访问资源时，就会出现竞争条件，导致错误，无论是错误计算、重复标识符还是对资源的冲突访问。比我在这里介绍的竞争条件类型还有更多，它们影响着软件和硬件的许多其他领域。

- Steam

Answer 4

你的问题描述正是为什么主键可以是复合的，例如由多个字段组成。这样，数据库将为您处理唯一性，您无需关心它。

在你的情况下，表定义可能类似于以下内容：

 CREATE TABLE `real_estate` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `property` varchar(255) DEFAULT NULL,
   `property_value` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `index_id_property_property_value` (`id`, `property`, `property_value`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

- mjuarez

---

您的独特键（unique key）没有太大意义，因为“id”本身就是主键，且定义为唯一。由于“id”是唯一的，任何“id”、“property”、“property_value”的组合也将是唯一的。 - GarethD