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

Question

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

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

Answer 1

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

您有一个表，该表具有一个PK和另一个唯一标识符，例如，email

• You使用email

• 读取一行，然后决定删除它，并在电子邮件更新为DELETE USER WHERE email='sample_email@gmail.com'

DELETE USER WHERE email='sample_email@gmail.com'的同时对行进行修改

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

Answer 2

在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)

现在在会话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)

一切正常-会话1删除了第5行，会话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命令“挂起”，大约一分钟后，它抛出一个错误: Lock wait timeout

让我们尝试删除其他行：

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 3

我假设你的意思是这样的陈述：

delete from table
    where column = value

当列是主键时，它会自动有一个惟一的索引(至少在我所知道的数据库中是这样)。这样可以更快地找到要删除的记录。

具有索引的另一列将几乎同样快，因为它可以使用索引查找。

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