11
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

MySQL 8.0.17 で Multi-Valued Indexes を試す

Last updated at Posted at 2019-07-28

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()での絞り込みから。

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


11
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
11
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?