数据库中用于删除行的主键或常规字段

Question

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

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

Answer 1

在MySql上，当使用非主键列删除/更新行时，您可能会在多用户环境中面临奇怪的锁定行为
这里有一个例子——两个会话试图删除行（自动提交被禁用）

C:mysqlbin>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)

现在在会话1中，我们将使用主键删除第5行

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

然后在会话2中，我们也使用PK 删除第2行

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)

和会话2

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中的Delete命令"挂起"，大约一分钟后抛出错误：锁定等待超时
让我们尝试删除其他行：

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行，从逻辑上讲，锁应该只放在删除后的记录#5上，但正如您在这些示例中看到的，当不使用主键时，MySql会在整个表的所有行上加锁。因此，只使用主键删除行更安全（至少在MySql上是这样）。

Answer 2

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

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

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

Answer 3

我假设您的意思是这样的语句：

delete from table
    where column = value

当列是主键时，它会自动有一个唯一的索引（至少在我所知道的数据库中是这样）。这样可以快速查找要删除的记录。

另一个带索引的列的速度也差不多，因为它可以使用索引查找。

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

Answer 4

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

主键必须始终是指定更新、删除或其他操作的"更好"方式，这并不是根本原因。使用最能表达预期更新的属性，特别是要记住某些属性值可能会发生更改。通常选择主键是因为它是被认为不太可能更改的候选密钥，或者因为它是更新的"首选"标识符。

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