GIPKことはじめ - Generated Invisible Primary Keys

※この記事はMySQL Advent Calendar 2023の4日目です。

MySQL 8.0シリーズでは正式版になってから多数の新機能が追加されるというリリースモデルであった。正式版になってから追加された新機能の中に、GIPK（Generated Invisible Primary Key）というものがある。これはなんとMySQL 8.0.30で追加された機能だ。MySQL 8.0が正式版になってから、なんと4年と3ヶ月後のことである。そんな感じでMySQL 8.0の新機能は正式リリース後にも増え続け、途方もない規模になっている。このブログでは一度に紹介するのは諦め、少しずつ気の向いたものから紹介していこうと思う。今回はその第一弾として、GIPKについて解説しよう。

GIPKとは

GIPKとは、その名前が示すように、自動的に生成される不可視の主キーである。主キーを持たないテーブルに対してのみ生成される。見えないと言っても、SHOW CREATE TABLEでは確認ができるのだが、クエリからはカラム名を直接指定されない限り見えない。見えないようにするために、主キーを構成するカラムにはINVISIBLEのキーワードが付与されている。実は、不可視のカラムというものがMySQL 8.0.23で実装されている。（これもまた新機能だ！）それを利用して、ユーザーからは通常は見えない主キーを作成するという機能がGIPKというワケだ。

不可視のカラム（Invisible Column）は、SELECT *では参照できない。値を参照するにはカラム名を明示的に指定する必要がある。WHERE句などでもカラム名を指定すれば参照は可能ではある。GIPKを構成するカラムはmy_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLEとして定義される。なのでmy_row_idというカラムを明示的にSELECTリストで指定すると、カラムの値を取得することができるというワケだ。

GIPKの意義

なぜGIPKのようなものが必要だったかというと、大ざっぱに言うと2つの理由がある。

1つ目はInnoDBのROWIDの実装上の問題だ。InnoDBは主キーがないテーブルを作成すると、内部的に6バイトのROWIDカラムを作成するが、このカラムの値は外部からクエリなどを用いてアクセスすることができない。また、InnoDBのROWIDはInnoDB固有の実装でもある。他のRDBMSにおけるROWIDに相当するものを、ストレージエンジンに依存せず使えるようにしたのがGIPKだと言える。また、InnoDBのROWIDはテーブルごとではなく、インスタンス全体で固有の値が割り振られる。そのため、2^48個のROWIDが発行されるとオーバーフローしてしまうし、IDを発行するときの排他処理も問題になってしまう。ちなみに、ROWIDオーバーフローしてしまうと非常にまずいことになる。現状の計算パワーでは2^48個のROWIDを使い切ってしまう心配はしなくても済みそうだが、将来的にはこのままではまずい。（毎秒1億行をINSERTすると32日ほどでROWIDは枯渇するが、今のところそんなにINSERTは速くない。）

2つ目はレプリケーションでの利用だ。行ベースレプリケーションは主キーがないテーブルの扱いが苦手である。行ベースレプリケーションでは更新された行ごとに、更新前の行データと、更新後の行データをバイナリログに記録するのだが、対象のテーブルに主キーがないとレプリカ側は更新対象の行を探すのにテーブルをスキャンする必要がある。GIPKがあれば、不可視といえ間違いなく主キーなので、レプリカが行を探すのは格段に効率的になる。また、グループレプリケーションではすべてのテーブルがInnoDBでなおかつ主キーを持っている必要があるという制約がある。従来の主キーがないテーブルはグループレプリケーションでは利用できないが、GIPKを利用すればこの要件はパスできる。そんなワケでGIPKは行ベースレプリケーションまたはグループレプリケーションと、主キーがないテーブルを組み合わせたい場合には嬉しい機能となっている。

GIPKは現時点ではユーザーが意図的に利用する機能となっている。将来のバージョンではデフォルトでONになるかも知れないが、現状はそうではないので留意しておいて欲しい。

GIPKのついたテーブルを作成する

ではGIPKの使い方をかんたんに説明しようと思う。詳細な使い方については、MySQLのマニュアルを参照して欲しい。

GIPKのコンセプトは、主キーが存在しないテーブルに対して自動的に見えない主キーが作成されるというものだ。GIPKを使用するには、sql_generate_invisible_primary_keyシステム変数をONに設定してから、主キーが存在しないテーブルを作成するというだけだ。以下はコマンド例である。

mysql> SET sql_generate_invisible_primary_key=ON;

mysql> CREATE TABLE t (a int);

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `a` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> INSERT INTO t VALUES(123);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT my_row_id, a FROM t;
+-----------+------+
| my_row_id | a    |
+-----------+------+
|         1 |    1 |
+-----------+------+
1 row in set (0.00 sec)

カラム名がROWIDではなくmy_row_idなのはご愛嬌だ。GIPKがONでも通常利用には特に支障は無いので、8.0.30以降のバージョンを使用している場合には、my.cnfでsql_generate_invisible_primary_key=ONを設定しておくことを検討しても良いだろう。

なお、show_gipk_in_create_table_and_information_schemaシステム変数をOFFにすると、SHOW CREATE TABLEや情報スキーマからGIPKが見えなくなる。完全に存在を隠したい場合には、show_gipk_in_create_table_and_information_schema=OFFも合わせて設定しておのも良いかも知れない。なお、show_gipk_in_create_table_and_information_schema=OFFでもSHOW CREATE TABLEや情報スキーマでGIPKが見えなくなるだけで、SELECTなどからは引き続きmy_row_idカラムは参照が可能である。

別の主キーを作成したい場合

GIPKを利用する上でちょっと引っかかると考えられるのは、「やっぱりテーブルに主キーつけようかな！！」となったときである。GIPKのあるテーブルに対して新たに主キーをつけるには少しコツがいる。ただ単に主キーを追加するだけではエラーになってしまうからだ。というのも、既に主キーはGIPKとして存在するからである。そのため、GIPKつきのテーブルに新たに主キーを作成する場合には、既存のGIPKを削除する必要がある。

具体的には次のようにALTER TABLEを実行する必要がある。

mysql> ALTER TABLE テーブル名 DROP PRIMARY KEY, DROP my_row_id, ADD PRIMARY KEY (...主キーの定義...);

ちょっと面倒だが覚えておいてほしい。

レプリケーションとGIPK

主キーがないテーブルは、行ベースレプリケーションではクッソ遅くなるし、グループレプリケーションではそもそも使えないということは上で述べた通りである。なのでレプリケーション利用時には特にGIPKの利用をオススメしたい。MySQL 5.7からバイナリログフォーマットのデフォルトはROWだし、なんとbinlog_formatオプションは8.0.34で廃止予定（Deprecated）になってしまった。つまり将来のバージョンのバイナリログは行フォーマットだけがサポートされ、STATEMENTやMIXEDは使えなくなるということだ。なので今から行ベースレプリケーションでアプリケーションが上手く動くように準備しておくのが賢明であると言える。

GIPKを利用したテーブルがレプリケーションされると、レプリカでもちゃんとGIPKが作成される。これは、バイナリログに書かれるCREATE TABLEコマンドが、GIPK（つまりmy_row_idカラム）を含んだ定義になるからだ。つまりshow_gipk_in_create_table_and_information_schema=ONのときのSHOW CREATE TABLE相当、上のSHOW CREATE TABLEコマンドの例のようなDDLがバイナリログに書き込まれる。そのため、「ソースとレプリカでsql_generate_invisible_primary_keyの設定を合わせておかないと！」と考える必要はないので安心して欲しい。ただし、ソースの障害発生時に切り替えを行いたいと考えているのであれば、レプリカ側でも元から設定を合わせておく必要があるだろう。

なお、MySQL 8.0.32ではレプリカ側で主キーのないテーブルを検出したときに、レプリカ上でだけGIPKを生成することができるようになっている。ただし、個人的にはこの機能はあまりオススメしない。前述したようにソース上でGIPKを作成したほうが、ソースとレプリカでテーブル定義を完全に同じにしておいたほうが、切り替えなどを考えると有用だからである。マニュアルには使い方が解説されているが、使わないことを強くオススメする。

まとめ

そんなわけでGIPKは便利なのでぜひ試してみてね！！