歡迎光臨
每天分享高質量文章

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":"[email protected]","address":"Shangahai"}');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (NULL,'{"name":"Amy","mail":"[email protected]"}');
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

赞(0)

分享創造快樂