MySql upsert和自动增量导致间隙

Question

我有一个带有自动增量主键的 MySql 表，似乎所有各种 upsert 方法（INSERT IGNORE 和 ON DUPLICATE KEY UPDATE）都受到自动增量字段增量的影响，甚至如果一行已更新但未插入。这意味着表中引入了间隙，我认为这是不可取的。

所以问题是：如果更新插入实际上只是更新行，是否有任何方法可以在具有自动增量字段的表中更新插入记录而不自动增量该字段。在我看来，这是 upsert 应该表现的方式，但似乎并非如此。

Answer 1

这个“问题”仅存在于InnoDB中。

这是设计使然，旨在提高并发性：另一个线程可以使用 AUTO_INCRMENT 而无需等待 UPSERT 操作的结果。

来自 文档 ：

服务器启动后，对于第一次插入表t，InnoDB执行与此语句等效的操作：

从 t 中选择 MAX(ai_col) 进行更新；

…

InnoDB 初始化但不增加值并存储它以供以后插入使用

…

访问自动递增计数器时，InnoDB 使用特殊的表级 AUTO-INC 锁，该锁将保留到当前 SQL< 的末尾/code> 语句，不至于事务结束。引入特殊的锁释放策略是为了提高向包含 AUTO_INCRMENT 列的表中插入的并发性。然而，两个事务不能同时在同一个表上拥有 AUTO-INC 锁，如果长时间持有 AUTO-INC 锁，可能会对性能产生影响。对于诸如 INSERT INTO t1 ... SELECT ... FROM t2 之类的将一个表中的所有行插入到另一个表中的语句来说，可能就是这种情况。




MyISAM 不会表现出这种行为，因为它的 AUTO_INCRMENT 算法的实现方式不同（由于其支持并发 DML 的能力有限）。