TiDB で AUTO_INCREMENT を MySQL 互換の動作にする

下記公式ドキュメントに記載があるように TiDB の AUTO_INCREMENT の動作はデフォルトでは MySQL とは異なり中央集権的な ID 発行による 1, 2, 3, ... といったような単調増加の動作とならない。

パフォーマンス上の理由から、値のバッチ (デフォルトでは 30,000) でAUTO_INCREMENT番号が各 TiDBサーバーに割り当てられます。これは、 AUTO_INCREMENT数値が一意であることが保証されている一方で、 INSERTのステートメントに割り当てられた値は、TiDBサーバーごとに単調であることを意味します。 AUTO_INCREMENT | PingCAP Docs

デフォルトではクラスターの TiDB サーバーごとに値のバッチ番号が割り当てられ、それぞれのサーバー内で INSERT する際にその番号を起点とした単調増加の ID が発行される。またデフォルトで各サーバーごとのバッチ番号は 30,000 個確保される。つまり INSERT をしていると 1, 2, 30000, 3, 30001 といったような形で ID が払い出される動作となるケースがあるということとなる。また、TiDB サーバーが再起動されるとバッチ番号は再割り当てされるため抜け番が多くでる。

分散 DB による恩恵を受けるためにはそもそも AUTO_INCREMENT な ID 列を持たないほうがよいという話ではあるが、すでに存在するデータの ID が URL に使われるなど露出する場合は少し気になる部分ではある。

その際には MySQL 互換の動作とすることも可能だが、これは CREATE TABLE の際に指定しないといけない模様。少なくとも手元 TiDB 7.1.1 では ALTER TABLE で実施したところクエリが通らなかった。下記のように CREATE TABLE 時に AUTO_ID_CACHE 1 を指定する必要がある。

CREATE TABLE t(a int AUTO_INCREMENT key) AUTO_ID_CACHE 1;

仕方がないので一旦 mysqldump をとり、各 CREATE TABLE 文末尾の AUTO_INCREMENT=\d+; を AUTO_INCREMENT=1 AUTO_ID_CACHE 1; に置き換え、リストアすることにより MySQL 互換の動作を手にいれることができた。ちなみに外部キー制約を持っているテーブルがあったため、リストア前に下記を実行した。

SET foreign_key_checks = 0