電子發(fā)燒友App
?
關(guān)系型的結(jié)構(gòu)化存儲(chǔ)存在一定的弊端，因?yàn)樗枰A(yù)先定義好所有的列以及列對(duì)應(yīng)的類型。但是業(yè)務(wù)在發(fā)展過(guò)程中，或許需要擴(kuò)展單個(gè)列的描述功能，這時(shí)，如果能用好 JSON 數(shù)據(jù)類型，那就能打通關(guān)系型和非關(guān)系型數(shù)據(jù)的存儲(chǔ)之間的界限，為業(yè)務(wù)提供更好的架構(gòu)選擇。當(dāng)然，很多同學(xué)在用 JSON 數(shù)據(jù)類型時(shí)會(huì)遇到各種各樣的問(wèn)題，其中最容易犯的誤區(qū)就是將類型 JSON 簡(jiǎn)單理解成字符串類型。但當(dāng)你看完這篇文章后，會(huì)真正認(rèn)識(shí)到 JSON 數(shù)據(jù)類型的威力，從而在實(shí)際工作中更好地存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)。JSON 數(shù)據(jù)類型
JSON（JavaScript Object Notation）主要用于互聯(lián)網(wǎng)應(yīng)用服務(wù)之間的數(shù)據(jù)交換。MySQL 支持RFC 7159定義的 JSON 規(guī)范，主要有 JSON 對(duì)象 和 JSON 數(shù)組 兩種類型。下面就是 JSON 對(duì)象，主要用來(lái)存儲(chǔ)圖片的相關(guān)信息：
{ ?"Image":?{ ???"Width":?800, ???"Height":?600, ???"Title":?"View?from?15th?Floor", ???"Thumbnail":?{ ?????"Url":?"http://www.example.com/image/481989943", ?????"Height":?125, ?????"Width":?100 ???}, ?"IDs":?[116,?943,?234,?38793] ?} }
[ ???{ ?????"precision":?"zip", ?????"Latitude":?37.7668, ?????"Longitude":?-122.3959, ?????"Address":?"", ?????"City":?"SAN?FRANCISCO", ?????"State":?"CA", ?????"Zip":?"94107", ?????"Country":?"US" ???}, ???{ ?????"precision":?"zip", ?????"Latitude":?37.371991, ?????"Longitude":?-122.026020, ?????"Address":?"", ?????"City":?"SUNNYVALE", ?????"State":?"CA", ?????"Zip":?"94085", ?????"Country":?"US" ???} ?]
業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)
用戶登錄設(shè)計(jì)
在數(shù)據(jù)庫(kù)中，JSON 類型比較適合存儲(chǔ)一些修改較少、相對(duì)靜態(tài)的數(shù)據(jù)，比如用戶登錄信息的存儲(chǔ)如下：DROP?TABLE?IF?EXISTS?UserLogin; CREATE?TABLE?UserLogin?( ????userId?BIGINT?NOT?NULL, ????loginInfo?JSON, ????PRIMARY?KEY(userId) );
由于當(dāng)前業(yè)務(wù)的登錄方式越來(lái)越多樣化，如同一賬戶支持手機(jī)、微信、QQ 賬號(hào)登錄，所以這里可以用 JSON 類型存儲(chǔ)登錄的信息。接著，插入下面的數(shù)據(jù)：SET?@a?=?' { ???"cellphone"?:?"13918888888", ???"wxchat"?:?"破產(chǎn)碼農(nóng)", ???"QQ"?:?"82946772" } '; INSERT?INTO?UserLogin?VALUES?(1,@a); SET?@b?=?' {?? ??"cellphone"?:?"15026888888" } '; INSERT?INTO?UserLogin?VALUES?(2,@b);
從上面的例子中可以看到，用戶 1 登錄有三種方式：手機(jī)驗(yàn)證碼登錄、微信登錄、QQ 登錄，而用戶 2 只有手機(jī)驗(yàn)證碼登錄。而如果不采用 JSON 數(shù)據(jù)類型，就要用下面的方式建表：SELECT ????userId, ????JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone"))?cellphone, ????JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat"))?wxchat FROM?UserLogin; +--------+-------------+--------------+ |?userId?|?cellphone???|?wxchat???????| +--------+-------------+--------------+ |??????1?|?13918888888?|?破產(chǎn)碼農(nóng)?????| |??????2?|?15026888888?|?NULL?????????| +--------+-------------+--------------+ 2?rows?in?set?(0.01?sec)
當(dāng)然了，每次寫 JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 表達(dá)式，和上述 SQL 效果完全一樣：SELECT? ????userId, ????loginInfo->>"$.cellphone"?cellphone, ????loginInfo->>"$.wxchat"?wxchat FROM?UserLogin;
當(dāng) JSON 數(shù)據(jù)量非常大，用戶希望對(duì) JSON 數(shù)據(jù)進(jìn)行有效檢索時(shí)，可以利用 MySQL 的 函數(shù)索引 功能對(duì) JSON 中的某個(gè)字段進(jìn)行索引。比如在上面的用戶登錄示例中，假設(shè)用戶必須綁定唯一手機(jī)號(hào)，且希望未來(lái)能用手機(jī)號(hào)碼進(jìn)行用戶檢索時(shí)，可以創(chuàng)建下面的索引：ALTER?TABLE?UserLogin?ADD?COLUMN?cellphone?VARCHAR(255)?AS?(loginInfo->>"$.cellphone"); ALTER?TABLE?UserLogin?ADD?UNIQUE?INDEX?idx_cellphone(cellphone);
上述 SQL 首先創(chuàng)建了一個(gè)虛擬列 cellphone，這個(gè)列是由函數(shù) loginInfo->>"$.cellphone" 計(jì)算得到的。然后在這個(gè)虛擬列上創(chuàng)建一個(gè)唯一索引 idx_cellphone。這時(shí)再通過(guò)虛擬列 cellphone 進(jìn)行查詢，就可以看到優(yōu)化器會(huì)使用到新創(chuàng)建的 idx_cellphone 索引：EXPLAIN?SELECT??*??FROM?UserLogin? WHERE?cellphone?=?'13918888888'G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?UserLogin ???partitions:?NULL ?????????type:?const possible_keys:?idx_cellphone ??????????key:?idx_cellphone ??????key_len:?1023 ??????????ref:?const ?????????rows:?1 ?????filtered:?100.00 ????????Extra:?NULL 1?row?in?set,?1?warning?(0.00?sec)
當(dāng)然，我們可以在一開(kāi)始創(chuàng)建表的時(shí)候，就完成虛擬列及函數(shù)索引的創(chuàng)建。如下表創(chuàng)建的列 cellphone 對(duì)應(yīng)的就是 JSON 中的內(nèi)容，是個(gè)虛擬列；uk_idx_cellphone 就是在虛擬列 cellphone 上所創(chuàng)建的索引。CREATE?TABLE?UserLogin?( ????userId?BIGINT, ????loginInfo?JSON, ????cellphone?VARCHAR(255)?AS?(loginInfo->>"$.cellphone"), ????PRIMARY?KEY(userId), ????UNIQUE?KEY?uk_idx_cellphone(cellphone) );
用戶畫像設(shè)計(jì)
某些業(yè)務(wù)需要做用戶畫像（也就是對(duì)用戶打標(biāo)簽），然后根據(jù)用戶的標(biāo)簽，通過(guò)數(shù)據(jù)挖掘技術(shù)，進(jìn)行相應(yīng)的產(chǎn)品推薦。這份架構(gòu)師圖譜建議看看，少走彎路。比如：- 在電商行業(yè)中，根據(jù)用戶的穿搭喜好，推薦相應(yīng)的商品；
- 在音樂(lè)行業(yè)中，根據(jù)用戶喜歡的音樂(lè)風(fēng)格和常聽(tīng)的歌手，推薦相應(yīng)的歌曲；
- 在金融行業(yè)，根據(jù)用戶的風(fēng)險(xiǎn)喜好和投資經(jīng)驗(yàn)，推薦相應(yīng)的理財(cái)產(chǎn)品。
CREATE?TABLE?Tags?( ????tagId?bigint?auto_increment, ????tagName?varchar(255)?NOT?NULL, ????primary?key(tagId) ); SELECT?*?FROM?Tags; +-------+--------------+ |?tagId?|?tagName??????| +-------+--------------+ |?????1?|?70后?????????| |?????2?|?80后?????????| |?????3?|?90后?????????| |?????4?|?00后?????????| |?????5?|?愛(ài)運(yùn)動(dòng)???????| |?????6?|?高學(xué)歷???????| |?????7?|?小資?????????| |?????8?|?有房?????????| |?????9?|?有車?????????| |????10?|??？措娪?????| |????11?|?愛(ài)網(wǎng)購(gòu)???????| |????12?|?愛(ài)外賣???????| +-------+--------------+
可以看到，表 Tags 是一張畫像定義表，用于描述當(dāng)前定義有多少個(gè)標(biāo)簽，接著給每個(gè)用戶打標(biāo)簽，比如用戶 David，他的標(biāo)簽是 80 后、高學(xué)歷、小資、有房、常看電影；用戶 Tom，90 后、?？措娪啊?ài)外賣。若不用 JSON 數(shù)據(jù)類型進(jìn)行標(biāo)簽存儲(chǔ)，通常會(huì)將用戶標(biāo)簽通過(guò)字符串，加上分割符的方式，在一個(gè)字段中存取用戶所有的標(biāo)簽：+-------+---------------------------------------+ |用戶????|標(biāo)簽???????????????????????????????????| +-------+---------------------------------------+ |David ?|80后?；?高學(xué)歷?；?小資?；?有房?；?？措娪???| |Tom ???|90后?；?？措娪?；?愛(ài)外賣?????????????????| +-------+---------------------------------------
這樣做的缺點(diǎn)是：不好搜索特定畫像的用戶，另外分隔符也是一種自我約定，在數(shù)據(jù)庫(kù)中其實(shí)可以任意存儲(chǔ)其他數(shù)據(jù)，最終產(chǎn)生臟數(shù)據(jù)。用 JSON 數(shù)據(jù)類型就能很好解決這個(gè)問(wèn)題：DROP?TABLE?IF?EXISTS?UserTag; CREATE?TABLE?UserTag?( ????userId?bigint?NOT?NULL, ????userTags?JSON, ????PRIMARY?KEY?(userId) ); INSERT?INTO?UserTag?VALUES?(1,'[2,6,8,10]'); INSERT?INTO?UserTag?VALUES?(2,'[3,10,12]');
其中，userTags 存儲(chǔ)的標(biāo)簽就是表 Tags 已定義的那些標(biāo)簽值，只是使用 JSON 數(shù)組類型進(jìn)行存儲(chǔ)。另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺(tái)發(fā)送：面試，可以在線閱讀。MySQL 8.0.17 版本開(kāi)始支持 Multi-Valued Indexes，用于在 JSON 數(shù)組上創(chuàng)建索引，并通過(guò)函數(shù) member of、json_contains、json_overlaps 來(lái)快速檢索索引數(shù)據(jù)。所以你可以在表 UserTag 上創(chuàng)建 Multi-Valued Indexes：ALTER?TABLE?UserTag ADD?INDEX?idx_user_tags?((cast((userTags->"$")?as?unsigned?array)));
如果想要查詢用戶畫像為?？措娪暗挠脩簦梢允褂煤瘮?shù) MEMBER OF：EXPLAIN?SELECT?*?FROM?UserTag? WHERE?10?MEMBER?OF(userTags->"$")G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?UserTag ???partitions:?NULL ?????????type:?ref possible_keys:?idx_user_tags ??????????key:?idx_user_tags ??????key_len:?9 ??????????ref:?const ?????????rows:?1 ?????filtered:?100.00 ????????Extra:?Using?where 1?row?in?set,?1?warning?(0.00?sec) SELECT?*?FROM?UserTag? WHERE?10?MEMBER?OF(userTags->"$"); +--------+---------------+ |?userId?|?userTags??????| +--------+---------------+ |??????1?|?[2,?6,?8,?10]?| |??????2?|?[3,?10,?12]???| +--------+---------------+ 2?rows?in?set?(0.00?sec)
如果想要查詢畫像為 80 后，且?？措娪暗挠脩?，可以使用函數(shù) JSON_CONTAINS：EXPLAIN?SELECT?*?FROM?UserTag? WHERE?JSON_CONTAINS(userTags->"$",?'[2,10]')G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?UserTag ???partitions:?NULL ?????????type:?range possible_keys:?idx_user_tags ??????????key:?idx_user_tags ??????key_len:?9 ??????????ref:?NULL ?????????rows:?3 ?????filtered:?100.00 ????????Extra:?Using?where 1?row?in?set,?1?warning?(0.00?sec) SELECT?*?FROM?UserTag? WHERE?JSON_CONTAINS(userTags->"$",?'[2,10]'); +--------+---------------+ |?userId?|?userTags??????| +--------+---------------+ |??????1?|?[2,?6,?8,?10]?| +--------+---------------+ 1?row?in?set?(0.00?sec)
如果想要查詢畫像為 80 后、90 后，且?？措娪暗挠脩?，則可以使用函數(shù) JSON_OVERLAP：EXPLAIN?SELECT?*?FROM?UserTag? WHERE?JSON_OVERLAPS(userTags->"$",?'[2,3,10]')G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?UserTag ???partitions:?NULL ?????????type:?range possible_keys:?idx_user_tags ??????????key:?idx_user_tags ??????key_len:?9 ??????????ref:?NULL ?????????rows:?4 ?????filtered:?100.00 ????????Extra:?Using?where 1?row?in?set,?1?warning?(0.00?sec) SELECT?*?FROM?UserTag? WHERE?JSON_OVERLAPS(userTags->"$",?'[2,3,10]'); +--------+---------------+ |?userId?|?userTags??????| +--------+---------------+ |??????1?|?[2,?6,?8,?10]?| |??????2?|?[3,?10,?12]???| +--------+---------------+ 2?rows?in?set?(0.01?sec)
總結(jié)
JSON 類型是 MySQL 5.7 版本新增的數(shù)據(jù)類型，用好 JSON 數(shù)據(jù)類型可以有效解決很多業(yè)務(wù)中實(shí)際問(wèn)題。最后，我總結(jié)下今天的重點(diǎn)內(nèi)容：
- 使用 JSON 數(shù)據(jù)類型，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時(shí)也支持 Multi-Valued Indexes；
- JSON 數(shù)據(jù)類型的好處是無(wú)須預(yù)先定義列，數(shù)據(jù)本身就具有很好的描述性；
- 不要將有明顯關(guān)系型的數(shù)據(jù)用 JSON 存儲(chǔ)，如用戶余額、用戶姓名、用戶身份證等，這些都是每個(gè)用戶必須包含的數(shù)據(jù)；
- JSON 數(shù)據(jù)類型推薦使用在不經(jīng)常更新的靜態(tài)數(shù)據(jù)存儲(chǔ)。
來(lái)源：https://blog.csdn.net/java_pfx/article/details/116594654
?
?
-End-
?
?
審核編輯 ：李倩
?
工商網(wǎng)監(jiān)
評(píng)論