在MySQL與PostgreSQL的對比中，PG的JSON格式支持優(yōu)勢總是不斷被拿來比較。其實早先MariaDB也有對非結(jié)構(gòu)化的數(shù)據(jù)進行存儲的方案，稱為dynamic column，但是方案是通過BLOB類型的方式來存儲。這樣導(dǎo)致的問題是查詢性能不高，不能有效建立索引，與一些文檔數(shù)據(jù)庫對比，優(yōu)勢并不大，故在社區(qū)的反應(yīng)其實比較一般。當(dāng)然，MariaDB的dynamic column功能還不僅限于非結(jié)構(gòu)化數(shù)據(jù)的存儲，但不在本文進行展開。

MySQL 5.7.7 labs版本開始InnoDB存儲引擎已經(jīng)原生支持JSON格式，該格式不是簡單的BLOB類似的替換。原生的JSON格式支持有以下的優(yōu)勢：

• JSON數(shù)據(jù)有效性檢查：BLOB類型無法在數(shù)據(jù)庫層做這樣的約束性檢查
• 查詢性能的提升：查詢不需要遍歷所有字符串才能找到數(shù)據(jù)
• 支持索引：通過虛擬列的功能可以對JSON中的部分數(shù)據(jù)進行索引

mysql> create table user ( uid int auto_increment, 
    -> data json,primary key(uid))engine=innodb; 
Query OK, 0 rows affected (0.01 sec) 
  
mysql> insert into user values (NULL, 
    -> '{"name":"David","mail":"jiangchengyao@gmail.com","address":"Shangahai"}'); 
Query OK, 1 row affected (0.00 sec) 
  
mysql> insert into user values (NULL,'{"name":"Amy","mail":"amy@gmail.com"}'); 
Query OK, 1 row affected (0.00 sec) 

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

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

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

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

當(dāng)然，最令人的激動的功能應(yīng)該是MySQL 5.7的虛擬列功能，通過傳統(tǒng)的B+樹索引即可實現(xiàn)對JSON格式部分屬性的快速查詢。使用方法是首先創(chuàng)建該虛擬列，然后在該虛擬列上創(chuàng)建索引：

mysql> ALTER TABLE user ADD user_name varchar(128) 
    -> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL; 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
  
mysql> select user_name from user; 
+-----------+ 
| user_name | 
+-----------+ 
| "Amy"     | 
| "David"   | 
+-----------+ 
2 rows in set (0.00 sec) 
  
mysql> alter table user add index idx_username (user_name); 
Query OK, 2 rows affected (0.01 sec) 
Records: 2  Duplicates: 0  Warnings: 0 

然后可以通過添加的索引對用戶名進行快速的查詢，這和普通類型的列查詢一樣。而通過explain可以驗證優(yōu)化器已經(jīng)選擇了在虛擬列上創(chuàng)建的新索引：

mysql> explain select * from user where user_name='"Amy"'\G 
*************************** 1. row *************************** 
           id: 1 
  select_type: SIMPLE 
        table: user 
   partitions: NULL 
         type: ref 
possible_keys: idx_username 
          key: idx_username 
      key_len: 131 
          ref: const 
         rows: 1 
     filtered: 100.00 
        Extra: NULL 
1 row in set, 1 warning (0.00 sec) 

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

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