MariaDB - INNODB 在创建增量记录时跳过数字序列 - 为什么?

问题描述

我不知道这是否是 INNODB 的预期行为，但我真的认为这很奇怪.

I do not know if this is expected behavior with INNODB, but I really think it's totally weird.

如果我通过 MYISAM 使用相同的 SQL 语句，行为会按预期发生.

If I use the same SQL statement using MYISAM, the behavior occurs as expected.

MYISAM

CREATE TABLE main_database.numero (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id)
) ENGINE = MYISAM DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO main_database.numero VALUES(NULL); -- First, run once time ...
INSERT INTO main_database.numero SELECT NULL FROM main_database.numero; -- After, more 12 times = 4096 records

结果(预期行为):

现在如果我使用完全相同的语句，但是，通知引擎是 INNODB.

Now if I use exactly the same statement, however, informing that the engine is INNODB.

INNODB

CREATE TABLE main_database.numero (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO main_database.numero VALUES(NULL); -- First, run once time ...
INSERT INTO main_database.numero SELECT NULL FROM main_database.numero; -- After, more 12 times = 4096 records

结果(奇怪的结果 - 跳过数字序列):

事实上，两个引擎都在创建预期的 4096 条记录，但我担心 INNO 的行为，因为我正在将我的数据库从 MYISAM 迁移到 INNODB，我不知道这会对我的应用程序产生多大影响.

In fact, both engines are creating the expected 4096 records, but I got worried with behavior of INNO, because I'm migrating my databases from MYISAM to INNODB and I do not know how much that can impact my applications.

推荐答案

需要 auto_increment 机制来生成 唯一 值，这些值大于之前生成的任何值.它不保证生成连续值.

The auto_increment mechanism is required to generate unique values, that are greater than any value it has generated previously. It does not guarantee to generate consecutive values.

这里有一些关于它的讨论:https://bugs.mysql.com/bug.php?id=57643

There's some discussion about it here: https://bugs.mysql.com/bug.php?id=57643

忠实地生成连续值并不重要，因为任何值都可能由于其他原因丢失":

There is little importance in generating consecutive values faithfully, because any value could be "lost" for other reasons:

• 您的 INSERT 失败，例如因为违反了 UNIQUE KEY 或 FOREIGN KEY 等约束.
• 您为 INSERT 回滚事务.
• 您成功并提交，但后来该行被您或其他会话删除.

Auto-inc 值不会返回到任何类型的队列，因为其他并发会话可能在此期间生成了更多的 id 值.InnoDB 维护一个未分配的 id 值池是不值得的，因为该池可能变得巨大且浪费.

Auto-inc values are not returned to any kind of queue, because other concurrent sessions might have generated further id values in the meantime. It's not worth InnoDB maintaining a pool of unallocated id values, because that pool could become huge and wasteful.

此外，丢失"一个 ID 值可能是合适的，否则有人会认为他们要删除的行不知何故又回来了.

Also, it might be appropriate to "lose" an ID value, or else someone would think the row they meant to DELETE somehow came back.

这篇关于MariaDB - INNODB 在创建增量记录时跳过数字序列 - 为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！