Mysql upsert和自动增量导致差距 [英] MySql upsert and auto-increment causes gaps

问题描述

我有一个带有自动递增主键的MySql表，而且似乎所有各种upsert方法(INSERT IGNORE和ON DUPLICATE KEY UPDATE)都具有自动递增字段的功能即使行已更新但未插入，它也会递增.这意味着在表中引入了间隙，我认为这是不希望的.

I've got a MySql table with an auto-increment primary key, and it seems that all of the various upsert methods (INSERT IGNORE and ON DUPLICATE KEY UPDATE) suffer from the, uh, feature that the auto-increment field increments, even if a row is updated and not inserted. This means that gaps are introduced into the table, which I find undesirable.

所以问题是:如果实际上是仅对行进行更新，那么有没有办法在没有自动递增字段的情况下对具有自动递增字段的表中的记录进行递增查询呢?在我看来，这是upsert的行为方式，但似乎并非如此.

So the question is: is there any way to upsert records in a table with an auto-increment field without auto-incrementing that field, if the upsert in fact merely updates the row. To my mind, this is the way upsert should behave, but it doesn't seem to.

推荐答案

此问题"仅在InnoDB中.

这是设计使然，旨在提高并发性:另一个线程可以使用AUTO_INCREMENT，而不必等待UPSERT操作的结果.

It is by design, and intended to improve concurrency: another thread can use an AUTO_INCREMENT without having to wait for the results of an UPSERT operation.

从 docs :

服务器启动后，对于第一次插入到表t中，InnoDB执行以下语句的等效项:

After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE;

…

InnoDB初始化但不增加该值并将其存储以供以后的插入使用

InnoDB initializes but does not increment the value and stores it for use by later inserts

…

访问自动增量计数器时，InnoDB使用特殊的表级AUTO-INC锁，该锁保留在当前SQL语句的末尾，而不是事务的末尾.引入了特殊的锁定释放策略，以提高插入包含AUTO_INCREMENT列的表中的并发性.但是，两个事务不能同时在同一表上具有AUTO-INC锁，如果长时间按住AUTO-INC锁，则会对性能产生影响.对于像INSERT INTO t1 ... SELECT ... FROM t2这样的语句，可能会将一个表的所有行插入到另一个表中.

When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Nevertheless, two transactions cannot have the AUTO-INC lock on the same table simultaneously, which can have a performance impact if the AUTO-INC lock is held for a long time. That might be the case for a statement such as INSERT INTO t1 ... SELECT ... FROM t2 that inserts all rows from one table into another.

MyISAM没有表现出这种行为，因为它的AUTO_INCREMENT算法实现方式不同(由于其支持并发DML的能力有限).

MyISAM does not exhibit this behavior, since it's AUTO_INCREMENT algorithm is implemented differently (due to its limited ability to support concurrent DML).

这篇关于Mysql upsert和自动增量导致差距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！