数据库 Auto Increment 的实际问题

本文中，我将展示我以后将在 Postgres 和 MySQL 中避免使用 auto-increment 自增长字段的两个原因。我更愿意使用 UUID 字段除非有非常好的不使用的理由。

› MySQL <8.0 auto-increment ID 重用

如果您运行的是旧版本的MySQL，那么自动递增的ID可能会被重复使用。让我们看看这个动作。

$ docker volume create mysql-data
$ docker run --platform linux/amd64 -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -v mysql-data:/var/lib/mysql mysql:5.7

上面为我们提供了一个运行MySQL 5.7的Docker容器，该容器连接到一个数据卷上，该卷将在该容器的运行期间持久保存数据。接下来，让我们获得一个可以使用的简单 schema ：

$ docker run -it --rm --network host --platform linux/amd64 mysql:5.7 mysql -h 127.0.0.1 -P 3306 -u root -p
mysql> CREATE DATABASE my_database;
Query OK, 1 row affected (0.01 sec)

mysql> USE my_database;
Database changed
mysql> CREATE TABLE my_table (
    -> ID INT AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.02 sec)

接下来插入一些行：

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

到现在为止，一直都还不错。我们可以重新启动MySQL服务器，再次运行相同的SELECT语句，得到相同的结果。

让我们删除一行。

mysql> DELETE FROM my_table WHERE ID=3;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

让我们插入一个新行，以确保ID 3不会被重用。

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

完美。让我们删除最新添加的行，重启服务并插入新行。

mysql> DELETE FROM my_table WHERE ID=4;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM my_table;
ERROR 2013 (HY000): Lost connection to MySQL server during query

$ docker run -it --rm --network host --platform linux/amd64 mysql:5.7 mysql -h 127.0.0.1 -P 3306 -u root -p

mysql> USE my_database;
Database changed

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

嗯。MySQL重新使用了ID 3。这是因为 InnoDB 中 auto-increment 的工作方式是，在服务器重新启动时，它将通过有效地运行此查询来确定下一个要使用的 ID:

SELECT MAX(ID) FROM my_table;

如果您在重新启动之前从表中删除了最新的记录，那么当服务器恢复运行时，已使用的ID将被重新使用。

理论上，这不应该给你带来麻烦。最佳实践表明，除非是某个外键字段，否则不应该使用该表之外的数据库表中的ID，当然也不会将该 ID 泄露到系统之外，对吧？

在实践中，这种情况会发生，并可能导致毁灭性的微妙错误。MySQL 8.0 通过将自动增量值存储在磁盘上的方式改变了这种行为，这种方式可以跨越重启将其持久化。

› Postgres 序列值不会被复制

像 MySQL 8.0 一样，Postgres 在磁盘上储存 auto-increment 值。它通过一个称为“sequence”的 schema 对象中实现。当你在 Postgres 中创建一个auto-increment自动递增字段时，在后台会创建一个序列来支持该字段，并持久地跟踪下一个值应该是什么。

让我们在实践中看看这一点。

$ docker volume create postgres-14-data
$ docker run --network host -e POSTGRES_PASSWORD=my-secret-pw -v postgres-14-data:/var/lib/postgresql/data -p postgres:14

随着Postgres的启动和运行，让我们继续创建我们的表：

$ docker run -it --rm --network host postgres:14 psql -h 127.0.0.1 -U postgres
postgres=# CREATE TABLE my_table (id SERIAL PRIMARY KEY);
CREATE TABLE

插入几行：

postgres=# INSERT INTO my_table DEFAULT VALUES;
INSERT 0 1
postgres=# INSERT INTO my_table DEFAULT VALUES;
INSERT 0 1
postgres=# INSERT INTO my_table DEFAULT VALUES;
INSERT 0 1
postgres=# SELECT * FROM my_table;
 id
----
  1
  2
  3
(3 rows)

目前一切都好。我们看一下该表：

postgres=# \d my_table
                            Table "public.my_table"
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------------------
 id     | integer |           | not null | nextval('my_table_id_seq'::regclass)
Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)

这个输出告诉我们id字段的默认值是 my_table_id_seq 的 nextval。让我们看看my_table_id_seq：

postgres=# \d my_table_id_seq
                  Sequence "public.my_table_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.my_table.id

postgres=# SELECT currval('my_table_id_seq');
 currval
---------
       3
(1 row)

不错，我们在 Postgres 中有一个真正的对象，它跟踪自动递增的 ID 值。如果我们重复我们在 MySQ L中所做的操作，删除一些行并重新启动，我们就不会有同样的问题了。my_table_id_seq 被保存到磁盘，并且不会丢失它的位置。

真是这样吗？

如果你想将Postgres更新到一个新的主版本，通常的方法是在你想升级的版本上创建一个新Postgres实例，从旧实例逻辑复制到新实例，然后切换你的应用程序与新实例对话。

首先，我们需要用一些新的配置重新启动 Postgres 14，以允许逻辑复制：

$ docker run --network host -e POSTGRES_PASSWORD=my-secret-pw -v postgres-14-data:/var/lib/postgresql/data -p postgres:14 -c wal_level=logical

现在让我们启动Postgres 15并运行

$ docker volume create postgres-15-data
$ docker run --network host -e POSTGRES_PASSWORD=my-secret-pw -v postgres-15-data:/var/lib/postgresql/data postgres:15 postgres:14 -c wal_level=logical -p 5431

接下来，我们在 Postgres 14 实例中创建一个 “publication”：

postgres=# CREATE PUBLICATION my_publication FOR ALL TABLES;
CREATE PUBLICATION

然后，我们在 Postgres 15 实例上创建“my_table”表和一个订阅 “subscription”：

postgres=# CREATE TABLE my_table (id SERIAL PRIMARY KEY);
CREATE TABLE
postgres=# CREATE SUBSCRIPTION my_subscription CONNECTION 'host=127.0.0.1 port=5432 dbname=postgres user=postgres password=my-secret-pw' PUBLICATION my_publication;
NOTICE:  created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION

然后，我们可以看到数据在两个实例之间的同步：

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5432 -c "SELECT * FROM my_table"
 id
----
  1
  2
  3
(3 rows)

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "SELECT * FROM my_table"
 id
----
  1
  2
  3
(3 rows)

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5432 -c "INSERT INTO my_table DEFAULT VALUES"
INSERT 0 1

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "SELECT * FROM my_table"
 id
----
  1
  2
  3
  4
(4 rows)

那么问题是什么呢?

Well...

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5432 -c "SELECT nextval('my_table_id_seq')"
 nextval
---------
       5
(1 row)

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "SELECT nextval('my_table_id_seq')"
 nextval
---------
       1
(1 row)

序列值没有被复制。如果我们在 Postgres 15 中插入一行，会得到：

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "INSERT INTO my_table DEFAULT VALUES"
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (id)=(2) already exists.

注意：它试图在这里插入id=2，因为当我们之前调用 nextval 时，它修改了序列。

如果严重依赖自动递增ID字段，这可能会使 Postgres 主版本更新变得非常棘手。您需要手动将序列值修改为您知道的值，因为在升级过程中无法获得该值，然后您可能需要根据您的工作负载在升级期间禁用写入。

› 结语

通过使用UUID字段而不是自动递增 interger，可以避免上述所有痛苦。它的好处是不可预测，并且如果您最终在表之外使用它们（不应该这样做），则不会泄露有关底层表基数的信息。

多亏了 Incident.io 上那些了不起的人的这篇文章，我现在意识到了德国坦克的问题。阅读该链接文章和维基百科相关页面很值得，因此有更多的理由不使用自动递增 ID 字段。