一、mysql中json对象的操作
mysql 从5.7版本开始增加了对json数据类型的支持。此前,用户通常将json字符串存储于varchar、blob或text数据类型中,使用时需要先将整个json对象从数据库读取出来,在内存中完成解析及相应的计算处理,这种方式增加了数据库的网络开销并降低处理效率。mysql通过对json数据类型的支持将数据处理的部分计算开销转移到了mysql,提高了用户的便利,同时降低数据的网络传输开销。
1、json对象基本类型
mysql支持由标准化文档RFC7159定义的全部json 数据类型。具体的包含四种基本类型和两种结构化类型。
JSON can represent four primitive types (strings, numbers, booleans, and null) and two structured types (objects and arrays).
例子:
(1)、json对象
{
"Image": {
"Width": 800,
"Height": 600,
"Title": "View from 15th Floor",
"Thumbnail": {
"Url": "http://www.example.com/image/481989943",
"Height": 125,
"Width": 100
},
"Animated" : false,
"IDs": [116, 943, 234, 38793]
}
}
(2)、json array
[
{
"precision": "zip",
"Latitude": 37.7668,
"Longitude": -122.3959,
"Address": "",
"City": "SAN FRANCISCO",
"State": "CA",
"Zip": "94107",
"Country": "US"
},
{
"precision": "zip",
"Latitude": 37.371991,
"Longitude": -122.026020,
"Address": "",
"City": "SUNNYVALE",
"State": "CA",
"Zip": "94085",
"Country": "US"
}
]
(3)、仅包含值的小型json文本
"Hello world!"
42
true
基于上述json对象实例,向mysql做插入测试,发现mysql完全支持上述类型。
+----+--------+-------------------------------------------+
| id | sku_id | sku_info |
+----+--------+-------------------------------------------+
| 1 | 0 | "string test" |
| 2 | 0 | [1, "z", {"a": 1, "b": 2}, [1, 2, 3]] |
| 3 | 0 | {"sku_id": 12345, "sku_name": "test sku"} |
| 4 | 0 | "PURE STRING TYPE" |
| 5 | 0 | 10 |
| 6 | 0 | 3.14 |
+----+--------+-------------------------------------------+
无论何种json对象类型,在插入时都可以以字符串的形式执行插入,以字符串形式插入json数据时需要以符号''
声明, 否则将返回错误。或者也可以选择利用json_object函数插入json类型数据。具体如下:
mysql> insert into sku_info_tb (sku_info) values ('3.14');
Query OK, 1 row affected (0.01 sec)
mysql> insert into sku_info_tb (sku_info) values (3.14);
ERROR 3140 (22032): Invalid JSON text: "not a JSON text, may need CAST" at position 0 in value for column 'sku_info_tb.sku_info'.
insert into sku_info_tb (sku_info) values ("PURE STRING TYPE");
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'sku_info_tb.sku_info'.
insert into sku_info_tb (sku_info) values ('"PURE STRING TYPE"');
Query OK, 1 row affected (0.00 sec)
//JSON_OBJECT()
mysql> insert into sku_info_tb (sku_info) values (JSON_OBJECT("a",1, "b", 2));
Query OK, 1 row affected (0.01 sec)
此外,在插入json对象时,mysql存储引擎会自动检查格式是否正确,如果插入的数据不是正确的json对象,将返回错误。具体如下:
insert into sku_info_tb (sku_info) values ('{"a": 1, "b": }');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 14 in value for column 'sku_info_tb.sku_info'.
2、json类型基本操作
mysql提供了很多json相关的函数,便于用户开发。具体可以参考官网文件JSON Function Reference 以获取支持的函数的最新动态。接下来主要挑选一些常用函数进行介绍。
(1)、json数据插入
上文已经介绍,json类型数据插入时有两种方式,一种是基于字符串格式插入,另一种是基于json_object()函数,在使用json_object()函数只需按k-v顺序,以,
符号隔开顺序插入即可。
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+
在插入过程中,如果存在相同的key值。在插入时会执行这种“第一个重复键获胜”规范化。即,以最先出现的值为准。
(2)、json合并
MySQL 8.0.3(及更高版本)支持两种合并算法,由函数 JSON_MERGE_PRESERVE()和 JSON_MERGE_PATCH(). 它们在处理重复键的方式上有所不同:JSON_MERGE_PRESERVE()保留重复键的 值,而 JSON_MERGE_PATCH()丢弃除最后一个值之外的所有值。具体的
-
JSON_MERGE_PRESERVE() 函数接受两个或多个 JSON 文档并返回组合结果。如果参数为两个object,相同的key将会把value合并为array(即使value也相同,也会合并为array),不同的key则直接合并。如果其中一个参数为json array,则另一个json object整体作为一个元素,加入array结果。
-
JSON_MERGE_PATCH()函数接受两个或多个 JSON 文档并返回组合结果。如果参数为两个object,相同的key的value将会被后面参数的value覆盖,不同的key则直接合并。如果合并的是数组,将按照“最后一个重复键获胜”逻辑仅保留最后一个参数。
mysql> SELECT JSON_MERGE_PRESERVE('{"a":1,"b":2}', '{"a":3,"c":3}');
+-------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a":1,"b":2}', '{"a":3,"c":3}') |
+-------------------------------------------------------+
| {"a": [1, 3], "b": 2, "c": 3} |
+-------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"a":3,"c":3}');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1,"b":2}', '{"a":3,"c":3}') |
+----------------------------------------------------+
| {"a": 3, "b": 2, "c": 3} |
+----------------------------------------------------+
1 row in set (0.02 sec)
mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '"a"','{"key": "value"}');
+-----------------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '"a"','{"key": "value"}') |
+-----------------------------------------------------------+
| ["a", 1, "a", {"key": "value"}] |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH('["a", 1]', '"a"','{"key": "value"}') ;
+--------------------------------------------------------+
| JSON_MERGE_PATCH('["a", 1]', '"a"','{"key": "value"}') |
+--------------------------------------------------------+
| {"key": "value"} |
+--------------------------------------------------------+
1 row in set (0.01 sec)
(3)、搜索json值
JSON 路径表达式用于提取json某个路径下的数值。例如,我们想要查询sku_info_tb表中sku_info字段的json对象中name字段的值。有两种基本方式,JSON_EXTRACT()函数用于解析json对象,->
符号是就一种JSON_EXTRACT()函数的等价模式。
//sku_info是字段名,也可以使用`sku_info`,但是$.name一定要加符号''
SELECT sku_info->'$.name' FROM sku_info_tb;
SELECT JSON_EXTRACT(sku_info,'$.name') FROM sku_info_tb;
+--------------------+
| sku_info->'$.name' |
+--------------------+
| "Aztalan" |
+--------------------+
根据结果我们发现,返回值包含""
符号,但是通常情况下,我们需要的仅仅是该字段的值,mysql同样提供了一个去掉外层""
符号的方法,及其等价符号。
select sku_info->>"$.name" from sku_info_tb;
select JSON_UNQUOTE(sku_info->"$.name") from sku_info_tb;
+----------------------------------+
| JSON_UNQUOTE(sku_info->"$.name") |
+----------------------------------+
| Aztalan |
+----------------------------------+
(4)、修改json值
mysql提供了 JSON_SET(field_name
,'$.key','new_value')函数用于修改某个字段值,第一个参数用于指定操作字段,第二个参数指定操作的json的key值;第三个参数用于定义新值。
UPDATE sku_info_tb set sku_info = JSON_SET(`sku_info`,'$.name',"set name");
+---------------------+
| sku_info->>"$.name" |
+---------------------+
| set name |
+---------------------+
(5)、为json对象内的key值创建索引
现在MySQL不支持对JSON列进行索引,官网文档的说明是:
JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that > extracts a scalar value from the JSON column.
虽然不支持直接在JSON列上建索引,但MySQL规定,可以首先使用路径表达式对JSON文档中的标量值建立虚拟列,然后在虚拟列上建立索引。这样用户可以使用表达式对自己感兴趣的键值建立索引。举个具体的例子来说明:
ALTER TABLE features ADD feature_street VARCHAR(30) AS (JSON_UNQUOTE(feature->"$.properties.STREET"));
ALTER TABLE features ADD INDEX (feature_street);
其中,feature_street列就是新添加的虚拟列。之所以取名虚拟列,是因为与它对应的还有一个存储列(stored column)。它们最大的区别为虚拟列只修改数据库的metadata,并不会存储真实的数据在硬盘上,读取过程也是实时计算的方式;而存储列会把表达式的列存储在硬盘上。两者使用的场景不一样,默认情况下通过表达式生成的列为虚拟列。
这样虚拟列的添加和删除都会非常快,而在虚拟列上建立索引跟传统的建立索引的方式并没有区别,会提高虚拟列读取的性能,减慢整体插入的性能。虚拟列的特性结合JSON的路径表达式,可以方便的为用户提供高效的键值索引功能。摘自MySQL 5.7 JSON 实现简介
本节只选取了最常用的几个函数进行介绍,其他函数的详细介绍可以结合官方文档去学习。mysql 8.0操作手册-json数据类型
二、mysql中json数据类型的存储原理及源码解析
前一节介绍的json对象操作方法,例如sku_info->'$.name'
,JSON_EXTRACT(sku_info,'$.name')
等,是mysql提供的对json对象的操作方法,与mysql字段的类型无关,也就是说,使用该方法操作存储类型为string的json对象一样可以成功。但是相比于普通string类型,json类型的数据操作性能更高。根据Oracle公司针对200K+数据文档做的性能测试表明,同样的数据用TEXT和JSON类型的查询性能差异达到两个数量级以上,而且用户还可以对经常访问的JSON键值做索引,进一步提升性能。官方文档之处,JSON数据操作性能的提升是基于JSON数据本身的存储结构的,那么mysql是如何实现json数据类型的快速访问呢?
上文介绍,mysql支持由标准化文档RFC7159定义的全部json 数据类型。具体的包含四种基本类型和两种结构化类型。下文会介绍json类型数据的底层存储逻辑及存储样式。
1、json object 的底层存储逻辑
mysql为了提供对json对象的支持,提供了一套将json字符串转为结构化二进制对象的存储方式,具体的,可以参考源码json_binary.cc和json_binary.h进行学习。
一些重要对象的定义如下:
doc ::= type value
type ::=
0x00 | // small JSON object
0x01 | // large JSON object
0x02 | // small JSON array
0x03 | // large JSON array
0x04 | // literal (true/false/null)
0x05 | // int16
0x06 | // uint16
0x07 | // int32
0x08 | // uint32
0x09 | // int64
0x0a | // uint64
0x0b | // double
0x0c | // utf8mb4 string
0x0f // custom data (any MySQL data type)
value ::=
object |
array |
literal |
number |
string |
custom-data
object ::= element-count size key-entry* value-entry* key* value*
array ::= element-count size value-entry* value*
// number of members in object or number of elements in array
element-count ::=
uint16 | // if used in small JSON object/array
uint32 // if used in large JSON object/array
// number of bytes in the binary representation of the object or array
size ::=
uint16 | // if used in small JSON object/array
uint32 // if used in large JSON object/array
key-entry ::= key-offset key-length
key-offset ::=
uint16 | // if used in small JSON object
uint32 // if used in large JSON object
key-length ::= uint16 // key length must be less than 64KB
value-entry ::= type offset-or-inlined-value
// This field holds either the offset to where the value is stored,
// or the value itself if it is small enough to be inlined (that is,
// if it is a JSON literal or a small enough [u]int).
offset-or-inlined-value ::=
uint16 | // if used in small JSON object/array
uint32 // if used in large JSON object/array
key ::= utf8mb4-data
literal ::=
0x00 | // JSON null literal
0x01 | // JSON true literal
0x02 | // JSON false literal
number ::= .... // little-endian format for [u]int(16|32|64), whereas
// double is stored in a platform-independent, eight-byte
// format using float8store()
string ::= data-length utf8mb4-data
custom-data ::= custom-type data-length binary-data
custom-type ::= uint8 // type identifier that matches the
// internal enum_field_types enum
data-length ::= uint8* // If the high bit of a byte is 1, the length
// field is continued in the next byte,
// otherwise it is the last byte of the length
// field. So we need 1 byte to represent
// lengths up to 127, 2 bytes to represent
// lengths up to 16383, and so on...
具体的,json会被转为二进制的doc对象存储于磁盘中。doc对象包含两个部分,type和value部分。其中type占1字节,可以表示16种类型:大的和小的json object类型、大的和小的 json array类型、literal类型(true、false、null三个值)、number类型(int6、uint16、int32、uint32、int64、uint64、double类型、utf8mb4 string类型和custom data(mysql自定义类型)。
-
value包含 object、array、literal、number、string和custom-data六种类型,与type 16种类型对应。
-
object表示json对象类型,由6部分组成:
object ::= element-count size key-entry* value-entry* key* value*
,其中:- element-count表示对象中包含的成员(key)个数,在array类型中表示数组元素个数。
- size表示整个json对象的二进制占用空间大小。小对象用2Bytes空间表示(最大64K),大对象用4Bytes表示(最大4G)
- key-entry可以理解为一个用于指向真实key值的数组。本身用于二分查找,加速json字段的定位。
- value-entry与key-enter功能类似,不同之处在于,value-entry可能存储真实的value值。
-
array表示json数组,array类型主要包含4部分。
array ::= element-count size value-entry* value*
-
key-entry由两个部分组成:
key-entry ::= key-offset key-length
,其中:- key-offset:表示key值存储的偏移量,便于快速定位key的真实值。
- key-length:表示key值的长度,用于分割不同key值的边界。长度为2Bytes,这说明,key值的长度最长不能超过64kb.
-
value-entry由两部分组成
value-entry ::= type offset-or-inlined-value
,其中:- type表示value类型,如上文所示,支持16种基本类型,从而可以表示各种类型的嵌套。
- offset-or-inlined-value:有两层含义,如果value值足够小,可以存储于此,那么就存储数据本身,如果数据本身较大,则存储真实值的偏移用于快速定位。
-
key 表示key值的真实值,类型为:
key ::= utf8mb4-data
,这里无需指定key值长度,因为key-entry中已经声明了key的存储长度。同时,在同一个json对象中,key值的长度总是一样的。
此外还包含一些简单的基本类型,这里不再赘述,需要指出的是,在mysql中json的对象的存储也是层级存储,同时支持类型的嵌套,从value-entry类型的定义就可以看出,因为它包含了一个type字段,该字段和doc中的type是一样的。
2、json如何实现快速定位
size ::=
uint16 | // if used in small JSON object/array
uint32 // if used in large JSON object/array
element-count ::=
uint16 | // if used in small JSON object/array
uint32 // if used in large JSON object/array
key-offset ::=
uint16 | // if used in small JSON object
uint32 // if used in large JSON object
offset-or-inlined-value ::=
uint16 | // if used in small JSON object/array
uint32 // if used in large JSON object/array
由上述定义可知,当type= 0x00|0x02 // small JSON object/small JSON array
时,size,element-count,key-offset和offset-or-inlined-value长度为2 Bytes;当type= 0x01|0x03 // large JSON object/large JSON array
时,size,element-count,key-offset和offset-or-inlined-value长度为4 Bytes。
这意味着,当type确定时,key-entry和value-entry字段将有固定的长度,且当多个key-value存在时,每个key-entry对象大小是一致的(4-6Bytes 取决于type类型),每个value-entry对象大小是一致的(3-4 bytes 取决于type类型)。且在硬盘上,它们的存储是连续的。也就是说,只要我们知道数据的起始位置,以及key的个数(我们确实知道,element-count字段),就很容易找到任意指定位置的key-enter和value-enter(原理类似于数组的存储)。
这里再补充一个知识点,保存到数据库的 JSON 数据并非以 JSON 文本存储,而是二进制格式。MySQL中存储JSON对象的二进制数据时,为了能利用二分搜索快速定位键,存入数据库的JSON对象的键是被排序过的,内部嵌套的json object对象也将被排序。具体如下:
mysql> insert into tbl values ('{"b": "c", "a": {"y": 1, "x": 2}}');
Query OK, 1 row affected (0.02 sec)
mysql> select * from tbl;
+-----------------------------------+
| data |
+-----------------------------------+
| {"a": {"x": 2, "y": 1}, "b": "c"} |
+-----------------------------------+
1 row in set (0.00 sec)
综上,我们很容易推测出,当我们查找指定key值时,是可以基于二分法快速定位key-enter的,同时在排序过程中,key-value的顺序是意义对应的,因此也很容易找到指定的value-enter,并最终取到value的真实值。这就是json可以实现快速定位的理论依据。
3、结合实例学习json对象的二进制存储模式
本节内容将结合string类型、json对象、json array三种类型的json实例,来学习json二进制编码的存储格式。接下来将分别以'"abc"'
,'[42, "xy", "abc"]'
,'{"b": 42, "a": "xy"}'
为例进行二进制分析。这部分内容文档MySQL 5.7 的 JSON 类型描述的非常详细,直接摘录整理如下:
MySQL 5.7.22 新增 json_storage_size() 函数,用于返回 json 文档二进制表示占用的存储空间。先来看下上述实例的真实存储长度:
mysql> select json_storage_size('"abc"');
+----------------------------+
| json_storage_size('"abc"') |
+----------------------------+
| 5 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select json_storage_size('[42, "xy", "abc"]');
+----------------------------------------+
| json_storage_size('[42, "xy", "abc"]') |
+----------------------------------------+
| 21 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select json_storage_size('{"b": 42, "a": "xy"}');
+-------------------------------------------+
| json_storage_size('{"b": 42, "a": "xy"}') |
+-------------------------------------------+
| 24 |
+-------------------------------------------+
1 row in set (0.00 sec)
(1)、基本类型的二进制存储
WL#8132 给出了 JSON 二进制格式的 BNF 语法描述。参考这个语法描述,可以推算出上文示例中的 "abc"、[42, "xy", "abc"]、{"b": 42, "a": "xy"} 对应的二进制表示。先来看下 "abc" 纯量,语法推导过程如下:
doc
=> type value // 使用产生式 doc ::= type value
=> 0x0c value // 使用产生式 type ::= 0x0c (utf8mb4 string 类型)
=> 0x0c string // 使用产生式 value ::= string
=> 0x0c data-length utf8mb4-data // 使用产生式 string ::= data-length utf8mb4-data
=> 0x0c 0x03 utf8mb4-data // 使用产生式 data-length ::= uint8*
=> 0x0c 0x03 0x61 0x62 0x63
对应的二进制值,共 5 个字节,依次为 0x0c 0x03 0x61 0x62 0x63,其中 0x61 0x62 0x63,就是 16 进制表示的字符串 abc。占用 5个字节,与 json_storage_size() 函数返回的结果一致。相应的语法树如下:
从二进制的角度看,纯量 "abc" 的 JSON 二进制表示如下:
(2)、json array类型的二进制存储
[42, "xy", "abc"] 的推导过程,如下:
doc
=> type value // 使用产生式 doc ::= type value
=> 0x02 array // 使用产生式 type ::= 0x02 (small JSON array 类型)
=> 0x02 element-count size value-entry* value* // 使用产生式 array ::= element-count size value-entry* value*
=> 0x02 0x03 0x00 size value-entry* value* // 使用产生式 element-count ::= uint16 (使用 little-endian)
=> 0x02 0x03 0x00 0x14 0x00 value-entry* value* // 使用产生式 size ::= uint16 (使用 little-endian)
=> 0x02 0x03 0x00 0x14 0x00 type offset-or-inlined-value value-entry* value* // 使用产生式 value-entry ::= type offset-or-inlined-value
=> 0x02 0x03 0x00 0x14 0x00 0x06 offset-or-inlined-value value-entry* value* // 使用产生式 type ::= 0x06 (uint16 类型)
=> 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 value-entry* value* // 使用产生式 offset-or-inlined-value ::= uint16
... 省略
=> 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 value*
=> 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 string value // 使用产生式 value ::= string
=> 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 data-length utf8mb4-data value // 使用产生式 string ::= data-length utf8mb4-data
=> 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 0x02 utf8mb4-data value // 使用产生式 data-length ::= uint8*
=> 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 0x02 0x78 0x78 value
... 省略
=> 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 0x02 0x78 0x79 0x03 0x61 0x62 0x63
[42, "xy", "abc"] 对应的二进制表示,共 21 个字节,依次为 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 0x02 0x78 0x79 0x03 0x61 0x62 0x63。如下图:
相对来说,产生式 array ::= element-count size value-entry* value*
,是整个JSON 数组二进制表示语法的核心。element-count
,表示元素个数。上图中,第 4、5 个字节是 size
字段,十进制值为 20(0x14)
,是完整二进制表示去掉开头 type
字段后的大小(文档没有明确这个字段的含义,不过通过源码推断出来)。另外,value-entry
由 type
和 offset-or-inlined-value
字段组成。type
很好理解,不做解释。offset-or-inlined-value
字段,官方文档给出了含义,含义如下:
// This field holds either the offset to where the value is stored,
// or the value itself if it is small enough to be inlined (that is,
// if it is a JSON literal or a small enough [u]int).
offset-or-inlined-value ::=
uint16 | // if used in small JSON object/array
uint32 // if used in large JSON object/array
就是说,如果实际要保存的值足够小,将直接内联在这个字段中,否则将保存偏移量(offset),也就是指向实际值的指针。在示例中,保存 xy 对应的 offset 值为 13(0x0d),指向的相对地址是 14。因为这里的 offset 并不是以相对地址 0 为基准地址,是以相对地址 1 为基准地址(图中箭头 B 指向的位置),所以偏移量是 13 而不是 14(这个字段的明确含义也是从源码推断而来)。类似的,保存 abc 对应的 offset 值为 16(0x10),指向的相对地址是 17。
阅读文档容易发现,element-count、size、offset 字段占用的字节大小是固定的,小 JSON(64KB 以内)是 2 字节,大 JSON 是 4 字节。所以,若要查找 JSON 数组的第 pos 个元素的 value-entry 的偏移量,可以使用下面的式子快速定位:
entry_offset = offset_size * 2 + (1 + offset_size) * pos
JSON 数组二进制表示的其他字段比较容易理解,文档都有解释,就不展开阐述了。
(3)、json object类型的二进制存储
现在来看下,JSON 对象 {"b": 42, "a": "xy"} 的二进制表示,如下图:
对于 JSON 对象二进制表示的语法,核心的产生式是 object ::= element-count size key-entry* value-entry* key* value*
。element-count
、size
和 value-entry
字段,在 JSON 数组中也有,不再赘述。而 key-entry
字段,类似于 value-entry
。key-entry
中的 key-offset
保存的是偏移量,是指向键的指针。另外,正如上文提到的 MySQL 会对 JSON 键排序,所以上图示例的第 20 和 21 个字节值分别是 0x61和 0x62,即 a 和 b,而非 b 和 a。同样的,键关联的值,按键排序后的次序排列,依次是 "xy" 和 42。
总结
mysql 5.7.8开始提供对json的原生支持,并提供了众多操作json对象的方法,这些方法与存储类型无关,string类型中被存储的json字符串也可以被这些方法直接操作。得益于mysql基于json数据本身的存储结构的优化,原生json的性能有了极大提升,特别是读性能非常好。根据Oracle公司针对200K+数据文档做的性能测试表明,同样的数据用TEXT和JSON类型的查询性能差异达到两个数量级以上,而且用户还可以对经常访问的JSON键值做索引,进一步提升性能。
除此之外,原生json类型还具有如下特点:
- 1、相比于普通其他类型,json原生类型的另一个优点在于存储时将自动检查json格式是否合规,错误的json在插入阶段将返回异常;
- 2、受限于key_length字段2字节大小限制,原生json类型要求单个key的大小不能超过64kb;
- 3、受限于size字段4字节大小限制,原生json类型要求单个json文件大小不能超过4G;
- 4、由于key_enter和value-enter结构的设计方式,MySQL对于大型文档的变长键值的更新操作可能会变慢,可能并不适合写密集的需求。
- 5、在将json序列化为二进制时,为了节省孔空间,会动态解析json大小,根据json对象大小确定type字段类型,此时后先默认为小对象,失败后调整为大对象。会造成对大对象进行两次解析。源码中指出:未来可能的优化:预先分析尺寸并选择第一次尝试时正确的格式,这样我们就不必重做部分序列化。
case Json_dom::J_ARRAY:
{
const Json_array *array= down_cast<const Json_array*>(dom);
(*dest)[type_pos]= JSONB_TYPE_SMALL_ARRAY;
result= serialize_json_array(array, dest, false, depth);
/*
If the array was too large to fit in the small storage format,
reset the destination buffer and retry with the large storage
format.
Possible future optimization: Analyze size up front and pick the
correct format on the first attempt, so that we don't have to
redo parts of the serialization.
*/
if (result == VALUE_TOO_BIG)
{
// If the parent uses the small storage format, it needs to grow too.
if (small_parent)
return VALUE_TOO_BIG;
dest->length(start_pos);
(*dest)[type_pos]= JSONB_TYPE_LARGE_ARRAY;
result= serialize_json_array(array, dest, true, depth);
}
break;
}
case Json_dom::J_OBJECT:
{
const Json_object *object= down_cast<const Json_object*>(dom);
(*dest)[type_pos]= JSONB_TYPE_SMALL_OBJECT;
result= serialize_json_object(object, dest, false, depth);
/*
If the object was too large to fit in the small storage format,
reset the destination buffer and retry with the large storage
format.
Possible future optimization: Analyze size up front and pick the
correct format on the first attempt, so that we don't have to
redo parts of the serialization.
*/
if (result == VALUE_TOO_BIG)
{
// If the parent uses the small storage format, it needs to grow too.
if (small_parent)
return VALUE_TOO_BIG;
dest->length(start_pos);
(*dest)[type_pos]= JSONB_TYPE_LARGE_OBJECT;
result= serialize_json_object(object, dest, true, depth);
}
break;
}