MySQL JSON数组列匹配指定国家ID的查询及性能优化咨询

问题背景 #

现有MySQL表TEST_TABLE，包含ID（自增主键）和COUNTRIES_IDS（JSON类型，存储国家ID数组）字段，数据量约134万行。需求如下：

1. 查询所有包含国家ID 15的行
2. 优化查询性能，避免全表扫描

1. 查询包含国家ID 15的行 #

使用MySQL内置的JSON_CONTAINS函数即可实现，该函数用于检查JSON文档中是否包含指定值：

SELECT ID, COUNTRIES_IDS
FROM TEST_TABLE
WHERE JSON_CONTAINS(COUNTRIES_IDS, '15', '$');

• 参数说明：
  • COUNTRIES_IDS：目标JSON列
  • '15'：要查找的国家ID（需用字符串形式匹配JSON中的数字类型）
  • '$'：指定JSON路径，$代表整个JSON文档（此处即整个数组）

若使用MySQL 8.0.14及以上版本，也可以用更简洁的MEMBER OF操作符：

SELECT ID, COUNTRIES_IDS
FROM TEST_TABLE
WHERE 15 MEMBER OF(COUNTRIES_IDS);

2. 查询优化方案 #

方案一：给JSON数组添加索引（MySQL 8.0.14+支持） #

MySQL 8.0.14及以上版本支持对JSON数组创建索引，直接针对数组元素建立索引后，查询可避免全表扫描：

-- 创建数组索引
CREATE INDEX idx_countries_ids_array ON TEST_TABLE((CAST(COUNTRIES_IDS AS UNSIGNED ARRAY)));

创建索引后，使用MEMBER OF或JSON_CONTAINS的查询会自动利用该索引，大幅提升查询速度。

方案二：重构表结构（最优范式化方案） #

用JSON数组存储多关联ID不符合关系数据库范式设计，大数据量下查询、维护的性能和灵活性都较差，更优方案是使用多对多中间表：

1. 创建中间表

CREATE TABLE TEST_TABLE_COUNTRIES (
    test_table_id INT(11) NOT NULL,
    country_id INT(11) NOT NULL,
    -- 联合主键避免重复关联
    PRIMARY KEY(test_table_id, country_id),
    -- 外键关联主表和国家表（假设国家表为COUNTRIES）
    FOREIGN KEY(test_table_id) REFERENCES TEST_TABLE(ID),
    FOREIGN KEY(country_id) REFERENCES COUNTRIES(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

2. 将原JSON数组中的数据拆分插入中间表
   例如原表ID=1的行，需插入8条记录：(1,41)、(1,9)、(1,38)...以此类推。
3. 查询包含国家ID 15的行

-- 如需返回JSON数组格式结果，可使用JSON_ARRAYAGG聚合
SELECT t.ID, JSON_ARRAYAGG(tc.country_id) AS COUNTRIES_IDS
FROM TEST_TABLE t
JOIN TEST_TABLE_COUNTRIES tc ON t.ID = tc.test_table_id
WHERE tc.country_id = 15
GROUP BY t.ID;

这种方案的优势：

• 利用中间表的联合主键索引，查询性能远优于JSON列
• 便于单独添加/删除某个国家ID关联，无需修改整个JSON数组
• 支持更复杂的关联查询（如统计每个国家关联的记录数）

内容的提问来源于stack exchange，提问作者Taha Sami