MySQL JSON字段索引

数据库版本MySQL 8+支持对json类型字段进行索引，在之前的版本中，可以利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。

1.虚拟字段索引

1.创建虚拟列

虚拟列语法格式：

<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

在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默认的方式

加完虚拟列的建表语句如下：

CREATE TABLE `table_name` (  
   `id` INT UNSIGNED NOT NULL,
   `business_attr` JSON NOT NULL,
   `attrs_virtual` VARCHAR(20) GENERATED ALWAYS AS (`business_attr` ->> '$.attrKey'), 
    -- 等效于 attrs_virtual` VARCHAR(20) GENERATED ALWAYS AS (json_extract(`business_attr` , '$.attrKey'))
   PRIMARY KEY (`id`)
);

2.虚拟列加索引

在刚刚加过的虚拟列上面加索引。

CREATE INDEX `index_attrs_key` ON `table_name`(`attrs_virtual`);  

3.查看执行计划

查看attrKey的值为‘1000’的记录的执行计划

explain SELECT * FROM table_name WHERE attrs_virtual='1000';

可以看到执行计划走了新建的索引。

2.json字段索引

MySQL并不支持直接为json数据类型创建索引，在MySQL8引入了间接创建的方式，可以间接指定json数据的成员或者元素作为创建索引的字段，而字段对应的数据类型我们需要手动指定。

从 MySQL 8.0.17 开始，InnoDB 支持创建多值索引，这是在存储值数组的 JSON 列上定义的二级索引，单个数据记录可以有多个索引记录。这样的索引使用关键部分定义，例如 CAST(data->’$.zipcode’ AS UNSIGNED ARRAY)。 MySQL 优化器自动使用多值索引来进行合适的查询，可以在 EXPLAIN 的输出中查看。

1.json字段的数据为json对象

添加索引

ALTER Table `table_name` ADD INDEX index_attrs_key(  
    (CAST( `business_attr` -> "$.attrKey"  as CHAR(32) ARRAY))
);

能够用到索引的查询方式

SELECT * FROM `table_name` WHERE JSON_OVERLAPS(`business_attr` -> "$.attrKey", CAST('"1000"' AS JSON ));

[p.s.]  如果是唯一匹配查询，可以使用MEMBER OF() 方法查询数组中是否存在某一值;

[p.s.]  mysql 8.024以上版本，可以使用JSON_CONTAINS() 查询数组中是否都包含某些值，交集（必须都包含）

2.json字段的数据为json数组

添加索引

ALTER Table `table_name` ADD INDEX index_attrs_arr_key(  
    (CAST( `business_attr` -> "$[*].attrArrKey" as CHAR(32) ARRAY))
);

能够用到索引的查询方式

SELECT * FROM `table_name` WHERE JSON_OVERLAPS(`business_attr` -> "$[*].attrArrKey", CAST('"1000"' AS JSON ));

mysql8.0 JSON文档

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