MySQL关联查询执行耗时过长，寻求优化方案

当前基于状态和json_contains关联两张表的查询功能正常，但执行耗时过长，已添加必要索引，寻求更高效的查询方案。

原SQL语句 #

SELECT
    ca.id AS customer_application_id,
    ca.partner_id AS application_partner_id,
    ca.seller_id AS application_seller_id,
    ca.status AS application_status,
    ca.overall_status AS application_overall_status,
    lc.id AS customer_id,
    lc.seller_id,
    lc.pre_registration_id,
    lc.status AS customer_status,
    lc.overall_status AS customer_overall_status,
    lc.deleted_at AS customer_deleted_at
FROM
    customer_applications ca
LEFT JOIN
    (SELECT
        c.id,
        c.pre_registration_id,
        c.status,
        c.seller_id,
        c.overall_status,
        c.deleted_at
    FROM
        customers c
    WHERE
        c.deleted_at IS NULL AND
         c.status NOT IN (30, 40) AND 
        c.id = (
            SELECT MAX(csub.id)
            FROM customers csub
            WHERE csub.pre_registration_id = c.pre_registration_id
            AND csub.deleted_at IS NULL
            AND csub.status NOT IN (30, 40)
        )) lc ON ca.id = lc.pre_registration_id
WHERE
1 = 1
    AND (ca.seller_id IN (2, 4) 
        OR lc.seller_id IN (2, 4)
    )
    AND ca.partner_id = 1
    AND ca.mode = 10
    AND (lc.deleted_at IS NULL OR ca.customer_id IS NULL) 
    AND (lc.status NOT IN (30, 40) OR ca.customer_id IS NULL)
    AND (ca.is_reopen = 1 
       OR (json_contains(CAST(ca.`data` AS JSON), 'true', '$.\"set_the_basics_finished\"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$.\"mcc_and_turnover_finished\"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$.\"card_rates_and_fees_finished\"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$.\"business_type_finished\"')))
ORDER BY
    ca.created_at DESC,
    lc.pre_registration_id DESC;

优化方案 #

1. 替换关联子查询为窗口函数 #

原customers子查询用关联子查询获取最大id，改用窗口函数只需扫描一次表，效率更高：

SELECT
    c.id,
    c.pre_registration_id,
    c.status,
    c.seller_id,
    c.overall_status,
    c.deleted_at
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY pre_registration_id ORDER BY id DESC) AS rn
    FROM customers
    WHERE deleted_at IS NULL AND status NOT IN (30, 40)
) c
WHERE c.rn = 1

2. 优化JSON字段查询逻辑 #

多次对ca.data做CAST和json_contains会产生大量解析开销，可按以下方式优化：

• 优先考虑将JSON中的四个finished字段提取为独立布尔列，直接用字段过滤，彻底规避JSON解析成本。
• 若无法修改表结构，改用JSON_EXTRACT减少重复解析，同时确保ca.data本身是JSON类型，避免强制转换：

AND (
    ca.is_reopen = 1 
    OR (
        JSON_EXTRACT(ca.data, '$.set_the_basics_finished') = 'true'
        AND JSON_EXTRACT(ca.data, '$.mcc_and_turnover_finished') = 'true'
        AND JSON_EXTRACT(ca.data, '$.card_rates_and_fees_finished') = 'true'
        AND JSON_EXTRACT(ca.data, '$.business_type_finished') = 'true'
    )
)

MySQL 8.0+可对JSON路径创建多值索引，进一步加速查询：

ALTER TABLE customer_applications ADD INDEX idx_data_finished (
    (JSON_EXTRACT(data, '$.set_the_basics_finished')),
    (JSON_EXTRACT(data, '$.mcc_and_turnover_finished')),
    (JSON_EXTRACT(data, '$.card_rates_and_fees_finished')),
    (JSON_EXTRACT(data, '$.business_type_finished'))
);

3. 拆分WHERE条件中的OR逻辑 #

(ca.seller_id IN (2,4) OR lc.seller_id IN (2,4))会导致索引失效，拆分为两个查询用UNION ALL合并，每个查询能更好利用索引：

-- 情况1：ca.seller_id符合条件
SELECT ...
FROM customer_applications ca
LEFT JOIN ... lc ON ca.id = lc.pre_registration_id
WHERE ca.partner_id = 1
  AND ca.mode = 10
  AND ca.seller_id IN (2,4)
  AND (ca.is_reopen = 1 OR ...)

UNION ALL

-- 情况2：ca.seller_id不符合，但lc.seller_id符合（排除重复记录）
SELECT ...
FROM customer_applications ca
JOIN ... lc ON ca.id = lc.pre_registration_id
WHERE ca.partner_id = 1
  AND ca.mode = 10
  AND ca.seller_id NOT IN (2,4)
  AND lc.seller_id IN (2,4)
  AND (ca.is_reopen = 1 OR ...)

4. 清理冗余条件 #

原WHERE中的(lc.deleted_at IS NULL OR ca.customer_id IS NULL)和(lc.status NOT IN (30,40) OR ca.customer_id IS NULL)属于冗余判断——因为lc的子查询已经过滤了deleted_at IS NULL和status NOT IN (30,40)，所以这两个条件可直接删除，减少计算开销。

5. 优化复合索引 #

• 针对customer_applications创建复合索引：(partner_id, mode, is_reopen, created_at)，覆盖过滤、排序字段。
• 针对customers创建复合索引：(pre_registration_id, deleted_at, status, id DESC)，配合窗口函数的分区排序需求，避免额外排序操作。

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