investors_diversificacao表数据量大时SQL查询性能低下优化求助

核心问题定位 #

当前查询在investors_diversificacao表数据量大时性能骤降，核心原因是冗余的过滤条件、不合理的聚合逻辑、缺少覆盖索引导致全表扫描和大量回表操作，以及大表未做预聚合就直接关联导致join开销过高。

具体优化措施 #

• 简化冗余过滤条件：原WHERE条件中investors_base_assessores.nome_investor = @investor_nome重复出现3次，可统一提取到外层，减少过滤判断开销，同时提前过滤出目标投资顾问对应的少量数据，降低后续join的数据量。
• 修正GROUP BY逻辑：原查询仅按cod_cliente分组，但SELECT中存在大量非聚合字段，不仅不符合严格SQL规范，还会产生额外的临时表、排序开销。建议非聚合字段全部加入GROUP BY，或先对大表做预聚合后再关联维度表。
• 新增覆盖索引：所有索引均覆盖关联键、过滤条件、查询所需字段，避免回表查询：
  • investors_diversificacao 加联合索引：(cod_cliente, produto, net, cnpj)
  • investors_base_assessores 加联合索引：(nome_investor, cod_assessor, squad, nome_assessor)
  • investors_saldo_financeiro 加联合索引：(cod_cliente, saldo_d0, nome_cliente)
  • investors_posicao_geral 加联合索引：(cod_cliente, vencimento, financeiro)
  • investors_guia_fundos 加联合索引：(cnpj, liquidez_total)
• 大表预聚合：将investors_diversificacao的聚合逻辑抽为独立子查询，提前聚合降维后再关联，避免全表关联后再聚合的高额开销。
• 可选优化：如果业务上允许仅保留存在investors_diversificacao对应数据的行，可将针对该表的LEFT JOIN改为INNER JOIN，进一步减少关联数据量。

优化后SQL示例 #

SELECT 
    ip.cod_cliente, 
    isf.nome_cliente, 
    iba.squad, 
    iba.nome_investor, 
    isf.saldo_d0, 
    SUM(CASE WHEN ipg.vencimento <= @vencimento THEN ipg.financeiro ELSE 0 END) AS vencimentos_ate_data, 
    div_agg.fundos_ate_data, 
    igf.liquidez_total, 
    ip.contatar_liquidity_map, 
    ip.id, 
    ipg.vencimento, 
    iba.nome_assessor
FROM investors_positivador ip
INNER JOIN investors_saldo_financeiro isf 
    ON ip.cod_cliente = isf.cod_cliente 
INNER JOIN investors_base_assessores iba 
    ON ip.cod_assessor = iba.cod_assessor 
INNER JOIN investors_posicao_geral ipg 
    ON ip.cod_cliente = ipg.cod_cliente 
LEFT JOIN (
    SELECT 
        cod_cliente, 
        ROUND(SUM(CASE WHEN produto = 'Fundos' THEN net / 6 ELSE 0 END), 2) AS fundos_ate_data,
        cnpj,
        net
    FROM investors_diversificacao
    GROUP BY cod_cliente, cnpj, net
) AS div_agg 
    ON ip.cod_cliente = div_agg.cod_cliente 
LEFT OUTER JOIN investors_guia_fundos igf 
    ON div_agg.cnpj = igf.cnpj
WHERE 
    iba.nome_investor = @investor_nome 
    AND (
        isf.saldo_d0 > 0 
        OR ipg.financeiro > 0 
        OR div_agg.net > 0
    )
GROUP BY 
    ip.cod_cliente,
    isf.nome_cliente,
    iba.squad,
    iba.nome_investor,
    isf.saldo_d0,
    igf.liquidez_total,
    ip.contatar_liquidity_map,
    ip.id,
    ipg.vencimento,
    iba.nome_assessor

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