MySQLのJSON型カラムはVIEWに展開すると非常に扱いやすくなる

  • 5
    いいね
  • 2
    コメント

 MySQL5.7から拡張されたJSON型カラムって、VIEWに展開できるのだろうか……と、ふと疑問に思ったので、試してみたらできた
 何気に、JSONデータを取り扱う時のステートメントは長くて、直感的でもないので、クエリ書くのが面倒だなぁ……と思っていたのだが、VIEWを使うと通常のカラムを取り扱うようなSELECT文でデータを検索できるようになる。さらに、データ参照のパフォーマンスも良好なのがわかった(これは間違ってました)。

JSON型カラムをVIEWに展開

 実例を交えて紹介していく。まず、JSON型カラムを含むテーブルを作る。

mysql> create table users (
    -> id bigint(20) unsigned not null auto_increment,
    -> name varchar(100),
    -> email varchar(255),
    -> meta json,
    -> created_at datetime not null default '0000-00-00 00:00:00',
    -> updated_at timestamp not null default current_timestamp on update current_timestamp,
    -> primary key(id))
    -> engine=InnoDB default charset=utf8mb4 auto_increment=1;
Query OK, 0 rows affected (0.16 sec)

mysql> desc users;
+------------+---------------------+------+-----+---------------------+-----------------------------+
| Field      | Type                | Null | Key | Default             | Extra                       |
+------------+---------------------+------+-----+---------------------+-----------------------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment              |
| name       | varchar(100)        | YES  |     | NULL                |                             |
| email      | varchar(255)        | YES  |     | NULL                |                             |
| meta       | json                | YES  |     | NULL                |                             |
| created_at | datetime            | NO   |     | 0000-00-00 00:00:00 |                             |
| updated_at | timestamp           | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------+---------------------+------+-----+---------------------+-----------------------------+
6 rows in set (0.00 sec)

 ユーザ属性をJSON型のmetaカラムに格納する建付けのテーブルで、属性は後付けでいくらでも拡張できるようなイメージだ。
 では、テスト用の初期データを入れてみる。

mysql> insert into users (name, email, meta) values
    -> ('Taro Yamada', 'yamada@dummy.email.com', '{"birthday":"1985-01-01","prefecture":"Tokyo","gender":"male"}'),
    -> ('Hanako Sato', 'sato@dummy.email.com','{"prefecture":"Osaka","gender":"female"}'),
    -> ('Ichiro Suzuki', 'suzuki@dummy.email.com','{"birthday":"1992-05-05","prefecture":"Kyoto","gender":"male"}'),
    -> ('Kenji Tanaka', 'tanaka@dummy.email.com', '{"gender":"male"}');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+---------------+------------------------+---------------------------------------------------------------------+---------------------+---------------------+
| id | name          | email                  | meta                                                                | created_at          | updated_at          |
+----+---------------+------------------------+---------------------------------------------------------------------+---------------------+---------------------+
|  1 | Taro Yamada   | yamada@dummy.email.com | {"gender": "male", "birthday": "1985-01-01", "prefecture": "Tokyo"} | 0000-00-00 00:00:00 | 2017-06-06 12:30:19 |
|  2 | Hanako Sato   | sato@dummy.email.com   | {"gender": "female", "prefecture": "Osaka"}                         | 0000-00-00 00:00:00 | 2017-06-06 12:30:19 |
|  3 | Ichiro Suzuki | suzuki@dummy.email.com | {"gender": "male", "birthday": "1992-05-05", "prefecture": "Kyoto"} | 0000-00-00 00:00:00 | 2017-06-06 12:30:19 |
|  4 | Kenji Tanaka  | tanaka@dummy.email.com | {"gender": "male"}                                                  | 0000-00-00 00:00:00 | 2017-06-06 12:30:19 |
+----+---------------+------------------------+---------------------------------------------------------------------+---------------------+---------------------+
4 rows in set (0.00 sec)

 これで準備OKだ。
 さて、ここでJSON型カラムからデータを取り出してみる。

mysql> select id,name,email,json_extract(meta, '$.birthday') as birthday from users;
+----+---------------+------------------------+--------------+
| id | name          | email                  | birthday     |
+----+---------------+------------------------+--------------+
|  1 | Taro Yamada   | yamada@dummy.email.com | "1985-01-01" |
|  2 | Hanako Sato   | sato@dummy.email.com   | NULL         |
|  3 | Ichiro Suzuki | suzuki@dummy.email.com | "1992-05-05" |
|  4 | Kenji Tanaka  | tanaka@dummy.email.com | NULL         |
+----+---------------+------------------------+--------------+
4 rows in set (0.01 sec)

 json_extract()の構文が長ったらしくてコーディングのコストが高い。なので、JSON_PATHを使った短縮形のクエリを使ってみる。

mysql> select id,name,email,meta->'$.birthday' as birthday from users;
+----+---------------+------------------------+--------------+
| id | name          | email                  | birthday     |
+----+---------------+------------------------+--------------+
|  1 | Taro Yamada   | yamada@dummy.email.com | "1985-01-01" |
|  2 | Hanako Sato   | sato@dummy.email.com   | NULL         |
|  3 | Ichiro Suzuki | suzuki@dummy.email.com | "1992-05-05" |
|  4 | Kenji Tanaka  | tanaka@dummy.email.com | NULL         |
+----+---------------+------------------------+--------------+
4 rows in set (0.00 sec)

 ややクエリが短くなったが、まだ可読性に難がある。
 そこで、JSONデータを展開したVIEWを作る。

mysql> create view user_view as select id,name,email,meta->'$.birthday' as birthday,meta->'$.prefecture' as prefecture,meta->'$.gender' as gender from users;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from user_view;
+----+---------------+------------------------+--------------+------------+----------+
| id | name          | email                  | birthday     | prefecture | gender   |
+----+---------------+------------------------+--------------+------------+----------+
|  1 | Taro Yamada   | yamada@dummy.email.com | "1985-01-01" | "Tokyo"    | "male"   |
|  2 | Hanako Sato   | sato@dummy.email.com   | NULL         | "Osaka"    | "female" |
|  3 | Ichiro Suzuki | suzuki@dummy.email.com | "1992-05-05" | "Kyoto"    | "male"   |
|  4 | Kenji Tanaka  | tanaka@dummy.email.com | NULL         | NULL       | "male"   |
+----+---------------+------------------------+--------------+------------+----------+
4 rows in set (0.00 sec)

 VIEW作成のクエリが長いのは一度切りと割り切ろう。一旦VIEW作ってしまえば、あとは、通常のカラムを扱う感じにJSON型のデータを参照できるようになる。

mysql> select name,birthday,gender from user_view where prefecture = 'Tokyo';
+-------------+--------------+--------+
| name        | birthday     | gender |
+-------------+--------------+--------+
| Taro Yamada | "1985-01-01" | "male" |
+-------------+--------------+--------+
1 row in set (0.02 sec)

 なお、一般のカラムをVIEWに展開した場合、(制限があるものの)VIEWに対してUPDATEやINSERTを行うことで、オリジナルテーブルの同カラムのデータを更新できるが、JSON型のカラムをVIEWに展開した場合には更新は一切行えないので注意が必要だ(下記参照)。

mysql> update user_view set name = 'Jiro Yamada' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,name,email,meta from users where id = 1;
+----+-------------+------------------------+---------------------------------------------------------------------+
| id | name        | email                  | meta                                                                |
+----+-------------+------------------------+---------------------------------------------------------------------+
|  1 | Jiro Yamada | yamada@dummy.email.com | {"gender": "male", "birthday": "1985-01-01", "prefecture": "Tokyo"} |
+----+-------------+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update user_view set birthday="2000-01-01" where id = 1;
ERROR 1348 (HY000): Column 'birthday' is not updatable

パフォーマンスの検証

 MySQLのVIEWは、VIEWに対してクエリが発行されるたびに毎回VIEW(ほぼテーブルと同義)を作成するのでオーバーヘッドがあるとよく云われるが、それは複数テーブルをJOINしたり、サブクエリが入っていたり、特定カラムの値を集計した結果値を含むといった、複雑なVIEWの場合だけだ。この記事の例のように、単一テーブルを参照用に展開しただけのものについてはオーバーヘッドは気にする必要がない。
 実際に、このテスト用のテーブルに100万レコード以上のデータを入れて、パフォーマンスを検証してみた。

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|  1001898 |
+----------+
1 row in set (1.72 sec)

 Prefectureが "Okinawa" かつgenderが "male" で、birthdayに 値がある データを、birthdayの昇順で10件検索してみる。
 まず、オリジナルのテーブルに対してSELECT文を発行してみた:

mysql> select id,name,meta->'$.birthday' as birthday from users where json_contains(meta, '{"prefecture":"Okinawa","gender":"male"}') and meta->'$.birthday' is not null order by meta->'$.birthday' limit 10;
+--------+----------------------------------+--------------+
| id     | name                             | birthday     |
+--------+----------------------------------+--------------+
| 490540 | a74fe07f63221e1f9df6f2d3d4663f8d | "1970-01-03" |
| 920166 | 2bf83d2434bd00aea57e2646830e501c | "1970-01-04" |
| 117837 | 92d4dc27795a82ab2348f61267446851 | "1970-01-10" |
| 691034 | 01f898146cd3f0f3d08bc8421027d3ad | "1970-01-11" |
| 173995 | d846580508c9c3cb8cf997ef602811c0 | "1970-01-19" |
| 306277 | 9c69ea4081a54a66442561f0dcc1d9b4 | "1970-01-20" |
| 880987 | c68f80d3b4c69fee2042ab727d3cdf0f | "1970-01-21" |
| 469553 | cdddc3dc97319c74be5ec24368ec92c9 | "1970-01-22" |
| 572468 | 5b2fda639d3691f40a0695deae530a44 | "1970-02-06" |
| 823204 | 7da999ace90fbaad4db9a48b7d56b117 | "1970-02-28" |
+--------+----------------------------------+--------------+
10 rows in set (15.48 sec)

 だいぶ検索に時間がかかった(15秒超)。
 次に、VIEWに対してSELECT文を発行してみる:

mysql> select id,name,birthday from user_view where prefecture = 'Okinawa' and gender = 'male' and birthday is not null order by birthday limit 10;
+--------+----------------------------------+--------------+
| id     | name                             | birthday     |
+--------+----------------------------------+--------------+
| 490540 | a74fe07f63221e1f9df6f2d3d4663f8d | "1970-01-03" |
| 920166 | 2bf83d2434bd00aea57e2646830e501c | "1970-01-04" |
| 117837 | 92d4dc27795a82ab2348f61267446851 | "1970-01-10" |
| 691034 | 01f898146cd3f0f3d08bc8421027d3ad | "1970-01-11" |
| 173995 | d846580508c9c3cb8cf997ef602811c0 | "1970-01-19" |
| 306277 | 9c69ea4081a54a66442561f0dcc1d9b4 | "1970-01-20" |
| 880987 | c68f80d3b4c69fee2042ab727d3cdf0f | "1970-01-21" |
| 469553 | cdddc3dc97319c74be5ec24368ec92c9 | "1970-01-22" |
| 572468 | 5b2fda639d3691f40a0695deae530a44 | "1970-02-06" |
| 823204 | 7da999ace90fbaad4db9a48b7d56b117 | "1970-02-28" |
+--------+----------------------------------+--------------+
10 rows in set (6.00 sec)

 パフォーマンスは歴然(6秒)で、VIEWへの参照の方が倍以上速い。

──と、思っていたのだが、これは真実ではなかった。

 次のクエリをオリジナルのテーブルに試してみた:

mysql> select id,name,meta->'$.birthday' as birthday
    ->   from users
    ->   where
    ->     meta->'$.prefecture' = 'Okinawa' and meta->'$.gender' = 'male' and meta->'$.birthday' is not null
    ->     order by meta->'$.birthday'
    ->     limit 10;
+--------+----------------------------------+--------------+
| id     | name                             | birthday     |
+--------+----------------------------------+--------------+
| 490540 | a74fe07f63221e1f9df6f2d3d4663f8d | "1970-01-03" |
| 920166 | 2bf83d2434bd00aea57e2646830e501c | "1970-01-04" |
| 117837 | 92d4dc27795a82ab2348f61267446851 | "1970-01-10" |
| 691034 | 01f898146cd3f0f3d08bc8421027d3ad | "1970-01-11" |
| 173995 | d846580508c9c3cb8cf997ef602811c0 | "1970-01-19" |
| 306277 | 9c69ea4081a54a66442561f0dcc1d9b4 | "1970-01-20" |
| 880987 | c68f80d3b4c69fee2042ab727d3cdf0f | "1970-01-21" |
| 469553 | cdddc3dc97319c74be5ec24368ec92c9 | "1970-01-22" |
| 572468 | 5b2fda639d3691f40a0695deae530a44 | "1970-02-06" |
| 823204 | 7da999ace90fbaad4db9a48b7d56b117 | "1970-02-28" |
+--------+----------------------------------+--------------+
10 rows in set (5.54 sec)

 つまりは、パフォーマンスの差は、MySQLのJSON関数とJSONPathとで如実に出るということだ。VIEW生成時にJSONPathを使っていたので、あたかもVIEWとオリジナルテーブルとでパフォーマンスが異なると思いこんでいたが、実際はJSONPath自体がJSON関数よりもパフォーマンスが良かっただけだった。

まとめ

 JSON型カラムを持つテーブルは、参照用のVIEWを作成して取り扱うことで、SQLの記述コストを向上させられることがわかった。また、JSON型のデータを取り扱う際は、JSON関数ではなくJSONPathを使った方が劇的にパフォーマンスが良くなることもわかった。INSERTやUPDATE、DELETEといった書き込みはオリジナルテーブルへ、参照系はすべからくJSONPathで作成したVIEWに行うことで、MySQL内のJSONデータを効率的に操作できるようになるのではないだろうか。

 ただ、難点もあって、JSON型カラムの特長でもあるデータ構造をフレキシブルに変更できる点をVIEWに反映するのが難しい。例えば、JSON型のmetaカラムに新たにzipcode等の新たなプロパティを追加した場合、既にあるVIEWをDROPして新たにVIEWを作成しない限り、VIEWを使ったzipcodeの検索はできないのだ。

 あと、惜しむらくは、VIEWにINDEXが貼れないことだ。それが出来れば、INDEXが設定できないJSON型カラムもVIEW経由でINDEXが使えるのだがなぁ……。

 なお、Generated Columnsを使うことでJSON型カラムにもINDEXを貼ることができるとのことだ。これは詳しく調べねば。