mysql - 在不命中 INFORMATION_SCHEMA 的情况下获取 AUTO_INCREMENT

Question

我被指派创建一个 bash 脚本来检查 AUTO_INCREMENT 列何时接近其限制。我认为最好的方法是获取“当前自动增量值”并与“支持最大值的列的数据类型”进行比较(比如 tinyint 为 255)。

我们的数据库中有大约 2Tb 的数据，我不想查询 INFORMATION SCHEMA 表来获取 AUTO_INCREMENT，因为这会花费太长时间。

因此我不能使用 (SHOW TABLE STATUS LIKE 'table_name') 也不能使用 (SELECT AUTO_INCREMENT FROM information_schema.tables) 因为两者都查询 INFORMATION SCHEMA 表。

有人告诉我可能有一种变通方法可以在不访问信息模式表的情况下获取当前的 AUTO_INCREMENT，但我看不出具体方法。有什么想法吗？

这是我获取数据类型的方式

DATA_TYPE="`$MYSQLCONNECT -D $DATABASE --batch -N -e "SELECT DATA_TYPE FROM         
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$DATABASE' AND TABLE_NAME='$TABLE' AND 
EXTRA='auto_increment'"`"

这就是我如何获得该列可以处理的最大值:

MAX_VALUE="`$MYSQLCONNECT -D $DATABASE --batch -N -e "SELECT ( CASE '$DATA_TYPE' WHEN 'tinyint' 
THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 
WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) ) 
as MAX_VALUE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$DATABASE' AND TABLE_NAME 
= '$TABLE' AND EXTRA='auto_increment'"`"

Answer 1

查询INFORMATION_SCHEMA确实需要很长时间，因为对TABLES或STATISTICS或其他一些表的查询导致MySQL分析表中的数据分布，计算平均行大小等。

此分析的开销更多地与表的数量有关，而不是表的大小。它基本上从每个表中读取 8 个随机页面。

您可以禁用自动统计分析的功能，这样 I_S 查询就可以在没有太多开销的情况下运行。

SET GLOBAL innodb_stats_on_metadata=0 

在 MySQL 5.6 中，这是默认配置的，但从 MySQL 5.1.7 开始，您也可以自己配置。

另请参阅:Solving INFORMATION_SCHEMA slowness

FWIW，我还创建了一个脚本来完成您正在做的事情。我在 GPL 下提供它，与 MySQL 使用的许可证相同。这是它的链接:
https://github.com/billkarwin/bk-tools/blob/master/pk-full-ratio.sql

我为所有整数列创建了一个类似的脚本，不仅是自动递增主键列:
https://github.com/billkarwin/bk-tools/blob/master/int-full-ratio.sql