[[403533]]

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

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

JSON了解

JSON就是一串字符串,只不過元素會使用特定的符號標注。比如：

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

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

MySQL里JSON文檔以二進制格式存儲，它提供以下功能：

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

JSON操作

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

1. 索引：

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

2.JSON值的比較和排序:

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

BETWEEN

IN()

GREATEST()

LEAST()

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

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

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

MySQL在JSON值和其他類型值之間轉(zhuǎn)換時遵循的規(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值聚合:

對于JSON值的聚合，NULL值和其他數(shù)據(jù)類型一樣被忽略。除MIN()、MAX()和GROUP_CONCAT()外，非NULL值被轉(zhuǎn)換為數(shù)字類型并聚合。對于數(shù)字標量的JSON值，(取決于值)可能會出現(xiàn)截斷和精度損失。

JSON使用索引方式：

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

• MySQL優(yōu)化器會在匹配JSON表達式的虛擬列上尋找兼容的索引。
• 在MySQL 8.0.17及以后版本中，InnoDB存儲引擎支持JSON數(shù)組上的多值索引
• MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函數(shù)，包括在從JSON列生成的列上創(chuàng)建索引，作為無法索引JSON列的解決方案。每個NDB表最多支持3個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)鍵字表示列值是如何存儲的，這對列的使用影響非常大:

• VIRTUAL:不存儲列值，但在讀取行時，在任何【BEFORE觸發(fā)器】之后立即計算列值。虛擬列不占用存儲空間，但暫居內(nèi)存。目前官方里沒有設置這個極限。
• STORED:當插入或更新行時，將計算并存儲列值。存儲的列需要存儲空間，并且可以建立索引。
• 如果沒有指定關(guā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ù)保存的機制。通過對虛擬列方式，提供快速的訪問，非常好的解決了JSON支持問題。

總結(jié)

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

關(guān)于作者

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