聊聊MySQL 8.0中的Json增强

数据库 MySQL
现在很多应用环境中都能看到JSON灵活的影子。各阶段数据层次的递归层次,能很好的分辨。一直对MySQL的JSON很期待的,最近才有时间研究一下。

[[403533]]

本文转载自微信公众号「数据和云」,作者崔虎龙。转载本文请联系数据和云公众号。

现在很多应用环境中都能看到JSON灵活的影子。各阶段数据层次的递归层次,能很好的分辨。一直对MySQL的JSON很期待的,最近才有时间研究一下。

JSON了解

JSON就是一串字符串,只不过元素会使用特定的符号标注。比如:

  • {} 双括号表示对象
  • [] 中括号表示数组
  • “” 双引号内是属性或值
  • : 冒号表示后者是前者的值

关系型数据库实现JSON难度在于,关系型数据库需要定义数据库和表结构。为了应对这一点,从MySQL 5.7开始,MySQL支持了JavaScript对象表示(JavaScriptObject Notation,JSON) 数据类型。之前,这类数据不是单独的数据类型,会被存储为字符串。新的JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。

MySQL里JSON文档以二进制格式存储,它提供以下功能:

  • 自动验证存储在JSON列中的JSON文档。无效文档产生错误。
  • 优化的存储格式。存储在JSON列中的JSON文档被转换为允许快速读取访问文档元素的内部格式。二进制格式存储的JSON值。
  • 对文档元素的快速读取访问。当服务器再次读取JSON文档时,不需要重新解析文本获取该值。通过键或数组索引直接查找子对象或嵌套值,而不需要读取文档中的所有值。
  • 存储JSON文档所需的空间大致与LONGBLOB或LONGTEXT相同。
  • 存储在JSON列中的任何JSON文档的大小都仅限于max_allowed_packet系统变量的值。
  • MySQL 8.0.13之前,JSON列不能有非NULL的默认值。

JSON操作

数据保存到MySQL,操作方面都提供哪些支持?目前MySQL 8.0版本的的JSON总共支持32个普通函数和2个空间函数:

 

1. 索引:

  • JSON列,像其他二进制类型的列一样,不直接索引;相反,您可以在生成的列上创建索引,从JSON列中提取标量值。有关详细示例,请参见为生成的列建立索引以提供JSON列索引。
  • MySQL优化器还会在匹配JSON表达式的虚拟列上寻找兼容的索引。
  • 在MySQL 8.0.17及以后版本中,InnoDB存储引擎支持JSON数组上的多值索引。看到多值索引。
  • MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函数,包括在从JSON列生成的列上创建索引,作为无法索引JSON列的解决方案。每个NDB表最多支持3个JSON列。

2.JSON值的比较和排序:

  • JSON值可以使用=、<、<=、>、>=、<>、!=和<=>操作符进行比较。
  • JSON值不支持以下比较操作符和函数:

BETWEEN

IN()

GREATEST()

LEAST()

  • 对于列出的比较操作符和函数,一种变通方法是将JSON值转换为本地MySQL数值或字符串数据类型,以便它们具有一致的非JSON标量类型。就是说转换成需要的MySQL字段继续换算,也算是一种折中方案。
  • JSON值的比较分为两个级别。第一级比较基于比较值的JSON类型。如果类型不同,则仅由哪个类型优先级更高来决定比较结果。如果两个值具有相同的JSON类型,则使用特定类型的规则进行第二级比较。

BLOB > BIT > OPAQUE > DATETIME > TIME > DATE > BOOLEAN > ARRAY > OBJECT > STRING > INTEGER, DOUBLE > NULL。

3.JSON和非JSON值之间的转换:

MySQL在JSON值和其他类型值之间转换时遵循的规则:

CAST(other type AS JSON)

结果为JSON类型的NULL值。

  1. mysql>SET @j5 = '{"id":123, "name":"kevin","age":20, "time":"2021-06-01 01:00:00"}'
  2. Query OK, 0 rows affected (0.00 sec) 
  3.  
  4. mysql>SELECT CAST(JSON_EXTRACT(@j5, '$.age'AS UNSIGNED); 
  5. +----------------------------------------------+ 
  6. CAST(JSON_EXTRACT(@j5, '$.age'AS UNSIGNED) | 
  7. +----------------------------------------------+ 
  8. |                                           20 | 
  9. +----------------------------------------------+ 
  10. 1 row in set (0.00 sec) 

4.JSON值聚合:

对于JSON值的聚合,NULL值和其他数据类型一样被忽略。除MIN()、MAX()和GROUP_CONCAT()外,非NULL值被转换为数字类型并聚合。对于数字标量的JSON值,(取决于值)可能会出现截断和精度损失。

JSON使用索引方式:

MySQL JSON列上无法创建索引,是通过从JSON列中提取标量值,创建索引。这样能更有效的结合MySQL优势。

  • MySQL优化器会在匹配JSON表达式的虚拟列上寻找兼容的索引。
  • 在MySQL 8.0.17及以后版本中,InnoDB存储引擎支持JSON数组上的多值索引
  • MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函数,包括在从JSON列生成的列上创建索引,作为无法索引JSON列的解决方案。每个NDB表最多支持3个JSON列。

1.虚拟列索引:

  1. col_name data_type [GENERATED ALWAYS] AS (expr) 
  2.   [VIRTUAL | STORED] [NOT NULL | NULL
  3.   [UNIQUE [KEY]] [[PRIMARYKEY
  4.   [COMMENT 'string'

VIRTUAL或STORED关键字表示列值是如何存储的,这对列的使用影响非常大:

  • VIRTUAL:不存储列值,但在读取行时,在任何【BEFORE触发器】之后立即计算列值。虚拟列不占用存储空间,但暂居内存。目前官方里没有设置这个极限。
  • STORED:当插入或更新行时,将计算并存储列值。存储的列需要存储空间,并且可以建立索引。
  • 如果没有指定关键字,则默认为VIRTUAL。
  1. mysql> DROP TABLE IF EXISTS `jemp`; 
  2. mysql> CREATE TABLE  `jemp` ( 
  3.         id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
  4.         c JSON, 
  5.     d JSON, 
  6.         g INT GENERATED ALWAYS AS  (c->"$.id") STORED, 
  7.         INDEX i (g) 
  8.     ); 
  9. Query OK, 0 rows affected (0.02 sec) 
  10. mysql> INSERT INTO jemp (c,d) VALUES  
  11. ('{"id": "1", "name": "Fred"}' , '{"user":"Fred",   "user_id":1, "zipcode":"[14471,14531]"}'), 
  12. ('{"id": "2", "name": "Wilma"}''{"user":"Wilma",  "user_id":2, "zipcode":[24472,24532]}'  ), 
  13. ('{"id": "3", "name": "Jack"}' , '{"user":"Jack", "user_id":3, "zipcode":[34473,34533]}'    ),  
  14. ('{"id": "4", "name": "Betty"}''{"user":"Betty",  "user_id":4, "zipcode":[44474,44534]}'  ); 
  15.  
  16. Query OK, 4 rows affected (0.02 sec) 
  17. Records: 4  Duplicates: 0  Warnings: 0 
  18.  
  19. mysql> EXPLAIN SELECT c->>"$.name" AS name  FROM jemp WHERE g > 2\G; 
  20. *************************** 1. row *************************** 
  21.            id: 1 
  22.   select_type: SIMPLE 
  23.         table: jemp 
  24.    partitions: NULL 
  25.          type: range 
  26. possible_keys: i 
  27.           key: i 
  28.       key_len: 5 
  29.           ref: NULL 
  30.          rows: 2 
  31.      filtered: 100.00 
  32.         Extra: Using where 
  33. 1 row in set, 1 warning (0.00 sec) 
  1. ERROR: 
  2. No query specified 
  3.  
  4. mysql> SHOW WARNINGS\G 
  5. *************************** 1. row *************************** 
  6.  Level: Note 
  7.    Code: 1003 
  8. Message: /* select#1 */ select json_unquote(json_extract(`db1`.`jemp`.`c`,'$.name')) AS `namefrom `db1`.`jemp` where (`db1`.`jemp`.`g` > 2) 
  9. 1 row in set (0.00 sec) 

2.使用多值索引

直接接口:MEMBER OF(),JSON_CONTAINS(),JSON_OVERLAPS()

  1. mysql> ALTER TABLE jemp ADD INDEX zips( (CAST(d->'$.zipcode' AS UNSIGNED ARRAY)) ); 
  2.  
  3. #MEMBER  OF 
  4. mysql> EXPLAIN  SELECT * FROM jemp    WHERE  24472 MEMBER  OF(d->'$.zipcode')\G 
  5. *************************** 1. row *************************** 
  6.            id: 1 
  7.   select_type: SIMPLE 
  8.         table: jemp 
  9.    partitions: NULL 
  10.          type: ref 
  11. possible_keys: zips 
  12.           key: zips 
  13.       key_len: 9 
  14.           ref: const 
  15.         rows: 1 
  16.      filtered: 100.00 
  17.         Extra: Using where 
  18. 1 row in set, 1 warning (0.00 sec) 
  19.  
  20. #JSON_CONTAINS 
  21. mysql> EXPLAIN SELECT * FROM jemp   WHERE JSON_CONTAINS(d->'$.zipcode'CAST('[14471,14531]' AS JSON))\G; 
  22. *************************** 1. row *************************** 
  23.            id: 1 
  24.   select_type: SIMPLE 
  25.         table: jemp 
  26.    partitions: NULL 
  27.          type: range 
  28. possible_keys: zips 
  29.           key: zips 
  30.       key_len: 9 
  31.           ref: NULL 
  32.          rows: 2 
  33.      filtered: 100.00 
  34.         Extra: Using where 
  35. 1 row in set, 1 warning (0.00 sec) 
  36.  
  37. #JSON_OVERLAPS 
  38. mysql> EXPLAIN SELECT * FROM jemp   WHERE JSON_OVERLAPS(d->'$.zipcode'CAST('[44474,94582]' AS JSON))\G; 
  39. *************************** 1. row *************************** 
  40.            id: 1 
  41.   select_type: SIMPLE 
  42.         table: jemp 
  43.    partitions: NULL 
  44.          type: range 
  45. possible_keys: zips 
  46.           key: zips 
  47.       key_len: 9 
  48.           ref: NULL 
  49.          rows: 2 
  50.      filtered: 100.00 
  51.         Extra: Using where 
  52. 1 row in set, 1 warning (0.00 sec) 

从上面例子里,数据的查询还是基于MySQL B+tree上,JSON只是一种数据保存的机制。通过对虚拟列方式,提供快速的访问,非常好的解决了JSON支持问题。

总结

  • MySQL里JSON的结合非常实用,虚拟列索引解决了查询的性能问题。
  • JSON大小确实个硬性问题,谨慎使用(空间大致与LONGBLOB或LONGTEXT相同,文档的大小都仅限于max_allowed_packet系统变量的值)。
  • 实际场景中,只能选择适中的JSON长度,可以考虑配合大页使用。

关于作者

崔虎龙,云和恩墨MySQL技术顾问,长期服务于金融、游戏、物流等行业的数据中心,设计数据存储架构,并熟悉数据中心运营管理的流程及规范,自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。

 

责任编辑:武晓燕 来源: 数据和云
相关推荐

2022-10-10 08:01:08

MySQL字典表

2021-10-19 10:26:31

MySQL.MySQLJSON

2021-11-17 08:11:35

MySQL

2023-11-09 11:56:28

MySQL死锁

2022-06-05 23:31:28

ClionMySQL数据

2023-03-29 08:13:48

MySQL检索成本

2021-01-28 14:50:50

微信朋友圏版本

2023-06-12 09:09:19

MySQLDDLNSTANT

2023-12-18 07:32:08

ChatGPTLLMCoT

2021-08-31 07:54:24

SQLDblink查询

2023-10-25 14:51:38

MySQL数据库JSON

2024-02-26 07:39:16

2022-11-26 08:16:26

2022-04-02 10:23:12

MySQL数据库

2020-02-04 17:31:49

Python 开发编程语言

2023-05-03 21:34:34

MySQL状态变量

2022-05-11 09:01:54

Swift类型系统幻象类型

2021-10-30 19:56:10

Flutter按钮 Buttons

2023-07-28 09:54:14

SQL数据Excel

2022-08-03 08:11:58

数据测试同类型
点赞
收藏

51CTO技术栈公众号