MySQL 26.7では、InnoDBのAUTO_INCREMENTカウンターをデフォルトで事前確保するようになりました。追加されたinnodb_autoinc_preallocateのデフォルト値は50で、設定を変更しなくても、先の値までまとめて確保してカウンターの保存回数を減らします。その代わり、INSERTをすべてcommitしたあとでも異常終了をきっかけに未使用の予約分が飛ばされ、欠番が発生する場合があります。

今回はMySQL 26.7.0と9.7.0で挙動を比較し、この変更の利点と注意点を確認します。

カウンターを事前確保する理由

InnoDBはテーブルごとにメモリ上のカウンターを持ち、再起動後も採番を続けられるよう、その値を永続化しています。

MySQL 9.7のマニュアルでは、カウンターが変わるたびにredo logへ記録し、checkpointでデータディクショナリに保存します。復旧時は保存済みの値とredo logを使います。INSERTが多いほど採番に伴う記録も増えるため、この負担を減らす余地がありました。

MySQL 26.7のマニュアルでは、現在のカウンターが保存済みの値を超えたときに、設定した事前確保数を加えて保存します。たとえば51まで保存しておけば、その範囲内で採番する間は永続化のメタデータ更新を繰り返さずに済みます。

公式マニュアルは、事前確保数を増やすと永続化の更新頻度が下がると説明しています。ソースコードの変数説明にも、設定値を大きくすると採番が速くなる旨が記載されています。INSERTが多い処理で、採番に伴う保存処理を効率化できるのが利点です。

事前確保するのはカウンターで、行の保存とcommitは通常どおりです。稼働中はメモリ上のカウンターを使うため、予約しただけでIDが飛ぶことはありません。

innodb_autoinc_preallocateについて

innodb_autoinc_preallocateは実行中に変更できるグローバル変数で、0にすると事前確保を無効にできます。SMALLINTやTINYINTなど、2バイト以下の整数型は対象外です。設定値は次のSQLで確認できます。

SHOW GLOBAL VARIABLES LIKE 'innodb_autoinc_preallocate';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_autoinc_preallocate | 50    |
+----------------------------+-------+

異常終了後は保存済みの予約値からカウンターを復元するため、未使用の値が飛ばされる場合があります。停止時にそのテーブルを使うトランザクションがなくても起こります。

正常終了では実際のカウンターを保存し、予約分による欠番は生じません。以下では、未commitのINSERTがない状態で違いを確認します。

テーブルの準備

auto_increment_incrementとauto_increment_offsetは1にしています。

デフォルトと同じ事前確保50をSET PERSISTで明示し、比較する条件ごとにテーブルを作り直します。

SET GLOBAL innodb_fast_shutdown = 1;
SET PERSIST innodb_autoinc_preallocate = 50;
SET SESSION auto_increment_increment = 1;
SET SESSION auto_increment_offset = 1;
SET SESSION autocommit = 1;
CREATE DATABASE IF NOT EXISTS autoinc_lab;
USE autoinc_lab;
DROP TABLE IF EXISTS autoinc_test;
CREATE TABLE autoinc_test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  memo VARCHAR(32) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO autoinc_test(memo) VALUES ('row-1');
INSERT INTO autoinc_test(memo) VALUES ('row-2');
INSERT INTO autoinc_test(memo) VALUES ('row-3');
SELECT COUNT(*) AS row_count, MAX(id) AS max_id FROM autoinc_test;
SELECT * FROM autoinc_test ORDER BY id;

最後の2つのSELECTの結果は次のとおりです。

+-----------+--------+
| row_count | max_id |
+-----------+--------+
|         3 |      3 |
+-----------+--------+

+----+-------+
| id | memo  |
+----+-------+
|  1 | row-1 |
|  2 | row-2 |
|  3 | row-3 |
+----+-------+

IDは1、2、3になりました。autocommitが有効なので、各INSERTはcommit済みです。

正常終了した場合

次のSQLでmysqldを正常終了します。

SHUTDOWN;

起動し直して行数と最大IDを確認し、1行追加します。

USE autoinc_lab;
SET SESSION auto_increment_increment = 1;
SET SESSION auto_increment_offset = 1;
SET SESSION autocommit = 1;
SELECT COUNT(*) AS row_count, MAX(id) AS max_id FROM autoinc_test;
SET @max_id_before_insert = (SELECT MAX(id) FROM autoinc_test);
INSERT INTO autoinc_test(memo) VALUES ('after restart');
SELECT LAST_INSERT_ID() AS assigned_id,
       LAST_INSERT_ID() - @max_id_before_insert - 1 AS skipped_ids;
SELECT * FROM autoinc_test ORDER BY id;

停止前の3行が残り、追加INSERTの結果は次のようになりました。

+-------------+-------------+
| assigned_id | skipped_ids |
+-------------+-------------+
|           4 |           0 |
+-------------+-------------+

+----+---------------+
| id | memo          |
+----+---------------+
|  1 | row-1         |
|  2 | row-2         |
|  3 | row-3         |
|  4 | after restart |
+----+---------------+

次のIDは4で、欠番はありません。innodb_fast_shutdownを0、1、2にした場合も同じ結果でした。設定値2でも、SQLのSHUTDOWNとSIGKILLによる強制終了では結果が異なります。

異常終了した場合

テーブルを作り直して同じ3行をINSERTします。今度はmysqldへSIGKILLを送り、強制終了します。

再起動直後は行数3、最大ID3でしたが、追加INSERTの結果は変わりました。

+-------------+-------------+
| assigned_id | skipped_ids |
+-------------+-------------+
|          52 |          48 |
+-------------+-------------+

次のIDは52で、48個の値が使われないままになりました。SELECT * FROM autoinc_test ORDER BY id;で見ると、元の3行は残っています。

+----+---------------+
| id | memo          |
+----+---------------+
|  1 | row-1         |
|  2 | row-2         |
|  3 | row-3         |
| 52 | after restart |
+----+---------------+

すべてcommit済みで、INSERTの失敗やrollbackがなくても、この結果になります。同じ条件で3回試し、すべて次のIDは52でした。

欠番数は予約範囲の使用状況で変わります。ID 51まで使ってから同じ強制終了を試すと、次のIDは52で欠番はありませんでした。デフォルト50でも、毎回50個の欠番ができるわけではありません。

マニュアルでは、設定値をN、auto_increment_incrementをYとすると、事前確保による欠番の大きさはmax(N, Y)以下とされています。他の操作による欠番まで、この範囲に収まるという意味ではありません。

事前確保を無効にした場合

設定を0にし、テーブルを作り直して同じ異常終了の手順を繰り返します。それまでの予約分を引き継がない条件で比較します。

SET PERSIST innodb_autoinc_preallocate = 0;

ID 1〜3をcommitしてから強制終了し、再起動後に1行追加すると、次の結果になりました。

+-------------+-------------+
| assigned_id | skipped_ids |
+-------------+-------------+
|           4 |           0 |
+-------------+-------------+

SELECT * FROM autoinc_test ORDER BY id;の結果もID 1〜4になりました。

+----+---------------+
| id | memo          |
+----+---------------+
|  1 | row-1         |
|  2 | row-2         |
|  3 | row-3         |
|  4 | after restart |
+----+---------------+

次のIDは4で、事前確保による欠番はありません。9.7.0でも次のIDは4でした。9.7.0にはこの変数がないため、設定は省いています。

3行をINSERTした条件の結果をまとめると、次のようになります。

設定500では次のIDが502に進み、対象外のSMALLINT UNSIGNEDでは4になりました。

AUTO_INCREMENTに連番を期待しない

AUTO_INCREMENTは、欠番のない連番を保証する仕組みではありません。rollbackなどでも欠番が生じるため、事前確保を0にしても連番の保証にはなりません。26.7のリリースノートも、アプリケーションは欠番のない採番に依存すべきではないと説明しています。

行の識別子として使い、欠番を許容する設計なら、欠番自体は通常問題になりません。問題になるのは、番号の連続性を前提にした処理です。

件数は実際の行を数え、取得処理は次に存在する行を検索します。失敗の判断にはSQLのエラーや処理記録を使います。欠番を許さない業務番号が必要なら、識別用IDとは別に採番を設計します。26.7への移行を機に、集計・取得・監視がIDの連続性に依存していないか確認するとよいと思います。

利用するときの注意点

設定値は、カウンター保存の負担と、異常終了時に飛ばされる値の大きさを考えて選びます。値を大きくするとメタデータ更新の頻度を減らせますが、未使用のまま飛ばされる範囲も大きくなり得ます。今回の3行の例でも、設定50では48個、500では498個の欠番が生じました。デフォルトの50を起点に、調整する場合は実際のINSERT負荷に近い条件で、処理件数や応答時間の変化を確認するとよいと思います。

事前確保を0にすると、今回の仕組みによる欠番は防げます。ただし、ソースコードの変数説明によると、26.7では0にすると採番のたびにmysql.innodb_dynamic_metadataを更新します。9.7の「カウンター変更をredo logへ記録する方式」に戻るわけではなく、INSERTが多い処理では以前のバージョンより遅くなる可能性があります。欠番を減らすために0にする場合も、書き込み性能への影響を確認します。

事前確保によって整数型の上限が増えることもありません。異常終了で未使用の値が飛ばされると、行の件数以上にIDの範囲を消費します。大きな設定値を選ぶ場合は、IDの増加ペースと型の上限までの余裕も確認します。

まとめ

MySQL 26.7では、デフォルトでカウンターの保存回数を減らす一方、異常終了後に予約分が飛ばされる場合があります。今回も、commit済みの3行が残ったまま次のIDが52になりました。

IDが飛んだ場合は、サーバーの終了方法と事前確保の設定も確認します。欠番の数をデータ消失の件数とはみなさず、実際に保存された行と合わせて判断するべきでしょう。

参考資料

• Changes in MySQL 26.7.0
• AUTO_INCREMENT Handling in InnoDB — MySQL 26.7
• innodb_autoinc_preallocate
• AUTO_INCREMENT Handling in InnoDB — MySQL 9.7
• ha_innodb.cc — MySQL 26.7.0