この投稿では、MySQL 8.0 の JSON 関数のハンズオン解説を扱います。JSON サポートは MySQL 5.7 から存在しますが、8.0 では重要な改善が追加されました — より優れたインデックス戦略、新しい関数、マルチバリューインデックス — これにより JSON データの取り扱いが大幅に実用的になりました。以下の内容では、最も一般的に必要なパターンのいくつかを文書化し、EXPLAIN の出力と知っておくべきパフォーマンス観察を含めています。
これは「JSON vs. リレーショナル」の議論投稿ではありません。MySQL に JSON を保存している場合、すでに理由をお持ちでしょう。ここでの目標は、利用可能なツールを効果的に使用していることを確認することです。
環境
mysql> SELECT @@version, @@version_comment\G
*************************** 1. row ***************************
@@version: 8.0.36
@@version_comment: MySQL Community Server - GPL
テストは 8GB RAM の VM で行われ、innodb_buffer_pool_size を 4G に設定しました。言及する価値のあるメンテナンス上の注意点として：query_cache_type は 8.0 では無関係です。なぜなら query cache が完全に削除されたからです。5.7 インスタンスから移行し、my.cnf にその変数が残っている場合、削除してください — MySQL 8.0 は起動エラーを投げます。
テストテーブルの設定
テストテーブルは、かなり一般的なパターンをシミュレートしています — アプリケーションがユーザー profile データとイベントメタデータを JSON blob として保存するものです：
CREATE TABLE user_events (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
event_data JSON NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_user (user_id)
) ENGINE=InnoDB;
INSERT INTO user_events (user_id, event_data) VALUES
(1, '{"action":"login","ip":"192.168.1.10","tags":["mobile","vpn"],"score":88}'),
(1, '{"action":"purchase","ip":"192.168.1.10","tags":["desktop"],"score":72,"amount":49.99}'),
(2, '{"action":"login","ip":"10.0.0.5","tags":["mobile"],"score":91}'),
(3, '{"action":"logout","ip":"10.0.0.9","tags":["desktop","vpn"],"score":65}'),
(2, '{"action":"purchase","ip":"10.0.0.5","tags":["mobile"],"score":84,"amount":129.00}');
基本的な抽出：JSON_VALUE vs. JSON_EXTRACT
JSON_VALUE() は MySQL 8.0.21 で導入され、スカラー値を抽出するよりクリーンな方法で、組み込みの型キャストが可能です。それ以前は JSON_EXTRACT()（または -> 省略形）を使用し、手動でキャストする必要があり、動作はしますがクエリにノイズを追加します。
-- Pre-8.0.21 approach
SELECT user_id,
JSON_EXTRACT(event_data, '$.action') AS action,
CAST(JSON_EXTRACT(event_data, '$.score') AS UNSIGNED) AS score
FROM user_events;
-- Cleaner 8.0.21+ approach
SELECT user_id,
JSON_VALUE(event_data, '$.action') AS action,
JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED) AS score
FROM user_events;
2 番目のクエリの出力：
+---------+----------+-------+
| user_id | action | score |
+---------+----------+-------+
| 1 | login | 88 |
| 1 | purchase | 72 |
| 2 | login | 91 |
| 3 | logout | 65 |
| 2 | purchase | 84 |
+---------+----------+-------+
5 rows in set (0.00 sec)
RETURNING 句は本当に有用です。煩わしいダブルキャストパターンを排除し、後でクエリコードを読む際に意図を明確にします。
マルチバリューインデックス：真のゲームチェンジャー
ここが 8.0 で JSON ワークロードの本当の進化点です。MySQL 8.0.17 以降利用可能なマルチバリューインデックスにより、JSON カラム内の配列要素を直接インデックス付けできます。実際の動作は以下のようになります：
ALTER TABLE user_events
ADD INDEX idx_tags ((CAST(event_data->'$.tags' AS CHAR(64) ARRAY)));
タグ値でフィルタリングするクエリに対する、EXPLAIN の前後を示します：
-- Without the multi-valued index:
EXPLAIN SELECT * FROM user_events
WHERE JSON_CONTAINS(event_data->'$.tags', '"vpn"')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_events
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 100.00
Extra: Using where
-- After adding the multi-valued index:
EXPLAIN SELECT * FROM user_events
WHERE JSON_CONTAINS(event_data->'$.tags', '"vpn"')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_events
partitions: NULL
type: range
possible_keys: idx_tags
key: idx_tags
key_len: 67
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
フルテーブルスキャンからレンジスキャンへ。5 行では些細なことですが、数百万行のテーブルで頻繁なタグベースのフィルタリングを行う場合、その差は顕著です。改善効果はテーブルサイズとクエリ頻度に比例してスケールします。
重要な注意点として：MEMBER OF() と JSON_OVERLAPS() もマルチバリューインデックスから恩恵を受けますが、JSON_SEARCH() は受けません。設計時にクエリパターンを選択する際に重要です：
-- This WILL use the multi-valued index:
SELECT * FROM user_events
WHERE 'vpn' MEMBER OF (event_data->'$.tags');
-- This will NOT use it:
SELECT * FROM user_events
WHERE JSON_SEARCH(event_data->'$.tags', 'one', 'vpn') IS NOT NULL;
JSON の集計と変換
よく知っておくべきいくつかの集計関数：
``````html-- Build a JSON array of actions per user
SELECT user_id,
JSON_ARRAYAGG(JSON_VALUE(event_data, '$.action')) AS actions
FROM user_events
GROUP BY user_id;
+---------+----------------------+
| user_id | actions |
+---------+----------------------+
| 1 | ["login","purchase"] |
| 2 | ["login","purchase"] |
| 3 | ["logout"] |
+---------+----------------------+
3 rows in set (0.01 sec)
-- Summarize into a JSON object keyed by action
SELECT user_id,
JSON_OBJECTAGG(
JSON_VALUE(event_data, '$.action'),
JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED)
) AS score_by_action
FROM user_events
GROUP BY user_id;
+---------+--------------------------------+
| user_id | score_by_action |
+---------+--------------------------------+
| 1 | {"login": 88, "purchase": 72} |
| 2 | {"login": 91, "purchase": 84} |
| 3 | {"logout": 65} |
+---------+--------------------------------+
3 rows in set (0.00 sec)
JSON_OBJECTAGG() は、グループ内に重複するキーが存在する場合にエラーをスローします。これは、本番の ETL パイプラインで遭遇する前に知っておく価値があります。その場合、上流で重複を除去するか、データがこの集計ステップに到達する前にアプリケーション logic で処理する必要があります。
JSON を多用したクエリ実行後の SHOW STATUS の確認
クエリパターンを評価する際、ハンドラーメトリクスの確認は有用な習慣です：
FLUSH STATUS;
SELECT * FROM user_events
WHERE JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED) > 80;
SHOW STATUS LIKE 'Handler_read%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 4 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 6 |
+----------------------------+-------+
7 rows in set (0.00 sec)
Handler_read_rnd_next の値はフルスキャンを確認しています — score 値に機能的インデックスがないため驚くことではありません。スケールでの score ベースのフィルタリングには、インデックス付きの生成カラムが正しい解決策です：
ALTER TABLE user_events
ADD COLUMN score_val TINYINT UNSIGNED
GENERATED ALWAYS AS (JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED)) VIRTUAL,
ADD INDEX idx_score (score_val);
これを追加した後、同じクエリは適切なインデックス範囲スキャンに低下します。JSON フィールド上の生成カラムは MySQL 8.0 と Percona Server 8.0 の両方で利用可能であり、いかなる意味のあるスケールでもスカラー JSON フィールドのフィルタリングに対して最も信頼性の高い方法です。
Percona Server を使用している場合、Percona Toolkit の pt-query-digest が、本番環境で実際に問題を引き起こしている JSON を多用したクエリを特定するための最も実践的な方法であり、推測的にインデックスを追加する前に使用することをお勧めします。
実践的な観察
- マルチバリューインデックス (8.0.17+) は長らく待ち望まれた改善であり、クエリパターンが JSON_CONTAINS() または MEMBER OF() と一致する場合にうまく機能します
- JSON_VALUE() with RETURNING (8.0.21+) は、古い抽出後のキャストパターンよりもクリーンで、一貫して採用する価値があります
- 生成カラムとインデックスは、スケールでのスカラー JSON フィールドフィルタリングに対して最も信頼性の高い方法です
- グループ化されたデータでの JSON_OBJECTAGG() の重複キーエラーを監視してください — ETL パイプラインでハードエラーとして表面化し、サンプルデータがたまたまクリーンであればテストで簡単に逃すことがあります
- 常に EXPLAIN でインデックスの使用を確認してください — オプティマイザは複雑な WHERE 句でマルチバリューインデックスを常に選択するわけではなく、仮定するよりも確認する価値があります
まとめ
MySQL 8.0 の JSON 改善は本当有用であり、特にマルチバリューインデックスと型キャスト付きの JSON_VALUE() がそうです。これらは良好なスキーマ設計に代わるものではありませんが、JSON ストレージが適切または継承された場合、オプティマイザが勝手に解決してくれることを願うのではなく、本物のツールが利用可能になりました。特に、特定の JSON フィールドが WHERE 句で頻繁に使用されることがわかっている場合は、生成カラムパターンを早い段階で評価する価値があります。
有用な参考資料：
