Zenn
⛳

TiDBのAUTO_INCREMENTは２種類ある

2023/12/20に公開
Database
Advent calendar
NewSQL
TiDB
tech

この記事は、TiDB Advent Calendar 2023 20日目の記事です。

TiDBはMySQLでよく使われているAUTO_INCREMENTをサポートしていますが、このAUTO_INCREMENTは２種類あります。１つはTiDBがデフォルトで提供しているものと、２つ目はMySQL互換のAUTO_INCREMENTです。この記事では、この２つについてそれぞれ解説してみたいと思います。

なお、この記事では執筆時点で最新の安定版であるv7.5.0のTiDBを前提としています。

TiDBデフォルトのAUTO_INCREMENT

TiDBでAUTO_INCREMENTを使いたいとき、CREATE TABLE文では以下のように記述します。

CREATE TABLE t(id int PRIMARY KEY AUTO_INCREMENT, c int);

そりゃそうだという感じがしますが、書き方はMySQLと同じです。ですが、採番の仕方はMySQLとは異なります。MySQLの場合、AUTO_INCREMENTは１つずつ増えます。一方で、TiDBの場合、デフォルトのAUTO_INCREMENTでは違います。実際に見てみましょう。

まず、tiup playgroundでPlaygroundクラスタを起動します。このとき、複数のTiDBノードが起動するように--db 2というオプションをつけて、TiDBノード（厳密に言えばプロセスですが、ここではあえて"ノード"と表現します）が２つになるようにします。

$ tiup playground --db 2
...
Waiting for tidb instances ready
127.0.0.1:4000 ... Done
127.0.0.1:4001 ... Done

🎉 TiDB Playground Cluster is started, enjoy!

Connect TiDB:    mysql --comments --host 127.0.0.1 --port 4001 -u root
Connect TiDB:    mysql --comments --host 127.0.0.1 --port 4000 -u root
...

TiDBノードが２台稼働しているPlaygroundクラスタが起動しました。それでは実際に試してみましょう。

-- TiDB:4000にログイン
tidb:4000 > create table t (id bigint primary key auto_increment);
Query OK, 0 rows affected (0.10 sec)

tidb:4000 > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

つぎに値を自動採番で挿入します。

tidb:4000 > insert into t values ();
Query OK, 1 row affected (0.01 sec)

tidb:4000 > select * from t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

予想通りですね。では改めてテーブル定義を確認してみます。

tidb:4000 > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.00 sec)

テーブルオプションにAUTO_INCREMENT=30001が追加されています。これはどういう意味でしょうか。MySQLの場合、テーブルオプションのAUTO_INCREMENTは次に自動採番される値を示すものです。そう考えてみると、明らかに値が大きすぎますね。先ほどのINSERT文をもう１度実行してみます。期待としては、2が入ってほしいですね。

tidb:4000 > insert into t values ();
Query OK, 1 row affected (0.00 sec)

tidb:4000 > select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

期待通りの値が挿入されました。では先ほどのAUTO_INCREMENT=30001は一体何でしょうか。これを説明するためには、TiDBにおけるAUTO_INCREMENTの仕組みを知る必要があります。

TiDBのAUTO_INCREMENTの仕組み

TiDBは、AUTO_INCREMENTが特定の列に設定されると、一定の範囲で値を各TiDBノードにキャッシュするようになります。デフォルトでは30,000ずつです。例えば、TiDBノードが３台あれば、1台目は1-30000、2台目は30001-60000、3台目は60001-90000をそれぞれキャッシュします。

TiDBは分散DBであり、クラスタを構成する各ノードにはそれぞれ役割がありますが、TiDBノードはクライアントから受け取ったSQLのパースやコンパイルを行う役割を担っています。TiDBノードがINSERT文を受け取り、処理対象のテーブルの中にAUTO_INCREMENTが設定された列があると、自身の中にキャッシュされている値を使って採番します。中央集権的に都度自動採番を行うよりも性能面で有利なやり方ですね。

話を先ほどのテーブルオプションのAUTO_INCREMENTに戻すと、ここに表示されている値は、次にキャッシュされる範囲の先頭の値ということになります。なお、最初のSHOW CREATE TABLEでAUTO_INCREMENTが表示されていなかったのは値がキャッシュされていなかったためです。

複数のTiDBノードがある場合

先ほど、AUTO_INCREMENTの値は各TiDBノードにキャッシュされると説明しました。ここで、Playgroundクラスタのもう１つのTiDBノードから採番するとどうなるかを見てみましょう。

-- TiDB:4001からINSERTを実行する
tidb:4001 > insert into t values ();
Query OK, 1 row affected (0.01 sec)

tidb:4001 > select * from t;
+-------+
| id    |
+-------+
|     1 |
|     2 |
| 30001 |
+-------+
3 rows in set (0.00 sec)

tidb:4001 > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=60001
1 row in set (0.01 sec)

2台目のTiDBノードに30001-60000までがキャッシュされて30001が挿入されました。そして、SHOW CREATE TABLE tのAUTO_INCREMENTには次のキャッシュ候補の先頭の値である60001が表示されるようになりました。

このように、それぞれ異なる値をキャッシュしているTiDBノードによって採番されるため、AUTO_INCREMENTが設定された列は必ずしも１つずつインクリメントするとは限らないのです。もしサービスがAUTO_INCREMENTの自動採番に依存しており、それが１つずつインクリメントされていることが期待されている場合は、つぎに紹介するMySQL互換のAUTO_INCREMENTを使うようにしましょう。

Tips1: 次に採番される値を知りたいときは？

これまで説明したように、TiDBでは必ずしも１つずつインクリメントされるとは限りません。では、もし次に採番される値を知りたいときはどうすれば良いでしょうか？MySQLであれば、SHOW CREATE TABLEで確認できますが、TiDBでその方法を使うことはできません。そんなときはINFORMATION_SCHEMAのTABLESの中にあるAUTO_INCREMENT列を参照すると良いでしょう[1]。

tidb:4000 > SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              3 |
+----------------+
1 row in set (0.01 sec)

このようにすれば、値が歯抜けになったとしても次の採番される値を知ることができます。

注意点があります。AUTO_INCREMENTの値は各TiDBノードにキャッシュされているので、このSQLで知ることができる値は、このSQLを実行した（別の言い方をすると、そのときに接続している）TiDBノードが次に採番する値である、ということです。異なるTiDBノードでこのSQLを実行すれば、当然に異なる値が出てきます。

tidb:4001 > SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't';
+----------------+
| AUTO_INCREMENT |
+----------------+
|          30002 |
+----------------+
1 row in set (0.04 sec)

このSQLを使って次に採番される値を知りたい場合は、どのTiDBノードで実行しているかということを強く意識するようにしましょう。

Tips2: あるTiDBノードがキャッシュしている値を別のTiDBノードで使ってしまったら？

MySQLと同様に、AUTO_INCREMENTが設定されている列に明示的に値を設定することは妨げられません[2]。もし、あるTiDBノードがキャッシュしている値を別のTiDBノードで使ってしまったら、どうなるでしょうか。試してみましょう。

-- "30002"はTiDB:4001でキャッシュされていて、次に採番される値
tidb:4000 > insert into t values (30002);
Query OK, 1 row affected (0.02 sec)

tidb:4000 > select * from t;
+-------+
| id    |
+-------+
|     1 |
|     2 |
| 30001 |
| 30002 |
+-------+
4 rows in set (0.00 sec)

-- TiDB:4001でINSERTを実施。前回のINSERTでは"30001"を払い出した
tidb:4001 > insert into t values ();
ERROR 1062 (23000): Duplicate entry '30002' for key 't.PRIMARY'

そりゃそうだという感じですが、このことから言えるのは、TiDBノード間ではキャッシュされている値を互いに気にしていないということです。

ところで、TiDB:4000が次に採番する値はいくつになるのでしょうか。見てみましょう。

tidb:4000 > SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't';
+----------------+
| AUTO_INCREMENT |
+----------------+
|          60001 |
+----------------+
1 row in set (0.02 sec)

なんと番号は大きくスキップして60001となりました。つまり、値を明示的に設定すると、直近で挿入した値をインクリメントした値が自分の中にキャッシュされていなければ、現在のキャッシュを破棄して次のキャッシュを取得する、という動きをしていることになります。

ということは、SHOW CREATE TABLEのAUTO_INCREMENTの値が更新されているはずです。見てみましょう。

tidb:4000 > show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=90001
1 row in set (0.01 sec)

やはり更新されていました。このことから、TiDB:4000ノードは1-30000までのキャッシュを破棄して、（30001-60000まではTiDB:40001でキャッシュされているので）新たに60001-90000までをキャッシュしていることになります。

以上のことから、TiDBでAUTO_INCREMENTが設定されている列に値を挿入すると、ややこしいことになりますし、スキップされた値は二度と使われることはありませんのでMySQL以上に番号を消費してしまうことになります。なるべくなら避けるようにしたほうが良いでしょう[3] 。更にいうと、キャッシュされている値はTiDBノードが再起動すると消えてしまい、二度と使われることはありません。なので、TiDBを利用するサービスにおいて、AUTO_INCREMENTに依存する場合は注意が必要です。

MySQL互換のAUTO_INCREMENT

もう１つのTiDBのAUTO_INCREMENTとして、MySQL互換のAUTO_INCREMENTがあります。つまり１つずつインクリメントすることを保証するAUTO_INCREMENTです。実はMySQL互換のAUTO_INCREMENTはv6.4.0で仕様が変わっています。まずは、v6.4.0以前と以後での違いを確認しましょう。

v6.4.0以前

デフォルトのAUTO_INCREMENTについて解説する際に、キャッシュされる値の範囲が30,000であると述べました。この30000という幅はTiDBの独自拡張であるAUTO_ID_CACHEというテーブルオプションのデフォルト値です。v6.4.0以前では、AUTO_ID_CACHEを１にすることでMySQL互換のAUTO_INCREMENTを実現していました[4]。AUTO_ID_CACHEを１にするということは、TiDBノードは値をキャッシュせずに常に新しい値を調達することを意味します。ところで、この値はどこから調達されているのでしょうか。実は、TiDBノードがキャッシュしている値はTiKVノードから払い出されています。つまり、値を払い出すたびにTiKVではトランザクションが走っています。そのため、v6.4.0以前にMySQL互換のAUTO_INCREMENTを使うことは、採番の度にTiKVのトランザクションが走ることになり、書き込み負荷が高い環境では性能面では不利でした。

v6.4.0以後

AUTO_ID_CACHEを１にすることでMySQL互換のAUTO_INCREMENTとなる点は変わっていませんが、値の払い出し方法が大きく変わりました。TiDBノードの中から１台がリーダーとして選ばれて、リーダーが値を集中的に管理するようになり、そこから他のTiDBノードへ払い出すようになりました。リーダーは一定の範囲で値をメモリ上にキャッシュしているため、v6.4.0以前に比べて値を払い出すオーバーヘッドが大きく改善されました。注意点としては、リーダーとなっているTiDBノードがクラッシュするなどしてダウンした場合、次のリーダーノードが払い出す値は直近で払い出された値とは連続せず、多少スキップしてしまうことです。これはフェイルオーバーしたTiDBノードが安全に値のユニーク性を確実にして値を払い出すようにするためです。必ずしもモノトニックになるとは限りませんが、このあたりは、基本的に書き込みを１台で行うMySQLに対して、複数のサーバーで書き込みを行う分散データベースの違いによるものと言えそうです。

デフォルトのAUTO_INCREMENTとの違い

MySQL互換の動作については特にみる必要はないでしょう。見た目上の動作はMySQLとほぼ同じです。

さて、これだけだと話が終わってしまうので、デフォルトのAUTO_INCREMENTと違う点を見ていきましょう。

まず、　違いの１つ目は次に採番される値はINFORMATION_SCHEMA.TABLESのAUTO_INCREMENT列を見てもわかりません。自動採番されている限り、AUTO_INCREMENT列はNULLとなります。ただし、もしINSERT文で値を明示的に設定すると、その値に+1した値がINFORMATION_SCHEMA.TABLESのAUTO_INCREMENT列に挿入されます。 (2024/01/29編集: こちらの記述は誤りでした。AUTO_RANDOMの場合にAUTO_INCREMENT列はNULLとなります)　次に採番される値を知りたいときはSHOW CREATE TABLEのAUTO_INCREMENTを見ると良いでしょう。

違いの２つ目は性能面です。"v6.4.0以前"の項目でも触れていますが、デフォルトのAUTO_INCREMENTはTiKVから各TiDBノードに対して値が一定の範囲で払い出されて、それぞれのTiDBノードが自身のメモリ上にキャッシュし、そこから採番を行っています。TiKVノード上でのトランザクションや通信が発生することによるオーバーヘッドがあるといっても、十分に広い範囲で値が払い出されていれば、このオーバーヘッドは無視できる程度の誤差です（もちろんネットワーク環境など他の要因を考慮する必要はあります）。MySQL互換の場合、大きく改善されてはいますが、TiDBノード間での通信が都度発生しており、書き込み負荷が高いと、このオーバーヘッドが相対的に性能面に影響しやすいです。そのため、性能にシビアな場合はMySQL互換よりはデフォルトのAUTO_INCREMENTのほうが良いでしょう[5]。

まとめ

いかがでしたか？この記事では、TiDBが提供する２種類のAUTO_INCREMENTについて説明しました。簡単にまとめるとこんな感じです。

  • TiDBのデフォルトのAUTO_INCREMENTはTiDBノードごとに値をキャッシュしており、採番は必ずしもモノトニックにはならない。MySQL互換と比べて性能面で有利。
  • MySQL互換のAUTO_INCREMENTは複数のTiDBノードがあっても値が１つずつインクリメントされるようになる。デフォルトのAUTO_INCREMENTに比べて性能面で不利。

何気なく使っているAUTO_INCREMENTですが、分散データベースの特性が見え隠れして面白いですよね。

脚注

  1. もったいぶった言い回しをしていますが、これはMySQLでも同様です。 ↩︎

  2. ちなみに、TiDBのAUTO_RANDOMの場合はallow_auto_random_explicit_insertというシステム変数を利用して明示的に値を設定することを拒否できます。 ↩︎

  3. 既存DBからデータをインポートする際には特に注意が必要です。このあたりのことはどこかでまた説明する予定です。 ↩︎

  4. ゼロにするとデフォルトの30,000になります。 ↩︎

  5. 高い書き込み性能を求める場合、AUTO_RANDOMのほうが良い選択となります。詳細は https://docs.pingcap.com/tidb/stable/high-concurrency-best-practices を御覧ください。 ↩︎

Discussion