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

zxrhhm

于 2024-07-20 01:03:50 发布

阅读量397

点赞数 10

文章标签： mysql json

本文链接：https://blog.csdn.net/zxrhhm/article/details/140559959

版权

在 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)