LoginSignup
32
26

More than 5 years have passed since last update.

@ka215(Ka2)

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

Last updated at Posted at 2017-06-06

　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を貼ることができるとのことだ。これは詳しく調べねば。

32
26
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
32
26