InnoDBにおける仮想列および効果的な"関数インデックス"(MySQL Server Blogより)

免責事項

この記事はJimmy Yang氏によるMySQL Server Blogの記事「Virtual Columns and Effective Functional Indexes in InnoDB」(2015/8/11)をユーザが翻訳したものであり、Oracle公式の文書ではありません。

4月に私はブログ投稿を行い、JSONのlab版リリースへの新しいInnoDBの仮想列および効果的な関数インデックスについて紹介した。現在ではMySQL 5.7.8で機能は公式なものとなっている。5.7のリリース候補版に何があるのかを記し、追加の例を示すにあたりその話題は再度確認する価値がある。いくつかの簡単な性能テストも実施しており、テーブルへの簡単な操作における仮想カラムおよびインデックスがある場合とない場合の比較および、マテリアライズされた生成列との比較を実施した。

主な機能

以前の投稿で、設計の詳細については述べている。まだ確認されていない場合は、始めに確認することをお勧めする。全ての設計の詳細には踏み込まないが、いくつかキーとなる点を再度述べることにする。

1. 仮想列はテーブルにおいて、もはやマテリアライズされない。完全に「仮想」でありInnoDBの行として保存されず(InnoDBはテーブルのデータをプライマリキーのレコード、つまりクラスタインデックス、に保存し、仮想列はInnoDBのプライマリキーのレコードに存在しない)、全体のテーブルのサイズを小さくする。これにより、テーブルスキャンおよび他の大きな操作が高速化されうる。
2. 完全に「仮想」であるので、仮想列の追加および削除にテーブルの再構成が不要である。新しいメタデータを登録する為のシステムテーブルの更新が必要なだけである。これによりスキーマ変更がシンプルで高速となる。
3. 仮想列へのインデックスの作成(セカンダリインデックスのみが許される)は、必然的に仮想列を計算された値がセカンダリインデックス内に保存されるようにインデックスレコードに"マテリアライズする"。値はプライマリキー(クラスタインデックス)には保存されない。従ってテーブル自体は小さいまま、セカンダリインデックス内の計算済みの(保存された)仮想列の値を素早く参照できる。
4. インデックスが仮想列に対して生成されたら、仮想列へのロールバックおよびパージ操作が発生したときに仮想列の値を不必要に再計算しなくてよいように、仮想列の値をMVCCで記録する。しかし、この目的はセカンダリインデックスのみを維持することにあるので、スペースを節約する為にある特定のデータ長までのみを記録する。インデックスはCOMPACTおよびREDUNDANT形式の場合は767バイト、DYNAMICおよびCOMPRESSED形式の場合は3072バイトのキー長の制限がある。

lab版のリリース以後の変更点

初期のlab版のリリースから変更/追加されたもののうち有用で重要なものをいくつか紹介する。

1. 単一の「関数インデックス」が、仮想列および非仮想列の両方を含んだ組合わせに対して作成可能となった。これは仮想および非仮想生成列の混合に対してコンポジット(複合)インデックスが作成可能となった。
2. インプレイスアルゴリズムを利用することで関数インデックスをオンラインで作成可能となった。これはDML文がインデックス作成中でも処理されるようにするためである。これを達成する為に、DML文が同時に発生する箇所の仮想列の値は、インデックス作成中に計算および記録され、後で関数インデックスに対してリプレイされる。
3. 仮想列が他の仮想列に対して作成可能となりそれに対してもインデックスを作成できるようになった。
4. 次の改善はユーザーにとっては分かりにくいが、言及するに値する。インデックスが作成された仮想列に対するパージ関連のアクティビティーに関する機能拡張に関するものだ。新しいコールバック(WL#8841)はサーバーレイヤの機能を提供し、これはInnoDBのパージスレッドから呼ばれ、仮想列のインデックスの値を計算する。一般的にこの計算はコネクションスレッド(またはセッション)から行われるが、内部のInnoDBパージスレッドはコネクションおよびセッションに対応付かないため、スレッドをもたないし、アクセスするテーブルオブジェクトをもたない。この取組みはパージスレッドが必要な計算を行う為に必要なサーバーレイヤのコールバックを提供する為に必要であった。

制限事項

依然として「関数インデックス」周辺にはいくつかの特筆すべき制限事項がある。 いくつかに関しては、後の取組みで解決する予定である。

• 1. 仮想列は主キーに加えられない
• 2. 仮想列には空間または全文検索インデックスを作成できない(この制限は最終的には解消する見込みである)
• 3. 仮想インデックスは外部キーとして利用できない
• 4. 仮想列は繰返し不可能な/非決定的な関数に対して作成できない。例:

mysql> ALTER TABLE t ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual;
ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.

• 5. 仮想列の追加および削除は操作が単体で行われたときのみインプレイスあるいはオンラインで実行でき、他のテーブル変更と同時には実行できない。この制限は後に削除される予定だが、インプレイスの仮想列の追加および削除操作を別々のDDL文になるようにすることで対処可能である。

いくつかの例

前回のブログ投稿で我々はいくつかのJSON関数と共に「関数インデックス」をどのように使うのかの例を示した。ユーザーは仮想カラムに対して非決定的でない限り(例えばNOW())どんな関数も利用可能である。それでは、JSON関数以外の追加の例をみてみよう。

XMLのフィールドに対するインデックス

mysql> create table t(a int, b varchar(100), c varchar(100) generated always as (ExtractValue(b, '//b[1]')) virtual);
Query OK, 0 rows affected (0.22 sec)

mysql> insert into t values (1, '<a><b>X</b><b>Y</b></a>', default);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+------+-------------------------+------+
| a    | b                       | c    |
+------+-------------------------+------+
|    1 | <a><b>X</b><b>Y</b></a> | X    |
+------+-------------------------+------+
1 row in set (0.00 sec)

幾何計算に対するインデックス

2つの地理上の点あるいは、座標のペア(緯度, 経度)間の距離(メートル)を保存する仮想列をどのように追加するかを例として示す。

まずは、地理データを格納するテーブルを作成する。

mysql> CREATE TABLE t (
id int(11) NOT NULL,
p1 geometry DEFAULT NULL,
p2 geometry DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t values(1, POINT(-75.341621, 41.061987), POINT(-75.3555043, 41.0515628));
Query OK, 1 row affected (0.04 sec)

mysql> insert into t values(2, POINT(-75.341621, 41.061987), POINT(-75.3215434, 41.0595024));
Query OK, 1 row affected (0.04 sec)

ここで2点間の距離(メートルで)を計りたい。テーブルを再構成することなく、仮想列を追加しインデックスを作成することができる。

mysql> ALTER TABLE t ADD distance double GENERATED ALWAYS AS (st_distance_sphere(p1, p2)) VIRTUAL;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx on t(distance);
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

これで、このテーブルを新しく追加された仮想列およびインデックスを利用することで、簡単にそして素早くクエリを発行できる。

mysql> explain select distance from t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx
      key_len: 9
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> select distance, id from t;
+--------------------+----+
| distance           | id |
+--------------------+----+
| 1642.7497709937588 |  1 |
| 1705.8728579019303 |  2 |
+--------------------+----+
2 rows in set (0.00 sec)

文字列操作

文字列操作関数を使った列をいくつか加えることもできる。 例としては下記である。

mysql> CREATE TABLE `t` (
`a` varchar(100) DEFAULT NULL,
`b` varchar(100) DEFAULT NULL
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t values ("this is an experiment", "with string manipulation");
Query OK, 1 row affected (0.03 sec)

mysql> ALTER TABLE t ADD COLUMN count1 int GENERATED ALWAYS AS (char_length(a)) VIRTUAL;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t ADD COLUMN count2 int GENERATED ALWAYS AS (char_length(b)) VIRTUAL;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t;
+-----------------------+--------------------------+--------+--------+
| a                     | b                        | count1 | count2 |
+-----------------------+--------------------------+--------+--------+
| this is an experiment | with string manipulation |     21 |     24 |
+-----------------------+--------------------------+--------+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t ADD COLUMN count3 int GENERATED ALWAYS AS (instr(a, "exp")) VIRTUAL;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select count3 from t;
+--------+
| count3 |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

最後に、独自の関数(もしくはユーザー定義関数)を作成することも可能であり、これを仮想列のベースとして利用できることを述べておこう。

簡単なベンチマーク

予想されている通り、仮想列上のインデックスを利用している場合は、それらをマテリアライズする必要がある場合(例えば、INSERTまたはUPDATE)、必要とされる計算の為に、追加の書込みコスト、言い換えればインデックスを作成あるいは維持するのに関連したコストがかかる。しかしながら、列に関数インデックスがなければ、コストは行の読取りに関連し、これは行が検査されるたびに値がマテリアライズされる必要があるためである。この追加のコストは利用される関数の計算の複雑さに直接関連する。

しかしながら、追加のコストがあるとはいえ、仮想生成列および「関数インデックス」は後にデータをクラスタインデックス(主キー)にマテリアライズする為に、ストアド生成列を含むテーブルを作成するよりははるかに良く、結果としてより大きなテーブル(ディスク上、メモリ上いずれも)となる。

次の簡単なテストを3種類のテーブルに対して実施する。

• 1. 仮想生成列のあるテーブル

REATE TABLE `t` (
`h` INT NOT NULL PRIMARY KEY,
`a` varchar(30),
`b` BLOB,
`v_a_b` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
`v_b` BLOB GENERATED ALWAYS AS (b) VIRTUAL,
`e` int,
`v_h_e` INT(11) GENERATED ALWAYS AS (h + e) VIRTUAL,
`v_e` INT GENERATED ALWAYS AS (e) VIRTUAL,
`v_a` INT GENERATED ALWAYS AS (char_length(a)) VIRTUAL
) ENGINE=InnoDB;

このテーブルは4つの通常の列と5つの仮想生成列をもつ。関数自体の影響を最小化する為に、仮想列に利用される関数は簡易なものとした。

※訳注: 「仮想列, 生成列(virtual column, generated column)」は「仮想生成列(virtual generated column)」と「ストアド生成列(stored generated column)」の総称である。通常、仮想列はマテリアライズされないため、原文では、1, 2, 3が区別しやすいように「仮想列」「仮想列なし」「マテリアライズされた、あるいはストアド生成列」となっている。本訳文では「仮想生成列」の意味で用いられる「仮想列」を適宜「仮想生成列」と修正して意訳している。

• 2. 仮想列が全くない(VIRTUALかSTOREDかに関わらず)「通常の」テーブル

CREATE TABLE `t_nv` (
`h` INT NOT NULL PRIMARY KEY,
`a` VARCHAR(30),
`b` BLOB,
`e` INT
) ENGINE=InnoDB;

• 3. マテリアライズされた、あるいはストアド生成列をもつテーブル

CREATE TABLE `t_m` (
`h` INT NOT NULL PRIMARY KEY,
`a` varchar(30),
`b` BLOB,
`v_a_b` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) STORED,
`v_b` BLOB GENERATED ALWAYS AS (b) STORED,
`e` int,
`v_h_e` INT(11) GENERATED ALWAYS AS (h + e) STORED,
`v_e` INT GENERATED ALWAYS AS (e) STORED,
`v_a` INT GENERATED ALWAYS AS (char_length(a)) STORED
) ENGINE=InnoDB;

それから、次のプロシージャを各テーブルに行をINSERTするのに使う。

CREATE PROCEDURE insert_values(n1 int, n2 int)
begin
        DECLARE i INT DEFAULT 1;
        WHILE (i+n1 <= 100000+n2) DO
                INSERT INTO t VALUES (n1+i, CAST(n1+i AS CHAR), REPEAT('b', 2000), DEFAULT, DEFAULT, n1+i+10, DEFAULT, DEFAULT, DEFAULT);
                INSERT INTO t_nv VALUES (n1+i, CAST(n1+i AS CHAR),  REPEAT('b', 2000), n1+i+10);
                INSERT INTO t_m VALUES (n1+i, CAST(n1+i AS CHAR), REPEAT('b', 2000), DEFAULT, DEFAULT, n1+i+10, DEFAULT, DEFAULT, DEFAULT);
                SET i = i + 1;
        END WHILE;
END|

全てのテストは48コアのx86_64 GNU/Linuxのマシンで、InnoDBバッファプールが10GBの状態で実施された。テストはシングルスレッドで実施され、結果は3回の結果の平均値である。ここに結果を記載する。

インデックスのない挿入

セカンダリインデックスのないテーブルに対する挿入に関しては、仮想列の全くないテーブルと、仮想生成列があるテーブルでとても良く似た実行時間である。後者は列をマテリアライズしていないので、挿入されるデータの量は2つのテーブルで厳密に等しい。しかしながら、列がマテリアライズされると(テーブル3)、かかる時間は明らかに長くなる。

1つだけ述べておく必要があるのはテーブル1とテーブル2でかかる時間がほとんど同じとはいえ、テーブル1の挿入の方がわずかに長いということだ。これは不要な計算が仮想生成列のあるテーブルに対して行われている為で、これはそのうち修正される予定である。

インデックスの作成

3つのテーブルの同じ位置づけの列にインデックスを作成する。

テーブル1の仮想生成列v_eへのインデックスの作成は、テーブル2の基底列へのそれよりわずかにコストが高く、これは仮想生成列へのインデックス作成が値の計算を必要とする為である。しかしながら、このケースにおいては、ストアド生成列の同じ列にインデックスを作成するより高速である。これはただ単に仮想列のあるテーブルは大きいため、単純なスキャンに時間がかかるためである。

仮想生成列にインデックスを付与した際のコストのスケールを示す為に、テーブルtへのCREATE INDEXのいくつかの結果を示す。

(※) テーブル1は仮想生成列のあるテーブルt

既に述べた通り、仮想生成列へのインデックス作成は、各行への計算が必要となるため、通常の列へのインデックス作成よりコストがかかる。

列の追加

通常のテーブルまたはストアド生成列のあるテーブルでは、ALTER TABLE ... ADD COLUMNを行うのに通常テーブル全体の再構成が必要となる。しかし仮想生成列を追加し、それが必要とされなければほぼ一瞬でおわる。

列の削除

同じように、仮想生成列の削除はいくつかの理由で、はるかに高速である。

仮想列または「関数インデックス」を用いたDML

INSERT

この例で示されたように、インデックスが作成された仮想生成列のあるテーブルへの挿入時間は、クラスタインデックス(プライマリキー)に値をマテリアライズするテーブル3に比べてはるかに短い。

UPDATE

次のUPDATE文が3つのテーブルに実施され結果は下記の通りであった。

mysql> update t set e=e+1;

お分かりの通り、インデックスが作成された仮想生成列へのUPDATE文はよりコストがかかる。これは追加のMVCCのコストが操作(e列関連のあらゆる操作に加え)に関連していることを実証している。なぜならば、

1. v_eの古い値が計算される必要がある(UNDOログ用)
2. v_eの新旧の値がUNDOログに記録される必要がある

為である。

DELETE

DELETE文に関しては、ストアド生成列があるテーブルより、仮想生成列があるテーブルの方が高速である。仮想生成列のあるテーブルは明らかにマテリアライズされたカラムのあるテーブルより小さい。従って削除操作ははるかに高速となる。

DELETE操作は、仮想生成列にインデックスが作成されている場合は、いくらか追加のMVCCの操作も必要となる。なぜならば、

1. v_eの古い値は計算される必要がある(UNDOログ用)
2. v_eの新旧の値がUNDOログに記録される必要がある

為である。従って、DELETE文は通常の列を利用するよりもわずかにコストが高いが、ストアド生成列に比べるとはるかに高速である。

SELECTクエリ

もちろん、お察しの通り、ストアド生成列のあるテーブルは、仮想生成列のあるテーブルよりはるかに大きい。これはテーブルスキャンを簡単に行ってみれば明らかである(事前にデータをバッファプール上に持ってくる為クエリを実行しておく)。

上記で示された通り、ストアド生成列のあるテーブルへのスキャンは、仮想生成列のあるテーブルへのスキャンより3倍時間がかかった。

仮想生成列および仮想インデックスがあるテーブルは小さい一方で、クエリを効果的に実行させる為のマテリアライズ(セカンダリ)インデックスをもつ長所を活かしている。

char_length(a)への「関数インデックス」がなければ、テーブル2は結果を得る為にフルテーブルスキャンが必要である。

まとめ

仮想列と関数インデックスはMySQL 5.7で公式に動作するようになった! これはすばらしい機能で、ユーザーが仮想生成列を追加/削除可能であり、同時にこれらの列に対してオプションでセカンダリインデックスを作成することができ、かつこれがオンライン操作で可能である。上記の簡単な性能調査で示した通り、マテリアライズしたデータは基底テーブルの容量を小さくし(InnoDBのクラスタ/プライマリインデックスの重複したコピーをもたないため)、永続化ストレージ領域およびメモリの利用をより効率化し、同時にクエリの性能を大幅に改善する。