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 を作成します。
マニュアルの例は数値型で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が選択されるようになりました。
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本（無料で配布中！）