如何优化含子查询与SUM函数的MySQL查询？

原查询的性能瓶颈分析 #

• 多层嵌套的关联子查询会被逐行执行，大幅增加IO开销
• LEFT JOIN condicionesp的条件中使用IF()函数，导致无法利用condicionesp.codigo上的索引
• 重复执行SELECT codigo FROM companies WHERE codigos = 76291019子查询，未复用结果
• 嵌套的IFNULL和SUM逻辑增加了计算复杂度

优化步骤与改写后的查询 #

1. 预查询目标公司编码：先获取companies表中codigos=76291019对应的codigo，避免重复查询
2. 替换关联子查询为JOIN聚合：将原本的子查询计算逻辑改为LEFT JOIN结合GROUP BY，减少逐行计算的开销
3. 修正JOIN条件：把IF(formapago.condiciondep=0,1,formapago.condiciondep)=condicionesp.codigo拆分为等价的逻辑条件，让索引可以生效
4. 简化计算逻辑：将嵌套的SUM和IF逻辑合并，提升可读性和执行效率

-- 预查询目标公司编码，避免重复计算
WITH target_company AS (
    SELECT codigo FROM companies WHERE codigos = 76291019
)
SELECT
    fp.codigos,
    fp.cuentacte AS cargos,
    IFNULL(SUM(
        -- 计算主referencias的金额
        CASE WHEN r.tipodoc IN (00,61) THEN r.valor ELSE r.valor * -1 END
        -- 减去关联的ref表金额
        - COALESCE(ref_sum.total, 0)
    ), 0) AS abonos
FROM formapago fp
-- 修正condicionesp的JOIN条件，去掉IF函数
LEFT JOIN condicionesp cp 
    ON (fp.condiciondep = 0 AND cp.codigo = 1) 
    OR (fp.condiciondep != 0 AND cp.codigo = fp.condiciondep)
LEFT JOIN clientes c ON fp.codigos = c.codigos
-- 关联referencias表，替代原外层子查询
LEFT JOIN referencias r 
    ON r.codigos = fp.codigos
    AND r.tipodocref = fp.tipodoc
    AND r.numerodocref = fp.numerodoc
    AND r.tipodoc IN (00,56,61)
    AND EXISTS (SELECT 1 FROM target_company tc WHERE r.empresa = tc.codigo)
-- 预聚合ref表的金额，替代原内层子查询
LEFT JOIN (
    SELECT
        codigos,
        tipodocref,
        numerodocref,
        SUM(CASE WHEN tipodoc = 56 THEN valor ELSE valor * -1 END) AS total
    FROM referencias
    WHERE tipodoc IN (56,61)
      AND EXISTS (SELECT 1 FROM target_company tc WHERE empresa = tc.codigo)
    GROUP BY codigos, tipodocref, numerodocref
) ref_sum 
    ON ref_sum.codigos = r.codigos
    AND ref_sum.tipodocref = r.tipodoc
    AND ref_sum.numerodocref = r.numerodoc
WHERE fp.empresa = 2
  AND fp.codigos = 77155568
  AND fp.tipodoc IN (33,34,35,39)
  AND fp.cuentacte <> 0
GROUP BY fp.codigos, fp.cuentacte
HAVING cargos <> abonos;

额外优化建议 #

• 确保以下字段存在合适的索引：
  • formapago(empresa, codigos, tipodoc, cuentacte)：覆盖WHERE条件和JOIN字段
  • referencias(codigos, tipodocref, numerodocref, tipodoc, empresa, valor)：覆盖JOIN和聚合计算的字段
  • condicionesp(codigo)：用于快速匹配JOIN条件
  • companies(codigos)：加速预查询

内容的提问来源于stack exchange，提问作者Carmen Cisterna Chamblas