当前位置:   article > 正文

MySQL中的数据类型JSON_mysql json反序列化

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形式的键值对,形如:

1

2

3

4

5

6

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

2

3

4

5

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为例:

1

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

 读取salary的值:

1

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

 借助JSON_SET和JSON_EXTRACT这两个Api,极大的降低了存取的复杂度,想深入了解MySQL JSON用法的请参考文章最后的推荐阅读内容。

说到这儿,借助MySQL的原生JSON类型以及相关的Api存取扩展数据在易用性方面已经没什么问题了,接下来从性能角度思考下是否有待提升。 

1

2

/*找出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会根据生成列关联的表达式自动计算填充,生成列的定义方式如下:

1

2

3

4

col_name data_type [GENERATED ALWAYS] AS (expr)

  [VIRTUAL | STORED] [NOT NULL | NULL]

  [UNIQUE [KEY]] [[PRIMARY] KEY]

  [COMMENT 'string']

AS (expr)指示生成列并定义用于计算列值的表达式,可以在前面加上GENERATED ALWAYS明确的表示这是一个生成列。  

回归到我们的场景中,分三步进行优化:

1

2

3

4

5

6

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,这个优化效果非常显著。

看下现在的执行计划已经使用了索引


总结

任何新技术的引入一定要有一个比较全面的认识,充分理解其利弊,不能只看到其光鲜的一面,而忽略其带来的弊端,对于弊端要有应对措施,知己知彼。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/不正经/article/detail/158062
推荐阅读
相关标签
  

闽ICP备14008679号