MySQL JSON字段索引支持情况调研

MySQL 从5.7.8之后开始支持了JSON的字段类型

JSON数据类型

• JSON array:["abc",10,null,true,false]
• JSON object:{"k1":"value","k2":10}
• Mixed JSON array and JSON object:[99,{"id":"HK500","cost":75.99},["hot","cold"]]{"k1":"value","k2":[10,20]}

创建索引的方式

MySQL中的JSON字段不支持直接创建索引,可通过如下方式进行创建

• 创建虚拟列(5.7.8之后支持)

虚拟列相当于间接引用了此类列的索引,定义生成了一个列,该列提取应建立索引的信息来创建索引,如下所示

-- 创建表结构
CREATE TABLE jemp ( 
c JSON, 
g INT GENERATED ALWAYS AS ( c -> "$.id" ), 
INDEX i ( g ) 
);
-- 插入数据
INSERT INTO jemp (c) VALUES
('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
-- 查询数据
SELECT c->>"$.name" AS name FROM jemp WHERE g > 2;
-- 执行计划
EXPLAIN SELECT c->>"$.name" AS name FROM jemp WHERE g > 2;

创建表之后查看设计表可以发现 虚拟列索引的创建使用了json_extract函数,以一种json path的方式来获取Json中的数据

执行后可以看到目前是已经走了索引在查询数据

• 多值索引(8.0.17之后支持)

多数索引用于为JSON数组创建索引, 如下所示:

-- 建立表
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON,
INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
);
-- 添加数据
INSERT INTO customers VALUES
(NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
(NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
(NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
(NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
(NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
-- 多值索引使用
-- MEMBER OF()
SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
-- JSON_CONTAINS()
SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
-- JSON_OVERLAPS()
SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

总结

1. 虚拟列索引主要是对Json_Object类型的数据类型,无法支持当存储的结构为JSON数组时的场景
2. 多值索引主要用于JSON_ARRAY的场景,支持MEMBER OF、JSON_CONTAINS、JSON_OVERLAPS三种语法来使用,但是多值索引需要较高的MySQL版本,在生产环境中往往不满足.
3. 两种索引主要都是针对存储的数据是JSON对象或者JSON对象数组,对于简单的JSON_ARRAY还未找到合适的方法进行支持,建议当存储数据格式是简单数组时采用varchar这种字符串类型.eg:["abc","edf"]