More than 3 years have passed since last update.

MySQL 8.0.17 で Multi-Valued Indexes を試す

Last updated at 2020-01-19Posted at 2019-07-28

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

Multi-Valued Indexes とは

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

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

JSON_CONTAINS()
MEMBER OF() ※8.0.17 で新規追加
JSON_OVERLAPS() ※同上

INDEX 作成方法

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

Multi-Valued Indexes（13.1.15 CREATE INDEX Syntax / MySQL 8.0 Reference Manual）

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

CAST()

マニュアルの例は数値型で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()での絞り込みから。

```sql: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`を見てみます。

```sql: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が選択されるようになりました。

Qiitaに投稿したMySQL 8.0関連記事
MySQL 8.0 の薄い本（無料で配布中！）

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

MySQL 8.0.17 で Multi-Valued Indexes を試す

Multi-Valued Indexes とは

INDEX 作成方法

SELECTでの絞り込みを試す

通常 INDEX と Multi-Valued Indexes

`SELECT`での絞り込みを試す