MySQL嵌套层级查询性能优化求助：特定字段致查询变慢

问题背景 #

内部子查询单独执行仅需2秒，但外层包裹后因引入rtpc.id_c和rtpc.service_level_c两个字段，整体查询耗时长达25秒。这两个字段已建立索引，尝试减少JOIN、将子查询替换为JOIN后仅提升8-9秒，未达预期。原查询如下：

SELECT
    first_base.case_id_c
FROM (
    SELECT DISTINCT
        (cc.case_id_c),
        (
            SELECT CONCAT(u.first_name, ' ', u.last_name)
            FROM users u
            WHERE u.id = cc.user_id1_c
        ) AS Case_Advocate,
        (
            SELECT CONCAT(u.first_name, ' ', u.last_name)
            FROM users u
            WHERE u.id = cc.user_id2_c
        ) AS Practitioner,
        (
            SELECT CONCAT(u.first_name, ' ', u.last_name)
            FROM users u
            WHERE u.id = cc.user_id3_c
        ) AS Tax_Preparer,
        c.id,
        rtpc.service_level_c AS tp_service_level, -- 导致性能下降
        cc.tax_preparation_level_c AS client_tax_prep_service_level,
        rtpc.id_c AS 'TaxPrepID',-- 导致性能下降
        (
            CASE WHEN rtp.deleted IS NULL THEN 0 ELSE rtp.deleted
        END
        ) AS TP_DELETED
    FROM contacts c
    LEFT JOIN contacts_cstm cc ON cc.id_c = c.id
    LEFT JOIN contacts_reso_resolutions_1_c crr1c ON crr1c.contacts_reso_resolutions_1contacts_ida = cc.id_c
    LEFT JOIN reso_resolutions_cstm AS rrc ON rrc.id_c = crr1c.contacts_reso_resolutions_1reso_resolutions_idb
    LEFT JOIN reso_resolutions AS rr ON rr.id = rrc.id_c
    LEFT JOIN contacts_reso_ancillary_services_1_c AS cras1c ON cras1c.contacts_reso_ancillary_services_1contacts_ida = c.id
    LEFT JOIN reso_ancillary_services_cstm AS rasc ON rasc.id_c = cras1c.contacts_reso_ancillary_services_1reso_ancillary_services_idb
    LEFT JOIN reso_ancillary_services AS ras ON ras.id = rasc.id_c
    LEFT JOIN contacts_reso_tax_preparation_1_c AS crtp1c ON crtp1c.contacts_reso_tax_preparation_1contacts_ida = c.id
    LEFT JOIN reso_tax_preparation_cstm AS rtpc ON rtpc.id_c = crtp1c.contacts_reso_tax_preparation_1reso_tax_preparation_idb
    LEFT JOIN reso_tax_preparation AS rtp ON rtp.id = rtpc.id_c
    WHERE c.deleted <> 1 AND cc.ctax_status_c = 'Active Service' AND preferred_language NOT LIKE '%en_us%'
) AS first_base

问题字段：

rtpc.id_c AS 'TaxPrepID',
rtpc.service_level_c AS tp_service_level,

优化方案 #

1. 移除不必要的JOIN与字段 #

外层仅需case_id_c，但子查询包含大量无关字段（如Case_Advocate、Practitioner等）和JOIN（如resolutions、ancillary services关联表），这些会增加子查询数据量和DISTINCT的计算开销。修改子查询，只保留必要内容：

SELECT
    first_base.case_id_c
FROM (
    SELECT DISTINCT
        cc.case_id_c,
        rtpc.service_level_c,
        rtpc.id_c
    FROM contacts c
    LEFT JOIN contacts_cstm cc ON cc.id_c = c.id
    -- 仅保留与rtpc相关的JOIN链
    LEFT JOIN contacts_reso_tax_preparation_1_c AS crtp1c ON crtp1c.contacts_reso_tax_preparation_1contacts_ida = c.id
    LEFT JOIN reso_tax_preparation_cstm AS rtpc ON rtpc.id_c = crtp1c.contacts_reso_tax_preparation_1reso_tax_preparation_idb
    WHERE c.deleted <> 1 AND cc.ctax_status_c = 'Active Service' AND preferred_language NOT LIKE '%en_us%'
) AS first_base

2. 优化DISTINCT逻辑 #

DISTINCT会对所有选中字段去重，字段越多开销越大。若case_id_c本身唯一，可直接移除DISTINCT；若不唯一，尝试用GROUP BY cc.case_id_c替代，MySQL对GROUP BY的优化有时优于DISTINCT。

3. 补全关联链上的索引 #

虽然rtpc的字段有索引，但需确保关联路径上的其他表有合适索引：

• 给contacts_reso_tax_preparation_1_c.contacts_reso_tax_preparation_1contacts_ida建索引，这是关联contacts的关键
• 给contacts_cstm建联合索引(ctax_status_c, id_c)，匹配WHERE条件和关联逻辑
• 给contacts建联合索引(deleted, preferred_language)，加速初始数据过滤

4. 移除嵌套，直接改写为单表查询 #

既然外层仅需case_id_c，可直接合并逻辑，去掉子查询嵌套：

SELECT DISTINCT cc.case_id_c
FROM contacts c
LEFT JOIN contacts_cstm cc ON cc.id_c = c.id
LEFT JOIN contacts_reso_tax_preparation_1_c AS crtp1c ON crtp1c.contacts_reso_tax_preparation_1contacts_ida = c.id
LEFT JOIN reso_tax_preparation_cstm AS rtpc ON rtpc.id_c = crtp1c.contacts_reso_tax_preparation_1reso_tax_preparation_idb
WHERE c.deleted <> 1 
  AND cc.ctax_status_c = 'Active Service' 
  AND preferred_language NOT LIKE '%en_us%'

5. 优化NOT LIKE '%en_us%'的性能 #

NOT LIKE '%xxx%'无法使用索引，会触发全表扫描。可根据业务场景替换：

• 若preferred_language取值有限，用NOT IN ('en_us')替代
• 新增is_non_english字段（1表示非英文），提前计算存储，然后用AND is_non_english = 1过滤，利用该字段的索引

6. 调整JOIN类型（需结合业务逻辑） #

若cc.ctax_status_c = 'Active Service'要求contacts_cstm必须有匹配记录，可将LEFT JOIN contacts_cstm改为INNER JOIN，减少空值数据处理开销。同理，若rtpc字段是业务必需，也可评估调整JOIN类型的可行性。

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