电子发烧友App
?
关系型的结构化存储存在一定的弊端，因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中，或许需要扩展单个列的描述功能，这时，如果能用好 JSON 数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。当然，很多同学在用 JSON 数据类型时会遇到各种各样的问题，其中最容易犯的误区就是将类型 JSON 简单理解成字符串类型。但当你看完这篇文章后，会真正认识到 JSON 数据类型的威力，从而在实际工作中更好地存储非结构化的数据。JSON 数据类型
JSON（JavaScript Object Notation）主要用于互联网应用服务之间的数据交换。MySQL 支持RFC 7159定义的 JSON 规范，主要有 JSON 对象 和 JSON 数组 两种类型。下面就是 JSON 对象，主要用来存储图片的相关信息：
{ ?"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" ???} ?]
业务表结构设计实战
用户登录设计
在数据库中，JSON 类型比较适合存储一些修改较少、相对静态的数据，比如用户登录信息的存储如下：DROP?TABLE?IF?EXISTS?UserLogin; CREATE?TABLE?UserLogin?( ????userId?BIGINT?NOT?NULL, ????loginInfo?JSON, ????PRIMARY?KEY(userId) );
由于当前业务的登录方式越来越多样化，如同一账户支持手机、微信、QQ 账号登录，所以这里可以用 JSON 类型存储登录的信息。接着，插入下面的数据：SET?@a?=?' { ???"cellphone"?:?"13918888888", ???"wxchat"?:?"破产码农", ???"QQ"?:?"82946772" } '; INSERT?INTO?UserLogin?VALUES?(1,@a); SET?@b?=?' {?? ??"cellphone"?:?"15026888888" } '; INSERT?INTO?UserLogin?VALUES?(2,@b);
从上面的例子中可以看到，用户 1 登录有三种方式：手机验证码登录、微信登录、QQ 登录，而用户 2 只有手机验证码登录。而如果不采用 JSON 数据类型，就要用下面的方式建表：SELECT ????userId, ????JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone"))?cellphone, ????JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat"))?wxchat FROM?UserLogin; +--------+-------------+--------------+ |?userId?|?cellphone???|?wxchat???????| +--------+-------------+--------------+ |??????1?|?13918888888?|?破产码农?????| |??????2?|?15026888888?|?NULL?????????| +--------+-------------+--------------+ 2?rows?in?set?(0.01?sec)
当然了，每次写 JSON_EXTRACT、JSON_UNQUOTE 非常麻烦，MySQL 还提供了 ->> 表达式，和上述 SQL 效果完全一样：SELECT? ????userId, ????loginInfo->>"$.cellphone"?cellphone, ????loginInfo->>"$.wxchat"?wxchat FROM?UserLogin;
当 JSON 数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的 函数索引 功能对 JSON 中的某个字段进行索引。比如在上面的用户登录示例中，假设用户必须绑定唯一手机号，且希望未来能用手机号码进行用户检索时，可以创建下面的索引：ALTER?TABLE?UserLogin?ADD?COLUMN?cellphone?VARCHAR(255)?AS?(loginInfo->>"$.cellphone"); ALTER?TABLE?UserLogin?ADD?UNIQUE?INDEX?idx_cellphone(cellphone);
上述 SQL 首先创建了一个虚拟列 cellphone，这个列是由函数 loginInfo->>"$.cellphone" 计算得到的。然后在这个虚拟列上创建一个唯一索引 idx_cellphone。这时再通过虚拟列 cellphone 进行查询，就可以看到优化器会使用到新创建的 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)
当然，我们可以在一开始创建表的时候，就完成虚拟列及函数索引的创建。如下表创建的列 cellphone 对应的就是 JSON 中的内容，是个虚拟列；uk_idx_cellphone 就是在虚拟列 cellphone 上所创建的索引。CREATE?TABLE?UserLogin?( ????userId?BIGINT, ????loginInfo?JSON, ????cellphone?VARCHAR(255)?AS?(loginInfo->>"$.cellphone"), ????PRIMARY?KEY(userId), ????UNIQUE?KEY?uk_idx_cellphone(cellphone) );
用户画像设计
某些业务需要做用户画像（也就是对用户打标签），然后根据用户的标签，通过数据挖掘技术，进行相应的产品推荐。这份架构师图谱建议看看，少走弯路。比如：- 在电商行业中，根据用户的穿搭喜好，推荐相应的商品；
- 在音乐行业中，根据用户喜欢的音乐风格和常听的歌手，推荐相应的歌曲；
- 在金融行业，根据用户的风险喜好和投资经验，推荐相应的理财产品。
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?|?高学历???????| |?????7?|?小资?????????| |?????8?|?有房?????????| |?????9?|?有车?????????| |????10?|?常看电影?????| |????11?|?爱网购???????| |????12?|?爱外卖???????| +-------+--------------+
可以看到，表 Tags 是一张画像定义表，用于描述当前定义有多少个标签，接着给每个用户打标签，比如用户 David，他的标签是 80 后、高学历、小资、有房、常看电影；用户 Tom，90 后、常看电影、爱外卖。若不用 JSON 数据类型进行标签存储，通常会将用户标签通过字符串，加上分割符的方式，在一个字段中存取用户所有的标签：+-------+---------------------------------------+ |用户????|标签???????????????????????????????????| +-------+---------------------------------------+ |David ?|80后?；?高学历?；?小资?；?有房?；常看电影???| |Tom ???|90后?；常看电影?；?爱外卖?????????????????| +-------+---------------------------------------
这样做的缺点是：不好搜索特定画像的用户，另外分隔符也是一种自我约定，在数据库中其实可以任意存储其他数据，最终产生脏数据。用 JSON 数据类型就能很好解决这个问题：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 存储的标签就是表 Tags 已定义的那些标签值，只是使用 JSON 数组类型进行存储。另外，MySQL 系列面试题和答案全部整理好了，微信搜索Java技术栈，在后台发送：面试，可以在线阅读。MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以你可以在表 UserTag 上创建 Multi-Valued Indexes：ALTER?TABLE?UserTag ADD?INDEX?idx_user_tags?((cast((userTags->"$")?as?unsigned?array)));
如果想要查询用户画像为常看电影的用户，可以使用函数 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 后，且常看电影的用户，可以使用函数 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 后，且常看电影的用户，则可以使用函数 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)
总结
JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。最后，我总结下今天的重点内容：
- 使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；
- JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；
- 不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
- JSON 数据类型推荐使用在不经常更新的静态数据存储。
来源：https://blog.csdn.net/java_pfx/article/details/116594654
?
?
-End-
?
?
审核编辑 ：李倩
?
工商网监
评论