MySQL5.7 JSON类型列创建索引查询一例

发布时间:2020-08-10 00:34:04 作者:chenfeng
来源:ITPUB博客 阅读:553
创建json类型的表test:
mysql> CREATE TABLE test(data JSON);
Query OK, 0 rows affected (0.47 sec)


mysql> insert into test values('{"name":"abc","sex":"nan","area":["1","2"]}');
Query OK, 1 row affected (0.39 sec)


mysql> insert into test values('{"name":"abc","sex":"nan","area":["2","3"]}');
Query OK, 1 row affected (0.39 sec)


mysql> insert into test values('{"name":"abc","sex":"nan","area":["3","4"]}');
Query OK, 1 row affected (0.39 sec)


mysql>  select json_type(data) from test;
+-----------------+
| json_type(data) |
+-----------------+
| OBJECT          |
+-----------------+
1 row in set (0.15 sec)




mysql> select * from test;
+---------------------------------------------------+
| data                                              |
+---------------------------------------------------+
| {"sex": "nan", "area": ["1", "2"], "name": "abc"} |
+---------------------------------------------------+
1 row in set (0.10 sec)




mysql> select json_extract(data, '$.name' )  from test;
+-------------------------------+
| json_extract(data, '$.name' ) |
+-------------------------------+
| "abc"                         |
+-------------------------------+
1 row in set (0.00 sec)


mysql> select json_extract(data, '$.sex' )  from test;
+------------------------------+
| json_extract(data, '$.sex' ) |
+------------------------------+
| "nan"                        |
+------------------------------+
1 row in set (0.00 sec)


mysql> select json_extract(data, '$.area' )  from test;
+-------------------------------+
| json_extract(data, '$.area' ) |
+-------------------------------+
| ["1", "2"]                    |
+-------------------------------+
1 row in set (0.00 sec)






在data列上,对"area"建立虚拟列
mysql> ALTER TABLE test ADD data_idx varchar(128) GENERATED ALWAYS AS (json_extract(data,'$.area')) VIRTUAL;
Query OK, 0 rows affected (0.93 sec)
Records: 0  Duplicates: 0  Warnings: 0


如果要在JSON列上进行检索,需要对检索的key创建虚拟列,然后再虚拟列上创建索引。
mysql> alter table test add index idx_data(data_idx);
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0


where条件需要使用虚拟列来进行检索,执行计划如下:
mysql> explain select * from test where data_idx='["3", "4"]';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_data      | idx_data | 387     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.04 sec)


发现走了索引
推荐阅读:
  1. MySQL5.7在线DDL相关知识简单介绍
  2. 总结mysql5.7中innodb存储引擎的一些知识

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

json 索引 mysql5.7

上一篇:“顶流”腾讯的双11饭局:排排坐,战天猫

下一篇:DevOps兴起意味着专职测试人员消失?三分钟测试:什么是DevOps?

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》