MySQL中使用GENERATED COLUMNS虚拟列和索引来优化包含JSON数据的查询

🕗 发布于 2024-07-25 10:09 mysql json

在 MySQL 中，使用 GENERATED COLUMNS（也称为虚拟列或计算列）和索引来优化包含 JSON 数据的查询是一种有效的方法。GENERATED COLUMNS 允许你基于表中其他列（包括 JSON 列）的值来创建新列，并且这些列可以是虚拟的（不存储数据，只在查询时计算）或存储的（物理存储在表中）。对于需要频繁访问的 JSON 数据片段，将它们作为 GENERATED COLUMNS 存储并索引可以显著提高查询性能。

1、JSON 字段常见的增删改查操作

mysql> CREATE table t_json(id bigint auto_increment primary key,col01 json);
Query OK, 0 rows affected (0.26 sec)

mysql> insert into t_json(col01) values('{ "name":"John", "age":30, "city":"New York"}');
Query OK, 1 row affected (0.03 sec)


mysql> insert into t_json(col01) values('{ "name":"supermao", "age":33, "city":"ShenZhen"}');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_json;
+----+-----------------------------------------------------+
| id | col01                                               |
+----+-----------------------------------------------------+
|  3 | {"age": 30, "city": "New York", "name": "John"}     |
|  4 | {"age": 33, "city": "ShenZhen", "name": "supermao"} |
+----+-----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select col01->"$.name" as nameinfo,col01->"$.age" as age from t_json;
+------------+------+
| nameinfo   | age  |
+------------+------+
| "John"     | 30   |
| "supermao" | 33   |
+------------+------+
2 rows in set (0.00 sec)

以上是直接插入 JSON 格式的字符串，也可使用函数，常用的有 JSON_ARRAY() 和 JSON_OBJECT()，前者用于构造 JSON 数组，后者用于构造 JSON 对象

mysql> insert into t_json(col01) select json_array(1, "abc", null, true,curtime());
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> insert into t_json(col01) select json_object('id', 87, 'name', 'carrot');
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select col01->"$.name" as nameinfo,col01->"$.age" as age from t_json;
+------------+------+
| nameinfo   | age  |
+------------+------+
| "John"     | 30   |
| "supermao" | 33   |
| NULL       | NULL |
| "carrot"   | NULL |
+------------+------+
4 rows in set (0.00 sec)

mysql> select * from t_json;
+----+-----------------------------------------------------+
| id | col01                                               |
+----+-----------------------------------------------------+
|  3 | {"age": 30, "city": "New York", "name": "John"}     |
|  4 | {"age": 33, "city": "ShenZhen", "name": "supermao"} |
|  5 | [1, "abc", null, true, "08:43:52.000000"]           |
|  6 | {"id": 87, "name": "carrot"}                        |
+----+-----------------------------------------------------+
4 rows in set (0.00 sec)

对于 JSON 文档，KEY 值不能重复。

如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。
从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

-- MySQL 5.7.36
mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 10, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.02 sec)

-- MySQL 8.0.28
mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 30, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.00 sec)

2、使用GENERATED COLUMNS

2.1、创建 GENERATED COLUMNS

首先，你需要为 JSON 列中你经常需要访问的数据路径创建 GENERATED COLUMNS。这些列应该是 STORED 的，以便它们可以被索引。

-- 语法
ALTER TABLE tablename
ADD COLUMN json_field_name VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.path.to.field'))) STORED;

-- 示例,向虚拟列中插入数据实际上是通过JSON字段插入，然后由虚拟列自动计算
(root@localhost)[superdb]> ALTER TABLE t_json ADD COLUMN nameinfo VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(col01,'$.name'))) STORED;
Query OK, 4 rows affected (0.40 sec)
Records: 4  Duplicates: 0  Warnings: 0

在这个例子中，json_column 是包含 JSON 数据的列名，'$.path.to.field' 是你想要提取的 JSON 字段的路径，json_field_name 是新创建的列名，用于存储提取的数据。

查看表结构及查询数据

(root@localhost)[superdb]> desc t_json;
+----------+-------------+------+-----+---------+------------------+
| Field    | Type        | Null | Key | Default | Extra            |
+----------+-------------+------+-----+---------+------------------+
| id       | bigint      | NO   | PRI | NULL    | auto_increment   |
| col01    | json        | YES  |     | NULL    |                  |
| nameinfo | varchar(64) | YES  |     | NULL    | STORED GENERATED |
+----------+-------------+------+-----+---------+------------------+
3 rows in set (0.46 sec)

(root@localhost)[superdb]> select * from t_json;
+----+-----------------------------------------------------+----------+
| id | col01                                               | nameinfo |
+----+-----------------------------------------------------+----------+
|  1 | {"age": 30, "city": "New York", "name": "John"}     | John     |
|  2 | {"age": 33, "city": "ShenZhen", "name": "supermao"} | supermao |
|  3 | [1, "abc", null, true, "21:36:49.000000"]           | NULL     |
|  4 | {"id": 87, "name": "carrot"}                        | carrot   |
+----+-----------------------------------------------------+----------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> explain select * from t_json where nameinfo='supermao';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_json | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

执行计划走全表type=ALL

2.2、为 GENERATED COLUMNS 创建索引

一旦你有了 GENERATED COLUMNS，就可以为它们创建索引了。这将加速基于这些列值的查询。

ALTER TABLE t_json ADD INDEX idx_t_json_nameinfo (nameinfo);

查询虚拟列的索引会被优化器识别和使用

(root@localhost)[superdb]> ALTER TABLE t_json ADD INDEX idx_t_json_nameinfo (nameinfo);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost)[superdb]> explain select * from t_json where nameinfo='supermao';
+----+-------------+--------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_json | NULL       | ref  | idx_t_json_nameinfo | idx_t_json_nameinfo | 259     | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

2.3、使用 GENERATED COLUMNS 进行查询

现在，你可以在查询中直接使用这些 GENERATED COLUMNS，并且 MySQL 可以利用这些列上的索引来加速查询。

(root@localhost)[superdb]> select * from t_json where nameinfo='supermao';
+----+-----------------------------------------------------+----------+
| id | col01                                               | nameinfo |
+----+-----------------------------------------------------+----------+
|  2 | {"age": 33, "city": "ShenZhen", "name": "supermao"} | supermao |
+----+-----------------------------------------------------+----------+
1 row in set (0.00 sec)

3、优点

性能提升：通过减少 JSON 数据的解析量和使用索引，查询性能可以显著提高。
简化查询：查询变得更简单，因为你不需要在 WHERE 子句中使用 JSON 函数来提取数据。
数据完整性：如果 JSON 数据结构发生变化，GENERATED COLUMNS 的定义也需要相应更新，这有助于保持数据的一致性。

4、注意事项

存储开销：GENERATED COLUMNS（特别是 STORED 类型）会增加表的存储需求。
更新开销：当基于它们计算的列（即 JSON 列或其他列）发生更改时，GENERATED COLUMNS 的值也需要更新，这可能会增加写操作的开销。
维护：如果 JSON 结构发生变化，你需要更新所有相关的 GENERATED COLUMNS 定义。

通过仔细规划和管理，使用 GENERATED COLUMNS 和索引可以是一种强大的策略，用于优化包含 JSON 数据的 MySQL 表的查询性能。

原文地址：https://blog.csdn.net/zxrhhm/article/details/140559959

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

算数基本定理@质因数分解原理
把自然数写成素数的乘积，结论就是著名的算术基本定理。此定理建立了自然数与素数之间的一个重要的关系式。算数基本定理是整除理论性质和结论的精华,是整个初等数论的基础证明一些方程是否有整数解能够从公式的角度
阅读更多2024-11-15
用 Python 从零开始创建神经网络（六）：优化（Optimization）介绍
在随机初始化的模型中，或者即使是采用更复杂方法初始化的模型中，我们的目标是随着时间的推移培训或教育一个模型。为了训练一个模型，我们调整权重和偏差以提高模型的准确性和置信度。为此，我们需要计算模型的错误
阅读更多2024-11-15
海思3403对RTSP进行目标检测
用ss928 实现rtsp流的目标检测
阅读更多2024-11-15
亮数据——助力全球数据抓取的高效代理平台
特别是在需要跨境数据采集的场景中，亮数据的全球代理池能够让用户根据目标网站的地域需求，选择不同地区的IP地址，以此提升数据抓取的稳定性和成功率。亮数据不仅提供了丰富的IP资源，还内置了智能管理系统，可
阅读更多2024-11-15
【卡尔曼滤波】递归算法Recursive的应用 C语言、Python实现（Kalman Filter）
【卡尔曼滤波】递归算法Recursive的应用 C语言、Python实现（Kalman Filter）
阅读更多2024-11-15
记一次工作中订单幂等处理方案
当然可以考虑在数据库新增一张表，这张表上有第三方订单号的字段，并为此字段建立唯一索引，每次下单前预先往这张表插入数据，如果此第三方订单号已存在，那么插入数据必然报错。第一次是通过分布式锁检测，这是在
阅读更多2024-11-15
大数据湖项目建设方案（100页WORD）
随着信息技术的飞速发展，数据量呈现出爆炸性增长的趋势。企业、政府机构及科研单位在运营过程中积累了大量的数据资源，这些数据中蕴含着巨大的价值，但如何高效地存储、处理和分析这些数据，成为了一个亟待解决的问
阅读更多2024-11-15
深度学习笔记14-卷积神经网络2
卷积神经网络，是包含卷积运算且具有深度结构的前馈神经网络。在卷积神经网络中，包含卷积层、池化层和全连接层三种重要的结构。相比前馈神经网络，卷积层和池化层是新增的网络结构，在提取特征时，卷积神经网络使用
阅读更多2024-11-15
[Mysql基础] 表的操作
1.1 语法说明：field表示列名datatype表示列的类型字符集，如果没有指定字符集，则以所在数据库的字符集为准collate校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。
阅读更多2024-11-15
Mysql-DDL语句
Mysql DDL语句的使用方法和配置命令，可以更加快速熟悉的掌握Mysql基本操作命令
阅读更多2024-11-15