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中的部分資料進行索引

首先我們來看如何在MySQL中使用原生的JSON格式：

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型別。這意味著我們可以對插入的資料做JSON格式檢查，確保其符合JSON格式的約束，如插入一條不合法的JSON資料會報如下錯誤：

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提供了一系列函式來高效地處理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)

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

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可以驗證最佳化器已經選擇了在虛擬列上建立的新索引：

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)

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

作者：薑承堯

連結：http://www.innomysql.net/article/15319.html