AUTO_INCREMENT の上限まであとどれくらいかを調べる SQL
Posted on
MySQL の AUTO_INCREMENT 属性を使っている場合、整数型の上限に達しないか注意する必要があります。たとえば INT 型だと約 21 億 4700 万、unsigned が指定されていると倍の約 42 億 9400 万が上限です。
上限に達する前により大きな型にマイグレーションしなければなりませんが、そのためには上限まであとどれくらいかを調べる必要があります。各テーブルで LAST_INSERT_ID() を叩けば最後に生成された AUTO_INCREMENT の値を取得できますが、すべてのテーブルを網羅的に調べる必要があるので information_schema から SQL で割り出してみました。
たとえば INT 型のカラムを対象に何パーセント使用しているかを調査する SQL です。 unsigned が指定されていると上限が倍になるので column_type を見て分母を変えています。
SELECT
c.table_name,
c.column_type,
t.auto_increment,
IF(
LOCATE('unsigned', c.column_type) = 0,
(t.auto_increment / 2147483647) * 100,
(t.auto_increment / 4294967295) * 100
) as use_rate
FROM
information_schema.tables AS t
INNER JOIN
information_schema.columns AS c
ON
t.table_name = c.table_name
WHERE
t.auto_increment IS NOT NULL
AND
c.extra = 'auto_increment'
AND
c.data_type = 'int'
ORDER BY
use_rate DESC
;
実際に使うときは閾値を超えたテーブルだけ知りたいので HAVING 句で絞って Slack に通知しています。
参考までに information_schema のテーブルはこんなスキーマになっています。
mysql> DESC information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
mysql> DESC information_schema.columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(30) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.01 sec)
まとめ
サービスが急成長していると INT 型の上限は射程に入ってきます。上限に達して障害を起こさないようにちゃんと監視する必要がありますね。