MySQLではバージョン 5.7.8 以降でJSON型がサポートされるようになりました。
このシリーズでは基本編、パフォーマンス編、論理設計編と、JSON型のデータ操作方法やどのような場合に使用を検討するべきかをベンチマーク結果も踏まえて探っていきたいと思います。
- MySQLでJSON型を使う（基本編）
- MySQLでJSON型を使う（パフォーマンス編）
- MySQLでJSON型を使う（論理設計編）
今回はJSON型を使った場合のパフォーマンスについて見ていきたいと思います。
検証マシンスペックとMySQLの設定
今回はAWSのEC2上にMySQL5.7.18を構築して検証を行いました。
マシンスペックは以下です。
インスタンスタイプ： t2.medium
CPU: v2core
メモリ: 4GB
MySQLで設定変更したのは以下のみで、後はデフォルトです。
[mysql]
[mysqld]
innodb_buffer_pool_size=3G
[/mysql]
JSON型はインデックスが使えません。
TEXT型やBLOB型と同様にインデックスは使えません。
参考 ： MySQL :: MySQL 5.7 Reference Manual :: 14.1.18.8 Secondary Indexes and Generated Columns
ただし、特定のフィールドだけインデックスを張りたいという場合は、Virtual Column を作成してそこにインデックスを張れば使うことができます。
参考 ： Indexing JSON documents via Virtual Columns | MySQL Server Blog
ちなみに、MariaDB 10.2.3 では Virtual Columns へのインデックスは未サポートなので、PERSISTENT で作成する必要があります。
参考 ： Virtual (Computed) Columns
テーブル定義
今回のパフォーマンステストでは、JSONフォーマットのテキストを以下のTEXT型、JSON型、JSON型+Virtual Column（インデックス）の３つのパターンでINSERT、UPDATE、SELECTの速度差を見てみたいと思います。
TEXT型
|
1 2 3 4 5 |
CREATE TABLE <code>text</code> ( id</code> int(11) NOT NULL AUTO_INCREMENT, text</code> mediumtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (<code>id</code>) ); |
JSON型
|
1 2 3 4 5 |
CREATE TABLE <code>json</code> ( id</code> int(11) NOT NULL AUTO_INCREMENT, json</code> json DEFAULT NULL, PRIMARY KEY (<code>id</code>) ); |
JSON型 + Virtual Column （＋インデックス）
|
1 2 3 4 5 6 7 |
CREATE TABLE <code>json_vc</code> ( id</code> int(11) NOT NULL AUTO_INCREMENT, json</code> json DEFAULT NULL, vc</code> varchar(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(<code>json</code>, '$.guid'))) VIRTUAL, PRIMARY KEY (<code>id</code>), KEY idx_vc (<code>vc</code>) ); |
INSERT
INSERTに使用するJSONデータは JSON Generator で作成しました。
[mysql]
{
"_id": "58f884046309022eced97572",
"index": 0,
"guid": "7c70dc5e-5d98-4d94-8b88-e54050a11d71",
"isActive": true,
"age": 38,
"eyeColor": "brown",
"name": "Mathews Cantrell",
"gender": "male",
"company": "NORSUP",
"email": "mathewscantrell@example.com",
"registered": "2016-12-25T03:44:09 -09:00",
"latitude": 44.302937,
"longitude": 3.081185,
"tags": [
"voluptate",
"ex",
"sit",
"proident",
"deserunt",
"pariatur",
"labore"
],
"greeting": "Hello, Mathews Cantrell! You have 9 unread messages.",
"favoriteFruit": "strawberry"
}
[/mysql]
JSONフォーマットの文字列をTEXT型のカラムに5万件インサート
INSERT文
[mysql]
INSERT INTO text
VALUES (null, ‘{"_id":"58f884046309022eced97572","index":0,"guid":"7c70dc5e-5d98-4d94-8b88-e54050a11d71","isActive":true,"age":38,"eyeColor":"brown","name":"Mathews Cantrell","gender":"male","company":"NORSUP","email":"mathewscantrell@example.com","registered":"2016-12-25T03:44:09 -09:00","latitude":44.302937,"longitude":3.081185,"tags":["voluptate","ex","sit","proident","deserunt","pariatur","labore"],"greeting":"Hello, Mathews Cantrell! You have 9 unread messages.","favoriteFruit":"strawberry"}’);
[/mysql]
実行結果
[sh]
$ time ./insert_plain_text.sh
real 4m28.527s
user 0m5.364s
sys 0m6.596s
[/sh]
JSONフォーマットの文字列をJSON型のカラムに5万件インサート
INSERT文
[mysql]
INSERT INTO json
VALUES (null, ‘{"_id":"58f884046309022eced97572","index":0,"guid":"7c70dc5e-5d98-4d94-8b88-e54050a11d71","isActive":true,"age":38,"eyeColor":"brown","name":"Mathews Cantrell","gender":"male","company":"NORSUP","email":"mathewscantrell@example.com","registered":"2016-12-25T03:44:09 -09:00","latitude":44.302937,"longitude":3.081185,"tags":["voluptate","ex","sit","proident","deserunt","pariatur","labore"],"greeting":"Hello, Mathews Cantrell! You have 9 unread messages.","favoriteFruit":"strawberry"}’);
[/mysql]
実行結果
[sh]
$ time ./insert_json_text.sh
real 4m35.597s
user 0m5.136s
sys 0m6.720s
[/sh]
インサート時にJSONのValidationが実行されているはずですが、パフォーマンスにはほとんど影響ないようです。
JSON_OBJECTで作成してJSON型のカラムに5万件インサート
INSERT文
[mysql]
INSERT INTO text
VALUES (null, JSON_OBJECT("_id", "58f884046309022eced97572", "index", 0, "guid", "7c70dc5e-5d98-4d94-8b88-e54050a11d71", "isActive", true, "age", 38, "eyeColor", "brown", "name", "Mathews Cantrell", "gender", "male", "company", "NORSUP", "email", "mathewscantrell@example.com", "registered", "2016-12-25T03:44:09 -09:00", "latitude", 44.302937, "longitude", 3.081185, "tags", JSON_ARRAY("voluptate", "ex", "sit", "proident", "deserunt", "pariatur", "labore"), "greeting", "Hello, Mathews Cantrell! You have 9 unread messages.", "favoriteFruit", "strawberry"));
[/mysql]
実行結果
[sh]
$ time ./insert_json_object.sh
real 4m32.630s
user 0m5.148s
sys 0m6.672s
[/sh]
JSON_OBJECT関数でJSON文字列に変更 + インサート時にJSONのValidationの実行でもパフォーマンスにはほとんど影響ありません。
文字列JSON + Virtual Column（インデックスあり）
INSERT文
[mysql]
INSERT INTO json_vc (json)
VALUES (‘{"_id":"58f884046309022eced97572","index":0,"guid":"7c70dc5e-5d98-4d94-8b88-e54050a11d71","isActive":true,"age":38,"eyeColor":"brown","name":"Mathews Cantrell","gender":"male","company":"NORSUP","email":"mathewscantrell@example.com","registered":"2016-12-25T03:44:09 -09:00","latitude":44.302937,"longitude":3.081185,"tags":["voluptate","ex","sit","proident","deserunt","pariatur","labore"],"greeting":"Hello, Mathews Cantrell! You have 9 unread messages.","favoriteFruit":"strawberry"}’);
[/mysql]
実行結果
[sh]
$ time ./insert_json_vc_text.sh
real 4m33.446s
user 0m5.268s
sys 0m6.828s
[/sh]
インサート時にJSONのValidationの実行 + Virtual Columnのインデックス作成が行われるがこちらもパフォーマンスにはそれほど影響は無いと考えられます。
| 実行方法 | 実行時間 |
|---|---|
| JSON文字列 => TEXT型 | 4分28秒 |
| JSON文字列 => JSON型 | 4分35秒 |
| JSON_OBJECT => JSON型 | 4分32秒 |
| JSON文字列 => JSON型 + Virtual Column | 4分33秒 |
UPDATE
TEXT型のJSONフォーマットの文字列を5万件アップデート
UPDATE文
[mysql]
UPDATE json
SET json='{"_id":"58f884046309022eced97572","index":0,"guid":"7c70dc5e-5d98-4d94-8b88-e54050a11d71","isActive":true,"age":38,"eyeColor":"blue","name":"Mathews Cantrell","gender":"male","company":"NORSUP","email":"mathewscantrell@example.com","registered":"2016-12-25T03:44:09 -09:00","latitude":44.302937,"longitude":3.081185,"tags":["voluptate","ex","sit","proident","deserunt","pariatur","labore"],"greeting":"Hello, Mathews Cantrell! You have 9 unread messages.","favoriteFruit":"strawberry"}’)
WHERE id={ID};
[/mysql]
変更箇所は eyeColor を brown から blue に変えるだけですが、全てのフィールドと値を渡す必要があります。
実行結果
[sh]
$ time ./update_plain_text.sh
real 4m40.188s
user 0m5.568s
sys 0m6.716s
[/sh]
JSON_SET を使って5万件アップデート
UPDATE文
[mysql]
UPDATE json
SET json=JSON_SET(json, ‘$.eyeColor’, ‘green’)
WHERE id={ID};
[/mysql]
SQLはJSONフォーマットの文字列をアップデートするよりもシンプルです。
実行結果
[sh]
$ time ./update_json_set.sh
real 4m36.274s
user 0m4.460s
sys 0m6.668s
[/sh]
| 実行方法 | 実行時間 |
|---|---|
| TEXT型 | 4分40秒 |
| JSON_SET | 4分36秒 |
JSON_SETでSQL自体はシンプルですが、どちらも全てのフィールドと値を渡して更新しているため、パフォーマンスにはそれほど差は出ませんでした。
SELECT
事前に1レコードだけguidを’1b06ee15-f591-4b99-bf22-a2b053f77fe8’に変更してこちらがヒットするSQLを1000回実行する際にかかった時間でパフォーマンスを計測します。
TEXT型の部分一致のLIKE検索
実行クエリ
[mysql]
SELECT id
FROM text
WHERE text LIKE ‘%"guid":"1b06ee15-f591-4b99-bf22-a2b053f77fe8"%’
[/mysql]
JSONフォーマットですが、単なる文字列のため、部分一致のLIKE検索となります。
EXPLAIN
[mysql]
mysql> EXPLAIN SELECT id FROM text WHERE text LIKE ‘%"guid":"1b06ee15-f591-4b99-bf22-a2b053f77fe8"%’\G
1. row
id: 1
select_type: SIMPLE
table: text
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 95337
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
[/mysql]
当然ですが、フルスキャンになってしまいます。
実行結果
[sh]
$ time ./search_like.sh
real 10m56.742s
user 0m0.080s
sys 0m0.104s
[/sh]
部分一致のLIKE検索のため、パフォーマンスはかなり悪いです。
JSON_EXTRACTを用いた検索
実行クエリ
[mysql]
SELECT id
FROM json
WHERE JSON_EXTRACT(json, ‘$.guid’) = ‘1b06ee15-f591-4b99-bf22-a2b053f77fe8’;
[/mysql]
EXPLAIN
[mysql]
mysql> EXPLAIN SELECT id FROM json WHERE JSON_EXTRACT(json, ‘$.guid’) = ‘1b06ee15-f591-4b99-bf22-a2b053f77fe8’\G
1. row
id: 1
select_type: SIMPLE
table: json
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 47970
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
[/mysql]
こちらも変わらずフルスキャンになります。
実行結果
[sh]
$ time ./search_json_extract.sh
real 0m49.388s
user 0m0.052s
sys 0m0.132s
[/sh]
しかし実行時間は部分一致のLIKE検索に比べて劇的に早くなりました。
Virtual Column を用いたインデックス検索
実行クエリ
[mysql]
SELECT id
FROM json_vc
WHERE vc = ‘1b06ee15-f591-4b99-bf22-a2b053f77fe8’;
[/mysql]
EXPLAIN
[mysql]
mysql> EXPLAIN SELECT id FROM json_vc WHERE vc = ‘1b06ee15-f591-4b99-bf22-a2b053f77fe8’\G
1. row
id: 1
select_type: SIMPLE
table: json_vc
partitions: NULL
type: ref
possible_keys: idx_vc
key: idx_vc
key_len: 259
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
[/mysql]
インデックスが使われます。
実行時間
[sh]
$ time ./search_virtual.sh
real 0m2.671s
user 0m0.048s
sys 0m0.072s
[/sh]
| 実行方法 | 実行時間 |
|---|---|
| 部分一致のLIKE検索 | 10分56秒 |
| JSON_EXTRACT | 0分49秒 |
| Virtual Column （インデックス） | 0分2秒 |
まとめ
JSON型をベンチマークの観点から検証を行いました。
JSON型のINSERTやUPDATEはTEXT型と比べてもパフォーマンスが大きく変わることはありませんでした。
TEXT型にJSON文字列を格納していた場合、検索条件にJSONのフィールドを使う場合、速度面や検索条件（LIKE検索なので値が数値の場合、以上や未満といった指定ができない等）に制限があるため、あまり実用的ではありませんでした。
JSON_EXTRACT関数を使うことで深いパスにあるフィールドにも問題無くアクセスできますし、数値の比較も可能となりました。
（インデックスは使えませんが、LIKE検索よりも高速です）
また、Virtual Columnを使うことで、特定のパスにインデックスを使った検索も可能です。
- INSERTとUPDATEはJSON型でも更新パフォーマンスは変わらない。
- JSON_EXTRACT関数などを使うことで複雑な検索条件の指定も可能になった。
- インデックスを使う場合はVirtual Columnを作成してそこにインデックスを張る。
次回は「MySQLでJSON型を使う」の論理設計編です。
- MySQLでJSON型を使う（基本編）
- MySQLでJSON型を使う（パフォーマンス編）
- MySQLでJSON型を使う（論理設計編）
[seminar-mds20210423]
