文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

2024-04-02 19:55

关注

多值索引简介

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 UNSIGNED array ) ) );

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

测试案例

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)) );

SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$.key' );

SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$.key',
	CAST( '[94582]' AS JSON ));

SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$.key',
	CAST( '[94477]' AS JSON ));

查看执行计划发现可以使用到索引:

MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

如果需要给字符类型创建多值索引,则必须是utf8mb4字符集且排序规则是utf8mb4_0900_as_cs,否则报错该版本不支持: 

如果要为binary二进制字符串创建多值索引的话,则排序规则必须是binary,否则报错不支持。

MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

修改排序规则后可成功添加索引:

MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

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)) );

SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$[*].key' );

SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$[*].key',
	CAST( '[94582, 94507]' AS JSON ));

SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$[*].key',
	CAST( '[94477, 94582]' AS JSON ));

查看执行计划发现可以使用到索引:

MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

在组合索引中创建多值索引

语法

语法跟普通组合索引差不多,同样也遵守最左匹配原则:

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 )) );

SELECT
	* 
FROM
	customers 
WHERE
	94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;

查看执行计划发现可以使用到索引:

MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

多值索引的局限

目前只有MEMBER OF、 JSON_CONTAINS()、 JSON_OVERLAB()三种语法可以使用到多值索引。

( CAST( custinfo -> '$.key' AS UNSIGNED array)),语法中的array是可以不加的,之所以要强制加是因为如果不加就不是数组结构,不是数组结构就没法直接使用上述三个语法,需要通过JSON_ARRAY()等方法转换后才能使用,这样就会导致索引失效!因此不管需要加索引的字段是单个值的字段还是数组字段,都要加上array关键字。

这句话的意思是该操作使用 ALGORITHM=COPY,即通过新建一张表结构,再将数据复制过去的方式实现索引的创建。因此该过程中不允许DML操作。

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

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

其他任何字符集或排序规则都不能创建多值索引,创建时会报错当前版本不支持。

应用场景

多值索引的应用场景非常广泛!有了他之后很多关联关系表都可以不用了!举个简单的例子:用户标签,很多场景下会给用户贴上各种标签,比如1高 2富 3帅,为了后续的更高效的做统计或筛选查询,我们不能直接将这个标签作为一个字段存储,因为没有索引查询效率不高,所以很多时候会使用一张关联关系表来存储用户-标签的关系。但是现在有了多值索引,我们就可以将标签作为一个字段存储了!

这只是其中一个小场景,类似的场景非常多,用户可以换成任何事物,标签也可以换成其他任何属性,只要是这个事物存在多种属性值就行,存在一个多对多关系,那么在没有需要这个属性与其他表做表关联的请况下),都可以使用多值索引实现!多值索引不支持表关联,因此如果需要用该字段在做表关联的话就不合适了。

关于JSON函数,还有其他的文章介绍:

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。 

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯