MySQLを運用していく上で、プライマリキーに整数型のカラムとAUTO_INCREMENTを設定して運用することはよくあると思います。このプライマリキーを運用していく中で、整数型によってはAUTO_INCREMENTの値が整数型のカラムの最大値に到達してしまい、エラーが出てしまう可能性があります。

実際、筆者は1日1件ずつINSERTされるテーブルのプライマリキーをINTEGER型カラムで作成しなければいけないところを誤ってTINYINT型で作成してしまい、128日後に大量のエラーが出てしまったという経験があります。この経験は極端ではありますが、実際INTEGERで運用していても、日次のデータ挿入の件数が大きければ、INTEGERでも足りないことが出てくるかもれしれません。

今回は、各整数型のカラムが、データ量によってどれくらいで消費されてしまうのかを見ていきたいと思います。

整数型カラムが最大になるタイミング

プライマリキーにAUTO_INCREMENTを設定していたとしましょう。この場合おおよそデータ1件に対して値が1上がっていきます（ここではデータの挿入が必ず成功し、AUTO_INCREMENTが1上がるものと仮定します⁠）⁠。このときに、プライマリキーが各整数値のカラムだった場合どれくらいで使い切るのでしょうか。

計算すると、1日10件程度のデータ増加であれば、TINYINTは12日ですが、INTEGERの場合は58万8,351年で使い切ります。1日に100件、1,000件、1万件… とデータの更新があった場合が以下のテーブルになります。

プライマリキーのIDに利用する値として多いのは、INTEGERとBIGINTかと思います。2つのカラムをさらに挿入件数を増やして計算してみると、以下のような期間で使い切ってしまいます。

※は十分に大きいため省略しています。また、どちらの表も各整数値型はsignedを想定しています。

データ挿入量が相当大きくない限りは、INTEGER型で足りるのであればINTEGER型で作ってしまって問題ないかもしれません。しかし、あとから足りなくなってデータ型の変更が必要になる可能性があるのであれば、最初からBIGINT型で作ってしまうことも検討したいところです。たしかにALTER TABLEでデータ型の変更は可能ではありますが、データ量の規模によっては時間がかかってしまうためです。だからといって、すべてをBIGINT型のカラムで作ってしまうのも無駄にデータ量が増えてしまうので、きちんと検討して決めることをおすすめします。

また、JOINに利用するカラムの場合は、データ型が違うと型変換が実行されるために実行が遅くなるので、極力データ型を合わせて利用したいところです。

BIGINTじゃ足りない!?

アプリケーションの作りによってはunsignedなBIGINTでも足りない状況が出てくるかもしれません。そういうときはプライマリキーに文字列型を利用して、UUIDやhash値などを利用することも検討しましょう。将来的にシャーディングをする可能性がある場合は、それも考慮に入れた値をプライマリキーに設定すると良いでしょう。また、ユーザーにidを推測されたくない場合などにもこの手段は有効かもしれません。

現在のAUTO_INCREMENT値を知る

ここまでは、プライマリキーが仮に整数型だった場合にどれくらいで使いきるのか、という内容でした。では各テーブルの現在のAUTO_INCREMENT値を見てみましょう。テーブルに設定してあるAUTO_INCREMENT値は、SHOW CREATE TABLEを実行することで現在の値を確認することができます。

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_schemaのtablesテーブルにも現在のAUTO_INCREMENTの値があるため、こちらでも確認することができます。

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_INCREMENTについては過去に「第49回　MySQLのAUTO_INCREMENTについて」にて記載していますので、こちらもあわせてご確認ください。

AUTO_INCREMENTを監視しよう

筆者は実際に、AUTO_INCREMENTに設定されているINTEGER型のプライマリキーを80％ほど使い切ってしまっているアプリケーションに、何度か出会ったことがあります。使い切ってしまいエラーが出てから気づく前に、AUTO_INCREMENTの値も監視対象として事前に検知できるようにしましょう。事前にわかっていれば、BIGINT型に変更する計画を立てたり、テーブル構成を変更するなどの余地もでてくるかもしれません。

下記は、『High Performance MySQL, 4th Edition Book』にあるAUTO_INCREMENT監視のSQLです。下記を利用することで、現状の値が最大値に対してどれくらいの利用率なのかを事前に観測することが可能ですので、参考にしてみてください。

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_auto_increment_columnsという便利なviewが準備されています。sysスキーマへの実行権限がある場合は、こちらでもauto_increment_ratioカラムで使用率が確認できます。schema_auto_increment_columnsについては、公式ドキュメントにも記載があるのでご覧ください。

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_INCREMENTを設定していた場合はどれくらいで使い切ってしまうのか、という視点でお話しました。実際に使いきってましまうことはまれにあるため、事前に検知して対応をとれるように監視を入れたり、テーブル作成をするときにデータの増加量なども意識して設計をしていく必要があります。