Spanner の Identity Column と AUTO_INCREMENT の比較
2025年1月30日に Spanner に SERIAL
(PostgreSQL interface) / AUTO_INCREMENT
(GoogleSQL)が追加されました。(この記事では PostgreSQL interface については触れません。)
Spanner supports new
SERIAL
andAUTO_INCREMENT
DDL syntax.SERIAL
is available in PostgreSQL-dialect databases andAUTO_INCREMENT
is available in GoogleSQL. They streamline the ability to generateIDENTITY
columns as primary keys. For more information, seeSERIAL
andAUTO_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_INCREMENT
は GENERATED BY DEFAULT AS IDENTITY
の最も単純な形の略記法に過ぎません。
なので、 GetDatabaseDdl
API 等でテーブルの DDL を取得すると AUTO_INCREMENT
は GENERATED 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 の設定値 |
AUTO_INCREMENT
, どちらを使うべきか
Identity Column と 内部的には全く同じなのでどちらを使っても良いでしょう。
GENERATED BY DEFAULT AS IDENTITY
は非常に長いですし、 最近可能になった PRIMARY KEY
をカラム定義の中に書く記法を使う場合の可読性には差があるように思います。
PK INT64 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
ではなく
PK INT64 AUTO_INCREMENT PRIMARY KEY
の方が PRIMARY KEY
が AUTO_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_INCREMENT
はGENERATED 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