[[403533]]

本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者崔虎龍。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。

現(xiàn)在很多應(yīng)用環(huán)境中都能看到JSON靈活的影子。各階段數(shù)據(jù)層次的遞歸層次，能很好的分辨。一直對(duì)MySQL的JSON很期待的，最近才有時(shí)間研究一下。

JSON了解

JSON就是一串字符串,只不過元素會(huì)使用特定的符號(hào)標(biāo)注。比如：

• {} 雙括號(hào)表示對(duì)象
• [] 中括號(hào)表示數(shù)組
• “” 雙引號(hào)內(nèi)是屬性或值
• : 冒號(hào)表示后者是前者的值

關(guān)系型數(shù)據(jù)庫實(shí)現(xiàn)JSON難度在于，關(guān)系型數(shù)據(jù)庫需要定義數(shù)據(jù)庫和表結(jié)構(gòu)。為了應(yīng)對(duì)這一點(diǎn)，從MySQL 5.7開始，MySQL支持了JavaScript對(duì)象表示(JavaScriptObject Notation，JSON) 數(shù)據(jù)類型。之前，這類數(shù)據(jù)不是單獨(dú)的數(shù)據(jù)類型，會(huì)被存儲(chǔ)為字符串。新的JSON數(shù)據(jù)類型提供了自動(dòng)驗(yàn)證的JSON文檔以及優(yōu)化的存儲(chǔ)格式。

MySQL里JSON文檔以二進(jìn)制格式存儲(chǔ)，它提供以下功能：

• 自動(dòng)驗(yàn)證存儲(chǔ)在JSON列中的JSON文檔。無效文檔產(chǎn)生錯(cuò)誤。
• 優(yōu)化的存儲(chǔ)格式。存儲(chǔ)在JSON列中的JSON文檔被轉(zhuǎn)換為允許快速讀取訪問文檔元素的內(nèi)部格式。二進(jìn)制格式存儲(chǔ)的JSON值。
• 對(duì)文檔元素的快速讀取訪問。當(dāng)服務(wù)器再次讀取JSON文檔時(shí)，不需要重新解析文本獲取該值。通過鍵或數(shù)組索引直接查找子對(duì)象或嵌套值，而不需要讀取文檔中的所有值。
• 存儲(chǔ)JSON文檔所需的空間大致與LONGBLOB或LONGTEXT相同。
• 存儲(chǔ)在JSON列中的任何JSON文檔的大小都僅限于max_allowed_packet系統(tǒng)變量的值。
• MySQL 8.0.13之前，JSON列不能有非NULL的默認(rèn)值。

JSON操作

數(shù)據(jù)保存到MySQL，操作方面都提供哪些支持?目前MySQL 8.0版本的的JSON總共支持32個(gè)普通函數(shù)和2個(gè)空間函數(shù)：

1. 索引：

• JSON列，像其他二進(jìn)制類型的列一樣，不直接索引;相反，您可以在生成的列上創(chuàng)建索引，從JSON列中提取標(biāo)量值。有關(guān)詳細(xì)示例，請(qǐng)參見為生成的列建立索引以提供JSON列索引。
• MySQL優(yōu)化器還會(huì)在匹配JSON表達(dá)式的虛擬列上尋找兼容的索引。
• 在MySQL 8.0.17及以后版本中，InnoDB存儲(chǔ)引擎支持JSON數(shù)組上的多值索引?？吹蕉嘀邓饕?/li>
• MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函數(shù)，包括在從JSON列生成的列上創(chuàng)建索引，作為無法索引JSON列的解決方案。每個(gè)NDB表最多支持3個(gè)JSON列。

2.JSON值的比較和排序:

• JSON值可以使用=、<、<=、>、>=、<>、!=和<=>操作符進(jìn)行比較。
• JSON值不支持以下比較操作符和函數(shù):

BETWEEN

IN()

GREATEST()

LEAST()

• 對(duì)于列出的比較操作符和函數(shù)，一種變通方法是將JSON值轉(zhuǎn)換為本地MySQL數(shù)值或字符串?dāng)?shù)據(jù)類型，以便它們具有一致的非JSON標(biāo)量類型。就是說轉(zhuǎn)換成需要的MySQL字段繼續(xù)換算，也算是一種折中方案。
• JSON值的比較分為兩個(gè)級(jí)別。第一級(jí)比較基于比較值的JSON類型。如果類型不同，則僅由哪個(gè)類型優(yōu)先級(jí)更高來決定比較結(jié)果。如果兩個(gè)值具有相同的JSON類型，則使用特定類型的規(guī)則進(jìn)行第二級(jí)比較。

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

3.JSON和非JSON值之間的轉(zhuǎn)換:

MySQL在JSON值和其他類型值之間轉(zhuǎn)換時(shí)遵循的規(guī)則:

CAST(other type AS JSON)

結(jié)果為JSON類型的NULL值。

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

4.JSON值聚合:

對(duì)于JSON值的聚合，NULL值和其他數(shù)據(jù)類型一樣被忽略。除MIN()、MAX()和GROUP_CONCAT()外，非NULL值被轉(zhuǎn)換為數(shù)字類型并聚合。對(duì)于數(shù)字標(biāo)量的JSON值，(取決于值)可能會(huì)出現(xiàn)截?cái)嗪途葥p失。

JSON使用索引方式：

MySQL JSON列上無法創(chuàng)建索引，是通過從JSON列中提取標(biāo)量值,創(chuàng)建索引。這樣能更有效的結(jié)合MySQL優(yōu)勢。

• MySQL優(yōu)化器會(huì)在匹配JSON表達(dá)式的虛擬列上尋找兼容的索引。
• 在MySQL 8.0.17及以后版本中，InnoDB存儲(chǔ)引擎支持JSON數(shù)組上的多值索引
• MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函數(shù)，包括在從JSON列生成的列上創(chuàng)建索引，作為無法索引JSON列的解決方案。每個(gè)NDB表最多支持3個(gè)JSON列。

1.虛擬列索引：

col_name data_type [GENERATED ALWAYS] AS (expr) 
  [VIRTUAL | STORED] [NOT NULL | NULL] 
  [UNIQUE [KEY]] [[PRIMARY] KEY] 
  [COMMENT 'string'] 

VIRTUAL或STORED關(guān)鍵字表示列值是如何存儲(chǔ)的，這對(duì)列的使用影響非常大:

• VIRTUAL:不存儲(chǔ)列值，但在讀取行時(shí)，在任何【BEFORE觸發(fā)器】之后立即計(jì)算列值。虛擬列不占用存儲(chǔ)空間，但暫居內(nèi)存。目前官方里沒有設(shè)置這個(gè)極限。
• STORED:當(dāng)插入或更新行時(shí)，將計(jì)算并存儲(chǔ)列值。存儲(chǔ)的列需要存儲(chǔ)空間，并且可以建立索引。
• 如果沒有指定關(guān)鍵字，則默認(rèn)為VIRTUAL。

mysql> DROP TABLE IF EXISTS `jemp`; 
mysql> CREATE TABLE  `jemp` ( 
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        c JSON, 
    d JSON, 
        g INT GENERATED ALWAYS AS  (c->"$.id") STORED, 
        INDEX i (g) 
    ); 
Query OK, 0 rows affected (0.02 sec) 
mysql> INSERT INTO jemp (c,d) VALUES  
('{"id": "1", "name": "Fred"}' , '{"user":"Fred",   "user_id":1, "zipcode":"[14471,14531]"}'), 
('{"id": "2", "name": "Wilma"}', '{"user":"Wilma",  "user_id":2, "zipcode":[24472,24532]}'  ), 
('{"id": "3", "name": "Jack"}' , '{"user":"Jack", "user_id":3, "zipcode":[34473,34533]}'    ),  
('{"id": "4", "name": "Betty"}', '{"user":"Betty",  "user_id":4, "zipcode":[44474,44534]}'  ); 
 
Query OK, 4 rows affected (0.02 sec) 
Records: 4  Duplicates: 0  Warnings: 0 
 
mysql> EXPLAIN SELECT c->>"$.name" AS name  FROM jemp WHERE g > 2\G; 
*************************** 1. row *************************** 
           id: 1 
  select_type: SIMPLE 
        table: jemp 
   partitions: NULL 
         type: range 
possible_keys: i 
          key: i 
      key_len: 5 
          ref: NULL 
         rows: 2 
     filtered: 100.00 
        Extra: Using where 
1 row in set, 1 warning (0.00 sec) 

ERROR: 
No query specified 
 
mysql> SHOW WARNINGS\G 
*************************** 1. row *************************** 
 Level: Note 
   Code: 1003 
Message: /* select#1 */ select json_unquote(json_extract(`db1`.`jemp`.`c`,'$.name')) AS `name` from `db1`.`jemp` where (`db1`.`jemp`.`g` > 2) 
1 row in set (0.00 sec) 

2.使用多值索引

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

mysql> ALTER TABLE jemp ADD INDEX zips( (CAST(d->'$.zipcode' AS UNSIGNED ARRAY)) ); 
 
#MEMBER  OF 
mysql> EXPLAIN  SELECT * FROM jemp    WHERE  24472 MEMBER  OF(d->'$.zipcode')\G 
*************************** 1. row *************************** 
           id: 1 
  select_type: SIMPLE 
        table: jemp 
   partitions: NULL 
         type: ref 
possible_keys: zips 
          key: zips 
      key_len: 9 
          ref: const 
        rows: 1 
     filtered: 100.00 
        Extra: Using where 
1 row in set, 1 warning (0.00 sec) 
 
#JSON_CONTAINS 
mysql> EXPLAIN SELECT * FROM jemp   WHERE JSON_CONTAINS(d->'$.zipcode', CAST('[14471,14531]' AS JSON))\G; 
*************************** 1. row *************************** 
           id: 1 
  select_type: SIMPLE 
        table: jemp 
   partitions: NULL 
         type: range 
possible_keys: zips 
          key: zips 
      key_len: 9 
          ref: NULL 
         rows: 2 
     filtered: 100.00 
        Extra: Using where 
1 row in set, 1 warning (0.00 sec) 
 
#JSON_OVERLAPS 
mysql> EXPLAIN SELECT * FROM jemp   WHERE JSON_OVERLAPS(d->'$.zipcode', CAST('[44474,94582]' AS JSON))\G; 
*************************** 1. row *************************** 
           id: 1 
  select_type: SIMPLE 
        table: jemp 
   partitions: NULL 
         type: range 
possible_keys: zips 
          key: zips 
      key_len: 9 
          ref: NULL 
         rows: 2 
     filtered: 100.00 
        Extra: Using where 
1 row in set, 1 warning (0.00 sec) 

從上面例子里，數(shù)據(jù)的查詢還是基于MySQL B+tree上，JSON只是一種數(shù)據(jù)保存的機(jī)制。通過對(duì)虛擬列方式，提供快速的訪問，非常好的解決了JSON支持問題。

總結(jié)

• MySQL里JSON的結(jié)合非常實(shí)用，虛擬列索引解決了查詢的性能問題。
• JSON大小確實(shí)個(gè)硬性問題，謹(jǐn)慎使用(空間大致與LONGBLOB或LONGTEXT相同,文檔的大小都僅限于max_allowed_packet系統(tǒng)變量的值)。
• 實(shí)際場景中，只能選擇適中的JSON長度，可以考慮配合大頁使用。

關(guān)于作者

崔虎龍，云和恩墨MySQL技術(shù)顧問，長期服務(wù)于金融、游戲、物流等行業(yè)的數(shù)據(jù)中心，設(shè)計(jì)數(shù)據(jù)存儲(chǔ)架構(gòu)，并熟悉數(shù)據(jù)中心運(yùn)營管理的流程及規(guī)范，自動(dòng)化運(yùn)維等。擅長MySQL、Redis、MongoDB數(shù)據(jù)庫高可用設(shè)計(jì)和運(yùn)維故障處理、備份恢復(fù)、升級(jí)遷移、性能優(yōu)化。自學(xué)通過了MySQL OCP 5.6和MySQL OCP 5.7認(rèn)證。2年多開發(fā)經(jīng)驗(yàn)，10年數(shù)據(jù)庫運(yùn)維工作經(jīng)驗(yàn)，其中專職做MySQL工作8年;曾經(jīng)擔(dān)任過項(xiàng)目經(jīng)理、數(shù)據(jù)庫經(jīng)理、數(shù)據(jù)倉庫架構(gòu)師、MySQL技術(shù)專家、DBA等職務(wù);涉及行業(yè)：金融(銀行、理財(cái))、物流、游戲、醫(yī)療、重工業(yè)等。