この記事はIntroducing MySQL HeatWave Generated Invisible Primary Keysの翻訳版です。

主キーとはテーブル内のある一行を一意に特定する列セットであり、同じキーを持つ他の行はありません。主キーを効果的に使うことで、高速なクエリ作成や対象となるデータを絞った更新が可能になります。主キーの使用はデータベースを効率的に運用する方法の一つとして一般に知られており、全てのテーブルに主キーを必要とする機能も多くなっています。特に、大量のデータを扱う場合にはテーブルに主キーを持たせることをおすすめします。

一方、既存データのデータ・ソースが主キーをサポートしていないテーブル、あるいは行を一意に特定できる列も列サブセットもないテーブルでは、サロゲート・キーとしてオートインクリメント列を主キーとして使用することもあります。しかし、以下に示すような例では、サロゲート・キーの実装が現実的ではないことがあります。

データの更新が行われない
インデックス等により既に最適な性能を実現している
列数が非常に多く、主キーを追加することで複雑度が増す

対策としては、自動で生成される不可視主キー(Generated Invisible Primary Key; GIPK)が有効です。GIPKはその名の通り、クエリ結果にはこの列は出力されません。そのため、アプリケーションやデータを扱うスクリプトを変更してこの列を考慮する必要はありません。

GIPKはオンプレミスのMySQLでも、Oracle Cloud Infrastructure(OCI)のMySQL HeatWaveでも利用できます。GIPKはOCIにおけるMySQL HeatWave DBシステムの高可用性構成などに役に立つので、本記事ではMySQL HeatWaveでGIPKを使う方法を主にご紹介します。

GIPKの有用性

MySQL HeatWave DBシステムの高可用性構成を作成し、主キーのないテーブルを含むデータを使用する際には、既存のテーブルに主キーを追加する必要があります。ここでGIPKオプションを使用しなければ、高可用性構成を有効にした後で作るテーブルは、主キーの明示的な付与なしには作成できません。そこでGIPKオプションを用いれば、主キーを明示的に付与しなくてもテーブルを作ることができます。

以下は、オンプレミスのMySQLサーバーで変数sql_generate_invisible_primary_key のステータスを確認し、有効にする方法です。MySQL HeatWave DBシステムでこの変数を有効にする方法は、その次のセクションでご紹介します。

オンプレミスのMySQLサーバーでGIPKを有効にする方法

MySQL Server 8.0.30以降では、変数 sql_generate_invisible_primary_key がON (デフォルトではOFF) の場合、主キー無しで作成されたInnoDBテーブルにGIPKが自動で生成されます。

mysql> SHOW VARIABLES LIKE '%invisible%';
+-------------------------------------------+-------+
| Variable_name                             | Value |
+-------------------------------------------+-------+
| sql_generate_invisible_primary_key        | OFF   |
+-------------------------------------------+-------+

1 row in set (0.00 sec)

この変数が無効で、かつMySQLの構成が高可用性構成でなければ、下のように主キーの無いテーブルを作ることができます。

mysql> USE test;
Database changed


mysql> CREATE TABLE no_pkey (a int, b char(20));
Query OK, 0 rows affected (0.01 sec)


mysql> SHOW CREATE TABLE no_pkey \G
*************************** 1. row ***************************
      Table: no_pkey
Create Table: CREATE TABLE `no_pkey` (
  `a` int DEFAULT NULL,
  `b` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

GIPKのオプションを有効にし(変数sql_generate_invisible_primary_keyの値をON)、主キーの明示的な付与なしでテーブルを作ると、以下の実行例のように自動的に主キーが作成されます。
訳者注：動作するように原文にSQL文を加えています。それ以降のサンプルは原文と同様です。

mysql> SET @@sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TABLE with_pkey (a int, b char(20));
Query OK, 0 rows affected (0.02 sec)


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

上記の例では、主キーが自動的に生成され不可視 “INVISIBLE” になっています。下のようにテーブルに実行するSELECT文では、GIPKは表示されません。

mysql> INSERT INTO with_pkey(a, b) VALUES (1,'one'), (2, 'two'), (3, 'three');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM  with_pkey;
+------+-------+
| a    | b     |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | three |
+------+-------+
3 rows in set (0.00 sec)

結果セットで不可視主キーの列を見せたい場合には、以下の様に列セットオプションで明示的にリクエストすれば表示できます。

mysql> SELECT my_row_id, a, b FROM with_pkey;
+-----------+------+-------+
| my_row_id | a    | b     |
+-----------+------+-------+
|         1 |    1 | one   |
|         2 |    2 | two   |
|         3 |    3 | three |
+-----------+------+-------+
3 rows in set (0.00 sec)

次のセクションでは、MySQL HeatWaveでGIPKを有効にする方法をご紹介します。

オンプレミスのMySQLにおけるGIPKの生成およびレプリケーションでのGIPKの使用に関しての詳細はこちらのWebマニュアルをご覧ください。

MySQL HeatWave DBシステムでGIPKを有効にする方法

MySQL HeatWave DBシステムでGIPKを使うには、MySQL構成オブジェクトを変更します。これはDBシステム構築時に作成した管理者ユーザー・アカウントには、先ほどオンプレミスのMySQLサーバーにおける例で示したSETコマンドを実行する権限がないためです。MySQL HeatWaveではMySQL構成オブジェクトを使用して、システム変数sql_generate_invisible_primary_keyなどの変数を設定することができます。

MySQLの構成はオブジェクトの一つであるため、MySQL HeatWave DBシステムを利用して任意の数の構成を作成できます。ただし、MySQL構成オブジェクトは、シェイプ・オブジェクトの一部であるMySQL構成オブジェクトに紐付けされているため、MySQLオブジェクトはシェイプ・オブジェクトに基づいている必要があります。

注: HeatWave DBシステムの構成について詳しくはこちらをご参照ください。

また、新しいMySQL構成を作成しそれを他のMySQL HeatWave DBシステムに適用することもできます。意図しないシェイプや誤ったオプションの選択を防ぐために、既存のDBシステムを表示しそのMySQL構成をコピーすることも可能です。

このセクションでは、MySQL HeatWave DBシステムのMySQL構成を変更する方法をご紹介します。主な作業としては、既存の構成のコピーを作成し、必要な変数の変更を行い、新しい設定をDBシステムに適用します。

注: DBシステムのMySQL構成を変更するには、DBシステムを再起動する必要があります。変更を行う前に、アプリケーションがDBシステムの再起動の影響を受けないようになっていることを確認してください。

前準備

変更を行う前に、DBシステムの手動バックアップの作成をおすすめします。変数や構成を変える前にバックアップすることで、何か問題が発生した場合にDBシステムを変更前の状態に戻すことができます。

既存の構成のコピー

まずはじめにMySQL HeatWaveのWebコンソールで、GIPKを有効にしたい(変数をONに設定したい)DBシステムを表示します。下の図にある通り、[DBシステムの詳細] ページの [DBシステム構成] セクションにて [構成:] に続いて表示されているシェイプ名のリンクをクリックします。

図1 DBシステムの詳細ページのDBシステム構成

ポップアップに [構成詳細の表示] というリンクが出るので、クリックしてページを移動します。

図2 構成詳細の表示

このページで、特定のシェイプに紐付けられていることを確認します。本実施例におけるシェイプは、図4の [構成詳細] ページが示す通り、MySQL.VM.Standard.E4.1.8GB.Standaloneです。下の図に示すようにページ下部までスクロールすると、シェイプに紐付けられた変数とその設定が表示されます。GIPKの変数は基本構成の変数リストに含まれていないので、次の手順で追加します。

図3 基本構成に含まれる変数の一覧

今回は構成のコピーを作成し、GIPKの変数の追加と有効化を行います。下の図のようにページ上部に行き [構成のコピー] ボタンをクリックしてください。

図4 構成のコピー

[構成のコピー] ページで、GIPKの変数を有効にした構成であることが分かるように任意の名前に変更できます。今回の例では、MySQL.VM.Standard.E4.1.8GB.Standalone with GIPKs という構成名にしました。

図5 構成名の変更

ページの一番下までスクロールすると、既存の変数を変えることができます。下に示すように [別の変数] ボタンをクリックすると新しい変数を追加できます。

図6 [構成のコピー] ページで変数の追加

下図のように左側の空のドロップダウン・ボックスで、使用可能な変数のリストからsql_generate_invisible_primary_keyを選択し、右側でONにします。[作成] ボタンをクリックすると、新しい構成が作られます。

図7 変数sql_generate_invisible_primary_keyを追加し有効化

次のセクションでは、変更を加えた構成をDBシステムに適用します。

変更構成の適用

先述の手順で作成した構成をDBシステムに適用するには、[DBシステムの詳細] ページに戻ります。下の図のように、ページの上部にある [編集] ボタンをクリックしてください。

図8 DBシステムの編集

下図のように [DBシステムの編集] ページで [構成] のセクションまで下にスクロールし、[構成の変更] を選択します。

図9 現在の構成の変更

[シェイプの構成の参照] ページで、先ほど作成した構成にチェックを入れ [構成の選択] ボタンをクリックします。

図10

図10 先ほど変数を追加した構成に変更

[DBシステムの編集] ページに戻ったら選択した構成になっていることを確認し、[変更の保存] をクリックします。この作業によってDBシステムが再起動されるため、あらかじめアプリケーションやユーザーによるDBシステムの運用が再起動の影響を受けないようにしておいてください。

図11

図11 構成の変更を保存

DBが再起動したら、次に適用した変更を確認します。

変更の確認

変更された構成になっていることを確認するには [DBシステムの詳細] ページに戻り、[DBシステム構成] で新しい構成名 (今回の例ではVM.Standard.E4.1.8GB.Standalone with GIPKs) が反映されていることを確認します。

図12

図12 DBシステム構成の確認

[構成:] の横に表示されているリンク (VM.Standard.E4.1.8GB.Standalone with GIPKs) を選択し、ポップアップ・ウィンドウで表示される [構成詳細の表示] をクリックして [構成詳細] ページに移動します。[構成詳細] ページを下までスクロールすると、下の図のように先ほど追加した変数が確認できます。

図13

図13 変更した構成に含まれる変数の一覧

作業は以上です。今後、主キーを明示的に付与せずに作成されたテーブルには不可視主キーが生成されるようになり、主キーを必要とする機能を有効にする際にエラーが発生することはありません。

制限事項

オンプレミスのMySQLおよびMySQL HeatWaveにおいてGIPKを使う際には、いくつかの制限事項があります。そのうち、既存データとレプリケーションに関するものを説明します。

既存データに関する制限事項

GIPKはMySQLのデータを高可用性構成を利用する際にとても便利ですが、主キーのないテーブルを含むデータベースが存在しているDBシステムでは、GIPKを有効にしても既存のテーブルは変更されません。高可用性構成にする前に、まず主キーのないテーブルに主キーを追加します。

MySQL Shellによるエクスポートおよびインポート機能を使って論理バックアップを取る際には、データのエクスポート、テーブル削除、GIPKの有効化、およびデータのインポートを含めることができます。GIPKオプションを有効にすると不可視主キーが生成されますが、データセットが大きいと時間がかかることに留意してください。データのエクスポートとインポートの詳細については、OCI MySQL HeatWaveのＷebマニュアルをご覧ください。

データのサイズが大きくなればなるほど実行時間がかかるのは避けられませんが、データの再ロードを行わないオプションを使用することで完全なダンプ、削除、インポートをするよりは時間の節約になります。また、GIPKではなく通常の主キーを追加することもできますが、前述のとおり、結果の列セットに指定していない場合でも可視列として表示されることがあります。あるいは、不可視主キーを手動で追加すれば、データをインポートする前にGIPKオプションを使用するのと同じ結果になります。これらの各オプションには、ALTER TABLEコマンドを使用します。

下に示す実行例では、employee_dataというテーブルに通常の主キーを追加しています。

訳者注: 実施例にあるemployee_dataテーブルは、MySQLのサンプル・データベースemployee dataデータベースのemployeesテーブルを元にしています。

ALTER TABLE employee_data ADD COLUMN pkey int AUTO_INCREMENT PRIMARY KEY FIRST;

変更されたテーブル構造は下記のようになります。

> EXPLAIN employee_data;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| pkey       | int           | NO   | PRI | NULL    | auto_increment |
| emp_no     | int           | NO   |     | NULL    |                |
| birth_date | date          | NO   |     | NULL    |                |
| first_name | varchar(14)   | NO   |     | NULL    |                |
| last_name  | varchar(16)   | NO   |     | NULL    |                |
| gender     | enum('M','F') | NO   |     | NULL    |                |
| hire_date  | date          | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

下記のように先程のテーブルに不可視主キーを追加することも可能です。 INVISIBLEオプションを使用することで、結果セットで明示的に指定しない限り表示されません。

ALTER TABLE employee_data ADD COLUMN pkey int INVISIBLE AUTO_INCREMENT PRIMARY KEY FIRST;

変更したテーブル構造は次のようになります。主キーはテーブル構造の一覧にはありますが “INVISIBLE“となっており、列セットに明示的に指定されなければSELECT * クエリでは表示されません。

mysql> explain employee_data;
+------------+---------------+------+-----+---------+--------------------------+
| Field      | Type          | Null | Key | Default | Extra                    |
+------------+---------------+------+-----+---------+--------------------------+
| pkey       | int           | NO   | PRI | NULL    | auto_increment INVISIBLE |
| emp_no     | int           | NO   |     | NULL    |                          |
| birth_date | date          | NO   |     | NULL    |                          |
| first_name | varchar(14)   | NO   |     | NULL    |                          |
| last_name  | varchar(16)   | NO   |     | NULL    |                          |
| gender     | enum('M','F') | NO   |     | NULL    |                          |
| hire_date  | date          | NO   |     | NULL    |                          |
+------------+---------------+------+-----+---------+--------------------------+
7 rows in set (0.01 sec)


mysql> SELECT * FROM employee_data LIMIT 3;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
+--------+------------+------------+-----------+--------+------------+
3 rows in set (0.00 sec)

上記でご紹介した方法などで、高可用性構成を有効にする前にデータのサイズに応じてデータを変更してください。既存のテーブルに対して一回実行すれば、GIPKオプションを有効にした後は、新しく作成するテーブルは主キーを明示的に付与しなくても、不可視主キーが自動的に作成されます。

レプリケーション関する制限事項

レプリケーション構成でGIPKを有効にする際にもいくつかの制限事項があります。下記の条件のいずれかが当てはまる場合は、エラーやデータの相違を避けるために、本番環境でGIPKを有効にする前にテスト環境などで試験することをおすすめします。

レプリカ上のGIPKを含むテーブルが、ソースのテーブルと一致していることを確認してください。状況によっては、一番右端の列が余分にあるレプリカではGIPKを正しく使用するために追加の設定が必要になる場合があります。

また、ソースとレプリカのバージョン間でGIPKのサポートが一致する必要があります。例えばソースには非表示列のサポートおよびGIPKがあり、一方レプリカには非表示列のサポートがない場合には、レプリカはGIPKを検出できず、エラーで停止したり変更が正しく適用されなかったりするおそれがあります。

レプリカにおけるGIPKの使用は推奨されません。もしソースのテーブルにGIPKがなく、2つのレプリカで共に変数 replica_generate_invisible_primary_key が有効になっている場合には、2つのレプリカで共通の行に対して異なるGIPKになってしまう可能性があるためです。

まとめ

GIPK を有効にすると、新たに作成するテーブルに不可視主キーが自動で生成されるため、主キーが必要な機能を使うときなどに非常に便利です。例えば、MySQL HeatWave DBシステムにおける高可用性構成は主キーが必要な機能の一つです。また、主キーのないテーブルを使用していたアプリケーションでは、明示的に指定しない限り、生成された不可視主キーをそれまでと同様に結果セットで表示させずに済むという利点があります。

本記事でご紹介したようなGIPKなどの機能拡張は、オラクルがお客様の多様なニーズを最前線で把握し、ビジネスの潮流に合わせてOCI環境のMySQLに関連するリソースを利活用していただけるよう新しい技術、製品、サービスの開発およびその支援に尽力している証拠です。

GIPKについて、詳しくは下記のWebマニュアルをご覧ください。

オンプレミスのMySQL: sql_generate_invisible_primary_keyを有効にする方法 (訳者注：英語版へのリンクです)
MySQL HeatWave: システム変数を有効にする方法

MySQL HeatWaveのGIPKについて

GIPKの有用性

オンプレミスのMySQLサーバーでGIPKを有効にする方法

制限事項

まとめ

Charles Bell

Principal Software Engineer

MySQLセミナー開催のご案内 2024年2月-3月

MySQL HeatWave Implementation Associate Rel 1 (1Z0-915-1-JPN)日本語試験提供開始のお知らせ

MySQL HeatWaveのGIPKについて

GIPKの有用性

オンプレミスのMySQLサーバーでGIPKを有効にする方法

制限事項

まとめ

Authors

Charles Bell

Principal Software Engineer

MySQLセミナー開催のご案内 2024年2月-3月

MySQL HeatWave Implementation Associate Rel 1 (1Z0-915-1-JPN)日本語試験提供開始のお知らせ