使用MySQL，請用好 JSON 這張牌！

2022-03-03 20:28:33　來源: Meta 舉報

分享至

來源：https://blog.csdn.net/java_pfx/article/details/116594654

關(guān)系型的結(jié)構(gòu)化存儲存在一定的弊端，因為它需要預(yù)先定義好所有的列以及列對應(yīng)的類型。但是業(yè)務(wù)在發(fā)展過程中，或許需要擴展單個列的描述功能，這時，如果能用好 JSON 數(shù)據(jù)類型，那就能打通關(guān)系型和非關(guān)系型數(shù)據(jù)的存儲之間的界限，為業(yè)務(wù)提供更好的架構(gòu)選擇。

當(dāng)然，很多同學(xué)在用 JSON 數(shù)據(jù)類型時會遇到各種各樣的問題，其中最容易犯的誤區(qū)就是將類型 JSON 簡單理解成字符串類型。但當(dāng)你看完這篇文章后，會真正認(rèn)識到 JSON 數(shù)據(jù)類型的威力，從而在實際工作中更好地存儲非結(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 對象和JSON 數(shù)組兩種類型。下面就是 JSON 對象，主要用來存儲圖片的相關(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]

從中你可以看到， JSON 類型可以很好地描述數(shù)據(jù)的相關(guān)內(nèi)容，比如這張圖片的寬度、高度、標(biāo)題等（這里使用到的類型有整型、字符串類型）。

JSON對象除了支持字符串、整型、日期類型，JSON 內(nèi)嵌的字段也支持?jǐn)?shù)組類型，如上代碼中的 IDs 字段。

另一種 JSON 數(shù)據(jù)類型是數(shù)組類型，如：

"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"

上面的示例演示的是一個 JSON 數(shù)組，其中有 2 個 JSON 對象。

到目前為止，可能很多同學(xué)會把 JSON 當(dāng)作一個很大的字段串類型，從表面上來看，沒有錯。但本質(zhì)上，JSON 是一種新的類型，有自己的存儲格式，還能在每個對應(yīng)的字段上創(chuàng)建索引，做特定的優(yōu)化，這是傳統(tǒng)字段串無法實現(xiàn)的。JSON 類型的另一個好處是無須預(yù)定義字段，字段可以無限擴展。而傳統(tǒng)關(guān)系型數(shù)據(jù)庫的列都需預(yù)先定義，想要擴展需要執(zhí)行 ALTER TABLE ... ADD COLUMN ... 這樣比較重的操作。

需要注意是，JSON 類型是從 MySQL 5.7 版本開始支持的功能，而 8.0 版本解決了更新 JSON 的日志性能瓶頸。如果要在生產(chǎn)環(huán)境中使用 JSON 數(shù)據(jù)類型，強烈推薦使用 MySQL 8.0 版本。

講到這兒，你已經(jīng)對 JSON 類型的基本概念有所了解了，接下來，我們進(jìn)入實戰(zhàn)環(huán)節(jié)：如何在業(yè)務(wù)中用好JSON類型？

業(yè)務(wù)表結(jié)構(gòu)設(shè)計實戰(zhàn)用戶登錄設(shè)計

在數(shù)據(jù)庫中，JSON 類型比較適合存儲一些修改較少、相對靜態(tài)的數(shù)據(jù)，比如用戶登錄信息的存儲如下：

DROP TABLE IF EXISTS UserLogin;

CREATE TABLE UserLogin ( userId BIGINT NOT NULL, loginInfo JSON, PRIMARY KEY(userId) );

由于當(dāng)前業(yè)務(wù)的登錄方式越來越多樣化，如同一賬戶支持手機、微信、QQ 賬號登錄，所以這里可以用 JSON 類型存儲登錄的信息。

接著，插入下面的數(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 登錄有三種方式：手機驗證碼登錄、微信登錄、QQ 登錄，而用戶 2 只有手機驗證碼登錄。

而如果不采用 JSON 數(shù)據(jù)類型，就要用下面的方式建表：

當(dāng)然了，每次寫 JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 表達(dá)式，和上述 SQL 效果完全一樣：

SELECT userId, loginInfo->>"$.cellphone" cellphone, loginInfo->>"$.wxchat" wxchat FROM UserLogin;

當(dāng) JSON 數(shù)據(jù)量非常大，用戶希望對 JSON 數(shù)據(jù)進(jìn)行有效檢索時，可以利用 MySQL 的函數(shù)索引功能對 JSON 中的某個字段進(jìn)行索引。

比如在上面的用戶登錄示例中，假設(shè)用戶必須綁定唯一手機號，且希望未來能用手機號碼進(jìn)行用戶檢索時，可以創(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)建了一個虛擬列 cellphone，這個列是由函數(shù) loginInfo->>"$.cellphone" 計算得到的。然后在這個虛擬列上創(chuàng)建一個唯一索引 idx_cellphone。這時再通過虛擬列 cellphone 進(jìn)行查詢，就可以看到優(yōu)化器會使用到新創(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)然，我們可以在一開始創(chuàng)建表的時候，就完成虛擬列及函數(shù)索引的創(chuàng)建。如下表創(chuàng)建的列 cellphone 對應(yīng)的就是 JSON 中的內(nèi)容，是個虛擬列；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è)計

某些業(yè)務(wù)需要做用戶畫像（也就是對用戶打標(biāo)簽），然后根據(jù)用戶的標(biāo)簽，通過數(shù)據(jù)挖掘技術(shù)，進(jìn)行相應(yīng)的產(chǎn)品推薦。比如：

在電商行業(yè)中，根據(jù)用戶的穿搭喜好，推薦相應(yīng)的商品；
在音樂行業(yè)中，根據(jù)用戶喜歡的音樂風(fēng)格和常聽的歌手，推薦相應(yīng)的歌曲；
在金融行業(yè)，根據(jù)用戶的風(fēng)險喜好和投資經(jīng)驗，推薦相應(yīng)的理財產(chǎn)品。

在這，我強烈推薦你用 JSON 類型在數(shù)據(jù)庫中存儲用戶畫像信息，并結(jié)合 JSON 數(shù)組類型和多值索引的特點進(jìn)行高效查詢。假設(shè)有張畫像定義表：

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 | 愛運動 | | 6 | 高學(xué)歷 | | 7 | 小資 | | 8 | 有房 | | 9 | 有車 | | 10 | 常看電影 | | 11 | 愛網(wǎng)購 | | 12 | 愛外賣 | +-------+--------------+

可以看到，表 Tags 是一張畫像定義表，用于描述當(dāng)前定義有多少個標(biāo)簽，接著給每個用戶打標(biāo)簽，比如用戶 David，他的標(biāo)簽是 80 后、高學(xué)歷、小資、有房、常看電影；用戶 Tom，90 后、常看電影、愛外賣。

若不用 JSON 數(shù)據(jù)類型進(jìn)行標(biāo)簽存儲，通常會將用戶標(biāo)簽通過字符串，加上分割符的方式，在一個字段中存取用戶所有的標(biāo)簽：

|用戶 |標(biāo)簽 | |David |80后；高學(xué)歷；小資；有房；常看電影 | |Tom |90后；常看電影；愛外賣 |

這樣做的缺點是：不好搜索特定畫像的用戶，另外分隔符也是一種自我約定，在數(shù)據(jù)庫中其實可以任意存儲其他數(shù)據(jù)，最終產(chǎn)生臟數(shù)據(jù)。

用 JSON 數(shù)據(jù)類型就能很好解決這個問題：

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 存儲的標(biāo)簽就是表 Tags 已定義的那些標(biāo)簽值，只是使用 JSON 數(shù)組類型進(jìn)行存儲。

MySQL 8.0.17 版本開始支持 Multi-Valued Indexes，用于在 JSON 數(shù)組上創(chuàng)建索引，并通過函數(shù) member of、json_contains、json_overlaps 來快速檢索索引數(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ù)中實際問題。最后，我總結(jié)下今天的重點內(nèi)容：

使用 JSON 數(shù)據(jù)類型，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時也支持 Multi-Valued Indexes；
JSON 數(shù)據(jù)類型的好處是無須預(yù)先定義列，數(shù)據(jù)本身就具有很好的描述性；
不要將有明顯關(guān)系型的數(shù)據(jù)用 JSON 存儲，如用戶余額、用戶姓名、用戶身份證等，這些都是每個用戶必須包含的數(shù)據(jù)；
JSON 數(shù)據(jù)類型推薦使用在不經(jīng)常更新的靜態(tài)數(shù)據(jù)存儲。

END

2021年Java原創(chuàng)面試題庫連載中

更多內(nèi)容，點擊上方名片查看

特別聲明：以上內(nèi)容(如有圖片或視頻亦包括在內(nèi))為自媒體平臺“網(wǎng)易號”用戶上傳并發(fā)布，本平臺僅提供信息存儲服務(wù)。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.