数据库中用于删除行的主键或普通字段？

Question

我想删除数据库中的一行，我有两个选项：第一个是使用普通列删除该行，第二个是主键？

我知道主键更好，但为什么？

- RedHood148

---

因为它是唯一标识您行的主键。如果您指定另一列，则存在以下风险：(a) 删除多个行，(b) 通常需要执行表扫描以通过任意列值查找行，或者需要首先在辅助索引中查找该值，然后根据主键值查找行并删除它。 - marc_s

Answer 1

在MySql中，如果使用非主键列删除/更新行，你可能会在多用户环境下遇到奇怪的锁定行为。以下是一个例子 - 两个会话正在尝试删除行（自动提交被禁用）。

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

mysql> create table test(
    ->   id int primary key,
    ->   val int
    -> );
Query OK, 0 rows affected (0.02 sec)

......

mysql> select * from test;
+----+------+
| id | val  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
+----+------+
6 rows in set (0.00 sec)

现在我们在第一讲中将使用主键删除第五行

mysql> delete from test where id = 5;
Query OK, 1 row affected (0.00 sec)

然后在第二个会话中，我们也使用主键删除了第二行

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id = 2;
Query OK, 1 row affected (0.00 sec)

一切看起来都很正常——第5行被会话1删除，第2行被会话2删除。


现在让我们看看当我们尝试使用非主键删除行时会发生什么：
会话1

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where val = 5;
Query OK, 1 row affected (0.00 sec)

以及第二个会话

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where val = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

会话2中的删除命令“挂起”，大约一分钟后会抛出一个错误：锁等待超时。让我们尝试删除其他行：

mysql> delete from test where val = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from test where val = 6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

会话1只删除了第五行，逻辑上仅应在被删除的第5条记录上放置锁，但正如您在这些示例中看到的，当不使用主键时，MySQL会在整个表的所有行上放置锁。因此，在MySQL上仅使用主键删除行更为安全。

- krokodilko

Answer 2

主键更好，因为您可以确定要删除的行：虽然从技术上讲，您可以更新主键列，但这不是一个正常的做法。然而，其他列是可变的，这可能会导致像这样的情况：

• 您有一个带有PK和另一个唯一标识符（比如email）的表
• 您读取了一个电子邮件为[e-mail]的行，并决定将其删除
• 在此期间，该行被同时修改，电子邮件已更新为[e-mail]
• 您执行DELETE USER WHERE email='[e-mail]'

DELETE命令没有删除任何内容，因为在您运行命令之前，电子邮件已经被更改。由于PK不应该改变，在正常情况下，这种情况是不可能发生的。当然，您的代码可以检测到删除未发生，重新读取并重新发出命令，但与使用主键相比，这需要大量的工作。

- Sergey Kalinichenko

---

这似乎是一个很好的例子，说明在所有情况下都不应该使用主键。在您的示例中，如果意图是删除具有指定电子邮件地址的用户，并且该电子邮件地址发生更改，则正确且最安全的操作可能是不删除行。基于用户主键的相同操作在某个时间点上可能没有产生相同的效果。 - nvogel

@sqlvogel 删除几乎普遍针对特定实体，而不是具有特定属性的实体。我的观点是，在主键上进行删除可以让您针对特定实体进行操作，而在非主键列上进行删除则无法像主键那样可靠地针对特定实体进行操作。 - Sergey Kalinichenko

在关系型数据库中，实体仅可通过其（键）属性进行识别。实体能够更可靠地通过“主”键属性而非其他键属性进行识别的假设是一种推测。在实践中，这取决于属性的选择以及设计师和用户的意图。 - nvogel

@sqlvogel 设计师添加主键的目的是为了通过某些非有意义的属性来识别实体（我不是在谈论当您可以使用一个或多个“有意义”的属性组合作为您的PK时的角落情况），这将“猜想”转化为“自我实现的预言”。多个高度成功的ORM解决方案仅允许通过PK进行删除，这一事实也大力支持了这一“猜想”。理论上，设计师的选择并不受限制；但在实践中，当选择行之有效时，设计师往往会做出相同的选择。 - Sergey Kalinichenko

所以你的回答是基于一个假设，关于OP选择什么作为他的主键，以及他的删除的预期影响是什么。我同意。我只是更喜欢将这些事情公开，而不是作为未声明的假设。 - nvogel

Answer 3

我假设你指的是这样一个语句：

。

delete from table
    where column = value

当列为主键时，在我所知道的数据库中，它会自动拥有唯一索引。这使得查找要删除的记录变得更快。

另一个带有索引的列几乎与主键一样快，因为它可以使用索引查找。

没有索引的列将会慢得多，因为查询必须进行全表扫描。

- Gordon Linoff

Answer 4

行通过超键唯一标识，包括候选键。主键是一个候选键，但不一定是唯一的。

没有根本性的原因要求主键必须始终是指定更新、删除或其他操作的“更好”方式。使用最能表达预期更新的属性，特别是考虑到某些属性值可能会发生变化。通常情况下，选择主键是因为它是一个被认为不太可能改变的候选键，或者是因为它是更新的“首选”标识符。

假设一个表有两个键：j和k，其中k被指定为主键。如果用户实际上想要基于j的值执行更新：DELETE ... WHERE j=123;那么根据有效的事务隔离级别和任何一个属性的稳定性，该更新可能会表达与某个时间点上基于相应的k值的类似更新完全不同的意图。这对任何一个属性都是正确的。因此，如果您关心更改键值的影响，则应考虑哪种键选择最能表达用户的预期更新。假设候选键值的更改很少发生，那么为所有更新使用指定的主键通常是“默认”假设，因为始终使用相同的键使编码更简单。

- nvogel