MySQL为JSON字段创建索引(Multi-Valued Indexes 多值索引)
从MySQL 8.0.17开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录。跟普通索引一样,也可以在`EXPLAIN`中查看到。
版权说明: 本文由博主keep丶原创,转载请保留该说明并置于顶部。
原文地址: https://blog.csdn.net/qq_38688267/article/details/119383103
环境说明: 本文测试用的MySQL版本为8.0.25,截至2021.8.5,MySQL最新版本为8.0.26,这两个版本在多值索引相关内容方面没有改动。
多值索引简介
从MySQL 8.0.17 开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录。此类索引特定的语法定义:CAST(expression AS type ARRAY)
,例如CAST(data->'$.zipcode' AS UNSIGNED ARRAY)
。 跟普通索引一样,也可以在EXPLAIN
中查看到。
创建多值索引
跟其他索引一样,多值索引可以在建表时添加,也可以通过ALTER TABLE
或者CREATE INDEX
创建。
JSON对象字段索引
语法
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS [TYPE] [array] ) ) );
说明(2023.5.18新增)
注意:这里在CAST
语法外面有两层单括号!,如果少写一个会报错!
支持创建唯一索引:ALTER TABLE customers ADD UNIQUE INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );
在创建索引时,需要保证没有重复数据,否则会报错。同样,创建唯一索引之后,如果插入重复数据也会报错。
CAST ... AS [TYPE] [ARRAY]
,ARRAY
表示转换成数组;[TYPE]
的取值为:DATE,DATEYIME
,DECIMAL,DOUBLE
,FLOAT,SIGNED
,TIME,UNSIGNED,YEAR
;
当单独使用CAST()
方法作类型转换时,还可以转换为CHAR,NCHAR,BINARY
。截至2023.5.18最新版本8.0.33多值索引还不支持使用CHAR
和BINARY
类型。
测试案例
PS:文中的案例是参考官方文档中的案例,只是作为测试,所以在命名等方面并不怎么规范,实际开发过程中要严格遵守公司团队的开发规范,不要偷懒!
DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON NOT NULL
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
( NULL, NOW(), '{"key":94582,"value":"asdf"}' ),
( NULL, NOW(), '{"key":94568,"value":"gjgasdasdf"}' ),
( NULL, NOW(), '{"key":94477,"value":"ghasdfsdf"}' ),
( NULL, NOW(), '{"key":94536,"value":"hagsdfgdf"}' ),
( NULL, NOW(), '{"key":94507,"value":"wasfgjdf"}' );
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
*
FROM
customers
WHERE
94507 MEMBER OF ( custinfo -> '$.key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_CONTAINS(
custinfo -> '$.key',
CAST( '[94582]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_OVERLAPS (
custinfo -> '$.key',
CAST( '[94477]' AS JSON ));
- 查看执行计划发现可以使用到索引:
- 不支持
CHAR
和BINARY
类型
JSON数组对象索引
语法
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array ) ) );
注意:这里在CAST
语法外面有两层单括号!,如果少写一个会报错!
测试案例
DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON NOT NULL
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
( NULL, NOW(), '[{"key":94582},{"key":94536}]'),
( NULL, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
( NULL, NOW(), '[{"key":94477},{"key":94507}]'),
( NULL, NOW(), '[{"key":94536}]'),
( NULL, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
*
FROM
customers
WHERE
94507 MEMBER OF ( custinfo -> '$[*].key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_CONTAINS(
custinfo -> '$[*].key',
CAST( '[94582, 94507]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_OVERLAPS (
custinfo -> '$[*].key',
CAST( '[94477, 94582]' AS JSON ));
- 查看执行计划发现可以使用到索引:
在组合索引中创建多值索引
语法
语法跟普通组合索引差不多,同样也遵守最左匹配原则:
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified
( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), modified );
注意:这里在CAST
语法外面需要使用小括号括起来!
测试案例
DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
age tinyint(4) not null,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON NOT NULL
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
( NULL, 21, NOW(), '[{"key":94582},{"key":94536}]'),
( NULL, 22, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
( NULL, 23, NOW(), '[{"key":94477},{"key":94507}]'),
( NULL, 24, NOW(), '[{"key":94536}]'),
( NULL, 25, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
alter table customers DROP INDEX idx_age_custinfo$list_modified ;
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )),modified );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ((CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), age,modified );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age,modified, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )) );
/*测试 MEMBER OF 语法*/
SELECT
*
FROM
customers
WHERE
94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;
- 查看执行计划发现可以使用到索引:
多值索引的局限
-
一个多值索引只允许包含一个属性的值
-
该索引目前只支持三个语法
目前只有MEMBER OF
、JSON_CONTAINS()
、JSON_OVERLAB()
三种语法可以使用到多值索引。 -
截至2023.5.18最新版本8.0.33还不支持
CHAR
和BINARY
类型作为索引列。 -
该索引不支持用于表关联
-
不能结合前缀索引
-
不支持排序
-
不支持在线创建多值索引
这句话的意思是该操作使用ALGORITHM=COPY
,即通过新建一张表结构,再将数据复制过去的方式实现索引的创建。因此该过程中不允许DML操作。 -
官方文档中说明:多值索引对字符集类型字段有明确的要求:
binary
字符集的排序规则必须是binary
utf8mb4
字符集的排序规则必须是utf8mb4_0900_as_cs
- 但测试了多个字符集和排序规则,并没有因为字符集和排序规则的问题报错,索引也能正常使用,很奇怪。
-
多值索引的每条记录的最大值数取决于单个undo日志页上可以存储的数据量,即 65221 字节(64K 减去 315 字节的开销),这意味着最大总数键值的长度也是 65221 字节。
应用场景
多值索引的应用场景非常广泛!有了他之后很多关联关系表都可以不用了!举个简单的例子:用户标签,很多场景下会给用户贴上各种标签,比如1高 2富 3帅
,为了后续的更高效的做统计或筛选查询,我们不能直接将这个标签
作为一个字段存储,因为没有索引查询效率不高,所以很多时候会使用一张关联关系表来存储用户-标签的关系。但是现在有了多值索引,我们就可以将标签作为一个字段存储了!
这只是其中一个小场景,类似的场景非常多,用户可以换成任何事物,标签也可以换成其他任何属性,只要是这个事物存在多种属性值就行,存在一个多对多关系,那么在没有需要这个属性与其他表做表关联的请况下),都可以使用多值索引实现!多值索引不支持表关联,因此如果需要用该字段在做表关联的话就不合适了。
关于JSON函数,还有其他的博客介绍:
更多推荐
所有评论(0)