スマートスタイル TECH BLOG

データベース＆クラウド技術情報

MySQLでJSON型を使う（パフォーマンス編）

81414約16分

MySQLではバージョン 5.7.8 以降でJSON型がサポートされるようになりました。
このシリーズでは基本編、パフォーマンス編、論理設計編と、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型

JSON型

JSON型 + Virtual Column （＋インデックス）

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型を使う」の論理設計編です。

[seminar-mds20210423]
MySQL

 

MySQL
Return Top