この記事は最終更新から3年以上経過しています。内容が古くなっている可能性があります。
今更となりますが、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カラムを用いたインデックスでは速度改善に繋がらないケースがあるという課題がありました。
|
1 2 3 4 5 6 7 8 |
mysql> -- color に red が含まれているレコードを抽出 mysql> SELECT * FROM t1 WHERE JSON_CONTAINS(data->'$.color', '["red"]'); +----+---------------------------------------+ | id | data | +----+---------------------------------------+ | 1 | {"color": ["red", "blue"]} | | 3 | {"color": ["white", "red", "yellow"]} | +----+---------------------------------------+ |
この課題に MySQL 8.0.17 以上のバージョンをお使いであれば、Multi-Valued Indexes を用いて改善する事が見込めるはずです。
それでは、Multi-Valued Indexes を用いて検索速度が向上するのかを確認していきたいと思います。
目次
検証環境の準備
使用バージョン、設定ファイル
今回の検証には、現時点の最新バージョンとなる MySQL 8.0.26 を使用します。
設定ファイルには、デフォルトから以下の設定のみ追加しました。
|
1 |
innodb_buffer_pool_size=3G |
JSONデータの作成（ダウンロード）
まずは、JSONデータを用意します。
データの生成には、JSON Generator を活用させて頂きます。
こちらで、5万件のレコードを生成したファイルをダウンロードしました。
（レコード件数を多くしすぎると、ブラウザがフリーズ状態となったり、提供元のサーバに負荷もかかるかと思うので、注意が必要です。）
個々のレコードは、こんな感じになっています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
{ "_id": "60e64e050fd170e92f447653", "index": 0, "guid": "8a41044d-7358-4c00-a401-3bf944acd5cf", "isActive": true, "balance": "$1,924.42", "picture": "http://placehold.it/32x32", "age": 35, "eyeColor": "blue", "name": "Lorrie Foster", "gender": "female", "company": "MAGMINA", "email": "lorriefoster@magmina.com", "phone": "+1 (800) 546-2630", "address": "995 Beard Street, Bath, Iowa, 6065", "about": "Cupidatat eu aute proident incididunt dolore nostrud. Sint minim non velit cupidatat sit adipisicing aliqua veniam adipisicing elit dolore adipisicing anim sint. Cillum non deserunt elit ullamco culpa tempor. Sunt magna adipisicing eiusmod et mollit. Minim et anim nulla proident qui mollit nulla sit voluptate esse enim proident.\r\n", "registered": "2016-04-23T12:20:19 -09:00", "latitude": 36.414446, "longitude": 127.136671, "tags": [ "aliquip", "labore", "nisi", "est", "elit", "aliquip", "sit" ], "friends": [ { "id": 0, "name": "Cara Carver" }, { "id": 1, "name": "Chase English" }, { "id": 2, "name": "Gilda Hale" } ], "greeting": "Hello, Lorrie Foster! You have 2 unread messages.", "favoriteFruit": "banana" }, |
tags に値の配列があるので、この項目を使用して検証を行います。
テーブルの作成
検証用に3つのテーブルを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE json_normal ( id INT NOT NULL AUTO_INCREMENT, data JSON, PRIMARY KEY (id) ); CREATE TABLE json_generate_col ( id INT NOT NULL AUTO_INCREMENT, data JSON, tags VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.tags'))), PRIMARY KEY (id), INDEX idx_tags(tags) ); CREATE TABLE json_mvi ( id INT NOT NULL AUTO_INCREMENT, data JSON, PRIMARY KEY (id), INDEX idx_tags((CAST(data->'$.tags' AS CHAR(20) ARRAY))) ); |
それぞれのテーブルの違いは、以下の通りです。
| テーブル名 | 備考 |
|---|---|
| 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)))
また、定義するデータ型は、配列の個々の値の最大長を考慮する必要があるので、注意して下さい。
ここで定義したサイズを超える値を配列に格納しようとすると、以下のエラーが返されます。
|
1 |
ERROR 3907 (22001): Data too long for functional index 'idx_tags'. |
JSONデータ登録用SQLファイルの作成
ダウンロードしたJSONファイルを、各テーブルにINSERT形式で登録できるようにしたSQLファイルを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# cat generated.json | jq -c . | \ sed -e "s/^\[//" -e "s/\]$//" \ -e "s/,{\"_id\"/\n{\"_id\"/g" \ -e "s/\\\/\\\\\\\/g" > temporary.json # cat temporary.json | \ sed -e "s/^/INSERT INTO json_mvi (data) VALUES (\'/g" \ -e "s/$/\');/g" > insert_json_mvi.sql # cat temporary.json | \ sed -e "s/^/INSERT INTO json_generate_col (data) VALUES (\'/g" \ -e "s/$/\');/g" > insert_json_generate_col.sql # cat temporary.json | \ sed -e "s/^/INSERT INTO json_normal (data) VALUES (\'/g" \ -e "s/$/\');/g" > insert_json_normal.sql |
作成したSQLファイルは、以下のような形式で5万件のINSERT文となります。
|
1 2 3 4 |
INSERT INTO json_normal (data) VALUES ('{"_id":"60e64e050fd170e92f447653","index":0,"guid":"8a41044d-7358-4c00-a401-3bf944acd5cf","isActive":true,"balance":"$1,924.42","picture":"http://placehold.it/32x32","age":35,"eyeColor":"blue","name":"Lorrie Foster","gender":"female","company":"MAGMINA","email":"lorriefoster@magmina.com","phone":"+1 (800) 546-2630","address":"995 Beard Street, Bath, Iowa, 6065","about":"Cupidatat eu aute proident incididunt dolore nostrud. Sint minim non velit cupidatat sit adipisicing aliqua veniam adipisicing elit dolore adipisicing anim sint. Cillum non deserunt elit ullamco culpa tempor. Sunt magna adipisicing eiusmod et mollit. Minim et anim nulla proident qui mollit nulla sit voluptate esse enim proident.\\r\\n","registered":"2016-04-23T12:20:19 -09:00","latitude":36.414446,"longitude":127.136671,"tags":["aliquip","labore","nisi","est","elit","aliquip","sit"],"friends":[{"id":0,"name":"Cara Carver"},{"id":1,"name":"Chase English"},{"id":2,"name":"Gilda Hale"}],"greeting":"Hello, Lorrie Foster! You have 2 unread messages.","favoriteFruit":"banana"}'); INSERT INTO json_normal (data) VALUES ('{"_id":"60e64e05cdbeb02dcedddbbb","index":1,"guid":"22a972fc-8697-46f2-b379-1048c917b511","isActive":false,"balance":"$2,097.69","picture":"http://placehold.it/32x32","age":28,"eyeColor":"brown","name":"Rosalinda Coffey","gender":"female","company":"FARMEX","email":"rosalindacoffey@farmex.com","phone":"+1 (997) 510-2599","address":"140 Harway Avenue, Belva, Illinois, 2404","about":"In id magna in dolor. Qui officia et sint esse eu occaecat. Minim irure aute cillum sint labore. Anim et aute est voluptate aute dolor fugiat sunt. Excepteur in ex duis aliquip ea ea sunt ea est. Magna dolore cupidatat consequat nulla quis ad laborum anim sint.\\r\\n","registered":"2017-12-09T03:32:53 -09:00","latitude":-71.800714,"longitude":29.568888,"tags":["sint","et","dolore","ut","nostrud","qui","amet"],"friends":[{"id":0,"name":"Levine Cochran"},{"id":1,"name":"Deana Griffin"},{"id":2,"name":"Sims Livingston"}],"greeting":"Hello, Rosalinda Coffey! You have 3 unread messages.","favoriteFruit":"apple"}'); INSERT INTO json_normal (data) VALUES ('{"_id":"60e64e05ce5cfef288e4f409","index":2,"guid":"1b77db69-a9e4-4f34-8d17-47ea946a6e80","isActive":true,"balance":"$1,951.94","picture":"http://placehold.it/32x32","age":29,"eyeColor":"green","name":"Bertha Fox","gender":"female","company":"EXTRAGENE","email":"berthafox@extragene.com","phone":"+1 (836) 503-2330","address":"685 Lafayette Avenue, Gadsden, Michigan, 5482","about":"Officia ullamco ullamco proident sint amet fugiat minim adipisicing exercitation qui fugiat commodo. Do occaecat id elit amet cupidatat irure eu. Consectetur elit Lorem anim laborum aute aute culpa consequat consectetur commodo sunt.\\r\\n","registered":"2016-01-03T11:43:59 -09:00","latitude":15.881621,"longitude":-48.266306,"tags":["est","cillum","incididunt","magna","non","sint","eiusmod"],"friends":[{"id":0,"name":"Natalie Nielsen"},{"id":1,"name":"Ferguson Lynn"},{"id":2,"name":"Dolly Bartlett"}],"greeting":"Hello, Bertha Fox! You have 7 unread messages.","favoriteFruit":"strawberry"}'); ・・・ |
各ファイルは、INSERT文のテーブル名が異なるだけで、登録するデータ内容は、同一となります。
JSONデータ登録用SQLファイルの実行
用意したSQLファイルで、それぞれのテーブルに5万件のレコードを登録します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# time mysql --login-path=root blog < insert_json_normal.sql real 1m53.702s user 0m1.251s sys 0m0.845s # time mysql --login-path=root blog < insert_json_generate_col.sql real 1m55.953s user 0m1.217s sys 0m0.901s # time mysql --login-path=root blog < insert_json_mvi.sql real 1m57.255s user 0m1.218s sys 0m0.895s |
5万件のレコードをINSERTしましたが、実行時間に大きな差はなく、それぞれ以下のようになりました。
| 対象テーブル | 実行時間 |
|---|---|
| インデックスなし | 1分53秒 |
| インデックスあり（GENERATEDカラム） | 1分55秒 |
| インデックスあり（Multi-Valued Indexes） | 1分57秒 |
検索時の実行時間の差を確認するには、もう少しレコード数があった方が確認しやすい為、各テーブルを以下の要領で 5万件 から 20万件 に増やしておきます。
|
1 2 3 4 5 6 7 |
mysql> INSERT INTO json_normal (data) SELECT data FROM json_normal; Query OK, 50000 rows affected (2.41 sec) Records: 50000 Duplicates: 0 Warnings: 0 mysql> INSERT INTO json_normal (data) SELECT data FROM json_normal; Query OK, 100000 rows affected (10.30 sec) Records: 100000 Duplicates: 0 Warnings: 0 |
各テーブルに20万件のレコードを登録しましたが、テーブルデータを格納するファイルサイズは、GENERATEDカラム＋インデックス と Multi-Valued Indexes で同じサイズとなりました。
|
1 2 3 4 5 |
# ls -lh /var/lib/mysql/blog total 853M -rw-r----- 1 mysql mysql 292M Jul 30 10:30 json_generate_col.ibd -rw-r----- 1 mysql mysql 292M Jul 30 10:31 json_mvi.ibd -rw-r----- 1 mysql mysql 268M Jul 30 10:27 json_normal.ibd |
Multi-Valued Indexes を用いた検索速度の比較
冒頭で案内しているリファレンスには、Multi-Valued Indexes は、WHERE句で次の関数が指定されている場合に使用されると記載されています。
※ MEMBER OF() と JSON_OVERLAPS() は、MySQL 8.0.17 で追加された関数となります。
それぞれの関数で、Multi-Valued Indexes を使用した場合の検索速度を比較してみたいと思います。
MEMBER OF() での検索
まずは、MEMBER OF() です。
tags配列 に、 velit が含まれているレコードという条件で検索してみます。
- インデックスなし
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_normal WHERE 'velit' MEMBER OF(data->'$.tags'); +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | json_normal | NULL | ALL | NULL | NULL | NULL | NULL | 182347 | 100.00 | Using where | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT COUNT(*) FROM json_normal WHERE 'velit' MEMBER OF(data->'$.tags'); +----------+ | COUNT(*) | +----------+ | 21740 | +----------+ 1 row in set (0.22 sec) |
- インデックスあり（GENERATEDカラム）
MEMBER OF() の引数にはJSON型を指定する必要がある為、CASTします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_generate_col WHERE 'velit' MEMBER OF(CAST(tags AS JSON)); +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | json_generate_col | NULL | index | NULL | idx_tags | 403 | NULL | 183975 | 100.00 | Using where; Using index | +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT COUNT(*) FROM json_generate_col WHERE 'velit' MEMBER OF(CAST(tags AS JSON)); +----------+ | COUNT(*) | +----------+ | 21740 | +----------+ 1 row in set (0.33 sec) |
- インデックスあり（Multi-Valued Indexes）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_mvi WHERE 'velit' MEMBER OF(data->'$.tags'); +----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | json_mvi | NULL | ref | idx_tags | idx_tags | 83 | const | 2959 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT COUNT(*) FROM json_mvi WHERE 'velit' MEMBER OF(data->'$.tags'); +----------+ | COUNT(*) | +----------+ | 21740 | +----------+ 1 row in set (0.17 sec) |
実行結果は以下のようになりました。
| 対象テーブル | 実行時間 |
|---|---|
| インデックスなし | 0.22秒 |
| インデックスあり（GENERATEDカラム） | 0.34秒 |
| インデックスあり（Multi-Valued Indexes） | 0.17秒 |
数字だけを見ると、それ程大きな差はないのですが、Multi-Valued Indexes を使用すると、インデックスを使用していない場合よりも、約1.3倍 速くなりました。
また、GENERATEDカラムにインデックスを付与した場合よりも、2倍 早くなっています。
JSON_CONTAINS() での検索
次は、JSON_CONTAINS() です。
tags配列 に、 velit と est の両方が含まれているレコードという条件で検索してみます。
- インデックスなし
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_normal WHERE JSON_CONTAINS(data->'$.tags', '["velit", "est"]'); +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | json_normal | NULL | ALL | NULL | NULL | NULL | NULL | 182347 | 100.00 | Using where | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT COUNT(*) FROM json_normal WHERE JSON_CONTAINS(data->'$.tags', '["velit", "est"]'); +----------+ | COUNT(*) | +----------+ | 2176 | +----------+ 1 row in set (0.58 sec) |
- インデックスあり（GENERATEDカラム）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_generate_col WHERE JSON_CONTAINS(tags, '["velit", "est"]'); +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | json_generate_col | NULL | index | NULL | idx_tags | 403 | NULL | 183975 | 100.00 | Using where; Using index | +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT COUNT(*) FROM json_generate_col WHERE JSON_CONTAINS(tags, '["velit", "est"]'); +----------+ | COUNT(*) | +----------+ | 2176 | +----------+ 1 row in set (0.69 sec) |
- インデックスあり（Multi-Valued Indexes）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_mvi WHERE JSON_CONTAINS(data->'$.tags', '["velit","est"]'); +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | json_mvi | NULL | ALL | idx_tags | NULL | NULL | NULL | 186417 | 41.14 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT /*+ INDEX(json_mvi idx_tags) */ COUNT(*) FROM json_mvi WHERE JSON_CONTAINS(data->'$.tags', '["velit","est"]'); +----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | json_mvi | NULL | range | idx_tags | idx_tags | 83 | NULL | 76696 | 100.00 | Using where | +----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT /*+ INDEX(json_mvi idx_tags) */ COUNT(*) FROM json_mvi WHERE JSON_CONTAINS(data->'$.tags', '["velit","est"]'); +----------+ | COUNT(*) | +----------+ | 2176 | +----------+ 1 row in set (0.37 sec) |
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 のどちらかが含まれているレコードという条件で検索してみます。
- インデックスなし
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_normal WHERE JSON_OVERLAPS(data->'$.tags', '["velit", "est"]'); +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | json_normal | NULL | ALL | NULL | NULL | NULL | NULL | 182347 | 100.00 | Using where | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT COUNT(*) FROM json_normal WHERE JSON_OVERLAPS(data->'$.tags', '["velit", "est"]'); +----------+ | COUNT(*) | +----------+ | 41192 | +----------+ 1 row in set (0.64 sec) |
- インデックスあり（GENERATEDカラム）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_generate_col WHERE JSON_OVERLAPS(tags, '["velit", "est"]'); +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | json_generate_col | NULL | index | NULL | idx_tags | 403 | NULL | 183975 | 100.00 | Using where; Using index | +----+-------------+-------------------+------------+-------+---------------+----------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT COUNT(*) FROM json_generate_col WHERE JSON_OVERLAPS(tags, '["velit", "est"]'); +----------+ | COUNT(*) | +----------+ | 41192 | +----------+ 1 row in set (0.54 sec) |
- インデックスあり（Multi-Valued Indexes）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> EXPLAIN SELECT COUNT(*) FROM json_mvi WHERE JSON_OVERLAPS(data->'$.tags', '["velit", "est"]'); +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | json_mvi | NULL | ALL | idx_tags | NULL | NULL | NULL | 186417 | 41.14 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT /*+ INDEX(json_mvi idx_tags) */ COUNT(*) FROM json_mvi WHERE JSON_OVERLAPS(data->'$.tags', '["velit", "est"]'); +----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | json_mvi | NULL | range | idx_tags | idx_tags | 83 | NULL | 76696 | 100.00 | Using where | +----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT /*+ INDEX(json_mvi idx_tags) */ COUNT(*) FROM json_mvi WHERE JSON_OVERLAPS(data->'$.tags', '["velit", "est"]'); +----------+ | COUNT(*) | +----------+ | 41192 | +----------+ 1 row in set (0.36 sec) |
実行結果は以下のようになりました。
| 対象テーブル | 実行時間 |
|---|---|
| インデックスなし | 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に関する関数や、ドキュメントストアとしての機能追加も頻繁に行われており、今後も、更に便利機能がリリースされる事を期待したいと思います。
