スマートスタイル TECH BLOG

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

MySQL8.0でJSONの配列にインデックスを使う

今更となりますが、2019年7月22日にリリースされた MySQL 8.0.17 で Multi-Valued Indexes 機能が追加されました。
(日本語リファレンスでは 複数値インデックス と記載されています。)

Multi-Valued Indexes は、JSON の値の配列に対するインデックス付けを目的とした機能です。
MySQLでJSON型を使用する場合は、インデックスを使用する際には、GENERATEDカラムを用いてインデックスを作成するのが、王道となっております。
これについては、以下のリファレンスにも記載されております。

また、弊社ブログの MySQLでJSON型を使う(パフォーマンス編) でもご紹介させて頂きました。

しかしながら、MySQL 8.0.16 以前のバージョンでは、以下のようなJSONの値の配列に対して検索を行うケースにおいては、GENERATEDカラムを用いたインデックスでは速度改善に繋がらないケースがあるという課題がありました。

この課題に MySQL 8.0.17 以上のバージョンをお使いであれば、Multi-Valued Indexes を用いて改善する事が見込めるはずです。
それでは、Multi-Valued Indexes を用いて検索速度が向上するのかを確認していきたいと思います。

検証環境の準備

使用バージョン、設定ファイル

今回の検証には、現時点の最新バージョンとなる MySQL 8.0.26 を使用します。
設定ファイルには、デフォルトから以下の設定のみ追加しました。

JSONデータの作成(ダウンロード)

まずは、JSONデータを用意します。
データの生成には、JSON Generator を活用させて頂きます。
こちらで、5万件のレコードを生成したファイルをダウンロードしました。
(レコード件数を多くしすぎると、ブラウザがフリーズ状態となったり、提供元のサーバに負荷もかかるかと思うので、注意が必要です。)

個々のレコードは、こんな感じになっています。

tags に値の配列があるので、この項目を使用して検証を行います。

テーブルの作成

検証用に3つのテーブルを作成します。

それぞれのテーブルの違いは、以下の通りです。

テーブル名 備考
json_normal JSONデータに関してインデックスを持たない
json_generate_col JSONデータの tags の値に GENERATEDカラム を用いたインデックスを付与
json_mvi JSONデータの tags の値に Multi-Valued Indexes を用いたインデックスを付与

Multi-Valued Indexes は、以下のようにデータ型に応じた ARRAY へ CAST して定義する必要があります。
INDEX idx_tags((CAST(data->'$.tags' AS CHAR(20) ARRAY)))

また、定義するデータ型は、配列の個々の値の最大長を考慮する必要があるので、注意して下さい。
ここで定義したサイズを超える値を配列に格納しようとすると、以下のエラーが返されます。

JSONデータ登録用SQLファイルの作成

ダウンロードしたJSONファイルを、各テーブルにINSERT形式で登録できるようにしたSQLファイルを作成します。

作成したSQLファイルは、以下のような形式で5万件のINSERT文となります。

各ファイルは、INSERT文のテーブル名が異なるだけで、登録するデータ内容は、同一となります。

JSONデータ登録用SQLファイルの実行

用意したSQLファイルで、それぞれのテーブルに5万件のレコードを登録します。

5万件のレコードをINSERTしましたが、実行時間に大きな差はなく、それぞれ以下のようになりました。

対象テーブル 実行時間
インデックスなし 1分53秒
インデックスあり(GENERATEDカラム) 1分55秒
インデックスあり(Multi-Valued Indexes) 1分57秒

検索時の実行時間の差を確認するには、もう少しレコード数があった方が確認しやすい為、各テーブルを以下の要領で 5万件 から 20万件 に増やしておきます。

各テーブルに20万件のレコードを登録しましたが、テーブルデータを格納するファイルサイズは、GENERATEDカラム+インデックス と Multi-Valued Indexes で同じサイズとなりました。

Multi-Valued Indexes を用いた検索速度の比較

冒頭で案内しているリファレンスには、Multi-Valued Indexes は、WHERE句で次の関数が指定されている場合に使用されると記載されています。

MEMBER OF()JSON_OVERLAPS() は、MySQL 8.0.17 で追加された関数となります。

それぞれの関数で、Multi-Valued Indexes を使用した場合の検索速度を比較してみたいと思います。

MEMBER OF() での検索

まずは、MEMBER OF() です。
tags配列 に、 velit が含まれているレコードという条件で検索してみます。

  • インデックスなし
  • インデックスあり(GENERATEDカラム)
    MEMBER OF() の引数にはJSON型を指定する必要がある為、CASTします。
  • インデックスあり(Multi-Valued Indexes)

実行結果は以下のようになりました。

対象テーブル 実行時間
インデックスなし 0.22秒
インデックスあり(GENERATEDカラム) 0.34秒
インデックスあり(Multi-Valued Indexes) 0.17秒

数字だけを見ると、それ程大きな差はないのですが、Multi-Valued Indexes を使用すると、インデックスを使用していない場合よりも、約1.3倍 速くなりました。
また、GENERATEDカラムにインデックスを付与した場合よりも、2倍 早くなっています。

JSON_CONTAINS() での検索

次は、JSON_CONTAINS() です。
tags配列 に、 velitest の両方が含まれているレコードという条件で検索してみます。

  • インデックスなし
  • インデックスあり(GENERATEDカラム)
  • インデックスあり(Multi-Valued Indexes)

Multi-Valued Indexes のケースで、EXPLAINを確認するとインデックスが使用されていなかった為、インデックスヒント句を追加していますが、必ずしもインデックスヒントを使用する必要がある訳ではありません。
EXPLAINを確認して、狙い通り、インデックスが使用されていない場合には、インデックスヒントを追加してみましょう。

実行結果は以下のようになりました。

対象テーブル 実行時間
インデックスなし 0.58秒
インデックスあり(GENERATEDカラム) 0.69秒
インデックスあり(Multi-Valued Indexes) 0.37秒

Multi-Valued Indexes を使用すると、インデックスを使用していない場合よりも、約 1.5倍 速くなりました。
また、GENERATEDカラムにインデックスを付与した場合よりも、約 1.8倍 早くなっています。

JSON_OVERLAPS() での検索

最後は、JSON_OVERLAPS() です。
tags配列 に、 velit もしくは est のどちらかが含まれているレコードという条件で検索してみます。

  • インデックスなし
  • インデックスあり(GENERATEDカラム)
  • インデックスあり(Multi-Valued Indexes)

実行結果は以下のようになりました。

対象テーブル 実行時間
インデックスなし 0.64秒
インデックスあり(GENERATEDカラム) 0.54秒
インデックスあり(Multi-Valued Indexes) 0.36秒

Multi-Valued Indexes を使用すると、インデックスを使用していない場合よりも、約 1.7倍 速くなりました。
また、GENERATEDカラムにインデックスを付与した場合よりも、約 1.5倍 早くなっています。

まとめ

Multi-Valued Indexes を使用して検索速度の向上が見込める事を確認しましたが、現時点で、Multi-Valued Indexes には、オンラインDDLが使用できない等のいくつかの制限もありますので、ご使用の際には、以下のリファレンスも確認するようにしましょう。

また、MySQL8.0 以降、JSONに関する関数や、ドキュメントストアとしての機能追加も頻繁に行われており、今後も、更に便利機能がリリースされる事を期待したいと思います。

Return Top