mysql - UNIQUE 约束与 INSERT 之前的检查

Question

我有一个 SQL 服务器表 RealEstate，其中包含 Id、Property、Property_Value 列。该表大约有 5-1000 万行，将来可能会增加更多。仅当此表中不存在 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标签。

Answer 1

我认为在大多数情况下，这两者之间的差异将足够小，以至于选择应该主要通过选择最终对第一次查看代码的人来说最容易理解的实现来驱动。

不过，我认为异常处理有几个小的优点:

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

• 如果您的代码不是存储过程并且必须通过网络与数据库交互(即应用程序和数据库不在同一个盒子上)，那么您希望避免有两个单独的网络调用(一个用于检查，另一个用于插入)并通过异常处理提供了一种通过单个网络调用来处理整个事情的简单方法。现在，有很多方法可以执行“检查然后插入”方法，同时仍然避免第二次网络调用，但简单地捕获异常可能是最简单的方法。

另一方面，异常处理需要唯一约束(实际上是唯一索引)，这带来了性能折衷:

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

我还要注意，如果您处于实际想要做的是“更新其他插入”的情况(即，如果具有唯一值的记录已经存在，那么您想要更新该记录，否则您插入一条新记录)，那么您真正想要使用的是您的特定数据库的 UPSERT 方法，如果它有的话。对于 SQL Server 和 Oracle，这将是一个 MERGE 语句。