在我们梳理的开发规范里面,明确规定对于lob类型的使用原则只有一个,那就是尽量不要使用。但是很明显,开发同学走到了我们前面,如果你碰到开发同学使用JSON数据类型该怎么建议呢,至少在建议前我们也得了解下JSON类型的使用要领吧。

在说JSON类型之前,我们来说下在没有JSON数据类型之前我们是怎么处理一些复杂的数据映射的。

对于开发语言还是数据库技术来说,字符串处理总是很有魅力的一个特性,所以我会花更多的精力在这个上面。比如之前做了一个简单的测试。里面用到了一些看起来复杂的字符串处理函数find_in_set,substring_index等。

问题的背景是我们为一个表创建了两个列col1,col2,然后插入一些属性值。即col1里面的属性值和col2里面的属性值是对应的。或者换句话来说,col1里面存放的是key,col2存放的是value.

create table test1 ( col1 varchar(100),col2 varchar(100));

insert test1 select

'26,59,6', '1502.5,1690,2276.77' union all select

'59,33,6', '3502.1,1020,2276.77' union all select

'22,8,59', '1332.6,2900,1520.77';

写入数据之后,表里的数据分布是这样的:

mysql> select *from test1;

+---------+---------------------+

| col1    | col2                |

+---------+---------------------+

| 26,59,6 | 1502.5,1690,2276.77 |

| 59,33,6 | 3502.1,1020,2276.77 |

| 22,8,59 | 1332.6,2900,1520.77 |

+---------+---------------------+

3 rows in set (0.00 sec)

现在我们如果要做一个数据查询,把key是59的value值查出来,然后需要value值小于2000.

如果使用SQL,可能会是这样的解决方法。

mysql> select col1,col2

-> from (select *,find_in_set('59',col1) as rn from test1) k

-> where substring_index(concat(',',substring_index(col2,',',rn)),',',-1)

->  <'2000';

+---------+---------------------+

| col1    | col2                |

+---------+---------------------+

| 26,59,6 | 1502.5,1690,2276.77 |

| 22,8,59 | 1332.6,2900,1520.77 |

+---------+---------------------+

2 rows in set (0.00 sec)

当然可能你会有更好的解决方案,但是看起来似乎也不是一个很好的解决方法,比如这种设计中,如果要加一个字段,那简直就是灾难性的。

在这种模式下,使用JSON其实也是一种改进思路,当然这是在MySQL 5.7之后了。

我们创建的表为json_test,然后插入两行记录。

create table json_test ( uid int auto_increment,data json,primary key(uid))engine=innodb;

insert into json_test values (NULL,'{"name":"jeanron","mobile":"02","location":"beijing"}');

insert into json_test values (NULL,'{"name":"jianrong","mobile":"003","location":"gansu"}');

现在到了JSON发挥作用的时候了,如果要查询出数据,我们可以使用类似引用的语法"->"即可。所以我们可以把数据很方便的解析出来。

mysql>  select data->"$.name" as name,(data->"$.location") from json_test group by name;

+------------+----------------------+

| name       | (data->"$.location") |

+------------+----------------------+

| "jeanron"  | "beijing"            |

| "jianrong" | "gansu"              |

+------------+----------------------+

2 rows in set (0.00 sec)

在这种模式下,上面的第一个难题其实就完全可以使用这种方式来解决了。

在这个基础上我们更近一步,在5.7里面还有辅助的特性虚拟列和相关的索引,可以提高我们查询的效率。我们添加一个虚拟列user_name.

ALTER TABLE json_test  ADD user_name varchar(128) GENERATED ALWAYS AS(json_extract(data,'$.name')) VIRTUAL;

使用desc查看,其实可以看到user_name的属性是相对特殊的。

mysql> desc json_test;

+-----------+--------------+------+-----+---------+-------------------+

| Field     | Type         | Null | Key | Default | Extra             |

+-----------+--------------+------+-----+---------+-------------------+

| uid       | int(11)      | NO   | PRI | NULL    | auto_increment    |

| data      | json         | YES  |     | NULL    |                   |

| user_name | varchar(128) | YES  | MUL | NULL    | VIRTUAL GENERATED |

+-----------+--------------+------+-----+---------+-------------------+

3 rows in set (0.00 sec)

然后在这个基础上添加一个索引。

alter table json_test add index idx_username(user_name);

使用show create table的方式查看建表DDL可以清晰的看到是有一个辅助索引。

CREATE TABLE `json_test` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`data` json DEFAULT NULL,

`user_name` varchar(128) GENERATED ALWAYS AS (json_extract(`data`,'$.name')) VIRTUAL,

PRIMARY KEY (`uid`),

KEY `idx_username` (`user_name`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

然后我们再次查询,注意在这里的user_name使用了双引号单引号混合的方式。

mysql>  select user_name,(data->"$.location") from json_test where user_name = '"jianrong"';

+------------+----------------------+

| user_name  | (data->"$.location") |

+------------+----------------------+

| "jianrong" | "gansu"              |

+------------+----------------------+

1 row in set (0.00 sec)

如果带有疑惑,我们只有单引号是否可以,答案会让你失望。

mysql>  select user_name,(data->"$.location") from json_test where user_name = 'jianrong';

Empty set (0.00 sec)

所以不是严格意义上100%的兼容性,至少在各式统一上我们还是需要一些额外的工作。

然后来看下执行计划的情况,可以看到语句明显使用到了索引,对于后期的数据分析和处理还是大有帮助的。

mysql>  explain select user_name,(data->"$.location")from json_test where user_name = '"jeanron"';

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

| id | select_type | table     | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | json_test | NULL       | ref  | idx_username  | idx_username | 387     | const |    1 |   100.00 | NULL  |

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

在这个基础上如果做更多的分析,其实explain format=json也是一种改进方式,对于执行计划,我们可以得到属性值,通过解析的方式能够把执行计划做得更好。

JSON的新特性对于MySQL来说确实是一个不错的特性,如果数据量巨大,还是需要考虑通过空间换时间的思路来改进。如果大家了解Oracle,PostgreSQL等数据库,其实这些特性也是有的,Oracle 12c里面明确有这个特性,postgreSQL也有这个特性,还区分为json和jsonb,对于NoSQL来说,那更是它们擅长的,所以MySQL实现这个是一种辅助,绝对不是做了颠覆性的改进。

历史的车轮要前进,我们的方案只能是折中之后的方案。就好比早年的时候Oracle全面支持XML,结果到现在这类需求明显有些凉了。

JSON也不是万金油,推荐大家参考这一篇。

作者:余华