MySQLでJSON型を使う（タグ検索の実装編）
過去に、MySQL 5.7とMySQL 8.0でJSON型を使うBlog 記事を連載を行いました。
- MySQLでJSON型を使う（基本編）
- MySQLでJSON型を使う（パフォーマンス編）
- MySQLでJSON型を使う（論理設計編）
- MySQLでJSON型を使う（JSON関数編）
- MySQL8.0でJSONの配列にインデックスを使う
上記の MySQL8.0でJSONの配列にインデックスを使う で取り上げた複数値インデックスを活用する実装例を紹介させていただきます。
動作検証は MySQL 8.0.41で行っていますが、MySQL 8.0.17以降のバージョンなら動作するはずです。
InnoDB; JSON: InnoDB now supports multi-valued indexes on JSON arrays. A multi-valued index is an index in which multiple index records can point to the same data record.
目次
一般的なタグ検索の実装例
一般的にタグ検索の機能を持ったアプリケーションを実装する場合は、タグと検索対象の関係がN:Mになるので、それぞれのテーブルのPK同士を関連付けるマッピングテーブルを作成する実装するのではないでしょうか？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- サンプル例 -- タグ名のマスタテーブル CREATE TABLE "tag_master" ( "tag_id" int NOT NULL, "tag_name" varchar(255) NOT NULL, "tag_value" varchar(255) NOT NULL, PRIMARY KEY ("tag_id"), UNIQUE KEY "tag_name" ("tag_name") ); -- タグ検索対象のオブジェクトのテーブル CREATE TABLE "objects" ( "object_id" int NOT NULL, "object_name" varchar(255) NOT NULL, "object_values" varchar(255) NOT NULL, PRIMARY KEY ("object_id") ); -- タグとオブジェクトをN:Mで対応させるテーブル CREATE TABLE "tag_object_mapping" ( "tag_id" int NOT NULL, "object_id" int NOT NULL, PRIMARY KEY ("tag_id", "object_id"), INDEX ("object_id") ); -- Objectのタグ名での検索方法 SELECT object_id, object_name, object_values FROM objects INNER JOIN tag_object_mapping USING(object_id) INNER JOIN tag_master USING(tag_id) WHERE tag_name = "tag_sample"; |
この実装は、実はいくつか避けられない問題を持っています。
- 複数のタグを組み合わせたAND/OR条件の検索の実装が少し面倒 (例えば、上記のクエリを単純にINに書き換えると重複が発生するので、OR条件の検索にはならない)
- 大量のオブジェクトに大量のタグが付く場合、N:Mマッピングするテーブルの行数が増えて性能問題の原因になる
JSONを利用したタグ検索の実装例
JSONの複数値インデックスは、JSON型カラムの一部に対して複数の値を持ったインデックスを作成することが可能になります。
この機能を活用すると、Objectsテーブル側にJSON型のカラム内の配列に対して複数値インデックスを作ることが可能になるためタグのような機能の実装に使えます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- サンプル例 -- タグ名のマスタテーブル CREATE TABLE "tag_master" ( "tag_id" int NOT NULL, "tag_name" varchar(255) NOT NULL, "tag_value" varchar(255) NOT NULL, PRIMARY KEY ("tag_id"), UNIQUE KEY "tag_name" ("tag_name") ); -- JSON インデックス付きタグ検索対象のオブジェクトのテーブル CREATE TABLE "json_index" ( "object_id" int NOT NULL, "object_name" varchar(255) NOT NULL, "object_values" varchar(255) NOT NULL, "json_val" JSON NOT NULL, PRIMARY KEY ("object_id"), INDEX <code>i_json_tags</code> ((CAST(json_val->'$.tags' as SIGNED ARRAY))) -- tagsのJSON配列に対して複数値Indexを作成する ); -- samle objects table row INSERT json_index VALUES (1, 'object1', 'object1 val', '{ \"tags\": [1,2], \"example_key\": \"example_val\" }'); |
この時に使うのが、MEMBER OF関数です。MEMBER OF関数の値として、サブクエリを使えないので、クエリが2回に分かれてしまいますが以下のようなクエリを書く必要があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- tag_idを取得するクエリ SELECT tag_id INTO @tag_id FROM tag_master WHERE tag_name = "tag_sample"; -- tag_idを元に検索 SELECT object_id, object_name, object_values FROM json_index WHERE (@tag_id MEMBER OF (json_val->'$.tags')); |
これでは少し不便ですし、タグのAND/OR条件の検索も行いたいので、JSON_CONTAINS関数とJSON_OVERLAPS関数を用います。
- JSON_CONTAINS(target, candidate[, path])
特定の candidate JSON ドキュメントが target JSON ドキュメント内に含まれているかどうか、または path 引数が指定されているかどうか (候補がターゲット内の特定のパスで見つかったかどうか) を 1 または 0 を返して示します。
- JSON_OVERLAPS(json_doc1, json_doc2)
2 つの JSON ドキュメントを比較します。 2 つのドキュメントに共通のキーと値のペアまたは配列要素がある場合、true (1) を返します。 両方の引数がスカラーの場合、この関数は単純な等価性テストを実行します。
JSON_CONTAINS関数は、第一引数のJSONドキュメントに第二引数の配列の全ての値が含まれている場合に、TRUE(=1)を返す仕様なのでAND条件と同じに挙動になります。
JSON_OVERLAPS関数は、第一引数のJSONドキュメントに第二引数の配列の一部の値が含まれていれば、TRUE(=1)を返す仕様なのでOR条件と同じ挙動になります。
また、どちらも第二引数にJSONの配列を渡せるので、この部分にサブクエリを組み込みます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
-- サブクエリとして組み込むクエリ SELECT cast(CONCAT ('[',GROUP_CONCAT(tag_id),']') AS json) AS j_array FROM tag_master WHERE tag_name IN ('tag1', 'tag2') GROUP BY 'dummy'; -- GROUP BYの結果が必ず1行になるようにダミーの固定文字列を設定 -- 上記の実行例 mysql> SELECT -> cast(CONCAT ('[',GROUP_CONCAT(tag_id),']') AS json) AS j_array -- tag_id の配列 -> FROM -> tag_master -> WHERE -> tag_name IN ('tag1', 'tag2') -> GROUP BY -> 'dummy'; +---------+ | j_array | +---------+ | [1, 2] | +---------+ 1 row in set (0.02 sec) -- Tag のAND検索 (JSON_CONTAINS) SELECT object_id, object_name, object_values FROM json_index WHERE JSON_CONTAINS(json_val-> '$.tags', ( SELECT cast(CONCAT ('[',GROUP_CONCAT(tag_id),']') AS json) AS j_array FROM tag_master WHERE tag_name IN ('tag1', 'tag2') GROUP BY 'dummy' ) ) ORDER BY object_id; -- 出力結果の確認用に順序固定 -- Tag のOR検索 (JSON_OVERLAPS) SELECT object_id, object_name, object_values FROM json_index WHERE JSON_OVERLAPS(json_val-> '$.tags', ( SELECT cast(CONCAT ('[',GROUP_CONCAT(tag_id),']') AS json) AS j_array FROM tag_master WHERE tag_name IN ('tag1', 'tag2') GROUP BY 'dummy' ) ) ORDER BY object_id; -- 出力結果の確認用に順序固定 |
上記のクエリでちゃんとインデックスが使われているのかを確認するために、Explain とExplain analyzeを取得してみました。
ちゃんと tag_masterテーブルからtag_idのJSON配列を作成するサブクエリは、1回しか実行されない"select_type: SUBQUERY"で、json_indexテーブルから、i_json_tagsを使って該当行を抽出できています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
■ JSON_CONTAINS の場合 +----+-------------+------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | 1 | PRIMARY | json_index | NULL | range | i_json_tags | i_json_tags | 9 | NULL | 10030 | 100.00 | Using where; Using filesort | | 2 | SUBQUERY | tag_master | NULL | range | tag_name | tag_name | 1022 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ 2 rows in set, 1 warning (0.01 sec) +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Sort: json_index.object_id (cost=4964 rows=10030) (actual time=112..112 rows=29 loops=1) -> Filter: json_contains(cast(json_extract(json_val,_utf8mb4'$.tags') as signed array),json'[1, 2]') (cost=4964 rows=10030) (actual time=0.939..112 rows=29 loops=1) -> Index range scan on json_index using i_json_tags over (2 MEMBER OF (json_extract(json_val,_utf8mb4'$.tags'))) OR (1 MEMBER OF (json_extract(json_val,_utf8mb4'$.tags'))) (cost=4964 rows=10030) (actual time=0.0749..90.4 rows=10001 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.12 sec) ■ JSON_OVERLAPS +----+-------------+------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | 1 | PRIMARY | json_index | NULL | range | i_json_tags | i_json_tags | 9 | NULL | 10030 | 100.00 | Using where; Using filesort | | 2 | SUBQUERY | tag_master | NULL | range | tag_name | tag_name | 1022 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ 2 rows in set, 1 warning (0.00 sec) +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Sort: json_index.object_id (cost=4964 rows=10030) (actual time=128..129 rows=10001 loops=1) -> Filter: json_overlaps(cast(json_extract(json_val,_utf8mb4'$.tags') as signed array),json'[1, 2]') (cost=4964 rows=10030) (actual time=0.0652..120 rows=10001 loops=1) -> Index range scan on json_index using i_json_tags over (2 MEMBER OF (json_extract(json_val,_utf8mb4'$.tags'))) OR (1 MEMBER OF (json_extract(json_val,_utf8mb4'$.tags'))) (cost=4964 rows=10030) (actual time=0.0592..100 rows=10001 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.14 sec) |
このように N:Mのマッピングするテーブルを使わずに、シンプルなテーブル構成でタグ検索の実装ができました。
MySQLのJOIN方法は基本的にNested loop joinなので、N:Mのマッピングするテーブルを使う設計で実行時に駆動表が大きくなると途端に性能が落ちます。
複数値インデックスを使ってテーブルから単純にデータを取り出す方法ならば、JOINが不要になります。
複数値インデックスの注意点
複数値インデックスが使える値の型には大きな制限があります。
基本的には数値型などの特定の型だけです。使いやすいJSON型や文字列型などは、CASTエラーが出て使えません。
マニュアルの中に使える型の種類が書かれていないようです。どの型が使えるかどうかの判定のロジックがソースコードの以下の部分に書かれているので、興味のある方は見てみるとよいかもしれません。
https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1884
まとめ
JSON型で使える複数値インデックスを使ったタグ検索の実装サンプルを書いてみました。
JSON型は、他のデータ型に比べて内部に複雑な情報を構造化して保存できるため、適切に扱えばデータの柔軟性と関数インデックスを用いた高速な検索の両方を得ることができます。
皆様の日々の開発を行う際の参考になれば幸いです。
