mysql中json數(shù)據(jù)類型的使用及底層實(shí)現(xiàn)邏輯（源碼解析）

一、mysql中json對(duì)象的操作

mysql 從5.7版本開始增加了對(duì)json數(shù)據(jù)類型的支持。此前，用戶通常將json字符串存儲(chǔ)于varchar、blob或text數(shù)據(jù)類型中，使用時(shí)需要先將整個(gè)json對(duì)象從數(shù)據(jù)庫讀取出來，在內(nèi)存中完成解析及相應(yīng)的計(jì)算處理，這種方式增加了數(shù)據(jù)庫的網(wǎng)絡(luò)開銷并降低處理效率。mysql通過對(duì)json數(shù)據(jù)類型的支持將數(shù)據(jù)處理的部分計(jì)算開銷轉(zhuǎn)移到了mysql，提高了用戶的便利，同時(shí)降低數(shù)據(jù)的網(wǎng)絡(luò)傳輸開銷。

1、json對(duì)象基本類型

mysql支持由標(biāo)準(zhǔn)化文檔RFC7159定義的全部json 數(shù)據(jù)類型。具體的包含四種基本類型和兩種結(jié)構(gòu)化類型。

JSON can represent four primitive types (strings, numbers, booleans,
and null) and two structured types (objects and arrays).

例子：

(1)、json對(duì)象

{
    "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對(duì)象實(shí)例，向mysql做插入測(cè)試，發(fā)現(xiàn)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對(duì)象類型，在插入時(shí)都可以以字符串的形式執(zhí)行插入，以字符串形式插入json數(shù)據(jù)時(shí)需要以符號(hào)''聲明， 否則將返回錯(cuò)誤?；蛘咭部梢赃x擇利用json_object函數(shù)插入json類型數(shù)據(jù)。具體如下：

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對(duì)象時(shí)，mysql存儲(chǔ)引擎會(huì)自動(dòng)檢查格式是否正確，如果插入的數(shù)據(jù)不是正確的json對(duì)象，將返回錯(cuò)誤。具體如下:

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相關(guān)的函數(shù)，便于用戶開發(fā)。具體可以參考官網(wǎng)文件JSON Function Reference 以獲取支持的函數(shù)的最新動(dòng)態(tài)。接下來主要挑選一些常用函數(shù)進(jìn)行介紹。

(1)、json數(shù)據(jù)插入

上文已經(jīng)介紹，json類型數(shù)據(jù)插入時(shí)有兩種方式，一種是基于字符串格式插入，另一種是基于json_object()函數(shù)，在使用json_object()函數(shù)只需按k-v順序，以,符號(hào)隔開順序插入即可。

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

在插入過程中，如果存在相同的key值。在插入時(shí)會(huì)執(zhí)行這種“第一個(gè)重復(fù)鍵獲勝”規(guī)范化。即，以最先出現(xiàn)的值為準(zhǔn)。

(2)、json合并

MySQL 8.0.3（及更高版本）支持兩種合并算法，由函數(shù) JSON_MERGE_PRESERVE()和 JSON_MERGE_PATCH(). 它們?cè)谔幚碇貜?fù)鍵的方式上有所不同：JSON_MERGE_PRESERVE()保留重復(fù)鍵的 值，而 JSON_MERGE_PATCH()丟棄除最后一個(gè)值之外的所有值。具體的

• JSON_MERGE_PRESERVE() 函數(shù)接受兩個(gè)或多個(gè) JSON 文檔并返回組合結(jié)果。如果參數(shù)為兩個(gè)object,相同的key將會(huì)把value合并為array(即使value也相同，也會(huì)合并為array),不同的key則直接合并。如果其中一個(gè)參數(shù)為json array，則另一個(gè)json object整體作為一個(gè)元素，加入array結(jié)果。

• JSON_MERGE_PATCH()函數(shù)接受兩個(gè)或多個(gè) JSON 文檔并返回組合結(jié)果。如果參數(shù)為兩個(gè)object,相同的key的value將會(huì)被后面參數(shù)的value覆蓋,不同的key則直接合并。如果合并的是數(shù)組，將按照“最后一個(gè)重復(fù)鍵獲勝”邏輯僅保留最后一個(gè)參數(shù)。

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 路徑表達(dá)式用于提取json某個(gè)路徑下的數(shù)值。例如，我們想要查詢sku_info_tb表中sku_info字段的json對(duì)象中name字段的值。有兩種基本方式，JSON_EXTRACT()函數(shù)用于解析json對(duì)象，->符號(hào)是就一種JSON_EXTRACT()函數(shù)的等價(jià)模式。

//sku_info是字段名，也可以使用`sku_info`，但是$.name一定要加符號(hào)''
SELECT sku_info->'$.name' FROM sku_info_tb;
SELECT JSON_EXTRACT(sku_info,'$.name') FROM sku_info_tb;

+--------------------+
| sku_info->'$.name' |
+--------------------+
| "Aztalan"          |
+--------------------+

根據(jù)結(jié)果我們發(fā)現(xiàn)，返回值包含""符號(hào),但是通常情況下，我們需要的僅僅是該字段的值，mysql同樣提供了一個(gè)去掉外層""符號(hào)的方法，及其等價(jià)符號(hào)。

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')函數(shù)用于修改某個(gè)字段值，第一個(gè)參數(shù)用于指定操作字段，第二個(gè)參數(shù)指定操作的json的key值；第三個(gè)參數(shù)用于定義新值。

UPDATE sku_info_tb set sku_info = JSON_SET(`sku_info`,'$.name',"set name");
+---------------------+
| sku_info->>"$.name" |
+---------------------+
| set name            |
+---------------------+

(5)、為json對(duì)象內(nèi)的key值創(chuàng)建索引

現(xiàn)在MySQL不支持對(duì)JSON列進(jìn)行索引，官網(wǎng)文檔的說明是：

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規(guī)定，可以首先使用路徑表達(dá)式對(duì)JSON文檔中的標(biāo)量值建立虛擬列，然后在虛擬列上建立索引。這樣用戶可以使用表達(dá)式對(duì)自己感興趣的鍵值建立索引。舉個(gè)具體的例子來說明：

ALTER TABLE features ADD feature_street VARCHAR(30) AS (JSON_UNQUOTE(feature->"$.properties.STREET"));
ALTER TABLE features ADD INDEX (feature_street);

其中，feature_street列就是新添加的虛擬列。之所以取名虛擬列，是因?yàn)榕c它對(duì)應(yīng)的還有一個(gè)存儲(chǔ)列(stored column)。它們最大的區(qū)別為虛擬列只修改數(shù)據(jù)庫的metadata，并不會(huì)存儲(chǔ)真實(shí)的數(shù)據(jù)在硬盤上，讀取過程也是實(shí)時(shí)計(jì)算的方式；而存儲(chǔ)列會(huì)把表達(dá)式的列存儲(chǔ)在硬盤上。兩者使用的場(chǎng)景不一樣，默認(rèn)情況下通過表達(dá)式生成的列為虛擬列。

這樣虛擬列的添加和刪除都會(huì)非?？欤谔摂M列上建立索引跟傳統(tǒng)的建立索引的方式并沒有區(qū)別，會(huì)提高虛擬列讀取的性能，減慢整體插入的性能。虛擬列的特性結(jié)合JSON的路徑表達(dá)式，可以方便的為用戶提供高效的鍵值索引功能。摘自MySQL 5.7 JSON 實(shí)現(xiàn)簡(jiǎn)介

本節(jié)只選取了最常用的幾個(gè)函數(shù)進(jìn)行介紹，其他函數(shù)的詳細(xì)介紹可以結(jié)合官方文檔去學(xué)習(xí)。mysql 8.0操作手冊(cè)-json數(shù)據(jù)類型

二、mysql中json數(shù)據(jù)類型的存儲(chǔ)原理及源碼解析

前一節(jié)介紹的json對(duì)象操作方法，例如sku_info->'$.name',JSON_EXTRACT(sku_info,'$.name')等，是mysql提供的對(duì)json對(duì)象的操作方法，與mysql字段的類型無關(guān)，也就是說，使用該方法操作存儲(chǔ)類型為string的json對(duì)象一樣可以成功。但是相比于普通string類型，json類型的數(shù)據(jù)操作性能更高。根據(jù)Oracle公司針對(duì)200K+數(shù)據(jù)文檔做的性能測(cè)試表明，同樣的數(shù)據(jù)用TEXT和JSON類型的查詢性能差異達(dá)到兩個(gè)數(shù)量級(jí)以上，而且用戶還可以對(duì)經(jīng)常訪問的JSON鍵值做索引，進(jìn)一步提升性能。官方文檔之處，JSON數(shù)據(jù)操作性能的提升是基于JSON數(shù)據(jù)本身的存儲(chǔ)結(jié)構(gòu)的，那么mysql是如何實(shí)現(xiàn)json數(shù)據(jù)類型的快速訪問呢？

上文介紹，mysql支持由標(biāo)準(zhǔn)化文檔RFC7159定義的全部json 數(shù)據(jù)類型。具體的包含四種基本類型和兩種結(jié)構(gòu)化類型。下文會(huì)介紹json類型數(shù)據(jù)的底層存儲(chǔ)邏輯及存儲(chǔ)樣式。

1、json object 的底層存儲(chǔ)邏輯

mysql為了提供對(duì)json對(duì)象的支持，提供了一套將json字符串轉(zhuǎn)為結(jié)構(gòu)化二進(jìn)制對(duì)象的存儲(chǔ)方式，具體的，可以參考源碼json_binary.cc和json_binary.h進(jìn)行學(xué)習(xí)。

一些重要對(duì)象的定義如下：

  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會(huì)被轉(zhuǎn)為二進(jìn)制的doc對(duì)象存儲(chǔ)于磁盤中。doc對(duì)象包含兩個(gè)部分，type和value部分。其中type占1字節(jié)，可以表示16種類型：大的和小的json object類型、大的和小的 json array類型、literal類型（true、false、null三個(gè)值）、number類型（int6、uint16、int32、uint32、int64、uint64、double類型、utf8mb4 string類型和custom data（mysql自定義類型）。

• value包含 object、array、literal、number、string和custom-data六種類型，與type 16種類型對(duì)應(yīng)。

• object表示json對(duì)象類型，由6部分組成：object ::= element-count size key-entry* value-entry* key* value*,其中:

  • element-count表示對(duì)象中包含的成員（key）個(gè)數(shù)，在array類型中表示數(shù)組元素個(gè)數(shù)。
  • size表示整個(gè)json對(duì)象的二進(jìn)制占用空間大小。小對(duì)象用2Bytes空間表示（最大64K），大對(duì)象用4Bytes表示（最大4G）
  • key-entry可以理解為一個(gè)用于指向真實(shí)key值的數(shù)組。本身用于二分查找，加速json字段的定位。
  • value-entry與key-enter功能類似，不同之處在于，value-entry可能存儲(chǔ)真實(shí)的value值。

• array表示json數(shù)組，array類型主要包含4部分。array ::= element-count size value-entry* value*

• key-entry由兩個(gè)部分組成：key-entry ::= key-offset key-length,其中:

  • key-offset：表示key值存儲(chǔ)的偏移量，便于快速定位key的真實(shí)值。
  • key-length：表示key值的長度，用于分割不同key值的邊界。長度為2Bytes，這說明，key值的長度最長不能超過64kb.

• value-entry由兩部分組成 value-entry ::= type offset-or-inlined-value,其中：

  • type表示value類型，如上文所示，支持16種基本類型，從而可以表示各種類型的嵌套。
  • offset-or-inlined-value：有兩層含義，如果value值足夠小，可以存儲(chǔ)于此，那么就存儲(chǔ)數(shù)據(jù)本身，如果數(shù)據(jù)本身較大，則存儲(chǔ)真實(shí)值的偏移用于快速定位。

• key 表示key值的真實(shí)值，類型為：key ::= utf8mb4-data,這里無需指定key值長度，因?yàn)閗ey-entry中已經(jīng)聲明了key的存儲(chǔ)長度。同時(shí)，在同一個(gè)json對(duì)象中，key值的長度總是一樣的。

此外還包含一些簡(jiǎn)單的基本類型，這里不再贅述，需要指出的是，在mysql中json的對(duì)象的存儲(chǔ)也是層級(jí)存儲(chǔ)，同時(shí)支持類型的嵌套，從value-entry類型的定義就可以看出，因?yàn)樗艘粋€(gè)type字段，該字段和doc中的type是一樣的。

2、json如何實(shí)現(xiàn)快速定位

 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

由上述定義可知，當(dāng)type= 0x00|0x02 // small JSON object/small JSON array時(shí)，size，element-count，key-offset和offset-or-inlined-value長度為2 Bytes;當(dāng)type= 0x01|0x03 // large JSON object/large JSON array時(shí),size，element-count，key-offset和offset-or-inlined-value長度為4 Bytes。

這意味著，當(dāng)type確定時(shí)，key-entry和value-entry字段將有固定的長度，且當(dāng)多個(gè)key-value存在時(shí)，每個(gè)key-entry對(duì)象大小是一致的（4-6Bytes 取決于type類型)，每個(gè)value-entry對(duì)象大小是一致的（3-4 bytes 取決于type類型）。且在硬盤上，它們的存儲(chǔ)是連續(xù)的。也就是說，只要我們知道數(shù)據(jù)的起始位置，以及key的個(gè)數(shù)（我們確實(shí)知道，element-count字段），就很容易找到任意指定位置的key-enter和value-enter(原理類似于數(shù)組的存儲(chǔ))。

這里再補(bǔ)充一個(gè)知識(shí)點(diǎn)，保存到數(shù)據(jù)庫的 JSON 數(shù)據(jù)并非以 JSON 文本存儲(chǔ)，而是二進(jìn)制格式。MySQL中存儲(chǔ)JSON對(duì)象的二進(jìn)制數(shù)據(jù)時(shí)，為了能利用二分搜索快速定位鍵，存入數(shù)據(jù)庫的JSON對(duì)象的鍵是被排序過的，內(nèi)部嵌套的json object對(duì)象也將被排序。具體如下：

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)

綜上，我們很容易推測(cè)出，當(dāng)我們查找指定key值時(shí)，是可以基于二分法快速定位key-enter的，同時(shí)在排序過程中，key-value的順序是意義對(duì)應(yīng)的，因此也很容易找到指定的value-enter，并最終取到value的真實(shí)值。這就是json可以實(shí)現(xiàn)快速定位的理論依據(jù)。

3、結(jié)合實(shí)例學(xué)習(xí)json對(duì)象的二進(jìn)制存儲(chǔ)模式

本節(jié)內(nèi)容將結(jié)合string類型、json對(duì)象、json array三種類型的json實(shí)例，來學(xué)習(xí)json二進(jìn)制編碼的存儲(chǔ)格式。接下來將分別以'"abc"','[42, "xy", "abc"]','{"b": 42, "a": "xy"}'為例進(jìn)行二進(jìn)制分析。這部分內(nèi)容文檔MySQL 5.7 的 JSON 類型描述的非常詳細(xì)，直接摘錄整理如下：

MySQL 5.7.22 新增 json_storage_size() 函數(shù)，用于返回 json 文檔二進(jìn)制表示占用的存儲(chǔ)空間。先來看下上述實(shí)例的真實(shí)存儲(chǔ)長度：

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)、基本類型的二進(jìn)制存儲(chǔ)

WL#8132 給出了 JSON 二進(jìn)制格式的 BNF 語法描述。參考這個(gè)語法描述，可以推算出上文示例中的 "abc"、[42, "xy", "abc"]、{"b": 42, "a": "xy"} 對(duì)應(yīng)的二進(jìn)制表示。先來看下 "abc" 純量，語法推導(dǎo)過程如下：

doc
  => type value                     // 使用產(chǎn)生式 doc ::= type value
  => 0x0c value                     // 使用產(chǎn)生式 type ::= 0x0c (utf8mb4 string 類型)
  => 0x0c string                    // 使用產(chǎn)生式 value ::= string
  => 0x0c data-length utf8mb4-data  // 使用產(chǎn)生式 string ::= data-length utf8mb4-data
  => 0x0c 0x03 utf8mb4-data         // 使用產(chǎn)生式 data-length ::= uint8*
  => 0x0c 0x03 0x61 0x62 0x63

對(duì)應(yīng)的二進(jìn)制值，共 5 個(gè)字節(jié)，依次為 0x0c 0x03 0x61 0x62 0x63，其中 0x61 0x62 0x63，就是 16 進(jìn)制表示的字符串 abc。占用 5個(gè)字節(jié)，與 json_storage_size() 函數(shù)返回的結(jié)果一致。相應(yīng)的語法樹如下：

mysql-jsonb-syntax-tree.png

從二進(jìn)制的角度看，純量 "abc" 的 JSON 二進(jìn)制表示如下：

mysql-jsonb-scalar.png

(2)、json array類型的二進(jìn)制存儲(chǔ)

[42, "xy", "abc"] 的推導(dǎo)過程，如下：

doc 
  => type value                          // 使用產(chǎn)生式 doc ::= type value
  => 0x02 array                          // 使用產(chǎn)生式 type ::= 0x02 (small JSON array 類型)
  => 0x02 element-count size value-entry* value*  // 使用產(chǎn)生式 array ::= element-count size value-entry* value*
  => 0x02 0x03 0x00 size value-entry* value*  // 使用產(chǎn)生式 element-count ::= uint16 (使用 little-endian)
  => 0x02 0x03 0x00 0x14 0x00 value-entry* value*  // 使用產(chǎn)生式 size ::= uint16 (使用 little-endian)
  => 0x02 0x03 0x00 0x14 0x00 type offset-or-inlined-value value-entry* value* // 使用產(chǎn)生式 value-entry ::= type offset-or-inlined-value
  => 0x02 0x03 0x00 0x14 0x00 0x06 offset-or-inlined-value value-entry* value* // 使用產(chǎn)生式 type ::= 0x06 (uint16 類型)
  => 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 value-entry* value*  // 使用產(chǎn)生式 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  // 使用產(chǎn)生式 value ::= string
  => 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 data-length utf8mb4-data value  // 使用產(chǎn)生式 string ::= data-length utf8mb4-data
  => 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 0x02 utf8mb4-data value // 使用產(chǎn)生式 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"] 對(duì)應(yīng)的二進(jìn)制表示，共 21 個(gè)字節(jié)，依次為 0x02 0x03 0x00 0x14 0x00 0x06 0x2a 0x00 0x0c 0x0d 0x00 0x0c 0x10 0x00 0x02 0x78 0x79 0x03 0x61 0x62 0x63。如下圖：

mysql-jsonb-array.png

相對(duì)來說，產(chǎn)生式 array ::= element-count size value-entry* value*，是整個(gè)JSON 數(shù)組二進(jìn)制表示語法的核心。element-count，表示元素個(gè)數(shù)。上圖中，第 4、5 個(gè)字節(jié)是 size 字段，十進(jìn)制值為 20（0x14），是完整二進(jìn)制表示去掉開頭 type 字段后的大?。ㄎ臋n沒有明確這個(gè)字段的含義，不過通過源碼推斷出來）。另外，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

就是說，如果實(shí)際要保存的值足夠小，將直接內(nèi)聯(lián)在這個(gè)字段中，否則將保存偏移量（offset），也就是指向?qū)嶋H值的指針。在示例中，保存 xy 對(duì)應(yīng)的 offset 值為 13（0x0d），指向的相對(duì)地址是 14。因?yàn)檫@里的 offset 并不是以相對(duì)地址 0 為基準(zhǔn)地址，是以相對(duì)地址 1 為基準(zhǔn)地址（圖中箭頭 B 指向的位置），所以偏移量是 13 而不是 14（這個(gè)字段的明確含義也是從源碼推斷而來）。類似的，保存 abc 對(duì)應(yīng)的 offset 值為 16（0x10），指向的相對(duì)地址是 17。

閱讀文檔容易發(fā)現(xiàn)，element-count、size、offset 字段占用的字節(jié)大小是固定的，小 JSON（64KB 以內(nèi)）是 2 字節(jié)，大 JSON 是 4 字節(jié)。所以，若要查找 JSON 數(shù)組的第 pos 個(gè)元素的 value-entry 的偏移量，可以使用下面的式子快速定位：

entry_offset = offset_size * 2 + (1 + offset_size) * pos

JSON 數(shù)組二進(jìn)制表示的其他字段比較容易理解，文檔都有解釋，就不展開闡述了。

(3)、json object類型的二進(jìn)制存儲(chǔ)

現(xiàn)在來看下，JSON 對(duì)象 {"b": 42, "a": "xy"} 的二進(jìn)制表示，如下圖：

mysql-jsonb-object.png

對(duì)于 JSON 對(duì)象二進(jìn)制表示的語法，核心的產(chǎn)生式是 object ::= element-count size key-entry* value-entry* key* value*。element-count、size 和 value-entry 字段，在 JSON 數(shù)組中也有，不再贅述。而 key-entry 字段，類似于 value-entry。key-entry 中的 key-offset 保存的是偏移量，是指向鍵的指針。另外，正如上文提到的 MySQL 會(huì)對(duì) JSON 鍵排序，所以上圖示例的第 20 和 21 個(gè)字節(jié)值分別是 0x61和 0x62，即 a 和 b，而非 b 和 a。同樣的，鍵關(guān)聯(lián)的值，按鍵排序后的次序排列，依次是 "xy" 和 42。

總結(jié)

mysql 5.7.8開始提供對(duì)json的原生支持，并提供了眾多操作json對(duì)象的方法，這些方法與存儲(chǔ)類型無關(guān)，string類型中被存儲(chǔ)的json字符串也可以被這些方法直接操作。得益于mysql基于json數(shù)據(jù)本身的存儲(chǔ)結(jié)構(gòu)的優(yōu)化，原生json的性能有了極大提升，特別是讀性能非常好。根據(jù)Oracle公司針對(duì)200K+數(shù)據(jù)文檔做的性能測(cè)試表明，同樣的數(shù)據(jù)用TEXT和JSON類型的查詢性能差異達(dá)到兩個(gè)數(shù)量級(jí)以上，而且用戶還可以對(duì)經(jīng)常訪問的JSON鍵值做索引，進(jìn)一步提升性能。

除此之外，原生json類型還具有如下特點(diǎn)：

• 1、相比于普通其他類型，json原生類型的另一個(gè)優(yōu)點(diǎn)在于存儲(chǔ)時(shí)將自動(dòng)檢查json格式是否合規(guī)，錯(cuò)誤的json在插入階段將返回異常；
• 2、受限于key_length字段2字節(jié)大小限制，原生json類型要求單個(gè)key的大小不能超過64kb；
• 3、受限于size字段4字節(jié)大小限制，原生json類型要求單個(gè)json文件大小不能超過4G；
• 4、由于key_enter和value-enter結(jié)構(gòu)的設(shè)計(jì)方式，MySQL對(duì)于大型文檔的變長鍵值的更新操作可能會(huì)變慢，可能并不適合寫密集的需求。
• 5、在將json序列化為二進(jìn)制時(shí)，為了節(jié)省孔空間，會(huì)動(dòng)態(tài)解析json大小，根據(jù)json對(duì)象大小確定type字段類型，此時(shí)后先默認(rèn)為小對(duì)象，失敗后調(diào)整為大對(duì)象。會(huì)造成對(duì)大對(duì)象進(jìn)行兩次解析。源碼中指出：未來可能的優(yōu)化：預(yù)先分析尺寸并選擇第一次嘗試時(shí)正確的格式，這樣我們就不必重做部分序列化。

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;
    }