🐥

Spanner の Identity Column と AUTO_INCREMENT の比較

2025/01/31に公開

2025年1月30日に Spanner に SERIAL(PostgreSQL interface) / AUTO_INCREMENT (GoogleSQL)が追加されました。(この記事では PostgreSQL interface については触れません。)

Spanner supports new SERIAL and AUTO_INCREMENT DDL syntax. SERIAL is available in PostgreSQL-dialect databases and AUTO_INCREMENT is available in GoogleSQL. They streamline the ability to generate IDENTITY columns as primary keys. For more information, see SERIAL and AUTO_INCREMENT.

この記事ではドキュメントを読んでもわかりづらいことも含め AUTO_INCREMENT を触ってわかることを多少説明します。

使い方

AUTO_INCREMENT には従来の Sequence や Identity Columns のような設定がないため、あらかじめ ALTER DATABASE でデータベースレベルの default_sequence_kind = 'bit_reversed_positive' オプションを設定するのが必須です。

spanner> ALTER DATABASE `sampledb` SET OPTIONS (default_sequence_kind = 'bit_reversed_positive');

AUTO_INCREMENT を指定できる場所は NOT NULL とインラインの PRIMARY KEY の間です。AUTO_INCREMENT も含め全て任意です。

spanner> CREATE TABLE AutoIncrementTable(
           PK INT64 NOT NULL AUTO_INCREMENT PRIMARY KEY,
           Col INT64 AUTO_INCREMENT
         );

値を指定せずに行を挿入すると、短調増加値を ビット反転 した値が自動的に割り当てられます。ビット反転されている理由については Cloud Spanner の自動生成主キーを使ってみる などで解説されている通り、書き込み負荷を分散不可能な短調増加値によるホットスポットを避けるためです。

spanner> INSERT AutoIncrementTable(PK) VALUES(DEFAULT), (DEFAULT)
         THEN RETURN
           PK, BIT_REVERSE(PK, TRUE) AS ReversedPK,
           Col, BIT_REVERSE(Col, TRUE) AS ReversedCol;
+---------------------+------------+---------------------+-------------+
| PK                  | ReversedPK | Col                 | ReversedCol |
| INT64               | INT64      | INT64               | INT64       |
+---------------------+------------+---------------------+-------------+
| 4611686018427387904 | 2          | 4611686018427387904 | 2           |
| 1152921504606846976 | 8          | 1152921504606846976 | 8           |
+---------------------+------------+---------------------+-------------+
Query OK, 2 rows affected (0.71 sec)

ドキュメントにも書かれているように、 AUTO_INCREMENTGENERATED BY DEFAULT AS IDENTITY の最も単純な形の略記法に過ぎません。
なので、 GetDatabaseDdl API 等でテーブルの DDL を取得すると AUTO_INCREMENTGENERATED BY DEFAULT AS IDENTITY として出力されます。
ここでは spanner-cli/spanner-mycli の SHOW CREATE TABLE <table> を使って DDL を出力しています。

spanner> SHOW CREATE TABLE AutoIncrementTable;
+--------------------+-------------------------------------------------------+
| Table              | Create Table                                          |
+--------------------+-------------------------------------------------------+
| AutoIncrementTable | CREATE TABLE AutoIncrementTable (                     |
|                    |   PK INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY, |
|                    |   Col INT64 GENERATED BY DEFAULT AS IDENTITY,         |
|                    | ) PRIMARY KEY(PK)                                     |
+--------------------+-------------------------------------------------------+
1 rows in set (1.86 sec)

これは、 AUTO_INCREMENT であっても ALTER IDENTITY の対象にすることが可能なことを意味します。

spanner> ALTER TABLE AutoIncrementTable ALTER COLUMN PK ALTER IDENTITY RESTART COUNTER WITH 3000;

INFORMATION_SCHEMA.COLUMNS における内部的な管理方法

2025-01-31 現在ドキュメントには反映されていませんが、 INFORMATION_SCHEMA.COLUMNS に次のようなカラムが追加されています。

spanner> SELECT TABLE_NAME, IS_IDENTITY, IDENTITY_GENERATION, IDENTITY_KIND, IDENTITY_START_WITH_COUNTER, IDENTITY_SKIP_RANGE_MIN, IDENTITY_SKIP_RANGE_MAX
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME="AutoIncrementTable";
+--------------------+-------------+---------------------+---------------+-----------------------------+-------------------------+-------------------------+
| TABLE_NAME         | IS_IDENTITY | IDENTITY_GENERATION | IDENTITY_KIND | IDENTITY_START_WITH_COUNTER | IDENTITY_SKIP_RANGE_MIN | IDENTITY_SKIP_RANGE_MAX |
| STRING             | STRING      | STRING              | STRING        | STRING                      | STRING                  | STRING                  |
+--------------------+-------------+---------------------+---------------+-----------------------------+-------------------------+-------------------------+
| AutoIncrementTable | YES         | BY DEFAULT          | NULL          | 3000                        | NULL                    | NULL                    |
| AutoIncrementTable | YES         | BY DEFAULT          | NULL          | NULL                        | NULL                    | NULL                    |
+--------------------+-------------+---------------------+---------------+-----------------------------+-------------------------+-------------------------+
2 rows in set (28.48 msecs)

これは AUTO_INCREMENT だけでなく Identity Columns の設定値を保持するもので、下記のような情報が管理され、取得可能のようです。

name type 解釈
IS_IDENTITY STRING YES or NO
IDENTITY_GENERATION STRING NULL or BY_DEFAULT
IDENTITY_KIND STRING NULL or BIT_REVERSED_POSITIVE_SEQUENCE (database default の場合は NULL のまま)
IDENTITY_START_WITH_COUNTER STRING NULL or START WITH COUNTER (RESTART WITH COUNTER) の設定値
IDENTITY_SKIP_RANGE_MIN, IDENTITY_SKIP_RANGE_MAX STRING NULL or SKIP RANGE skip_range_min, skip_range_max の設定値

Identity Column と AUTO_INCREMENT, どちらを使うべきか

内部的には全く同じなのでどちらを使っても良いでしょう。

GENERATED BY DEFAULT AS IDENTITY は非常に長いですし、 最近可能になった PRIMARY KEY をカラム定義の中に書く記法を使う場合の可読性には差があるように思います。

PK INT64 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

ではなく

PK INT64 AUTO_INCREMENT PRIMARY KEY

の方が PRIMARY KEYAUTO_INCREMENT であると読みやすいでしょう。長いと PRIMARY KEY であることも見逃しかねません。

ただし、下記の DDL リファレンス内の構文を読めばわかるように、 AUTO_INCREMENT では sequence_option_clause が指定できないため、 CREATE TABLE 時に何らかの設定が必要であり、 ALTER IDENTITY を別途書くことを避けたいのであれば GENERATED BY DEFAULT AS IDENTITY を使った方が良いでしょう。

CREATE TABLE [ IF NOT EXISTS ] table_name ( [
   { column_name data_type [NOT NULL]
     [ { DEFAULT ( expression ) | AS ( expression ) [ STORED ]
       | GENERATED BY DEFAULT AS IDENTITY [ ( sequence_option_clause ... ) ]
       | AUTO_INCREMENT } ]
     [ PRIMARY KEY ]
...
and sequence_option_clause is:
    { BIT_REVERSED_POSITIVE
    | SKIP RANGE skip_range_min, skip_range_max
    | START COUNTER WITH start_with_counter }

まとめ

  • ドキュメントに書かれている通り、 AUTO_INCREMENT は Identity Column の別名でしかありません。
  • GetDatabaseDdl API 等で今のテーブルスキーマを出力すると、 AUTO_INCREMENTGENERATED BY DEFAULT AS IDENTITY として出力されます。
  • AUTO_INCREMENT PRIMARY KEY が読みやすいと思ったら AUTO_INCREMENT を使いましょう。

余談

cloud-spanner-emulator の repo を見ると、 Identity Column の内部情報を取得する GET_TABLE_COLUMN_IDENTITY_STATE というドキュメンテーションされていない関数があるようです。

spanner> SELECT GET_TABLE_COLUMN_IDENTITY_STATE('AutoIncrementTable.PK') AS PKState, GET_TABLE_COLUMN_IDENTITY_STATE('AutoIncrementTable.Col') AS ColState;
+---------+----------+
| PKState | ColState |
| INT64   | INT64    |
+---------+----------+
| 5998    | 2998     |
+---------+----------+
1 rows in set (7.76 msecs)

返す値の意味は明記されておらず、次に取得される値そのものではありませんが、 RESTART WITH COUNTER を設定すると進むことなどから、直近で採番される可能性がある最大値と可能性があることが示唆されます。
ドキュメントされていない以上何ら保証はありませんが、興味がある方は検証したり cloud-spanner-emulator のソースを読んでも良いかもしれません。

Discussion