ALGORITHM=INSTANTを使用した列追加・削除その２ ―設計と仕組みを深堀り―

本記事はMySQL 8.0 INSTANT ADD and DROP Column(s) – 2の翻訳版です。

前回の記事では、8.0.29で導入されたALGORITHM=INSTANTを使用した列追加・削除を簡単にご紹介しました。本記事では、その設計と仕組みについてさらに詳しく説明します。

まず、INSTANTを使用した列追加・削除の理解に欠かせない、InnoDBのディスクに格納される行について解説します。

InnoDBの行フォーマットとディスク上の行

ディスクに格納されているInnoDBの行(行1、行2、…、行N)を概念図で表すと下の様になります(図1)。

　　図1　ディスクに格納されている行

図中の行メタデータは各行に保持される情報であり、行が読み取られる際にその情報を提供します。 このメタデータは、レコードを連結リストに保存するのと同様に、ページ内の次のレコードへのポインターも保持します。

図中で行2は行1の右隣、行Nは行1〜2の下段に規則的に配置されていますが、物理的にはディスク上にランダムに配置される可能性があります。その際、ポインタの相対位置を示すオフセットの値は連結リストを維持するために前のレコードに格納されます。

InnoDBではREDUNDANT、COMPACT、DYNAMIC、COMPRESSEDの4つの行フォーマットがサポートされており、いずれも上記の基本設計に従っています。次のセクションから行メタデータについて解説していきます。

　　図2　行メタデータの内容
 

追加のメタデータ

可変長列の長さや、行フォーマットがCOMPACTおよびDYNAMICの際のNULLビットマップなど、追加のメタデータを保持するのに使用されます。
 

行ヘッダー

行フォーマットがREDUNDANTでは行ヘッダーの長さは6バイト、行フォーマットがCOMPACT、DYNAMIC、COMPRESSEDでは行ヘッダーは5バイトです。この行ヘッダーは、ページ内の次のレコードのオフセット値などの情報を保持するために使用されます。これらの情報とともに、この後のセクションで重要になってくる4ビットのINFO_BITSも行ヘッダーは保持しています。

ディスク上の行のインスタント・メタデータ

全ての行フォーマットでINFO_BITSは4ビットです。これらの情報ビットは削除マークなど、行の状態を表すのに利用されます。図2、図3に示した左から2番目のビット(X)は従来使用されていなかったため、その行の行バージョンの有無を示すことにし、これをINSTANTビットと呼ぶことにしました。

テーブル内の行にバージョンが付与されるのはいつか、という疑問が出てくると思いますのでここで説明します。テーブルがINSTANTを使用した列追加・削除を実行しない限り、テーブル・メタデータのバージョンは0です。また、このテーブルのどの行も行バージョンをもっていません。このテーブルの各行においてレコード・ヘッダーの行バージョンを示すビットは0であり、この行がテーブル・メタデータ のバージョン0に属していることが分かります。

テーブルに1回以上、INSTANTを使用した列追加・削除が実行されるとテーブル・メタデータのバージョンが上がります。このテーブルでは、挿入・更新される各行に行バージョンを示すビットが設定され、行バージョンが明示的に記録されます(図3)。

注: INFO_BITSにINSTANTビットが設定されていない場合、行には1バイトの行バージョンは保存されません。

　　図3　行バージョン
　

列に関するデータ・ディクショナリ(DD)のインスタント・メタデータ

列追加・削除に関するメタデータが導入されました。
 

　　表1  列追加・削除に関するメタデータ

フェッチ中の行の動作

上記の二つのメタデータ情報があれば、列の値がディスク上のどの行に存在するかを簡単に判断できます。次に示すルールが行の理解に役立ちます。

• VERSION_DROPPED > 0 のすべての列の値を無視
• VERSION_ADDED > ROW_VERSION の列にはデフォルト値を使用

下に例を示します。
 

手順

1. テーブルt0 [c1, c2, c3, c4] を作成
   CREATE TABLE t0 (C1 CHAR(10), C2 CHAR(10), C3 CHAR(10), C4 CHAR(10));
    
2. 行R1を挿入
   INSERT INTO t0 VALUES ("r1c1", "r1c2", "r1c3", "r1c4");
    
3. 列C3を削除し、テーブルt0をt0(c1, c2, c4)にする
   ALTER TABLE t0 DROP COLUMN C3, ALGORITHM=INSTANT;
    
4. 列C5を追加し、テーブルt0をt0(c1, c2, c4, c5)にする
   ALTER TABLE t0 ADD COLUMN C5 CHAR(10) DEFAULT "C5d", ALGORITHM=INSTANT;
    
5. テーブルを表示する
   SELECT * from t0;

想定される結果

• 列C3の値は行R1のディスク上にあるが、フェッチ結果には表示されない
• 列C5の値は行R1のディスク上には存在しないが、フェッチ結果には表示される
   

想定結果に至る考え方

　　図4  列追加・削除による列メタデータの変化

　　図5  フェッチに反映される行データ
 

上記のような列追加・削除を行った後のテーブル・メタデータのバージョンは下記のようになります。

　　図6  列追加・削除のバージョン

実施例

下記のように実行したところ、想定した結果と一致しました。

mysql> CREATE TABLE t0 (C1 char(10), C2 char(10), C3 char(10), C4 char(10));
Query OK, 0 rows affected (0.02 sec)


mysql> INSERT INTO t0 VALUES ("r1c1", "r1c2", "r1c3", "r1c4");
Query OK, 1 row affected (0.00 sec)


mysql> SELECT * FROM t0;
+------+------+------+------+
| C1   | C2   | C3   | C4   |
+------+------+------+------+
| r1c1 | r1c2 | r1c3 | r1c4 |
+------+------+------+------+
1 row in set (0.00 sec)


mysql> ALTER TABLE t0 ADD COLUMN C5 CHAR(10) DEFAULT "c5_def", ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql>  INSERT INTO t0 values ("r2c1", "r2c2", "r2c3", "r2c4", "r2c5");
Query OK, 1 row affected (0.00 sec)


mysql> SELECT * FROM t0;
+------+------+------+------+--------+
| C1   | C2   | C3   | C4   | C5     |
+------+------+------+------+--------+
| r1c1 | r1c2 | r1c3 | r1c4 | c5_def |
| r2c1 | r2c2 | r2c3 | r2c4 | r2c5   |
+------+------+------+------+--------+
2 rows in set (0.00 sec)


mysql> ALTER TABLE t0 DROP COLUMN C3, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SELECT * FROM t0;
+------+------+------+--------+
| C1   | C2   | C4   | C5     |
+------+------+------+--------+
| r1c1 | r1c2 | r1c4 | c5_def |
| r2c1 | r2c2 | r2c4 | r2c5   |
+------+------+------+--------+
2 rows in set (0.00 sec)

INSTANTを使用した列追加・削除によるディスク上の行とデータ・ディクショナリ(DD)内の列メタデータ

テーブルでINSTANTによる列追加・削除を実行した際に、行がどのようにディスクに格納されるか、またデータ・ディクショナリ(DD)内の列メタデータがどのように変化するかを説明します。

CREATE TABLE t0 (C1 char(10), C2 char(10), C3 char(10), C4 char(10));

INSERT INTO t0 values ("r1c1", "r1c2", "r1c3", "r1c4");

　　表2  テーブル作成・データr1挿入後の行メタデータ

　　表3  テーブル作成・データr1挿入後の列メタデータ

ALTER TABLE t0 ADD COLUMN C5 CHAR(10) DEFAULT "c5_def", ALGORITHM=INSTANT;

INSERT INTO t0 values ("r2c1", "r2c2", "r2c3", "r2c4", "r2c5");

　　表4  列C5追加・データr2挿入後の行メタデータ

　　表5  列C5追加・データr2挿入後の列メタデータ

ALTER TABLE t0 DROP COLUMN C3, ALGORITHM=INSTANT;

INSERT INTO t0 values ("r3c1", "r3c2", "r3c4", "r3c5");

　　表6  列C3削除・データr3挿入後の行メタデータ

　　表7  列C3削除・データr3挿入後の列メタデータ

列がランダムな位置で追加・削除された時や、新しい行の挿入時、および既存行の更新時の動作は、メモリ内およびディスク上で処理されるプロセスなどを含むので、今回ご紹介した例より複雑になります。機会があれば実行例とともに、メモリおよびディスクで実行される処理の解説を交えてご紹介します。

MySQLアップグレード時の留意事項

前回のブログ記事でご紹介したとおり、MySQL 8.0.12の実装ではINSTANTによる列追加がテーブルの最終列としてのみ有効でした。そのため、INSTANTによる列追加を実行したテーブルを含む可能性のある、MySQL 8.0.12より前のリリースからのアップグレードに問題はありません。MySQL 8.0.29以上へのアップグレードの際の留意点を下記に示します。

MySQL 8.0.12より前のバージョンからアップグレードされるテーブル

• これらのテーブルにはINSTANTによる列追加で作ったカラムがありません。
• アップグレード後に、INSTANTによる列追加・削除を実行すると、新たにテーブル・メタデータのバージョン1が作成されます。
• これらの行は全て読取り・書き込み操作時に処理されます。
   

MySQL 8.0.12より後、MySQL 8.0.29より前のバージョンからアップグレードされるテーブル

• これらのテーブルはINSTANTを使った列追加が実行された可能性があり、またINSTANT ADD実行によって追加された列の値を持つ行と持たない行が存在することが考えられます。
• したがって、アップグレードされたテーブルには次の4種類の行が存在する可能性があります。
  • 以前の実装(MySQL 8.0.12)のINSTANTによる列追加が実行される前に挿入された行
  • 以前の実装のINSTANTによる列追加が実行された後、アップグレード前に挿入された行
  • 以前の実装のINSTANTによる列追加実行後かつアップグレード後、また現在の実装(MySQL 8.0.29)のINSTANTによる列追加・削除実行前に挿入された行
  • 現在の実装のINSTANTによる列追加・削除実行後に挿入された行
• これらの行は全ては、読み取り・書き込み操作時に処理されます。
   

INSTANT ADDによって追加された列を含むテーブルのアップグレードがどのように行われるか、また上記のようにディスクに格納されている様々な行がどのように処理されるかなど、機会があればまたブログ記事などでご紹介します。