php - 插入时基于 MAX() 子查询安全地自动递增 MySQL 字段

Question

我有一个表，其中包含一个标准的自动递增 ID、一个类型标识符、一个数字和一些其他不相关的字段。当我向这个表中插入一个新对象时，数字应该根据类型标识符自动递增。

下面是输出的示例:

id      type_id     number
1       1           1
2       1           2
3       2           1
4       1           3   
5       3           1
6       3           2
7       1           4
8       2           2

如您所见，每次我插入一个新对象时，数字都会根据 type_id 递增(即，如果我插入一个 type_id 为 1 的对象并且已经有 5 个对象与此 type_id 匹配，则新对象上的数字应该是 6)。

我正试图找到一种高效的方式来实现巨大的并发性。例如，对于同一个 type_id，同一秒内可能有 300 次插入，需要按顺序处理。

我已经尝试过的方法:

PHP

这是个坏主意，但为了完整起见，我添加了它。已请求获取项目类型的 MAX() 编号，然后添加编号 + 1 作为插入的一部分。这很快但不能同时工作，因为在 MAX() 请求和导致多个具有相同编号和 type_id 的对象的特定插入之间可能有 200 次插入。

锁定

在每次插入之前和之后手动锁定和解锁表以保持增量。由于并发插入的数量以及在整个应用程序中不断读取表，这导致了性能问题。

子查询事务

这就是我目前的做法，但它仍然会导致大量性能问题:

START TRANSACTION;
INSERT INTO objects (type_id,number) VALUES ($type_id, (SELECT COALESCE(MAX(number),0)+1 FROM objects WHERE type_id = $type_id FOR UPDATE));
COMMIT;

关于此方法的另一个负面影响是我需要执行后续查询以获取添加的数字(即搜索 $type_id 按数字 desc 排序的对象，以便我可以看到添加的数字创建 - 这是基于 $user_id 完成的，所以它可以工作，但添加了一个我想避免的额外查询)

触发器

我研究了使用触发器以便在插入时动态添加数字，但这不是高效的，因为我需要对要插入的表执行查询(这是不允许的，所以必须在导致性能问题的子查询)。

分组自增

我看过分组自动递增(这样数字会根据 type_id 自动递增)，但后来我丢失了自动递增 ID。

---

有没有人知道如何在我需要的并发插入级别上实现这种性能？我的表目前是 MySQL 5.5 上的 InnoDB

感谢任何帮助!

更新:为了以防万一，对象表中有数百万个对象。一些 type_id 可以分配给它们大约 500,000 个对象。

Answer 1

使用事务并选择...进行更新。这将解决并发冲突。