基于 JSON 的 MySQL 可扩展性设计

目标：实现自定义字段，字段的内容是可扩展的， 高性能查询，高扩展性（功能扩展） 。

1.比如项目管理中一个项目涉及到多个参与人，可以对项目打标签（不用多表关联的方式，采用多表关联要求一个项目对应多个相关人，是一对多的关系），将项目和标签的数据都存储在一个项目表里面，降低多表关联，提高查询性能。

2.项目又分不同类型，不同类型对应的扩展字段存储的内容不一样，比如研发类型的项目，测试类型的项目，每一个都会有一些特有的扩展内容。

扩展字段比如叫extValue,存储内容不一样。

一、通常的做法是：

查看目前mysql版本：

select version();

8.0 使用 json-partial-updates 特性来提高json的更新效率, 也就是只更新指定的json字段, 其它字段不变.而5.7版本, 会更新整个json. 生产环境强烈建议使用8.0版本。

https://cloud.tencent.com/developer/article/1843196

JSON特点 ：

1.可以使用json中的字段做join（ JSON Table Functions ）；

2.json长度不固定，不适合存储更新需求较多的场景；8.0 json性能好：8.0.17有 Multi-Valued Indexes ，就比5.7版本好很多

通常实现的的方式,（单表，TEXT存储）

extValue, TEXT类型 ：

[{"id":"xxxxx","realName":"高*","userName":"高*","userAccount":"gaolaozhuang"},{"id":"yyyyyy":"测***","userName":"测***","userAccount":"135*VA*3312"}]

优点：单表查询，支持自定义扩展内容；缺点把整个字段作为一个字符串，需要解析处理，无法支持根据JSON里面的内容为条件建立索引来查询，根据某一个参与人为条件获取该参与人所有任务列表。

多表关联的实现方式

缺点：结构固定，有新的扩展字段需求需要改代码，加新字段，或者预留extValue扩展字段方式。

Mysql JSON方式的优点：

MySQL5.7中就新增加了一个数据类型JSON， 使用mysql的json类型字段做扩展字段，可以以json串形式动态的存储任意结构的数据 ，包括list结构的数据也不必再创建子表。 代码的实体类和Dao层不必修改，其他层代码修改量也能够减少 。 没有JSON数据类型的支持，我们一样可以通过varchar类型或者text等类型来保存这一格式的数据。

JSON 类型的另一个好处是 无须预定义字段 ，字段可以无限扩展。而传统关系型数据库的列都需预先定义，想要扩展需要执行 ALTER TABLE ... ADD COLUMN ... 这样比较重的操作（或者提前预留扩展字段方式，但是预留的字段的含义无法事先确定） 。

参考Mysql8.0官网内容：

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

JSON对象除了支持字符串、整型、日期类型，JSON 内嵌的字段也支持数组类型。

字段类型处理器（TypeHandler）

MyBatis  中的  TypeHandler  类型处理器用于  JavaType  与  JdbcType  之间的转换

通过  @TableField  注解将  FastjsonTypeHandler  这个类型处理器快速注入到  mybatis  容器中

注意 ：使用字段类型处理器时，必须开启映射注解  @TableName(autoResultMap = true) 。否则插入没问题，但查询时该字段会为空

自定义类型处理器

https://note.youdao.com/ynoteshare/index.html?id=b5dcac49d70bda88bee06741493e0c6e&type=note&_time=1632899105427

自定义的  TypeHandler  类型处理器，则需继承  ListTypeHandler  接口，接口作用是用于指定  jdbc  与  java  的数据类型间对应关系处理。接口代码如下：

MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以你可以在表 UserTag 上创建 Multi-Valued Indexes

例子：

CREATE TABLE `extra_info` (

`id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`extra_object` json NULL,

`extra_list` json NULL,

`extra_array` json NULL

);

SELECT * FROM extra_info.extra_info;

DROP TABLE IF EXISTS UserLogin;

CREATE TABLE UserLogin (

userId BIGINT NOT NULL,

loginInfo JSON,

PRIMARY KEY(userId)

);

INSERT INTO `extra_info` VALUES (1, '{\"id\": 1, \"name\": \"2\"}', '[{\"id\": 1, \"name\": \"2\"}]', '[{\"id\": 1, \"name\": \"2\"}]');

SET @a = '

{

"cellphone" : "13918888888",

"wxchat" : "破产码农",

"QQ" : "82946772"

}

';

INSERT INTO UserLogin1(userId,loginInfo) VALUES (1,@a);

SET @b = '

{  

"cellphone" : "15026888888"

}

';

INSERT INTO UserLogin1(userId,loginInfo) 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,

loginInfo->>"$.QQ" qq

FROM UserLogin;

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 UserLogin1 (

userId BIGINT,

loginInfo JSON,

cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),

PRIMARY KEY(userId),

UNIQUE KEY uk_idx_cellphone(cellphone)

);

CREATE TABLE `usertag` (

`userId` bigint NOT NULL,

`userTags` json DEFAULT NULL,

PRIMARY KEY (`userId`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

ALTER   TABLE  UserTag

ADD   INDEX  idx_user_tags (( cast ((userTags-> "$" )  as   unsigned   array )));

EXPLAIN SELECT * FROM UserTag 

WHERE 10 MEMBER OF(userTags->"$");

查询用户画像为常看电影的用户，可以使用函数 MEMBER OF：

SELECT * FROM UserTag 

WHERE 10 MEMBER OF(userTags->"$");

画像为 80 后，且常看电影的用户，可以使用函数 JSON_CONTAINS：

XPLAIN   SELECT  *  FROM  UserTag 

WHERE  JSON_CONTAINS(userTags-> "$" ,  '[2,10]' )

想要查询画像为 80 后、90 后，且常看电影的用户，则可以使用函数 JSON_OVERLAP：

EXPLAIN   SELECT  *  FROM  UserTag 

WHERE  JSON_OVERLAPS(userTags-> "$" ,  '[2,3,10]' )

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

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

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

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

• https://mp.weixin.qq.com/s/MxyYM0wdffs6V-f4vLrsBA

multi-value index是functional index的一种实现，列的定义是一个虚拟列，值是从json column上取出来的数组

数组上存在相同值的话，会只存储一个到索引上。支持的类型：DECIMAL, INTEGER, DATETIME,VARCHAR/CHAR。另外index上只能有一个multi-value column。

常见的可扩展性方案：

【MySQL】对JSON数据操作

例子：

CREATE TABLE `dept` (

`id` int(11) NOT NULL,

`dept` varchar(255) DEFAULT NULL,

`json_value` json DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');

insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');

insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');

insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');

insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

一般基础查询操作

1、使用 json字段名->’$.json属性’ 进行查询条件

查询deptLeader=张五的数据，那么sql语句如下：

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';