聊聊MySQL 8.0中的Json增強
本文轉載自微信公眾號「數據和云」，作者崔虎龍。轉載本文請聯系數據和云公眾號。
現在很多應用環境中都能看到JSON靈活的影子。各階段數據層次的遞歸層次，能很好的分辨。一直對MySQL的JSON很期待的，最近才有時間研究一下。
JSON了解
JSON就是一串字符串,只不過元素會使用特定的符號標注。比如：
- {} 雙括號表示對象
- [] 中括號表示數組
- “” 雙引號內是屬性或值
- : 冒號表示后者是前者的值
關系型數據庫實現JSON難度在于，關系型數據庫需要定義數據庫和表結構。為了應對這一點，從MySQL 5.7開始，MySQL支持了JavaScript對象表示(JavaScriptObject Notation，JSON) 數據類型。之前，這類數據不是單獨的數據類型，會被存儲為字符串。新的JSON數據類型提供了自動驗證的JSON文檔以及優化的存儲格式。
MySQL里JSON文檔以二進制格式存儲，它提供以下功能：
- 自動驗證存儲在JSON列中的JSON文檔。無效文檔產生錯誤。
- 優化的存儲格式。存儲在JSON列中的JSON文檔被轉換為允許快速讀取訪問文檔元素的內部格式。二進制格式存儲的JSON值。
- 對文檔元素的快速讀取訪問。當服務器再次讀取JSON文檔時，不需要重新解析文本獲取該值。通過鍵或數組索引直接查找子對象或嵌套值，而不需要讀取文檔中的所有值。
- 存儲JSON文檔所需的空間大致與LONGBLOB或LONGTEXT相同。
- 存儲在JSON列中的任何JSON文檔的大小都僅限于max_allowed_packet系統變量的值。
- MySQL 8.0.13之前，JSON列不能有非NULL的默認值。
JSON操作
數據保存到MySQL，操作方面都提供哪些支持?目前MySQL 8.0版本的的JSON總共支持32個普通函數和2個空間函數：
1. 索引：
- JSON列，像其他二進制類型的列一樣，不直接索引;相反，您可以在生成的列上創建索引，從JSON列中提取標量值。有關詳細示例，請參見為生成的列建立索引以提供JSON列索引。
- MySQL優化器還會在匹配JSON表達式的虛擬列上尋找兼容的索引。
- 在MySQL 8.0.17及以后版本中，InnoDB存儲引擎支持JSON數組上的多值索引。看到多值索引。
- MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函數，包括在從JSON列生成的列上創建索引，作為無法索引JSON列的解決方案。每個NDB表最多支持3個JSON列。
2.JSON值的比較和排序:
- JSON值可以使用=、<、<=、>、>=、<>、!=和<=>操作符進行比較。
- JSON值不支持以下比較操作符和函數:
BETWEEN
IN()
GREATEST()
LEAST()
- 對于列出的比較操作符和函數，一種變通方法是將JSON值轉換為本地MySQL數值或字符串數據類型，以便它們具有一致的非JSON標量類型。就是說轉換成需要的MySQL字段繼續換算，也算是一種折中方案。
- JSON值的比較分為兩個級別。第一級比較基于比較值的JSON類型。如果類型不同，則僅由哪個類型優先級更高來決定比較結果。如果兩個值具有相同的JSON類型，則使用特定類型的規則進行第二級比較。
BLOB > BIT > OPAQUE > DATETIME > TIME > DATE > BOOLEAN > ARRAY > OBJECT > STRING > INTEGER, DOUBLE > NULL。
3.JSON和非JSON值之間的轉換:
MySQL在JSON值和其他類型值之間轉換時遵循的規則:
CAST(other type AS JSON)
結果為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值和其他數據類型一樣被忽略。除MIN()、MAX()和GROUP_CONCAT()外，非NULL值被轉換為數字類型并聚合。對于數字標量的JSON值，(取決于值)可能會出現截斷和精度損失。
JSON使用索引方式：
MySQL JSON列上無法創建索引，是通過從JSON列中提取標量值,創建索引。這樣能更有效的結合MySQL優勢。
- MySQL優化器會在匹配JSON表達式的虛擬列上尋找兼容的索引。
- 在MySQL 8.0.17及以后版本中，InnoDB存儲引擎支持JSON數組上的多值索引
- MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函數，包括在從JSON列生成的列上創建索引，作為無法索引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關鍵字表示列值是如何存儲的，這對列的使用影響非常大:
- VIRTUAL:不存儲列值，但在讀取行時，在任何【BEFORE觸發器】之后立即計算列值。虛擬列不占用存儲空間，但暫居內存。目前官方里沒有設置這個極限。
- STORED:當插入或更新行時，將計算并存儲列值。存儲的列需要存儲空間，并且可以建立索引。
- 如果沒有指定關鍵字，則默認為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)
從上面例子里，數據的查詢還是基于MySQL B+tree上，JSON只是一種數據保存的機制。通過對虛擬列方式，提供快速的訪問，非常好的解決了JSON支持問題。
總結
- MySQL里JSON的結合非常實用，虛擬列索引解決了查詢的性能問題。
- JSON大小確實個硬性問題，謹慎使用(空間大致與LONGBLOB或LONGTEXT相同,文檔的大小都僅限于max_allowed_packet系統變量的值)。
- 實際場景中，只能選擇適中的JSON長度，可以考慮配合大頁使用。
關于作者
崔虎龍，云和恩墨MySQL技術顧問，長期服務于金融、游戲、物流等行業的數據中心，設計數據存儲架構，并熟悉數據中心運營管理的流程及規范，自動化運維等。擅長MySQL、Redis、MongoDB數據庫高可用設計和運維故障處理、備份恢復、升級遷移、性能優化。自學通過了MySQL OCP 5.6和MySQL OCP 5.7認證。2年多開發經驗，10年數據庫運維工作經驗，其中專職做MySQL工作8年;曾經擔任過項目經理、數據庫經理、數據倉庫架構師、MySQL技術專家、DBA等職務;涉及行業：金融(銀行、理財)、物流、游戲、醫療、重工業等。
