スマートスタイル TECH BLOG

MySQLでJSON型を使う(パフォーマンス編)

MySQLではバージョン 5.7.8 以降でJSON型がサポートされるようになりました。
このシリーズでは基本編、パフォーマンス編、論理設計編と、JSON型のデータ操作方法やどのような場合に使用を検討するべきかをベンチマーク結果も踏まえて探っていきたいと思います。

今回はJSON型を使った場合のパフォーマンスについて見ていきたいと思います。

検証マシンスペックとMySQLの設定

今回はAWSのEC2上にMySQL5.7.18を構築して検証を行いました。
マシンスペックは以下です。

インスタンスタイプ: t2.medium
CPU: v2core
メモリ: 4GB

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(インデックス)の3つのパターンでINSERT、UPDATE、SELECTの速度差を見てみたいと思います。

TEXT型

JSON型

JSON型 + Virtual Column (+インデックス)

INSERT

INSERTに使用するJSONデータは JSON Generator で作成しました。

JSONフォーマットの文字列をTEXT型のカラムに5万件インサート

INSERT文

実行結果

JSONフォーマットの文字列をJSON型のカラムに5万件インサート

INSERT文

実行結果

インサート時にJSONのValidationが実行されているはずですが、パフォーマンスにはほとんど影響ないようです。

JSON_OBJECTで作成してJSON型のカラムに5万件インサート

INSERT文

実行結果

JSON_OBJECT関数でJSON文字列に変更 + インサート時にJSONのValidationの実行でもパフォーマンスにはほとんど影響ありません。

文字列JSON + Virtual Column(インデックスあり)

INSERT文

実行結果

インサート時に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文

変更箇所は eyeColor を brown から blue に変えるだけですが、全てのフィールドと値を渡す必要があります。

実行結果

JSON_SET を使って5万件アップデート

UPDATE文

SQLはJSONフォーマットの文字列をアップデートするよりもシンプルです。

実行結果

実行方法 実行時間
TEXT型 4分40秒
JSON_SET 4分36秒

JSON_SETでSQL自体はシンプルですが、どちらも全てのフィールドと値を渡して更新しているため、パフォーマンスにはそれほど差は出ませんでした。

SELECT

事前に1レコードだけguidを’1b06ee15-f591-4b99-bf22-a2b053f77fe8’に変更してこちらがヒットするSQLを1000回実行する際にかかった時間でパフォーマンスを計測します。

TEXT型の部分一致のLIKE検索

実行クエリ

JSONフォーマットですが、単なる文字列のため、部分一致のLIKE検索となります。

EXPLAIN

当然ですが、フルスキャンになってしまいます。

実行結果

部分一致のLIKE検索のため、パフォーマンスはかなり悪いです。

JSON_EXTRACTを用いた検索

実行クエリ

EXPLAIN

こちらも変わらずフルスキャンになります。

実行結果

しかし実行時間は部分一致のLIKE検索に比べて劇的に早くなりました。

Virtual Column を用いたインデックス検索

実行クエリ

EXPLAIN

インデックスが使われます。

実行時間

実行方法 実行時間
部分一致の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

 

Return Top