程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL 5.7原生JSON格式支持

MySQL 5.7原生JSON格式支持

編輯:MySQL綜合教程

MySQL 5.7原生JSON格式支持


在MySQL與PostgreSQL的對比中,PG的JSON格式支持優勢總是不斷被拿來比較。其實早先MariaDB也有對非結構化的數據進行存儲的方案,稱為dynamic column,但是方案是通過BLOB類型的方式來存儲。這樣導致的問題是查詢性能不高,不能有效建立索引,與一些文檔數據庫對比,優勢並不大,故在社區的反應其實比較一般。當然,MariaDB的dynamic column功能還不僅限於非結構化數據的存儲,但不在本文進行展開。

MySQL 5.7.7 labs版本開始InnoDB存儲引擎已經原生支持JSON格式,該格式不是簡單的BLOB類似的替換。原生的JSON格式支持有以下的優勢:

  • JSON數據有效性檢查:BLOB類型無法在數據庫層做這樣的約束性檢查
  • 查詢性能的提升:查詢不需要遍歷所有字符串才能找到數據
  • 支持索引:通過虛擬列的功能可以對JSON中的部分數據進行索引
  1. mysql> create table user ( uid int auto_increment, 
  2.     -> data json,primary key(uid))engine=innodb; 
  3. Query OK, 0 rows affected (0.01 sec) 
  4.   
  5. mysql> insert into user values (NULL, 
  6.     -> '{"name":"David","mail":"[email protected]","address":"Shangahai"}'); 
  7. Query OK, 1 row affected (0.00 sec) 
  8.   
  9. mysql> insert into user values (NULL,'{"name":"Amy","mail":"[email protected]"}'); 
  10. Query OK, 1 row affected (0.00 sec) 

可以看到我們新建了表user,並且將列data定義為了JSON類型。這意味著我們可以對插入的數據做JSON格式檢查,確保其符合JSON格式的約束,如插入一條不合法的JSON數據會報如下錯誤:

  1. mysql> insert into user values (NULL,"test"); 
  2. ERROR 3130 (22032): Invalid JSON text: "Invalid value" at position 2 in value (or column) 'test'. 

此外,正如前面所說的,MySQL 5.7提供了一系列函數來高效地處理JSON字符,而不是需要遍歷所有字符來查找,這不得不說是對MariaDB dynamic column的巨大改進:

  1. mysql> select jsn_extract(data, '$.name'),jsn_extract(data,'$.address') from user; 
  2. +-----------------------------+-------------------------------+ 
  3. | jsn_extract(data, '$.name') | jsn_extract(data,'$.address') | 
  4. +-----------------------------+-------------------------------+ 
  5. | "David" | "Shangahai" | 
  6. | "Amy" | NULL | 
  7. +-----------------------------+-------------------------------+ 
  8. 2 rows in set (0.00 sec) 

當然,最令人的激動的功能應該是MySQL 5.7的虛擬列功能,通過傳統的B+樹索引即可實現對JSON格式部分屬性的快速查詢。使用方法是首先創建該虛擬列,然後在該虛擬列上創建索引:

  1. mysql> ALTER TABLE user ADD user_name varchar(128) 
  2.     -> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL; 
  3. Query OK, 0 rows affected (0.01 sec) 
  4. Records: 0 Duplicates: 0 Warnings: 0 
  5.   
  6. mysql> select user_name from user; 
  7. +-----------+ 
  8. | user_name | 
  9. +-----------+ 
  10. | "Amy"     | 
  11. | "David"   | 
  12. +-----------+ 
  13. 2 rows in set (0.00 sec) 
  14.   
  15. mysql> alter table user add index idx_username (user_name); 
  16. Query OK, 2 rows affected (0.01 sec) 
  17. Records: 2  Duplicates: 0  Warnings: 0 

然後可以通過添加的索引對用戶名進行快速的查詢,這和普通類型的列查詢一樣。而通過explain可以驗證優化器已經選擇了在虛擬列上創建的新索引:

  1. mysql> explain select * from user where user_name='"Amy"'\G 
  2. *************************** 1. row *************************** 
  3.            id: 1 
  4.   select_type: SIMPLE 
  5.         table: user 
  6.    partitions: NULL 
  7.          type: ref 
  8. possible_keys: idx_username 
  9.           key: idx_username 
  10.       key_len: 131 
  11.           ref: const 
  12.          rows: 1 
  13.      filtered: 100.00 
  14.         Extra: NULL 
  15. 1 row in set, 1 warning (0.00 sec) 

可以發現MySQL 5.7對於JSON格式堪稱完美,相信PostgreSQL陣營需要尋找新的策略來“攻擊”MySQL了吧。如無意外,還是會停留在優化器這塊,畢竟這塊是目前MySQL必須要克服的最大問題,好在MySQL團隊已經在重構優化器代碼,相信更好的優化器將會在下一個版本中全面爆發。而一大堆文檔數據庫們已經哭暈在廁所了吧。

博文出處:http://www.cnblogs.com/zoucaitou/p/4424575.html
 



  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved