TiDBのAutoincrementにAUTO_ID_CACHE 1;を設定しても再起動すると4001番が発番される件
発生した問題点
ドキュメントを読むと、
TiDB は、最小のギャップで ID が厳密に増加するようにする、自動増分列の MySQL 互換モードを提供します。このモードを有効にするには、テーブルの作成時にAUTO_ID_CACHEから1設定します。
CREATE TABLE t(a int AUTO_INCREMENT key) AUTO_ID_CACHE 1;
ということで設定をしたのだが、再起動するとなぜか4001番から採番されるされるという。
本家ドキュメントさらに読み込む
AUTO_INCREMENTキャッシュは TiDBサーバーの再起動後は保持されません。
再起動するとIDのキャッシュ分、連番が飛ぶ仕様とのこと。
何も設定しないとデフォルト3万なので大きく飛ぶ。
AUTO_ID_CACHEを設定すればその設定値分飛ぶはずで、AUTO_ID_CACHE=1設定で4000飛ぶのは納得いかないところ。
（AUTO_ID_CACHE=4000の設定で4000飛ぶなら納得できるが。。。）
ためしにAUTO_ID_CACHE=2でやってみたら再起動しても最大で2ぐらいしか飛ばないので良さそう
Node数分の確保なのでinsertされたNodeにより連番は飛ぶのですが、2ぐらいなら気にならないかと。
Node数=3で、2ずつ保持すると、下記となります。
Node1=1,2
Node2=3,4
Node3=5,6
どのNodeに挿入されるのか？によって最大で4ぐらい飛びそうだが4000飛ぶよりはマシとするか？
一応、AIに聞いてみたらTiDBは集中的に書き込んだ後にリバランスするらしいので、分散Nodeに均等にInsertしていく仕様ではないらしく、その点もよさそうかと。
（AIお得意の嘘でないと良いが。知っている人いたらコメントほしいです。）
テスト用の環境とSQL
tidbのplaygroundを動かすdockerfile
SQL
DROP TABLE aaa;
CREATE TABLE aaa(id int AUTO_INCREMENT key) AUTO_ID_CACHE 2;
INSERT INTO aaa VALUES ();
INSERT INTO aaa (id) VALUES
(null),(null),(null),(null),(null),
(null),(null),(null),(null),(null),
(null);
SELECT * FROM aaa order by id desc;
気になる人は、AUTO_ID_CACHEの値を1と2以上で変えてみて実行してみてほしい。
一応、TiDB Cloudでも同じ結果となったので、playground特有ではないはず。
おまけ（AI（o3-mini）に聞いてみました）
Q. tidbでAUTO_ID_CACHE 1;を設定して作ったテーブルでinsertして問題無い状況で、TiDBを再起動してからinsertすると番号が4001番から発番されます。そこまで大きく飛ぶことはないはずで、なぜですか？
A. TiDBでは、パフォーマンス向上のためにオートインクリメント値がキャッシュされます。
たとえAUTO_ID_CACHEを1に設定していても、TiDBは内部でキャッシュサイズ（多くの場合4000ずつ）の単位で自動IDを確保しており、再起動時に未使用のキャッシュ分は破棄されます。そのため、再起動後の最初のinsertで前回キャッシュされていたIDより大きな番号（例：4001）から発番される挙動になります。これは意図した設計であり、データの一意性や並行性を担保するためのものです。
Q. また、AUTO_ID_CACHE 2;を設定して同じ動作をすると、4001番で発番されません。それはなぜですか？
A. TiDB は内部的に ID を事前確保する仕組み（バッチ分配）を採用しています。
実は AUTO_ID_CACHE=1 などの非常に小さい値を指定すると、TiDB はキャッシュを無効化した状態と同等に扱い、内部のバッチ分配サイズ（通常は約 4000 単位）で自動 ID を確保します。
一方、AUTO_ID_CACHE=2 のように 1 より大きい値を指定すると、明示的にキャッシュを有効化した状態となり、指定した値分（この場合は 2 個）だけ ID をキャッシュするため、再起動後に大きく飛ばずに連続値が発番されます。
Q. TiDBが3つNodeで起動していて、Aテーブルの中身が、各Nodeに分散して配置された状態で、insertをしていくと各Nodeに均等にinsertするのですか？
A. TiDBでは、データの格納先はTiKVによりKeyレンジ（リージョン）に分割され、そのリージョンが各TiKVノードに配置されます。
自動採番（auto_increment）の場合、新しいレコードのキーは連続したレンジに属するため、最初は特定のリージョン（場合によっては単一ノード）に集中します。
ただし、TiDBはリージョンのスプリットやリージョン再配置（rebalance）を自動で実施するため、長期的には負荷分散されることが期待されます。
つまり、insert時に均等にinsertされるというよりは、キーの連続性の影響により初期は偏りが生じる可能性があり、その後TiDBが内部でデータの再配置を行う形になります。
Q. 特定のNodeにデータが集中的に入ってから、あとで再配置する処理フローって感じかな？
A. はい、その通りです。初期のinsertでは連続したキーが特定のリージョン（結果的に特定のNode）に集中する場合があり、
その後、TiDB/TiKVが自動でリージョンの再分割や再配置を行い、データを負荷分散します。
別案:シーケンステーブル
シーケンステーブルを作って、そちらで発番管理すればとりあえず連番は維持できそう。
PerplexityAIに、「MySQLで連番を発行するシーケンステーブルのDDLとクエリを作って。」と聞いて作ってもらったのは下記。
DROP TABLE sequence_table;
CREATE TABLE sequence_table (
id INT UNSIGNED NOT NULL PRIMARY KEY,
sequence_name VARCHAR(50) NOT NULL UNIQUE,
current_value BIGINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO sequence_table (id, sequence_name, current_value)
VALUES (1, 'your_sequence_name', 0);
--- ここから下をトランザクションを使って実行するイメージ
UPDATE sequence_table
SET current_value = LAST_INSERT_ID(current_value + 1)
WHERE sequence_name = 'your_sequence_name';
SELECT LAST_INSERT_ID() AS next_value;
高負荷なサービスではパフォーマンスが悪そうだが、そこまででないなら採用もありかと。
そもそもIDに連番使わなければよいのでは？
一部のデータは外部に連番で露出したいという要望のため、そこだけでも連番にしたいというのが発端でした。
最後に
普通のRDBMSで連番振るのは簡単に実現できますが、分散環境になると大変になりますね💦
他にも分散ならではの動作や、各種DB特有の仕様だったりあるので、DBはある程度固定でいきたいところですね。
Discussion