免責事項
この記事はJimmy Yang氏によるMySQL Server Blogの投稿「JSON Labs Release: Effective Functional Indexes in InnoDB」(2015/4/9)をユーザが翻訳したものであり、Oracle公式の文書ではありません。
MySQL 5.7.6では、我々は生成列(Generated Columns)と呼ばれる新しい機能を追加した。最初の段階では全ての生成列は、仮想的なものであってもマテリアライズされていた。これでは不要なディスクスペースが使用され、ディスクI/Oが発生するだけでなく、いかなるテーブルの変更に対してもテーブルを完全にリビルドする必要がある。新しいMySQL 5.7.7のJSON Labリリースでは、全ての問題を新しい機能、すなわちユーザーがマテリアライズされない仮想列を生成できるだけでなく、インデックスを作成できる機能、これをInnoDBを実装することで解決した。仮想列のデータは関数を利用して生成することもできるので、ある程度までは、"仮想インデックス"を関数インデックスまたは関数ベースのインデックスとしてみることができる。
このブログ投稿では、仮想列と仮想インデックスの設計について少し詳細にみて、InnoDBでどのように実装されているかの理解をお手伝いしようと思う。
InnoDBにおける仮想列
まずはInnoDB内部で仮想列がどのように表現されるかをみてみよう。簡単にいうと、仮想列は今やInnoDBのユーザーテーブルにも、クラスタインデックスにも全く保存されず、InnoDBのシステムテーブル上で表現される。詳細をみてみよう。
1. 仮想列はInnoDBのテーブル内には保存されない
仮想列はInnoDBにおいて今や真の"仮想"であり、これはInnoDBはクラスタインデックス(テーブルのデータを基本的に格納するのに使われる)内の列にいかなるデータも保存しないことを意味する。例を見てみよう。
# 仮想列の例
mysql> CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), PRIMARY KEY(a));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t VALUES (11, 3, default);
Query OK, 1 row affected (0.00 sec)
ここで列'c'は仮想列である。InnoDBのこのテーブルの物理的なデータ配置を見てみると、2つのユーザー列'a'と'b'および、2つの標準であるInnoDBの隠し/内部列(DATA_TRX_IDとDATA_ROLL_PTR)しか持っていない
# 物理的なレコード配置
not-deleted PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000b; asc ;; /* column 'a' */
1: len 6; hex 00000000670b; asc g ;; /* InnoDB hidden column */
2: len 7; hex a90000011d0110; asc ;; /* InnoDB hidden column */
3: len 4; hex 80000003; asc ;; /* column 'b' */
したがって'c'列はInnoDBのテーブルや列として保存されておらず、代わりにテーブルにクエリを発行した時にその場で計算される。
# クエリの例
mysql> SELECT * FROM t;
+----+------+------+
| a | b | c |
+----+------+------+
| 11 | 3 | 14 |
+----+------+------+
1 row in set (0.00 sec)
2. 仮想列のメタデータの表現
仮想列自体はInnoDB内に保存されないとしても、メタデータはある。そのような列にセカンダリインデックスを作成するのをサポートする為にメタデータを保存する必要があるのだ。
仮想列のメタデータ情報は、InnoDBのSYS_COLUMNSシステムテーブルに他の列と共に保存されており、'PRTYPE'の値に追加のDATA_VIRTUAL(8192)ビットが設定されている点のみが異なる。
# SYS_COLUMNSの例
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "t%");
+----------+------+-------+-------+--------+-----+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
+----------+------+-------+-------+--------+-----+
| 74 | a | 0 | 6 | 1283 | 4 |
| 74 | b | 1 | 6 | 1027 | 4 |
| 74 | c | 65538 | 6 | 9219 | 4 |
+----------+------+-------+-------+--------+-----+
3 rows in set (0.01 sec)
仮想列'c'が、SYS_COLUMNSシステムテーブルに登録されており、'PRTYPE'にDATA_VIRTUAL(8192)ビットがたっている点に注意して欲しい。'POS'フィールドもまた特別で、元のテーブルの位置(第3カラム)と、仮想列としてのシークエンス(最初の仮想列)の両方をエンコードしている。
SYS_COLUMNSシステムテーブルに加え、SYS_VIRTUALと呼ばれるシステムテーブルも新しく追加した。仮想列が他の列(基底列, the base columns)にもとづいて生成されているかどうかを記録するためである。上記の例では、'c'列は'a'列および'b'列から計算されている。
# SYS_VIRTUALの例
myql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL;
+----------+-------+----------+
| TABLE_ID | POS | BASE_POS |
+----------+-------+----------+
| 74 | 65538 | 0 |
| 74 | 65538 | 1 |
+----------+-------+----------+
2 rows in set (0.00 sec)
上記の例では、'POS'列はSYS_COLUMNS内の仮想列の'POS'の値を表し(この例では'c'列)、'BASE_POS'はSYS_COLUMNS内の基底列の'POS'の値(0は'a'列、1は'b'列)を表す。現在のところ、"基底列"は通常のマテリアライズされた列のみで構成可能であり、他の生成列からは構成できない。
仮想列が他の標準の列と同様にシステムテーブルに追加されたが、これらは別ドメインに表現されておりメモリ内のメタデータを格納する為の通常の列ではない。このようにして、InnoDBにはほとんど変更を加える必要はない。なぜならば、大多数のケースで仮想列が存在しないものとして動作し続けるからである。しかし、それと同時に実際に必要になればいつでも仮想列の情報を取得することができる。例えば、dict_table_t::cols構造体は通常のマテリアライズされた列の全ての情報を持っており、一方で新しいdict_table_t::v_cols構造体は仮想またはマテリアライズされない列の全ての情報を持っている。
この設計において、仮想列は簡単に追加して削除することができ、かつテーブル全体をリビルドする必要もない。これにより関連するテーブルのスキーマ変更がとても簡単かつ高速になる。
# 高速な仮想列の管理
mysql> ALTER TABLE t ADD new_col INT GENERATED ALWAYS AS (a - b) VIRTUAL;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t;
+----+------+------+---------+
| a | b | c | new_col |
+----+------+------+---------+
| 11 | 3 | 14 | 8 |
+----+------+------+---------+
1 row in set (0.01 sec)
マテリアライズしない仮想カラムへのインデックスの作成
前の節でみたように、仮想列はとても柔軟であり、簡単に追加や削除ができる。しかしながら、仮想列はInnoDBのクラスタインデックス内に保存されていない為、クエリで値を取得する為に条件を満たす可能性のある各列に対し、基底列のデータを取得し、必要な計算をする必要がある。これによりクエリが遅く、非効率なものになる。しかし通常の列と同じ位クエリを効率的にする方法がある!今や仮想列にセカンダリインデックスを簡単に作ることができるのだ!
ひとたびセカンダリインデックスが仮想列に作成されたら、仮想列のデータはセカンダリインデックスのレコードに必然的にマテリアライズされ格納される。これは仮想列にクエリが発行された際に、仮想列の値を計算する必要がないことを意味している。再度になるが、これは関数インデックスや関数ベースのインデックスを効果的にするものだ。
1. 仮想インデックスの作成
インデックス作成の記法は他のセカンダリインデックスを作成する時と同様である。
# 仮想列上のインデックスの作成
mysql> CREATE INDEX idx ON t(c);
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
新しい'idx'インデックスがSYS_INDEXESシステムテーブルに追加され、仮想列'c'はSYS_FIELDSシステムテーブルに追加される。
# SYS_INDEXSおよびSYS_FIELDSレコードの例
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "t%");
+----------+---------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+---------+----------+------+----------+---------+-------+-----------------+
| 123 | PRIMARY | 75 | 3 | 1 | 3 | 63 | 50 |
| 124 | idx | 75 | 128 | 1 | 4 | 63 | 50 |
+----------+---------+----------+------+----------+---------+-------+-----------------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID = 124;
+----------+------+-----+
| INDEX_ID | NAME | POS |
+----------+------+-----+
| 124 | c | 0 |
+----------+------+-----+
1 row in set (0.03 sec)
仮想列はInnoDBのメタデータシステムテーブル内にある他の列と同じように保存されるため、仮想インデックスのメタデータは通常のインデックスのメタデータと同じように表現される。
通常の列に対するインデックス作成との違いは、インデックス作成時に、インデックスがはられた列が仮想列だと分かったら、最終的に特定の生成関数を呼ぶ前にその"基底列"が取得されコールバック関数が基底列にアクセスするのに使われるところだ。一度このコールバック関数から値が計算されれば、値はソーターに渡され、後でインデックスレコードをインスタンス化するのに使われる。
2. DMLの実行
仮想列のデータは今やセカンダリインデックスを通じて"マテリアライズされ"ているため、どのようなDML(INSERT、UPDATE、DELETE)でもインデックスに影響をおよぼしうる。仮想列の値は他のインデクスがはられた列の値と同様に更新される。しかし、仮想列に対して値を直接INSERTまたはUPDATEすることはできない。代わりにINSERTとUPDATE操作は基底列の変更を通じて間接的に実行される。前に示した例のテーブルを使ってこれを実演してみよう。
# インデックスレコードの更新の実例
mysql> select * from t;
+----+------+------+---------+
| a | b | c | new_col |
+----+------+------+---------+
| 11 | 3 | 14 | 8 |
+----+------+------+---------+
1 row in set (0.00 sec)
mysql> UPDATE t SET a = 20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+----+------+------+---------+
| a | b | c | new_col |
+----+------+------+---------+
| 20 | 3 | 23 | 17 |
+----+------+------+---------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT c FROM t;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | index | NULL | idx | 5 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT c FROM t;
+------+
| c |
+------+
| 23 |
+------+
1 row in set (0.00 sec)
ご覧いただいたように、列'c'のインデックスの値は基底列'a'が更新されるのに伴い更新されている。
ここで注目いただきたいのは、データの変更がMVCC、クラッシュリカバリ、UNDO操作に対して再計算が不要であるように仮想列のDMLをログに記録していることである。もちろんこれらのインデックスがはられた仮想列への操作だけは記録される。
"関数インデックス"を使ったクエリ
仮想列の"関数インデックス"があれば、ユーザーは条件を満たした列をカバードスキャンおよびノンカバードスキャンの両方を利用して検索できる。"関数インデックス"はクエリ可能で、シナリオ(分離レベルなど)に応じて、クラスタインデックスが続いて参照される。
UNDOログ内に仮想カラムの更新も記録する為、仮想列へのクエリもMVCCをサポートする。しかしながら、最大インデックスサイズの制限を考慮する必要があり、COMPACT/REDUNDANT ROWフォーマットなら767バイト、COMPRESSED/DYNAMIC ROWフォーマットなら3072バイトである。クエリが発行されたオブジェクトがこれより長い場合は、値は基底列からその場で生成されることになる。
クエリは全ての分離レベルをサポートしており、これはしかるべき場合にはインデックスがはられた仮想列にギャップロックを生成しうることを意味する。
その上、"関数インデックス"はユニークインデックスだけでなく、プレフィックスインデックスもサポートしている。ここに新しいJSONサポートを含む全ての例を示す。
# JSONベースの例
mysql> create table employees(id bigint not null primary key auto_increment, info JSON);
Query OK, 0 rows affected (0.20 sec)
mysql> insert into employees (info) values ('{ "name": "Matt Lord", "age": 38, "Duties": { "Product Manager": ["stuff", "more stuff"]} }');
Query OK, 1 row affected (0.04 sec)
mysql> select jsn_valid(info) from employees;
+-----------------+
| jsn_valid(info) |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select id, jsn_extract(info, '$.name') from employees;
+----+-----------------------------+
| id | jsn_extract(info, '$.name') |
+----+-----------------------------+
| 1 | "Matt Lord" |
+----+-----------------------------+
1 row in set (0.00 sec)
mysql> alter table employees add name varchar(100) generated always as (jsn_extract(info, '$.name')) virtual;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table employees add index (name);
Query OK, 1 row affected (0.51 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`info` json DEFAULT NULL,
`name` varchar(100) GENERATED ALWAYS AS (jsn_extract(info, '$.name')) VIRTUAL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain format=JSON select id, name from employees where name = "Matt Lord"\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "employees",
"access_type": "ref",
"possible_keys": [
"name"
],
"key": "name",
"used_key_parts": [
"name"
],
"key_length": "103",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "128"
},
"used_columns": [
"id",
"info",
"name"
]
}
}
}
1 row in set, 1 warning (0.00 sec)
制限事項
仮想インデックス周辺には現在いくつかの制限事項があり、そのうちのいくつかをここにあげる。
- 主キーはいかなる仮想列も包含できない
- 仮想列と非仮想列の混在に対してはインデックスが生成できない
- 仮想列に空間あるいはフルテキストインデックスは生成できない(この制限事項は今後解消される)
- 仮想インデックスは外部キーとして利用できない
まとめ
要約すれば、新しく実装された仮想列、仮想インデックス、効果的な"関数インデックス"により、ユーザーは新しい仮想列をすばやく追加/削除できるようになり、そのような列にセカンダリインデックスを生成することで効果的なクエリが発行できる可能性がある。大きなTEXT/JSONフィールドやその他のリレーショナルでないデータのインデクスの理想的な解決策となり、効率的なデータ収容、操作、そのようなデータへのクエリ発行の助けとなる。
これらの新機能についての考えを教えて欲しい!みなからフィードバックを必要としており、生成列やJSONサポートの拡張関連でその他に知りたいことを教えて欲しい。新しい機能を使う上で問題に直面したら、ここのコメントで知らせていただき、bugs.mysql.comのバグレポートをオープンするかサポートチケットをオープンして欲しい。
MySQLを使ってくれてありがとう!
