Help us understand the problem. What is going on with this article?

MySQL 8.0.17 で Multi-Valued Indexes を試す

MySQL 8.0.17 では JSON カラムで Multi-Valued Indexes が使えるようになりましたので少し試してみます。

Multi-Valued Indexes とは

JSON カラムの中に、キー値に対応する値として(配列のように)複数値を持つものが含まれている場合にも有効に機能する INDEX です。

WHERE句で以下の関数等を使って絞り込む場合に有効です。

INDEX 作成方法

こちらに説明があります。

具体的には、CAST()関数をARRAYキーワードとともに使って INDEX を作成します。

マニュアルの例は数値型でUNSIGNEDなので、こちらは文字型でCHAR(3)を試してみます。

(注)VARCHAR(3)はエラーになります。

INDEX作成例
mysql> CREATE DATABASE json_test;
Query OK, 1 row affected (0.02 sec)

mysql> USE json_test;
Database changed
mysql> CREATE TABLE circle_member (
    ->     id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     name VARCHAR(50),
    ->     attend JSON,
    ->     INDEX name(name),
    ->     INDEX pref((CAST(attend->'$.pref' AS CHAR(3) ARRAY)))
    -> );
Query OK, 0 rows affected (0.04 sec)

SELECTを試すためにデータも入れておきます。

データINSERT
mysql> INSERT INTO circle_member SET name='佐藤', attend='{"count":5,"pref":["東京","神奈川"]}';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO circle_member SET name='鈴木', attend='{"count":4,"pref":["東京","大阪","愛知"]}';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO circle_member SET name='山田', attend='{"count":2,"pref":["千葉",""]}';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO circle_member SET name='吉田', attend='{"count":0,"pref":[""]}';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO circle_member SET name='和田', attend='{"count":0}';
Query OK, 1 row affected (0.01 sec)

SELECTでの絞り込みを試す

前述のテーブルでSELECTしてみます。まずはMEMBER OF()での絞り込みから。

SELECT(1)`
mysql> SELECT * FROM circle_member WHERE "東京" MEMBER OF(attend->'$.pref');
+----+--------+------------------------------------------------------+
| id | name   | attend                                               |
+----+--------+------------------------------------------------------+
|  1 | 佐藤   | {"pref": ["東京", "神奈川"], "count": 5}             |
|  2 | 鈴木   | {"pref": ["東京", "大阪", "愛知"], "count": 4}       |
+----+--------+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM circle_member WHERE "" MEMBER OF(attend->'$.pref');
Empty set (0.00 sec)

mysql> SELECT * FROM circle_member WHERE null MEMBER OF(attend->'$.pref');
Empty set (0.00 sec)

"東京"を指定すると 2 行結果が返ってきましたが、空文字("")やnullでは結果が返りませんでした。

このあたりは、前述のリファレンスマニュアルに制約事項として記載があります。

EXPLAINを見てみます。

EXPLAIN(1)
mysql> EXPLAIN SELECT * FROM circle_member WHERE "東京" MEMBER OF(attend->'$.pref');
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | circle_member | NULL       | ref  | pref          | pref | 15      | const |    1 |   100.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Multi-Valued Indexes(pref)が効いています。

次にJSON_CONTAINS()を試してみます。

SELECTとEXPLAIN(2)
mysql> SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京"]' AS JSON));
+----+--------+------------------------------------------------------+
| id | name   | attend                                               |
+----+--------+------------------------------------------------------+
|  1 | 佐藤   | {"pref": ["東京", "神奈川"], "count": 5}             |
|  2 | 鈴木   | {"pref": ["東京", "大阪", "愛知"], "count": 4}       |
+----+--------+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京"]' AS JSON));
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | circle_member | NULL       | range | pref          | pref | 15      | NULL |    2 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京","大阪"]' AS JSON));
+----+--------+------------------------------------------------------+
| id | name   | attend                                               |
+----+--------+------------------------------------------------------+
|  2 | 鈴木   | {"pref": ["東京", "大阪", "愛知"], "count": 4}       |
+----+--------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京","大阪"]' AS JSON));
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | circle_member | NULL       | range | pref          | pref | 15      | NULL |    3 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MEMBER OF()と同じような絞り込みが可能です。

["東京","大阪"]で絞り込むと、両方を含むものが抽出されます。抽出行は 1 行に減りましたが、走査行数(rows)は 1 行増えて 3 行になりました。

JSON_OVERLAPS()を試してみます。

SELECTとEXPLAIN(3)
mysql> SELECT * FROM circle_member WHERE JSON_OVERLAPS(attend->'$.pref', CAST('["東京","大阪"]' AS JSON));
+----+--------+------------------------------------------------------+
| id | name   | attend                                               |
+----+--------+------------------------------------------------------+
|  1 | 佐藤   | {"pref": ["東京", "神奈川"], "count": 5}             |
|  2 | 鈴木   | {"pref": ["東京", "大阪", "愛知"], "count": 4}       |
+----+--------+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM circle_member WHERE JSON_OVERLAPS(attend->'$.pref', CAST('["東京","大阪"]' AS JSON));
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | circle_member | NULL       | range | pref          | pref | 15      | NULL |    3 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT * FROM circle_member WHERE JSON_OVERLAPS(attend->'$.pref', CAST('["東京","千葉"]' AS JSON));
+----+--------+------------------------------------------------------+
| id | name   | attend                                               |
+----+--------+------------------------------------------------------+
|  1 | 佐藤   | {"pref": ["東京", "神奈川"], "count": 5}             |
|  2 | 鈴木   | {"pref": ["東京", "大阪", "愛知"], "count": 4}       |
|  3 | 山田   | {"pref": ["千葉", ""], "count": 2}                   |
+----+--------+------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM circle_member WHERE JSON_OVERLAPS(attend->'$.pref', CAST('["東京","千葉"]' AS JSON));
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | circle_member | NULL       | range | pref          | pref | 15      | NULL |    3 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

指定要素のいずれか 1 つに合致する行が抽出されます。

通常 INDEX と Multi-Valued Indexes

nameに対する絞り込み条件を加えてみて、通常の INDEX(name)と Multi-Valued Indexes(pref)のどちらが使われるかを試してみます。

データを 1 行だけ追加して 2 つの条件で絞り込んでみます。

INDEX選択(1)
mysql> INSERT INTO circle_member SET name='鈴木2', attend='{"count":3,"pref":["愛知","京都"]}';
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京"]' AS JSON)) AND name LIKE '鈴木%';
+----+--------+------------------------------------------------------+
| id | name   | attend                                               |
+----+--------+------------------------------------------------------+
|  2 | 鈴木   | {"pref": ["東京", "大阪", "愛知"], "count": 4}       |
+----+--------+------------------------------------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京"]' AS JSON)) AND name LIKE '鈴木%';
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
| id | select_type | table         | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | circle_member | NULL       | range | name,pref     | name | 203     | NULL |    2 |    40.00 | Using index condition; Using where |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

ここはnameの INDEX が選択されています。

行を追加していきます。

INDEX選択(2)
mysql> INSERT INTO circle_member SET name='鈴木3', attend='{"count":2,"pref":["兵庫"]}';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO circle_member SET name='鈴木4', attend='{"count":3,"pref":["大阪","京都"]}';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO circle_member SET name='鈴木5', attend='{"count":1,"pref":["福岡"]}';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京"]' AS JSON)) AND name LIKE '鈴木%';
+----+--------+------------------------------------------------------+
| id | name   | attend                                               |
+----+--------+------------------------------------------------------+
|  2 | 鈴木   | {"pref": ["東京", "大阪", "愛知"], "count": 4}       |
+----+--------+------------------------------------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM circle_member WHERE JSON_CONTAINS(attend->'$.pref', CAST('["東京"]' AS JSON)) AND name LIKE '鈴木%';
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | circle_member | NULL       | range | name,pref     | pref | 15      | NULL |    2 |    55.56 | Using where |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Multi-Valued Indexes(pref)のほうが絞り込み効率が高くなったため、nameではなくprefが選択されるようになりました。


hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。 https://hmatsu47.qrunch.io/
https://hmatsu47.hatenablog.com/
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です
https://wp.infra-workshop.tech/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away