MySQL5.7版本开始支持JSON格式，在创建表时，可以指定列表的数据类型为JSON，但是如何在JSON格式上创建索引呢？？
本人做了一个简单测试。
第一步：建立一个包含JSON类型的表：
|
1
2
3
4
5
|
CREATE TABLE json_test` ( id` int (8) NOT NULL AUTO_INCREMENT, content` json NOT NULL , PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
第二步：初始化数据
|
1
2
3
|
insert into json_test(content) value( '{"name":"zhangsan","age":18}' );insert into json_test(content) value( '{"name":"lisi","age":19}' );insert into json_test(content) value( '{"name":"wangwu","age":20}' ); |
第三步：查询JSON类列的数据
select json_extract(content, '$.name' ) from json_test where json_extract(content, '$.name' )= "zhangsan" ;
通过expain分析改查询语句，发现其走全表扫描
在网上查询资料，得知如果要在JSON列上进行检索，需要对检索的key创建虚拟列，然后再虚拟列上创建索引
第四步：在content列上，对"name"建立虚拟列
ALTER TABLE json_test ADD name_virtual varchar (32) GENERATED ALWAYS AS (json_extract(content, '$.name' )) VIRTUAL;
第五步：对虚拟列创建索引
CREATE INDEX name_virtual_index ON json_test(name_virtual);
再次做查询（ 注，where条件需要使用虚拟列来进行检索，如果直接用JSON列比较，还是会走全表扫描 ）
explain select json_extract(content, '$.name' ) from json_test where name_virtual= "zhangsan" \G
总结：
其实MySQL通过一种空间换时间的做法，类似创建一个触发器，把JSON列上的数据冗余存储到虚拟列上，比较的时候通过走虚拟列的索引，再定位到实际数据。
