MySQL JSON单字段和多字段（集合）索引创建和使用

位置：首页>文章>详情分类：教程分享阅读(0) 2024-04-10 11:03:21

MySQL，从大概5.7版本开始支持json数据类型，但是5.7的json查询不支持创建索引，在MySQL 8.0.17 后支持了索引，但是创建和查询使用索引与常规索引不同，一般的数据库工具如navcate没法看到

环境

确保当前MySQL版本大于8.0.17 低于这个版本JSON多值索引无法创建无需折腾，不支持。单字段（属性）通过虚拟列也至少需要MySQL5.7+

MySQL json索引创建

JSON 数组（多值索引，索引值为数组）

MySQL 表字段为json，json字段内容是个json数组，MySQL version 8.0.17 +

json数组字段，也就是表中存的数据类型为json，数据就是一个数组，如

DROP TABLE IF EXISTS `student`;
CREATE TABLE student(
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    book_ids json NOT NULL comment '测试json字段'
);

插入测试数据

INSERT INTO student (book_ids) VALUES ('[1,2,3]');
INSERT INTO student (book_ids) VALUES ('[2,3,5]');
INSERT INTO student (book_ids) VALUES ('[6,9,10]');
INSERT INTO student (book_ids) VALUES ('[-1,20]');

未添加索引，查询语句尝试：

观察，可以看到此时json数据查询并未走索引。接下来创建json数组的索引

ALTER Table `student` 
ADD INDEX idx_bookIds
(
    (CAST( `book_ids` -> "$[*]"  as CHAR(32)  ARRAY))
);

再次查询和检查索引情况

此时可以看到查询已经走索引了，上方使用到了JSON_CONTAINS 函数

提示：

目前MySQL 8 只支持MEMBER OF，JSON_CONTAINS,JSON_OVERLAB 三个函数可以使用到多值索引。

索引不支持用于表关联

不能结合前缀索引

不支持排序

字符集utf8mb4字符集的排序规则必须是utf8mb4_0900_as_cs

binary字符集的排序规则必须是binary

三个函数使用参考：
select * from student where JSON_CONTAINS(book_ids ->'$[*]','-1');

select * from student where -1 MEMBER OF(book_ids ->'$[*]');

select * from student where JSON_OVERLAPS(book_ids ->'$[*]',CAST('[1,-1]' AS JSON));
特别注意，JSON_CONTAINS(book_ids ->'$[*]','-1');如果参数不是数据库中的字段的话，一定要加引号，就算是整型也得加

对于已知值得情况下，使用MEMBER OF 效率高于JSON_CONTAINS JSON_CONTAINS第二个参数可以是指定值或者数据库中字段由于需要解析所以效率比MEMBER OF略低

JSON_OVERLAPS 函数解释参考：MySQL JSON_OVERLAPS() 函数使用指南 (sjkjc.com)

创建表时候添加json数组索引参考：

DROP TABLE IF EXISTS `student`;
CREATE TABLE student(
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        book_ids json NOT NULL comment '测试json字段',
        index idx_json_tenantIds((CAST(`book_ids` -> "$[*]" AS CHAR(32) ARRAY)))
);

注意！

需要注意，查询语句必须是JSON_CONTAINS(book_ids -> '$[*]','-1') 如果是JSON_CONTAINS(book_ids, '-1') 同样不会走索引，参考

创建多值索引语法：

ALTER TABLE your_table_name ADD INDEX idx_json_your_index_name( ( CAST( your_table_json_column_name -> '$.json_key' AS [TYPE] [array] ) ) );

注意意：这里在CAST语法外面有两层单括号！，如果少写一个会报错！

[TYPE]的取值为：DATE,DATEYIME,DECIMAL,DOUBLE,FLOAT,SIGNED,TIME,UNSIGNED,YEAR

同时支持唯一索引：

ALTER TABLE your_table_name ADD UNIQUE INDEX idx_json_your_index_name( ( CAST( your_table_json_column_name -> '$.json_key' AS [TYPE] [array] ) ) );

提示：在创建唯一索引时，需要保证没有重复数据，否则会报错。同样，创建唯一索引之后，如果插入重复数据也会报错。

可以看到数据虽然同样能查询出来，但是没有走索引

JSON对象单字段/属性索引

MySQL 表字段为json类型，json字段存储为json对象的内容，通过虚拟列+对虚拟列创建普通索引实现 MySQL version 5.7+

示例表设计和数据：

DROP TABLE IF EXISTS `student`;
CREATE TABLE student(
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    book json NOT NULL comment '测试json字段'
);

插入测试数据

INSERT INTO student (`book`) VALUES ('{"id":1,"name":"书1"}');
INSERT INTO student (`book`) VALUES ('{"id":2,"name":"书2"}');
INSERT INTO student (`book`) VALUES ('{"id":3,"name":"书3"}');
INSERT INTO student (`book`) VALUES ('{"id":4,"name":"书4"}');

未添加索引，查询书id大于2的数据尝试：

EXPLAIN
SELECT * FROM student WHERE book->'$.id' > 2;

观察，可以看到此时json数据查询并未走索引。接下来创建json对象的属性索引

ALTER TABLE `student` 
    ADD COLUMN `book_id` BIGINT 
        GENERATED ALWAYS AS (json_extract(`book`, '$.id')) VIRTUAL, 
ADD INDEX idx_json_bookId(`book_id`);