复制到tmp表，该表在自动增量时更改表

Question

我们对MySql (5.5.x)有一个问题，我希望您能帮助我们。在白天的某个时候，我注意到状态为“复制到tmp表”的进程，这个查询：

ALTER TABLE `MyTableName` AUTO_INCREMENT=200000001

在此之后，所有其他查询都会得到一个“等待表元数据锁”，所有查询都会被冻结，没有任何处理。

我需要终止这个过程，从那时起，所有的查询都重新启动。

为什么？我怎样才能解决这个问题？

Answer 1

在MySQL 5.5中，像您运行的这样的会复制整个表。桌子越大，时间就越长。尤其是当你储存得很慢的时候。

您的表的大小(您可以从SHOW TABLE STATUS LIKE 'MyTableName'\G获得它并查看data_length + index_length)？

我刚在笔记本电脑上做了个测试。我在一个MySQL 5.5实例中填充了一个表，直到该表的大小大约为175 is。运行alter来设置自动增量值大约需要5-6秒。您的结果可能有所不同，这取决于服务器的功能和存储速度。

在alter运行时，执行该操作的线程在表上持有一个元数据锁，它阻止所有其他查询，甚至是只读SELECT语句。

作为MySQL 5.6的一个特性，ALTER在2013年得到了改进。有些类型的修改被优化为“就地”，这样如果没有必要的话，它们就不必复制整个表。更改AUTO_INCREMENT就是这些操作之一。无论表有多大，如果您更改表以更改AUTO_INCREMENT，它都是快速的，因为它只更改表的一个属性，而不需要复制任何数据行。

请参阅https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

在MySQL 5.5中，这些优化没有实现。因此，任何更改表都需要很长的时间，与表的大小成正比。

在您的情况下，我建议解决此问题的最佳方法是升级到较新的版本。MySQL 5.5已经超越了它的终身制.就连MySQL 5.6也在2021年2月达到了生命的尽头。是时候升级了。

如果您不能升级，那么您应该调查客户端正在执行的ALTER语句。你说你在白天的某个时候注意到了这一点。把它找出来。在processlist中，它将告诉客户端主机运行SQL语句的位置。它还将告诉您他们登录的MySQL用户。您还可能需要对使用此数据库的任何应用程序或脚本的源代码进行搜索。或者问你的队友。

一旦您找到了执行该ALTER的客户端，请尝试将客户端运行此语句的时间更改为某个时间，此时ALTER不会阻止重要的查询。或者问负责的开发人员是否真的有必要经常做这个alter呢？

Answer 2

这个问题可能是由于服务器重新启动造成的，因为InnoDb将最后一个自动增量索引存储在内存中，在服务器重新启动(增量计数器初始化)时重新计算该索引：

如果为AUTO_INCREMENT表指定InnoDB列，则InnoDB数据字典中的表句柄包含一个称为自动增量计数器的特殊计数器，用于为该列分配新值。此计数器仅存储在主内存中，而不存储在磁盘上。 要在服务器重新启动后初始化自动增量计数器，InnoDB在包含AUTO_INCREMENT列的表的第一个insert上执行以下语句。 从ai_col中选择MAX( table_name )进行更新； InnoDB增加语句检索的值，并将其分配给表的列和自动增量计数器。默认情况下，该值递增1。此默认值可被auto_increment_increment配置设置覆盖。 如果表为空，InnoDB将使用值1。此默认设置可以被auto_increment_offset配置设置覆盖。

查看mysql日志，如果服务器重新启动，则尝试查找，从而导致ALTER重置自动增量计数器。

尝试重新启动mysql服务器，以查看是否得到此行为。

如果是这样的话，您可以尝试：

• 防止重新启动mysql服务器，可能有cron进程每天重新启动它一次。
• 将mysql版本升级到8 (自动保存到表元数据中)：

在MySQL 8.0中，此行为将被更改。当前最大自动增量计数器值每次更改时都写入重做日志，并保存到每个检查点上的引擎专用系统表中。在服务器正常关闭后重新启动时，InnoDB使用存储在数据字典系统表中的当前最大自动增量值初始化内存中的自动增量计数器。

• 您可以尝试加速“复制到tmp表”操作，跳过复制到磁盘mysql上的tmp表

参考文献

如何使InnoDB表在服务器重新启动时不重置自动增量？

https://serverfault.com/questions/228690/mysql-auto-increment-fields-resets-by-itself