MySQLを運用していく上で、プライマリキーに整数型のカラムとAUTO_
実際、筆者は1日1件ずつINSERTされるテーブルのプライマリキーをINTEGER型カラムで作成しなければいけないところを誤ってTINYINT型で作成してしまい、128日後に大量のエラーが出てしまったという経験があります。この経験は極端ではありますが、実際INTEGERで運用していても、日次のデータ挿入の件数が大きければ、INTEGERでも足りないことが出てくるかもれしれません。
今回は、各整数型のカラムが、データ量によってどれくらいで消費されてしまうのかを見ていきたいと思います。
整数型カラムが最大になるタイミング
プライマリキーにAUTO_
計算すると、1日10件程度のデータ増加であれば、TINYINTは12日ですが、INTEGERの場合は58万8,351年で使い切ります。1日に100件、1,000件、1万件… とデータの更新があった場合が以下のテーブルになります。
1日あたりのデータ増加数 | 100件 | 1000件 | 10000件 | 100000件 |
---|---|---|---|---|
TINYINT | 1日 | ― | ― | |
SMALLINT | 327日 | 32日 | ― | ― |
MEDIUMINT | 229年 | 22年 | 2年 | 83日 |
プライマリキーのIDに利用する値として多いのは、INTEGERとBIGINTかと思います。2つのカラムをさらに挿入件数を増やして計算してみると、以下のような期間で使い切ってしまいます。
1日あたりのデータ増加数 | 10000件 | 100000件 | 1000000件 |
---|---|---|---|
INT | 588年 | 58年 | 5年 |
BIGINT | ※ | ※ | 25269512429年 |
※は十分に大きいため省略しています。また、どちらの表も各整数値型はsignedを想定しています。
データ挿入量が相当大きくない限りは、INTEGER型で足りるのであればINTEGER型で作ってしまって問題ないかもしれません。しかし、あとから足りなくなってデータ型の変更が必要になる可能性があるのであれば、最初からBIGINT型で作ってしまうことも検討したいところです。たしかにALTER TABLEでデータ型の変更は可能ではありますが、データ量の規模によっては時間がかかってしまうためです。だからといって、すべてをBIGINT型のカラムで作ってしまうのも無駄にデータ量が増えてしまうので、きちんと検討して決めることをおすすめします。
また、JOINに利用するカラムの場合は、データ型が違うと型変換が実行されるために実行が遅くなるので、極力データ型を合わせて利用したいところです。
BIGINTじゃ足りない!?
アプリケーションの作りによってはunsignedなBIGINTでも足りない状況が出てくるかもしれません。そういうときはプライマリキーに文字列型を利用して、UUIDやhash値などを利用することも検討しましょう。将来的にシャーディングをする可能性がある場合は、それも考慮に入れた値をプライマリキーに設定すると良いでしょう。また、ユーザーにidを推測されたくない場合などにもこの手段は有効かもしれません。
現在のAUTO_INCREMENT値を知る
ここまでは、プライマリキーが仮に整数型だった場合にどれくらいで使いきるのか、という内容でした。では各テーブルの現在のAUTO_
mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `name1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL, `name2` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2147483645 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec)
また、information_
mysql> SELECT table_schema, table_name, auto_increment FROM tables WHERE table_schema IN ('d1') AND AUTO_INCREMENT ORDER BY 3 DESC LIMIT 4; +--------------+-------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT | +--------------+-------------+----------------+ | d1 | t1 | 2147483647 | | d1 | item | 6 | | d1 | cp_t2 | 3 | +--------------+-------------+----------------+ 3 rows in set (0.01 sec)
AUTO_
AUTO_INCREMENTを監視しよう
筆者は実際に、AUTO_
下記は、
SELECT
t.TABLE_SCHEMA AS `schema`,
t.TABLE_NAME AS `table`,
t.AUTO_INCREMENT AS `auto_increment`,
c.DATA_TYPE AS `pk_type`,
(
t.AUTO_INCREMENT /
(CASE DATA_TYPE
WHEN 'tinyint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
255,
127
)
WHEN 'smallint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
65535,
32767
)
WHEN 'mediumint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
16777215,
8388607
)
WHEN 'int'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
4294967295,
2147483647
)
WHEN 'bigint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
18446744073709551615,
9223372036854775807
)
END / 100)
) AS `max_value`
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE
t.AUTO_INCREMENT IS NOT NULL
AND c.COLUMN_KEY = 'PRI'
AND c.DATA_TYPE LIKE '%int'
sysスキーマにもschema_
mysql> SELECT * FROM sys.schema_auto_increment_columns limit 1; +--------------+------------+-------------+-----------+-------------+-----------+-------------+------------+----------------+----------------------+ | table_schema | table_name | column_name | data_type | column_type | is_signed | is_unsigned | max_value | auto_increment | auto_increment_ratio | +--------------+------------+-------------+-----------+-------------+-----------+-------------+------------+----------------+----------------------+ | d1 | t1 | id | int | int | 1 | 0 | 2147483647 | 2147483647 | 1.0000 | +--------------+------------+-------------+-----------+-------------+-----------+-------------+------------+----------------+----------------------+
まとめ
今回は、プライマリキーにINTEGER型のカラムを使い、AUTO_