MySQL 5.7原生JSON格式支持 2015-04-15 13:33 鄒菜頭 cnblogs 字號(hào)：T?|?T

在MySQL與PostgreSQL的對(duì)比中，PG的JSON格式支持優(yōu)勢(shì)總是不斷被拿來(lái)比較。其實(shí)早先MariaDB也有對(duì)非結(jié)構(gòu)化的數(shù)據(jù)進(jìn)行存儲(chǔ)的方案，稱為dynamic column，但是方案是通過(guò)BLOB類(lèi)型的方式來(lái)存儲(chǔ)。這樣導(dǎo)致的問(wèn)題是查詢性能不高，不能有效建立索引，與一些文檔數(shù)據(jù)庫(kù)對(duì)比，優(yōu)勢(shì)并不大，故在社區(qū)的反應(yīng)其實(shí)比較一般。AD：

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

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

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

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類(lèi)型。這意味著我們可以對(duì)插入的數(shù)據(jù)做JSON格式檢查，確保其符合JSON格式的約束，如插入一條不合法的JSON數(shù)據(jù)會(huì)報(bào)如下錯(cuò)誤：

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

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

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)然，最令人的激動(dòng)的功能應(yīng)該是MySQL 5.7的虛擬列功能，通過(guò)傳統(tǒng)的B+樹(shù)索引即可實(shí)現(xiàn)對(duì)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?

然后可以通過(guò)添加的索引對(duì)用戶名進(jìn)行快速的查詢，這和普通類(lèi)型的列查詢一樣。而通過(guò)explain可以驗(yàn)證優(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對(duì)于JSON格式堪稱完美，相信PostgreSQL陣營(yíng)需要尋找新的策略來(lái)“攻擊”MySQL了吧。如無(wú)意外，還是會(huì)停留在優(yōu)化器這塊，畢竟這塊是目前MySQL必須要克服的最大問(wèn)題，好在MySQL團(tuán)隊(duì)已經(jīng)在重構(gòu)優(yōu)化器代碼，相信更好的優(yōu)化器將會(huì)在下一個(gè)版本中全面爆發(fā)。而一大堆文檔數(shù)據(jù)庫(kù)們已經(jīng)哭暈在廁所了吧。