拋磚系列之-MySQL中的資料型別JSON

踩刀詩人發表於2022-01-05
MySql資料型別JSON

 

 

 今天介紹一個MySQL中的資料型別-JSON，相信大家對JSON都不陌生，在日常工作中使用到的頻率也很高，話不多說，直接開始。

 

何謂JSON

看下RFC文件對於JSON的描述

1.基於 JavaScript 語言的輕量級的資料交換格式

2.基於文字

3.語言無關

 

JSON應用場景

我大概使用過以下兩類：

1.介面的資料交換，比如ajax請求時的application/json、rpc呼叫時的JSON序列化\反序列化；

2.以JSON格式儲存資料，我接觸過以下兩種：

    2.1 以Mongodb為代表的文件型資料庫，很好的支援JSON格式的資料儲存；

    2.2 以MySQL為代表的關係型資料庫，5.7.8之前沒有JSON這種資料型別，只能以varchar或者text形式變相的支援JSON，存取鍵值極不方便；5.7.8開始有JSON資料型別，有專門語法支援鍵值的存取，易用性得到很大提升。

 

接下來重點聊聊MySQL中如何存取JSON以及存在的一些問題。

                                        

MySQL 儲存JSON

熟悉關係型資料庫的同學都知道，資料儲存在表中，得先有表才能插資料，看一條普通的SQL insert語句

insert into user(id,name,age) values(1,'jack',10);

代表的語義是往user表中插入一條資料，這條資料有三個屬性，分別是id、name、age，各自對應MySQL user表中的三個列，如果我們向user表中插入一個不存在的列salary，MySQL會報錯

Error Code: 1054. Unknown column 'salary' in 'field list'

結論是要往MySQL表中插入資料，必須提前定義好表結構，表結構包括表名、表字符集、表包含的欄位、欄位名、欄位型別等等。

 

有什麼辦法能不給表加物理欄位就可以為資料增加屬性呢？

給表預置一個擴充套件欄位是一種解決思路，比如extdata，裡面儲存JSON形式的鍵值對，形如：

 

extdata

{"salary":1000,
"sex":'女',
"其他key":'其他值'
}

至於存哪些key完全由使用方決定，key的數量不限，value的型別也不限，是不是有很好的擴充套件性，不管業務怎麼變，底層儲存都是支援的。

 

這也就是為什麼要在MySQL中存取JSON的目的，主要是為了追求擴充套件性。

 

具體到MySQL中怎麼實現，前面提到MySQL 5.7.8之前是不支援JSON的，要支援JSON語義，只能以字串形式來變相實現，比如要修改extdata中的salary為2000，是沒有辦法直接修改的，需要先在應用層將extdata讀出然後反序列化為JSON物件，通過JSON物件的Api來修改salary的值，修改完以後將新的JSON物件序列化為新JSON串，最後整體修改user表中的extdata欄位為新JSON串，用程式碼實現大體如下：

1.result = db.execute("select extdata from user where id = xxx");
2.JSONObj = JSONUtil.parse(result.get("extdata"));
3.JSONObj.put("salary",2000);
4.extdata_str = JSONObj.toJSONString();
5.db.execute("update user set extdata=extdata_str where id=xxx");

　　

這一套更新操作繁瑣且效能低，讀取操作也存在類似問題，由於沒有原生Api的支援，這一切感覺有點糟糕。

到了MySQL 5.7.8開始，MySQL開始支援JSON這種資料型別，看下官方文件的介紹：

MySQL新增加的原生JSON型別比在字串列中儲存 JSON 格式的字串相比有兩個優點：

    1.自動的資料校驗，對於JSON型別的列MySQL會校驗其合法性；

    2.提供了更方便的Api用於存取，避免了繁瑣的應用層操作。

 

看下基於MySQL 5.7.8，如何優雅的存取JSON型別中的鍵值，依然以修改extdata中的salary為例：

update user set extdata = JSON_SET(user.extdata, '$.salary',2000) where id =1;

 讀取salary的值：

select JSON_EXTRACT(user.extdata, '$.salary') from user where id =1;

 藉助JSON_SET和JSON_EXTRACT這兩個Api，極大的降低了存取的複雜度，想深入瞭解MySQL JSON用法的請參考文章最後的推薦閱讀內容。

 

說到這兒，藉助MySQL的原生JSON型別以及相關的Api存取擴充套件資料在易用性方面已經沒什麼問題了，接下來從效能角度思考下是否有待提升。 

/*找出salary等於2000的user*/
select * from user where JSON_EXTRACT(user.extdata, '$.salary') =2000;

 在我自己的pc機上，user表中共300萬條資料，執行這條SQL花費接近3秒，不談快慢，就論是否有優化空間，貼個執行計劃出來

 

面對大名鼎鼎的全表掃描如何優化呢？

 

 優化JSON查詢

按照過往的思路，我們只要設計合理的索引就能避免全表掃描，但這次面對JSON似乎有點黔驢技窮了，別擔心，大名鼎鼎的MySQL早已幫你做了既生瑜又生亮的美事，看看官方怎麼說。

  1. JSON型別列無法直接索引；

  2. 可以基於JSON建立一個生成列，然後基於生成列建立索引，從而達到對JSON型別列加索引的效果。

接著看下何謂生成列

 

生成列的值在插入資料時不需要設定，MySQL會根據生成列關聯的表示式自動計算填充，生成列的定義方式如下：

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

AS (expr)指示生成列並定義用於計算列值的表示式，可以在前面加上GENERATED ALWAYS明確的表示這是一個生成列。　　

 

迴歸到我們的場景中，分三步進行優化：

1.建立一個生成列v_salary，計算列值表示式為extdata->"$.salary"，代表提取extdata中的salary值
ALTER TABLE `user` ADD COLUMN `v_salary` DECIMAL(10,2) as (extdata->"$.salary") AFTER `extdata`;
2.針對v_salary建立索引
ALTER TABLE `user` ADD INDEX `idx_salary` (`v_salary`) ;
3.替換查詢語句中JSON_EXTRACT(user.extdata, '$.salary')為v_salary;
select * from user where v_salary =2000

select * from user where v_salary =2000，執行耗時為0.047s，這個優化效果非常顯著。

看下現在的執行計劃已經使用了索引

 

 

 

總結

任何新技術的引入一定要有一個比較全面的認識，充分理解其利弊，不能只看到其光鮮的一面，而忽略其帶來的弊端，對於弊端要有應對措施，知己知彼。

 

推薦閱讀

 https://dev.mysql.com/doc/refman/5.7/en/json.html

 https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-set

 https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

 https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

 rfc7159 (ietf.org)

相關文章