MySQL · 专家投稿 · MySQL5.7 的 JSON 实现

介绍

本文将介绍 MySQL 5.7 中如何实现非结构化(JSON)数据的存储,在介绍 MySQL 5.7 的非结构化数据存储之前,首先介绍在之前的 MySQL 的版本中,用户如何通过 BLOB 实现 JSON 对象的存储,以及这样处理的缺点是什么,这些缺点也就是 MySQL 5.7 支持 JSON 的理由;然后我们介绍了 MySQL 5.7 如何支持 JSON 格式,本文将重点关注MySQL 5.7 JSON 的存储格式。

5.7 之前 BLOB 方式实现 JSON 对象的存储

MySQL 是一个关系型数据库,在 MySQL 5.7 之前,没有提供对非结构化数据的支持,但是如果用户有这样的需求,也可以通过 MySQL 的 BLOB 来存储非结构化的数据。如下所示:

mysql> create table t(json_data blob);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t values('{"key1":"data1", "key2":2, "key3":{"sub_key1":"sub_val1"}}');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------------------------------------------------------------+
| json_data                                                  |
+------------------------------------------------------------+
| {"key1":"data1", "key2":2, "key3":{"sub_key1":"sub_val1"}} |
+------------------------------------------------------------+
1 row in set (0.00 sec)

在本例中,我们使用 BLOB 来存储 JSON 数据,使用这种方法,需要用户保证插入的数据是一个能够转换成 JSON 格式的字符串,MySQL 并不保证任何正确性。在MySQL看来,这就是一个普通的字符串,并不会进行任何有效性检查,此外提取 JSON 中的字段,也需要用户的代码中完成,如下所示:

#!/usr/bin/python

import pymysql
import json

try:
    conn = pymysql.connect(host="127.0.0.1", db="test", user="root", passwd="root", port=7799)
    sql = "select * from t"
    cur = conn.cursor()
    cur.execute(sql)
    rows =  cur.fetchall()
    print json.dumps(json.loads(rows[0][0]), indent=4)
except:
    conn.close()

执行python脚本的结果如下所示:

[email protected]:~# python test.py
{
    "key3": {
        "sub_key1": "sub_val1"
    },
    "key2": 2,
    "key1": "data1"
}

这种方式虽然也能够实现 JSON 的存储,但是有诸多缺点,最为显著的缺点有:

  1. 需要用户保证 JSON 的正确性,如果用户插入的数据并不是一个有效的 JSON 字符串,MySQL 并不会报错;
  2. 所有对 JSON 的操作,都需要在用户的代码里进行处理,不够友好;
  3. 即使只是提取 JSON 中某一个字段,也需要读出整个 BLOB,效率不高;
  4. 无法在 JSON 字段上建索引。

5.7中的JSON实现

MySQL本身已经是一个比较完备的数据库系统,对于底层存储并不适合有太大的改动,那么 MySQL 是如何支持 JSON 格式的呢?说来也巧,和我们前面的做法几乎一样——通过 BLOB 来存储。也就是说,MySQL 5.7支持 JSON 的做法是,在server层提供了一堆便于操作 JSON 的函数,至于存储,就是简单地将 JSON 编码成 BLOB,然后交由存储引擎层进行处理,也就是说,MySQL 5.7的JSON 支持与存储引擎没有关系,MyISAM 存储引擎也支持 JSON 格式,如下所示:

mysql> create table t_innodb(data json)engine=innodb;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into t_innodb values('{"key":"val"}');
Query OK, 1 row affected (0.03 sec)

mysql> create table t_myisam(data json)engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_myisam values('{"key":"val"}');
Query OK, 1 row affected (0.00 sec)

MySQL 5.7 提供了很多操作 JSON 的函数,都是为了提高易用性,可以参考官方文档。本文将主要关注实现。

关于MySQL 5.7的JSON存储,MySQL的源码里写得比较清楚,在sql/json_binary.h中有下面这段注释:

If the value is a JSON object, its binary representation will have a
header that contains:

- the member count
- the size of the binary value in bytes
- a list of pointers to each key
- a list of pointers to each value

The actual keys and values will come after the header, in the same
order as in the header.

Similarly, if the value is a JSON array, the binary representation
will have a header with

- the element count
- the size of the binary value in bytes
- a list of pointers to each value

从注释里面我们可以知道,对于JSON数组和JSON对象,MySQL如何编码成BLOB对象,数组比较简单,下面给出JSON对象的示意图(见json_binary.cc中的serialize_json_object函数),如下所示:


JSON 对象

说明如下:首先存放的是 JSON 的元素个数,然后存放的是转换成 BLOB 以后的字节数,接下来存放的是key pointers和value pointers。为了加快查找速度,MySQL 内部会对key进行排序,以便对key进行二分查找,以提高处理速度。

此外,对于key pointers,有如下注释:

/*
  The size of key entries for objects when using the small storage
  format or the large storage format. In the small format it is 4
  bytes (2 bytes for key length and 2 bytes for key offset). In the
  large format it is 6 (2 bytes for length, 4 bytes for offset).
*/
#define KEY_ENTRY_SIZE_SMALL      (2 + SMALL_OFFSET_SIZE)
#define KEY_ENTRY_SIZE_LARGE      (2 + LARGE_OFFSET_SIZE)

也就是说,在MySQL 5.7中,key的长度只用2个字节保存(65535),如果超过这个长度,MySQL将报错,如下所示:

mysql> insert into t1 values(JSON_OBJECT(repeat('a', 65535), 'val'));
Query OK, 1 row affected (0.37 sec)

mysql> insert into t1 values(JSON_OBJECT(repeat('a', 65536), 'val'));
ERROR 3151 (22032): The JSON object contains a key name that is too long.

如果查看MySQL的源码,可以看到,与JSON相关的文件有:

json_binary.cc
json_binary.h
json_dom.cc
json_dom.h
json_path.cc
json_path.h

其中,json_binary 处理JSON 的编码、解码,json_dom 是 JSON 的内存表示,json_path 用以将字符串解析成 JSON,具体说明见WL#7909

对于 JSON 的编码,入口是json_binary.cc 文件中的serialize函数,对于 JSON 的解码,即将 BLOB 解析成 JSON 对象,入口是json_binary.cc文件中的parse_binary函数,只要搞清楚了 JSON 的存储格式,这两个函数是很好理解的。

作者介绍
赖明星 厦门大学硕士毕业,网易杭研服务器端开发工程师,MySQL 爱好者,网名“不知一不知二”。

时间: 2016-05-23

MySQL · 专家投稿 · MySQL5.7 的 JSON 实现的相关文章

MySQL · 专家投稿 · MySQL数据库SYS CPU高的可能性分析

问题背景 我们在管理繁忙的 MySQL 数据库时,可能都有碰到 SYS CPU 高的经历:系统突然 SYS CPU 高起来,甚至比 USER CPU 高很多,这时系统 QPS.TPS 急剧下降. SYS CPU高是什么造成的呢?主要有2种可能: 1. context switch 不高,但在内核态 spin,导致 SYS CPU 高 2. context switch 高,每秒超过 200K,有时超过1M,过多 context switch 导致 SYS CPU 高 下面我们对这两种情况逐一分析

MySQL · 专家投稿 · InnoDB物理行中null值的存储的推断与验证

前言 想写这边文章,是因为之前想写一个解析innodb ibd文件的工具,在写这个工具的过程中,发现逻辑记录转物理记录的转换中,最难的有两部分,一是每行每字段null值占用的字节和存储,二是变长字段占用的字节和存储的格式.本文中重点针对第一种情况. 之前看有关介绍compact行记录格式: 变长字段之后的第二个部分是NULL标志位,该位指示了该行数据中是否有NULL值,有则用1表示.该部分所占字节为1字节 -–<InnoDB存储引擎> 之后便思考是否不管有多少个列都是NULL,该部分都只占1个

mysql案例:mysql5.6.14配置my.cnf多实例

mysql案例:mysql5.6.14配置my.cnf多实例,mysql_install_db初始化不读取my.cnf配置文件 1.1.1. mysql5.6.14多实例my.cnf时,初始化不读取my.cnf配置文件 [环境描述] 在多实例配置的/etc/my.cnf环境中,执行mysql_install_db后,启动Mysql报错. [操作步骤] /etc/my.cnf配置文件: [mysqld3307] innodb_data_file_path =ibdata1:1G:autoexten

【探索新域,拥抱未来】MySQL专家肖鹏侃侃话开源

编辑手记:嘉年华倒计时2天,今年的嘉年华特别设置了开源专场,为广大MySQL技术爱好者提供分享和学习的平台.我们特别邀请新浪MySQL专家肖鹏老师谈一谈对开源世界的认识.肖鹏老师对开源数据库特别是MySQL研究特别深入,今天我们一起来听专家大话开源. 嘉宾介绍 肖鹏 微博研发中心数据库技术负责人,主要负责微博数据库(MySQL/Reids/HBase/Memcached)相关的业务保障,性能优化,架构设计以及周边的自动化系统建设.专注于数据库的高性能和高可用技术保障方向. 开源之路,我们一起走过

centos编译安装mysql(以mysql-5.6.26为例)

1)安装前准备 ①下载mysql,从官网获取源码包,http://dev.mysql.com/downloads/mysql #wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.27.tar.gz #tar xvf mysql-5.6.27.tar.gz #cd mysql-5.6.27 ②检查是否存在mysql #rpm -qa | grep mysql 存在的话就删除 rpm -e mysql   //普通删除模式 rpm -e

[MySQL 源码]MySQL5.1版本 lock table write与DML操作产生的MySQL层/Innodb层死锁

------------- 当在set autocommit=0时,执行lock table write操作,如果此时有同一个表上进入Innodb层的DML,可能导致死锁,这种死锁MySQL不做检测,只能等待Innodb层超时,简单的分析如下: 1. 对于lock table write操作,backtrace如下: SQL :set aucommit = 0 && lock tables t1 write: mysql_execute_command     –>open_and_

MySQL · 引擎特性 · MySQL5.7 崩溃恢复优化

在MySQL5.7之前的版本中, InnoDB每次做crash recovery之前都需要扫描数据目录,打开每个文件并创建内存对象.当目录下文件个数特别多时,会严重影响到崩溃恢复的速度. 为了解决这个问题,MySQL5.7通过结合checkpoint + 标注被修改的文件的方式,从一个checkpoint点开始,可以找到所有崩溃恢复需要打开的文件,从而避免扫描数据目录. 本文简单的记录了相关的代码,以及一个相关的优化点. 提交mini transaction 入口函数: mtr_commit -

[MySQL 5.6] MySQL5.6新参数

以下罗列了我所感兴趣的MySQL5.6新参数,不定期更新本文,完善参数的说明,先大概列一下,做简单说明,以后在一个个补上 ///////////////////////////////////////////////  #mysqld table_open_cache_instances #对table cache进行划分,减少锁竞争 metadata_locks_hash_instances # 对server层的metalock hash进行划分, metadata_locks_cache_

mysql procedure-关于mysql5.6存储与游标的问题

问题描述 关于mysql5.6存储与游标的问题 如下是我的过程,不解的是当我把从游标里的任何一列当做判断结束游标条件时,无论何时它都为空,但它确确实实可以循环完,百思不得其解. 例如 WHILE ( TYPE IS NOT NULL ) . 这里的type永远都为空. 再如果我将任意一列 , event_value 当做while 条件 event_value 同样为空. 在此跪求大鸟解答了. DELIMITER $$ CREATE PROCEDURE test( IN event_value_