1. 背景

* 在MySQL 5.7.8中，MySQL支持由RFC 7159定义的本地JSON数据类型，它支持对JSON(JavaScript对象标记)文档中的数据进行有效访问.

* MySQL会对DML JSON数据自动验证。无效的DML JSON数据操作会产生错误.

• 优化的存储格式。存储在JSON列中的JSON文档转换为一种内部格式，允许对Json元素进行快速读取访问.

• MySQL Json类型支持通过虚拟列方式建立索引，从而增加查询性能提升.

2. Json 索引

   • 创建Json索引表 json_key [ name 为虚拟列, virtual 表明不占用磁盘空间 ]

[ GENERATED ALWAYS 与 VIRTUAL可以不写 ]

    指定获取json中的name key

mysql> CREATE TABLE json_key(
-> uid BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> data JSON NOT NULL,
-> name VARCHAR(32) GENERATED ALWAYS AS (json_extract(data, ‘$.name‘)) VIRTUAL,
-> )ENGINE=INNODB CHARSET=utf8mb4;

• 插入数据带 data中name key [ 插入数据时需要显示指定非虚拟列 ]

mysql> INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT(‘name‘, ‘tom‘, ‘sex‘, ‘male‘, ‘age‘, ‘26‘);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

• 插入数据不带 data中name key

mysql> INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT(‘sex‘, ‘female‘, ‘age‘, ‘29‘);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

• 查看json_key所有数据

mysql> select * from json_key;
±----±--------------------------------------------±------+
| uid | data | name |
±----±--------------------------------------------±------+
| 1 | {“age”: “26”, “sex”: “male”, “name”: “tom”} | “tom” |
| 2 | {“age”: “29”, “sex”: “female”} | NULL |
±----±--------------------------------------------±------+
2 rows in set (0.01 sec)

mysql原生并不支持json列中的属性索引，但是我们可以通过mysql的虚拟列间接的为json中的某些属性创建索引，原理就是为json中的属性创建虚拟列，然后通过给虚拟列建立索引，从而间接的给属性创建了索引。

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

如果需要Stored Generated Golumn的话，可能在Virtual Generated Column上建立索引更加合适，一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式

许你写的格式如下：

fieldname [ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT ]
首先我们创建一张带有虚拟列的表新表:

CREATE TABLE players (
id INT UNSIGNED NOT NULL primary key auto_increment,
player JSON NOT NULL,
vname VARCHAR(50) GENERATED ALWAYS AS (player ->> ‘$.name’) NOT NULL – name的虚拟列
);
利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段，我把它定义成不可以为空。在实际的工作中，需要结合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。

然后我们查一下这个表的列有哪些：

SHOW COLUMNS FROM players;
结果如下，会发现vname的附加信息里面显示列类型为虚拟生成列：

然后，我们写一个存储过程，向表中插入八百万条记录，写之前先执行确认开启mysql存储过程：

show variables like ‘log_bin_trust_function_creators’; – 查看是否开启存储函数
set global log_bin_trust_function_creators=1; – 开启mysql存储函数
存储过程：

delimiter $$createprocedureinser{t}_{p}layer(inma{x}_{n}umint(10))begindeclareiintdefault0;declarejso{n}_{d}atavarchar(2000)defaul{t}^{\prime }{1}^{\prime };setautocommit=0;repeatseti=i+1;setjso{n}_{d}ata=concat(concat{(}^{\prime }{"name":"yaoming{-}^{\prime },i){,}^{\prime }","age":34,"gender":"man","type":"basketBall"}^{\prime });insertintoplayers(id,player)values(null,jso{n}_{d}ata);untili=ma{x}_{n}umendrepeat;commit;end$$
调用一下存储过程，插入2000000条记录：

call insert_player(2000000);
插入完毕，总共耗时：

在添加索引之前我们先通过vname直接查询name为yaoming-990099的那条记录，总共耗时3.107s：

我们看一下这条语句的查询计划：

EXPLAIN SELECT * FROM players WHERE vname = “yaoming-990099”
结果如下：

然后我们为vname添加索引：

CREATE INDEX name_idx ON players(vname);

再次查询这条语句的执行计划：

速度已经快到飞起了！！！

我们在看一下查询计划详情：

发现已经走了索引查询！

但是需要注意的是，这种方法只能只能对json的某个单值属性或者给数组中的某一个特定位置上的元素有效，如果你想给对象数组中的所有元素的某个属性使用索引，那么最后你只能匹配数组中的所有对象的该属性或者使用like,但是使用like的话就不能使用索引，也就是说对于这种情况是不可用的