Mysql+Json

引用自网站https://mp.weixin.qq.com/s/lIVSQozEgFgivy5yvOZkfg

关系型数据库的弊端，需要自定义列及列对应的类型，在后续业务的扩展中，或许需要扩展单个列的描述功能，如果能用好 JSON 数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。

JSON 类型是 MySQL 5.7 版本新增的数据类型，所以需要mysql5.7以上版本，

实战

用户登录设计

建表及插入测试数据

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

SET @a = '
{
   "cellphone" : "13918888888",
   "wxchat" : "破产码农",
   "QQ" : "82946772"
}';
INSERT INTO UserLogin VALUES (1,@a);

SET @b = '

{  
  "cellphone" : "15026888888"
}';
INSERT INTO UserLogin VALUES (2,@b);　

查询

SELECT
    userId,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;

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

　　

优化 

（当 JSON 数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的 函数索引 功能对 JSON 中的某个字段进行索引）

首先创建了一个虚拟列 cellphone，这个列是由函数 loginInfo->>"$.cellphone" 计算得到的。然后在这个虚拟列上创建一个唯一索引 idx_cellphone。这时再通过虚拟列 cellphone 进行查询

ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");

ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);
EXPLAIN 
SELECT  *  FROM UserLogin 
WHERE cellphone = '13918888888'

　　汇总创建表的时候，就完成虚拟列及函数索引的创建

CREATE TABLE User_Login (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY uk_idx_cellphone(cellphone)
);

　

用户画像设计

某些业务需要做用户画像（也就是对用户打标签），然后根据用户的标签，通过数据挖掘技术，进行相应的产品推荐。这份架构师图谱建议看看，少走弯路。　

比如：

• 在电商行业中，根据用户的穿搭喜好，推荐相应的商品；
• 在音乐行业中，根据用户喜欢的音乐风格和常听的歌手，推荐相应的歌曲；
• 在金融行业，根据用户的风险喜好和投资经验，推荐相应的理财产品。

在这，我强烈推荐你用 JSON 类型在数据库中存储用户画像信息，并结合 JSON 数组类型和多值索引的特点进行高效查询。假设有张画像定义表：

CREATE TABLE Tags (
    tagId bigint auto_increment,
    tagName varchar(255) NOT NULL,
    primary key(tagId)
);

INSERT INTO `Tags` VALUES (1, '70后');
INSERT INTO `Tags` VALUES (2, '80后');
INSERT INTO `Tags` VALUES (3, '90后');
INSERT INTO `Tags` VALUES (4, '00后');
INSERT INTO `Tags` VALUES (5, '爱运动');
INSERT INTO `Tags` VALUES (6, '高学历');
INSERT INTO `Tags` VALUES (7, '小资');
INSERT INTO `Tags` VALUES (8, '有房 ');
INSERT INTO `Tags` VALUES (9, '有车');
INSERT INTO `Tags` VALUES (10, '常看电影');
INSERT INTO `Tags` VALUES (11, '爱网购');
INSERT INTO `Tags` VALUES (12, '爱外卖');

创建用户

如果使用传统的存储方式，我们一般情况是使用分隔符存储,那样的话分隔符是一种约束，同时也不利于后续的搜索查找，因为查询某一个标签的人员只能使用LIke，不能走索引，所以使用json数组的话，没有分隔符的限制，后续还能创建索引，非常方便，

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]');

　　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)));
SELECT * FROM UserTag 
WHERE 10 MEMBER OF(userTags->"$");
SELECT * FROM UserTag 
WHERE JSON_CONTAINS(userTags->"$", '[2,10]')；
SELECT * FROM UserTag 
WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');

　　

 

 

重点内容

①使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；

②JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；

③不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；

④JSON 数据类型推荐使用在不经常更新的静态数据存储