本文详细介绍了 MySQL 8.0 的 JSON 函数的实际操作演练。JSON 支持从 MySQL 5.7 开始引入，但 8.0 增加了一组重要的改进——更好的索引策略、新函数以及多值索引——这些使得处理 JSON 数据变得更加实用。以下文档介绍了几个最常用的模式，包括 EXPLAIN 输出和值得了解的性能观察。
这不是一篇“JSON 与关系型”的辩论文章。如果你选择在 MySQL 中存储 JSON，你可能已经有自己的理由。此处的目标是确保你有效利用现有的工具。
环境
mysql> SELECT @@version, @@version_comment\G
*************************** 1. row ***************************
@@version: 8.0.36
@@version_comment: MySQL Community Server - GPL
测试在一台具有 8GB RAM 的虚拟机上进行，并将 innodb_buffer_pool_size 设置为 4G。值得一提的一个维护注意事项：query_cache_type 在 8.0 中无关紧要，因为查询缓存已被完全移除。如果你从 5.7 实例迁移过来，并且在 my.cnf 中仍有该变量，请删除它——MySQL 8.0 会抛出启动错误。
设置测试表
测试表模拟了一个相当常见的模式——应用程序将用户配置文件数据和事件元数据存储为 JSON 块：
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 与 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;
第二个查询的输出：
+---------+----------+-------+
| 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 管道中遇到之前了解这一点很有价值。那时，你需要在上游去重，或在数据到达此聚合步骤前在应用逻辑中处理。
JSON 重查询后检查 SHOW STATUS
在评估查询模式时，检查 handler 指标是一个有用的习惯：
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() 匹配时工作良好
- 带 RETURNING 的 JSON_VALUE()（8.0.21+）比旧的提取后转换模式更简洁，值得一致采用
- 生成列加索引仍然是大规模标量 JSON 字段过滤的最可靠路径
- 注意分组数据中的 JSON_OBJECTAGG() 重复键错误——它在 ETL 管道中表现为硬错误，如果你的样本数据恰好干净，在测试中很容易遗漏
- 始终使用 EXPLAIN 验证索引使用——优化器并不总能在复杂 WHERE 子句中选择多值索引，值得确认而非假设
总结
MySQL 8.0 的 JSON 改进确实很有用，特别是多值索引和带类型转换的 JSON_VALUE()。它们不能取代良好的架构设计，但对于 JSON 存储合适或已继承的情况，你现在有了真正的工具可用，而不是仅仅希望优化器能搞定。特别是生成列模式，如果知道某些 JSON 字段将定期用于 WHERE 子句，值得及早评估。
有用参考：
