MySQL 8.0.17 で Multi-Valued Indexes を試す

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

# Multi-Valued Indexes とは

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

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

 - **[JSON_CONTAINS()](https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains)**
 - **[MEMBER OF()](https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of)** ※8.0.17 で新規追加
 - **[JSON_OVERLAPS()](https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-overlaps)** ※同上

# INDEX 作成方法

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

 - **[Multi-Valued Indexes](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued)（13.1.15 CREATE INDEX Syntax / MySQL 8.0 Reference Manual）**

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

 - **[CAST()](https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast)**

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

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

```sql: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`を試すためにデータも入れておきます。

```sql:データ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()`を試してみます。

```sql: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()`を試してみます。

```sql: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 つの条件で絞り込んでみます。

```sql: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 が選択されています。

行を追加していきます。

```sql: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関連記事](https://hmatsu47.hatenablog.com/qiita_mysql80)**
 - **[MySQL 8.0 の薄い本](https://github.com/hmatsu47/mysql80_no_usui_hon)（無料で配布中！）**