自增主键留有计数间隔

Question

我有一个带有自增主键的表。这个表用于存储数百万条记录，目前不需要删除任何内容。问题在于，当插入新行时，由于某些错误，自增键留下了一些空缺的自增id。例如，在5之后，下一个id是8，留下6和7的空洞。结果是当我计算行数时，结果为28000，但最大id为58000。可能的原因是什么？我没有删除任何内容。如何解决这个问题。

P.S. 我在插入记录时使用insert ignore，以便在尝试在唯一列中插入重复条目时不会出现错误。

- Sourabh

---

可能是http://stackoverflow.com/questions/16348925/strictly-auto-increment-value-in-mysql的重复问题。 - Ian Kenney

可能是[MySql upsert和auto-increment导致间隙]的重复问题（https://dev59.com/9lDTa4cB1Zd3GeqPIl5T） - Barmar

我写了一个InnoDB gap的答案，在这里。 - Drew

Answer 1

这是有意设计的，且将一直发生。

为什么？

让我们考虑两个重叠的进行INSERT操作的事务：

• 事务1执行了一个INSERT操作，获取值（假设为42），然后执行其他工作
• 事务2执行了一个INSERT操作，获取值43，然后执行其他工作

然后：

• 事务1失败。回滚。42保持未使用
• 事务2完成并使用了43

如果保证连续的值，则每个事务都必须一个接一个地发生，这样不太可扩展。

还请参见“插入记录是否总是接收连续的标识值”（适用于SQL Server，但原理相同）

- gbn

---

我不明白，为什么会发生这种情况？是因为插入忽略或其他原因吗？如果自动递增不按正确顺序进行，那还有什么意义呢？ - Sourabh

自增的唯一要求是它们必须是唯一的，不必连续。 - Barmar

@Sourabh，它们的顺序是正确的，后面插入的将具有更高的ID，它们只是不连续的。如果您需要连续的数字，可以在这里使用基于触发器的解决方案：https://dev59.com/tk_Sa4cB1Zd3GeqP-DP7 - Ian Kenney

即使是临时表也会发生这种情况，它们不能在两个不同的事务中同时使用。 - Barry Kelly

我写了一个关于InnoDB间隙的答案在这里。你可以避免它们发生，但是需要付出代价。 - Drew

Answer 2

您可以创建一个触发器来处理自增：

CREATE DEFINER=`root`@`localhost` TRIGGER `mytable_before_insert` BEFORE INSERT ON `mytable` FOR EACH ROW 
BEGIN
  SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM mytable);;
END

- Mackraken

---

这是一个好的做法吗？如果出现并发插入的情况，后面会不会引起任何错误？ - exiang

这是不好的做法。如果从列表末尾删除键，则它们将被重用。这在内部不会成为问题，因为外键将防止引用完整性受损，但对于您数据的任何外部引用（例如，密钥作为网站上的文章ID使用，或者您将数据导出到合并到其自己的另一个系统中），可能会“断裂”。如果您没有在需要的地方定义适当的FK，那么内部引用完整性也可能会被破坏。此外，这比使用内置的增量支持效率要低。 - David Spillett

引用文章是一个好的观点，但不适用于这种情况，因为它会在记录插入之前触发，而不是当它们存在时触发。如果没有插入记录，MySql不会重复使用ID。如果取消了一个包含40k条记录的插入事务，在插入下一条记录时会留下一个40k的间隙。尽管这不是每种情况的理想选择，但对于定期批量导入大量记录来说，它是一个有效的解决方案。 - Mackraken

Answer 3

这是MySQL存储引擎InnoDB中的一个问题。

实际上这并不是一个问题，当您查看关于 InnoDB 自增处理 的文档时（请参见此处），可以发现 InnoDB 在启动时使用一个特殊的表来进行自增处理。

它所使用的查询类似于：

SELECT MAX(ai_col) FROM t FOR UPDATE;

这样做可以提高并发性，同时不会对数据产生影响。

如果不想使用此功能，请改用MyISAM作为存储引擎而非InnoDB。

- cjds

Answer 4

也许（我没有测试过）的解决方案是将 innodb_autoinc_lock_mode 设置为 0。根据 http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html 的说法，这可能会使事情变得有点慢（如果您在单个查询中执行多行插入），但应该可以消除间隙。

- qbolec

---

看起来你没有阅读你提供的页面，该页面上写着：“在所有锁定模式（0、1和2）中，如果生成自动递增值的事务回滚，则这些自动递增值将被“丢失”。" - Thomas Clowes

你可能是对的。我的理解是，初始问题是关于在没有错误/回滚的情况下数字间隙的问题。至少这是我的经验和我找到这个问题和答案的原因。 - qbolec

Answer 5

你可以尝试像这样插入：

insert ignore into table select (select max(id)+1 from table), "value1", "value2" ;

这将尝试

• 使用最后一个未使用的ID（非自动增量）插入新数据
• 如果在唯一字段中发现重复条目，则忽略它
• 否则正常插入新数据
  
  （但是，如果发现重复条目，则此方法不支持更新字段）

- a55