Edited at

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