MySQL + JSON = 王炸??！
加入技術(shù)交流群
掃碼加入
和技術(shù)大咖面對面交流
海量資料庫查詢(xún)
作者 | 「已注銷(xiāo)」
來(lái)源 | CSDN博客
關(guān)系型的結構化存儲存在一定的弊端，因為它需要預先定義好所有的列以及列對應的類(lèi)型。但是業(yè)務(wù)在發(fā)展過(guò)程中，或許需要擴展單個(gè)列的描述功能，這時(shí)，如果能用好 JSON 數據類(lèi)型，那就能打通關(guān)系型和非關(guān)系型數據的存儲之間的界限，為業(yè)務(wù)提供更好的架構選擇。
當然，很多同學(xué)在用 JSON 數據類(lèi)型時(shí)會(huì )遇到各種各樣的問(wèn)題，其中最容易犯的誤區就是將類(lèi)型 JSON 簡(jiǎn)單理解成字符串類(lèi)型。但當你看完這篇文章后，會(huì )真正認識到 JSON 數據類(lèi)型的威力，從而在實(shí)際工作中更好地存儲非結構化的數據。
JSON 數據類(lèi)型
JSON（JavaScript Object Notation）主要用于互聯(lián)網(wǎng)應用服務(wù)之間的數據交換。MySQL 支持RFC 7159定義的 JSON 規范，主要有 JSON 對象 和 JSON 數組 兩種類(lèi)型。
下面就是 JSON 對象，主要用來(lái)存儲圖片的相關(guān)信息：
{
: {
: 800,
: 600,
: ,
: {
: ,
: 125,
: 100
},
: [116, 943, 234, 38793]
}
}從中你可以看到， JSON 類(lèi)型可以很好地描述數據的相關(guān)內容，比如這張圖片的寬度、高度、標題等（這里使用到的類(lèi)型有整型、字符串類(lèi)型）。JSON對象除了支持字符串、整型、日期類(lèi)型，JSON 內嵌的字段也支持數組類(lèi)型，如上代碼中的 IDs 字段。另一種 JSON 數據類(lèi)型是數組類(lèi)型，如：
{
: ,
: 37.7668,
: -122.3959,
: ,
: ,
: ,
: ,
:
},
{
: ,
: 37.371991,
: -122.026020,
: ,
: ,
: ,
: ,
:
}
]上面的示例演示的是一個(gè) JSON 數組，其中有 2 個(gè) JSON 對象。
到目前為止，可能很多同學(xué)會(huì )把 JSON 當作一個(gè)很大的字段串類(lèi)型，從表面上來(lái)看，沒(méi)有錯。但本質(zhì)上，JSON 是一種新的類(lèi)型，有自己的存儲格式，還能在每個(gè)對應的字段上創(chuàng )建索引，做特定的優(yōu)化，這是傳統字段串無(wú)法實(shí)現的。
JSON 類(lèi)型的另一個(gè)好處是無(wú)須預定義字段，字段可以無(wú)限擴展。而傳統關(guān)系型數據庫的列都需預先定義，想要擴展需要執行 ALTER TABLE ... ADD COLUMN ... 這樣比較重的操作。
需要注意是，JSON 類(lèi)型是從 MySQL 5.7 版本開(kāi)始支持的功能，而 8.0 版本解決了更新 JSON 的日志性能瓶頸。如果要在生產(chǎn)環(huán)境中使用 JSON 數據類(lèi)型，強烈推薦使用 MySQL 8.0 版本。
講到這兒，你已經(jīng)對 JSON 類(lèi)型的基本概念有所了解了，接下來(lái)，我們進(jìn)入實(shí)戰環(huán)節：如何在業(yè)務(wù)中用好JSON類(lèi)型？
業(yè)務(wù)表結構設計實(shí)戰
用戶(hù)登錄設計
在數據庫中，JSON 類(lèi)型比較適合存儲一些修改較少、相對靜態(tài)的數據，比如用戶(hù)登錄信息的存儲如下：
DROP TABLE IF EXISTS UserLogin; CREATE TABLE UserLogin ( userId BIGINT NOT NULL, loginInfo JSON, PRIMARY KEY(userId) );
由于當前業(yè)務(wù)的登錄方式越來(lái)越多樣化，如同一賬戶(hù)支持手機、微信、QQ 賬號登錄，所以這里可以用 JSON 類(lèi)型存儲登錄的信息。
接著(zhù)，插入下面的數據：
SET @a = ; INSERT INTO UserLogin VALUES (1,@a); SET @b = ; INSERT INTO UserLogin VALUES (2,@b);
從上面的例子中可以看到，用戶(hù) 1 登錄有三種方式：手機驗證碼登錄、微信登錄、QQ 登錄，而用戶(hù) 2 只有手機驗證碼登錄。
而如果不采用 JSON 數據類(lèi)型，就要用下面的方式建表：
SELECT userId, JSON_UNQUOTE(JSON_EXTRACT(loginInfo,)) cellphone, JSON_UNQUOTE(JSON_EXTRACT(loginInfo,)) wxchat FROM UserLogin; +--------+-------------+--------------+ | userId | cellphone | wxchat | +--------+-------------+--------------+ | 1 | 13918888888 | 破產(chǎn)碼農 | | 2 | 15026888888 | NULL | +--------+-------------+--------------+ 2 rows (0.01 sec)
當然了，每次寫(xiě) JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 表達式，和上述 SQL 效果完全一樣：
SELECT userId, loginInfo->> cellphone, loginInfo->> wxchat FROM UserLogin;
當 JSON 數據量非常大，用戶(hù)希望對 JSON 數據進(jìn)行有效檢索時(shí)，可以利用 MySQL 的 函數索引 功能對 JSON 中的某個(gè)字段進(jìn)行索引。
比如在上面的用戶(hù)登錄示例中，假設用戶(hù)必須綁定唯一手機號，且希望未來(lái)能用手機號碼進(jìn)行用戶(hù)檢索時(shí)，可以創(chuàng )建下面的索引：
ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>); ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);
上述 SQL 首先創(chuàng )建了一個(gè)虛擬列 cellphone，這個(gè)列是由函數 loginInfo->>"$.cellphone" 計算得到的。然后在這個(gè)虛擬列上創(chuàng )建一個(gè)唯一索引 idx_cellphone。這時(shí)再通過(guò)虛擬列 cellphone 進(jìn)行查詢(xún)，就可以看到優(yōu)化器會(huì )使用到新創(chuàng )建的 idx_cellphone 索引：
EXPLAIN SELECT * FROM UserLogin WHERE cellphone = G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserLogin partitions: NULL : const possible_keys: idx_cellphone key: idx_cellphone key_len: 1023 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row , 1 warning (0.00 sec)
當然，我們可以在一開(kāi)始創(chuàng )建表的時(shí)候，就完成虛擬列及函數索引的創(chuàng )建。如下表創(chuàng )建的列 cellphone 對應的就是 JSON 中的內容，是個(gè)虛擬列；uk_idx_cellphone 就是在虛擬列 cellphone 上所創(chuàng )建的索引。
CREATE TABLE UserLogin ( userId BIGINT, loginInfo JSON, cellphone VARCHAR(255) AS (loginInfo->>), PRIMARY KEY(userId), UNIQUE KEY uk_idx_cellphone(cellphone) );
用戶(hù)畫(huà)像設計
某些業(yè)務(wù)需要做用戶(hù)畫(huà)像（也就是對用戶(hù)打標簽），然后根據用戶(hù)的標簽，通過(guò)數據挖掘技術(shù)，進(jìn)行相應的產(chǎn)品推薦。
比如：
在電商行業(yè)中，根據用戶(hù)的穿搭喜好，推薦相應的商品；
在音樂(lè )行業(yè)中，根據用戶(hù)喜歡的音樂(lè )風(fēng)格和常聽(tīng)的歌手，推薦相應的歌曲；
在金融行業(yè)，根據用戶(hù)的風(fēng)險喜好和投資經(jīng)驗，推薦相應的理財產(chǎn)品。
在這，我強烈推薦你用 JSON 類(lèi)型在數據庫中存儲用戶(hù)畫(huà)像信息，并結合 JSON 數組類(lèi)型和多值索引的特點(diǎn)進(jìn)行高效查詢(xún)。假設有張畫(huà)像定義表：
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)運動(dòng) | | 6 | 高學(xué)歷 | | 7 | 小資 | | 8 | 有房 | | 9 | 有車(chē) | | 10 | ?？措娪?nbsp; | | 11 | 愛(ài)網(wǎng)購 | | 12 | 愛(ài)外賣(mài) | +-------+--------------+
可以看到，表 Tags 是一張畫(huà)像定義表，用于描述當前定義有多少個(gè)標簽，接著(zhù)給每個(gè)用戶(hù)打標簽，比如用戶(hù) David，他的標簽是 80 后、高學(xué)歷、小資、有房、?？措娪?；用戶(hù) Tom，90 后、?？措娪?、愛(ài)外賣(mài)。
若不用 JSON 數據類(lèi)型進(jìn)行標簽存儲，通常會(huì )將用戶(hù)標簽通過(guò)字符串，加上分割符的方式，在一個(gè)字段中存取用戶(hù)所有的標簽：
+-------+---------------------------------------+ |用戶(hù) |標簽 | +-------+---------------------------------------+ |David |80后 ； 高學(xué)歷 ； 小資 ； 有房 ；?？措娪?nbsp; | |Tom |90后 ；?？措娪?nbsp;； 愛(ài)外賣(mài) | +-------+---------------------------------------
這樣做的缺點(diǎn)是：不好搜索特定畫(huà)像的用戶(hù)，另外分隔符也是一種自我約定，在數據庫中其實(shí)可以任意存儲其他數據，最終產(chǎn)生臟數據。
用 JSON 數據類(lèi)型就能很好解決這個(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,); INSERT INTO UserTag VALUES (2,);
其中，userTags 存儲的標簽就是表 Tags 已定義的那些標簽值，只是使用 JSON 數組類(lèi)型進(jìn)行存儲。
另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺發(fā)送：面試，可以在線(xiàn)閱讀。
MySQL 8.0.17 版本開(kāi)始支持 Multi-Valued Indexes，用于在 JSON 數組上創(chuàng )建索引，并通過(guò)函數 member of、json_contains、json_overlaps 來(lái)快速檢索索引數據。所以你可以在表 UserTag 上創(chuàng )建 Multi-Valued Indexes：
ALTER TABLE UserTag ADD INDEX idx_user_tags ((cast((userTags->) as unsigned array)));
如果想要查詢(xún)用戶(hù)畫(huà)像為?？措娪暗挠脩?hù)，可以使用函數 MEMBER OF：
EXPLAIN SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->)G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL : ref possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: Using 1 row , 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 (0.00 sec)
如果想要查詢(xún)畫(huà)像為 80 后，且?？措娪暗挠脩?hù)，可以使用函數 JSON_CONTAINS：
EXPLAIN SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->, )G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL : range possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 3 filtered: 100.00 Extra: Using 1 row , 1 warning (0.00 sec) SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->, ); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | +--------+---------------+ 1 row (0.00 sec)
如果想要查詢(xún)畫(huà)像為 80 后、90 后，且?？措娪暗挠脩?hù)，則可以使用函數 JSON_OVERLAP：
EXPLAIN SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->, )G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL : range possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 4 filtered: 100.00 Extra: Using 1 row , 1 warning (0.00 sec) SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->, ); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | | 2 | [3, 10, 12] | +--------+---------------+ 2 rows (0.01 sec)
總結
JSON 類(lèi)型是 MySQL 5.7 版本新增的數據類(lèi)型，用好 JSON 數據類(lèi)型可以有效解決很多業(yè)務(wù)中實(shí)際問(wèn)題。
最后，我總結下今天的重點(diǎn)內容：
使用 JSON 數據類(lèi)型，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時(shí)也支持 Multi-Valued Indexes；
JSON 數據類(lèi)型的好處是無(wú)須預先定義列，數據本身就具有很好的描述性；
不要將有明顯關(guān)系型的數據用 JSON 存儲，如用戶(hù)余額、用戶(hù)姓名、用戶(hù)身份證等，這些都是每個(gè)用戶(hù)必須包含的數據；
JSON 數據類(lèi)型推薦使用在不經(jīng)常更新的靜態(tài)數據存儲。
版權聲明：本文為博主原創(chuàng )文章，遵循 CC 4.0 BY-SA 版權協(xié)議，轉載請附上原文出處鏈接和本聲明。
*博客內容為網(wǎng)友個(gè)人發(fā)布，僅代表博主個(gè)人觀(guān)點(diǎn)，如有侵權請聯(lián)系工作人員刪除。
