MySQL 8.0.35中JSON数组对象的查询索引创建与优化

环境 #

• MySQL 8.0.35

场景说明 #

需要查询所有满足cancel.cancels[*].cancel_no等于"202401215050123"的数据行，并通过索引优化查询性能。测试数据示例：

INSERT INTO pay.payment (payment_no, cancel) VALUES ('2024012150200010', '{"cancels": [{"cancel_no": "202401215050123", "amount": 100}, {"cancel_no": "202401215050125", "amount": 200}]}');

已尝试操作 #

在全新数据库中执行了以下步骤：

SELECT VERSION(); -- 结果为8.0.35

CREATE DATABASE pay;

USE pay;

CREATE TABLE payment
(
    payment_no varchar(50) NOT NULL
        PRIMARY KEY,
    cancel     JSON        NULL
);

-- 此操作失败
ALTER TABLE payment
  ADD INDEX cancel_no_idx ((
    CAST(cancel->>"$.cancels[*].cancel_no" as CHAR(255) ARRAY) COLLATE utf8mb4_bin
  )) USING BTREE;

-- 此操作成功
ALTER TABLE payment
  ADD INDEX cancel_no_idx ((
    CAST(cancel->>"$.cancels[*].cancel_no" as CHAR(255) ARRAY)
  )) USING BTREE;

-- 插入测试数据
INSERT INTO pay.payment (payment_no, cancel) VALUES ('2024012150200010', '{"cancels": [{"cancel_no": "202401215050123", "amount": 100}]}');

-- 查询语句
EXPLAIN SELECT cancel FROM payment WHERE JSON_CONTAINS(cancel, '{"cancel_no": "202401215050123"}', '$.cancels');

但EXPLAIN结果显示select_type为SIMPLE，未使用创建的索引。

问题诉求 #

• 如何创建符合需求的索引，并验证查询是否使用该索引？
• 需要兼容cancels数组包含多个或0个元素的情况。
• 不局限于多值索引，只要能实现高效查询即可。
  此前曾提出类似问题，但仅得到针对cancel[0]的解决方案。

解决方案 #

方案一：正确使用多值索引 #

MySQL的多值索引需要配合MEMBER OF()或JSON_OVERLAPS()等支持多值索引的函数才能触发，JSON_CONTAINS()目前无法直接使用多值索引。

1. 重新创建多值索引
   先删除已创建的索引，再创建带排序规则的多值索引（修正之前的语法错误）：

DROP INDEX cancel_no_idx ON payment;

ALTER TABLE payment
ADD INDEX cancel_no_idx (
    CAST(JSON_EXTRACT(cancel, '$.cancels[*].cancel_no') AS CHAR(255) ARRAY) COLLATE utf8mb4_bin
) USING BTREE;

2. 修改查询语句触发索引
   使用MEMBER OF()函数匹配数组元素：

EXPLAIN SELECT cancel FROM payment 
WHERE '202401215050123' MEMBER OF(CAST(cancel->>"$.cancels[*].cancel_no" AS CHAR(255) ARRAY));

执行EXPLAIN后，若key列显示cancel_no_idx，则说明索引已生效。

方案二：拆分JSON为关联表（推荐长期方案） #

如果数据量较大，JSON查询性能始终不如关系型模型稳定，建议拆分出单独的取消记录表：

1. 创建关联表

CREATE TABLE payment_cancel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_no VARCHAR(50) NOT NULL,
    cancel_no VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (payment_no) REFERENCES payment(payment_no),
    INDEX idx_cancel_no (cancel_no)
);

2. 迁移数据并维护关联
   将原JSON中的cancels数组数据迁移到payment_cancel表，后续新增取消记录时直接插入该表。

3. 高效查询
   通过关联表快速定位：

SELECT p.cancel FROM payment p
JOIN payment_cancel pc ON p.payment_no = pc.payment_no
WHERE pc.cancel_no = '202401215050123';

此方案查询性能最优，符合关系型数据库设计规范，便于后续扩展维护。

方案三：生成列索引 #

若不想拆分表，可使用生成列将JSON中的cancel_no数组转换为字符串，再创建普通索引：

1. 添加生成列

ALTER TABLE payment ADD COLUMN cancel_no_list TEXT GENERATED ALWAYS AS (
    JSON_UNQUOTE(JSON_EXTRACT(cancel, '$.cancels[*].cancel_no'))
) STORED;

2. 创建索引

ALTER TABLE payment ADD INDEX idx_cancel_no_list (cancel_no_list(255));

3. 查询语句
   使用JSON_SEARCH()查询（数组元素较多时性能略差于多值索引）：

EXPLAIN SELECT cancel FROM payment 
WHERE JSON_SEARCH(cancel, 'one', '202401215050123', NULL, '$.cancels[*].cancel_no') IS NOT NULL;

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