MySQL中MEMBER OF查询过慢，求JSON数组替代查询方案

问题背景 #

系统需接收JSON数组作为筛选参数匹配记录，原使用MEMBER OF的查询语句性能极差：

SELECT ai.ID 
FROM association_internal ai 
WHERE ai.source_object_record_id MEMBER OF('[57928,57927]');

该查询耗时380ms，而等价的IN查询仅需11ms。且MEMBER OF的性能会随数组元素数量增加持续下降（最多可达100+元素），无法满足企业级场景需求。由于IN无法直接接收JSON数组参数，且不想采用动态SQL，需寻求无需MEMBER OF、可直接传入JSON数组且性能接近IN的查询方案。

可行解决方案 #

1. 利用JSON_TABLE转换JSON数组为行集（推荐） #

JSON_TABLE可将JSON数组拆分为结构化的临时行数据，再通过JOIN或IN子查询关联主表，该方式能充分利用source_object_record_id字段上的索引，性能与IN查询接近。

MySQL 8.0+ 示例

-- JOIN 写法
SELECT ai.ID
FROM association_internal ai
JOIN JSON_TABLE(
    '[57928,57927]',
    '$[*]' COLUMNS(record_id INT PATH '$')
) AS jt ON ai.source_object_record_id = jt.record_id;

-- IN 子查询写法
SELECT ai.ID
FROM association_internal ai
WHERE ai.source_object_record_id IN (
    SELECT record_id
    FROM JSON_TABLE(
        '[57928,57927]',
        '$[*]' COLUMNS(record_id INT PATH '$')
    ) AS jt
);

Oracle 12c+ 示例

SELECT ai.ID
FROM association_internal ai
WHERE ai.source_object_record_id IN (
    SELECT record_id
    FROM JSON_TABLE(
        '[57928,57927]',
        '$[*]' COLUMNS(record_id NUMBER PATH '$')
    )
);

2. PostgreSQL 专属方案 #

使用json_array_elements_text拆分JSON数组并转换为数值类型，再配合IN查询：

SELECT ai.ID
FROM association_internal ai
WHERE ai.source_object_record_id IN (
    SELECT json_array_elements_text('[57928,57927]')::INT
);

3. 索引优化前提 #

确保association_internal表的source_object_record_id字段已创建索引，以最大化查询性能：

CREATE INDEX idx_assoc_source_record_id ON association_internal(source_object_record_id);

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