概述

mysql 从 8.0.17 开始，InnoDB 有了一个叫做多值索引的功能，可以为 json 字段建立索引，从而提高查询效率。详见：https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

假设有一张表，如下所示，tag_ids 字段是个 Json 数组类型的标签：

CREATE TABLE `topic_tag_relate`
(
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `topic_id`    BIGINT UNSIGNED NOT NULL COMMENT '主题 ID',
  `tag_ids`     JSON NOT NULL COMMENT '标签id示例，[1,2,3]，默认json: []',
  `update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_TopicID` (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主题标签关系表';

根据资料，尝试针对 tag_ids 字段创建索引，CAST 函数是个类型转换的功能，CAST(tag_ids AS UNSIGNED ARRAY) 的作用是将 tag_ids 转成无符号的数组类型：

ALTER TABLE topic_tag_relate
    ADD INDEX zips( (CAST(tag_ids AS UNSIGNED ARRAY)) );

如果我们的数字是嵌套在 json 的某个 key 里面，就可以

ALTER TABLE topic_tag_relate
    ADD INDEX zips( (CAST(tag_ids->'$.some_array_field' AS UNSIGNED ARRAY)) );

对于添加索引前后的查询效果

正常查询代码，其中 CAST 是将 [1] 转成 Json 对象：

-- 查询包含 1 的记录，如果要同时包含1和2的话，可以JSON_CONTAINS(tag_ids, CAST('[1,2]' AS JSON))
SELECT * FROM topic_tag_relate WHERE JSON_CONTAINS(tag_ids, CAST('[1]' AS JSON));

添加索引前的 explain，ALL 全表扫描：

explain SELECT * FROM topic_tag_relate WHERE JSON_CONTAINS(tag_ids, CAST('[1]' AS JSON));

添加索引后，使用了索引

explain SELECT * FROM topic_tag_relate WHERE JSON_CONTAINS(tag_ids, CAST('[1]' AS JSON));

多值索引背后的实现原理和性能对比

多值索引实现原理

普通索引和多值索引的对比图，主要区别在于普通索引是一对一的，而多值索引是多对一的。

比如我们现在有一批数据如下

{
    "id": 1, 
    "topic_id": 1,
    "tag_ids": [10001,10002,10003],
    "update_time":1709032134080
}

将为 tag_ids 数组生成一个多值索引。这意味着对于给定的JSON文档，将创建3个索引记录(10001,10002,10003)，这3个索引记录都指向相同的数据记录id:1。

具体性能对比

这里参考了一篇国外文章的性能对比，原文链接在后面。

意思是不建立多值索引的时候，查询会检索接近10万行，花费时间150ms，建立多值索引后，命中索引只需要一行，花费时间1ms。

PS：这里示例中的数字中的数字是用的邮编，其实区分度很快，效率相对会比较高。

参考文章

实现原理：https://www.mydbops.com/blog/improving-query-performance-with-multi-valued-indexing-in-mysql-80

mysql 官方文档：https://dev.mysql.com/doc/refma