Firebird 3执行IN子查询超时致FastReport崩溃原因排查

问题SQL代码 #

SELECT TREQDEVMAT.COD_PRODUTO,
       TPRODUTO.DES_PRODUTO,
       TPRODUTO.COD_SUBFAMILIA,
       TPRODUTOIDIOMA.DES_PRODUTO AS DES_PRODUTO_IDIOMA,
       TREQDEVMAT.NUM_CHAVEOP,
       (TREQDEVMAT.QTD_REQUISITADA / TORDEMPROD.QTD_PRODUZIR) AS QTD_POREQUIP,
       TPRODUTO.FLG_ASSISTTEC,
       TPRODUTO.VAL_ULTCUSTOMOV,
       TPRODUTO.DAT_ULTCOMPRA
FROM TREQDEVMAT
LEFT JOIN TPRODUTO ON TPRODUTO.COD_PRODUTO = TREQDEVMAT.COD_PRODUTO
LEFT JOIN TORDEMPROD ON TORDEMPROD.NUM_CHAVE = TREQDEVMAT.NUM_CHAVEOP
LEFT JOIN TPRODUTOIDIOMA ON TPRODUTOIDIOMA.COD_PRODUTO = TREQDEVMAT.COD_PRODUTO
WHERE TREQDEVMAT.NUM_CHAVEOP IN (
    SELECT tordemprod.NUM_CHAVE
    FROM tmrp
    LEFT JOIN tordemprod ON tordemprod.NUM_CHMRP = tmrp.NUM_CHAVE
    WHERE TRIM(NUM_MRP) = '1354'
)
AND TPRODUTO.FLG_ASSISTTEC = 1
AND TPRODUTOIDIOMA.COD_IDIOMA = 1
AND TREQDEVMAT.COD_EMPRESA = 1
AND (TPRODUTO.COD_SUBFAMILIA NOT LIKE 'CE-UM' OR TPRODUTO.COD_SUBFAMILIA = 'CM-FP')
ORDER BY TREQDEVMAT.COD_PRODUTO ASC

核心原因分析 #

• 子查询逻辑冗余导致数据膨胀
  IN子查询使用LEFT JOIN tordemprod会保留tmrp中所有符合TRIM(NUM_MRP) = '1354'的记录，哪怕tordemprod无匹配项，生成大量无效NULL值。IN子查询处理这些无意义数据时会额外消耗CPU和内存，若tmrp.NUM_CHAVE与tordemprod.NUM_CHMRP无索引，还会触发全表扫描，拖慢查询速度。

• JOIN与WHERE条件冲突引发隐式转换
  主查询使用LEFT JOIN关联TPRODUTO、TPRODUTOIDIOMA，但WHERE条件又对这两个表的字段做等值过滤（FLG_ASSISTTEC = 1、COD_IDIOMA = 1），这会强制将LEFT JOIN转为INNER JOIN，导致查询计划混乱，数据库需要重复计算关联逻辑，增加不必要的开销。

• 索引缺失导致全表扫描
  若TREQDEVMAT.COD_EMPRESA、TREQDEVMAT.NUM_CHAVEOP、TPRODUTO.COD_PRODUTO等核心关联/过滤字段未建立索引，数据库只能通过全表扫描定位数据，数据量较大时会直接导致查询长时间无响应。

• ORDER BY触发大量内存排序
  主查询末尾的ORDER BY TREQDEVMAT.COD_PRODUTO ASC若未对应索引，数据库需将所有查询结果加载到内存完成排序，占用大量系统资源，最终导致FastReport因内存耗尽崩溃。

针对性优化建议 #

1. 修正子查询关联逻辑
   将子查询的LEFT JOIN改为INNER JOIN，避免生成无效NULL值：

   SELECT tordemprod.NUM_CHAVE
   FROM tmrp
   INNER JOIN tordemprod ON tordemprod.NUM_CHMRP = tmrp.NUM_CHAVE
   WHERE TRIM(NUM_MRP) = '1354'
   

2. 调整JOIN与过滤条件的绑定关系
   将右表的过滤条件移至JOIN的ON子句中，避免隐式转换：

   LEFT JOIN TPRODUTO ON TPRODUTO.COD_PRODUTO = TREQDEVMAT.COD_PRODUTO AND TPRODUTO.FLG_ASSISTTEC = 1
   LEFT JOIN TPRODUTOIDIOMA ON TPRODUTOIDIOMA.COD_PRODUTO = TREQDEVMAT.COD_PRODUTO AND TPRODUTOIDIOMA.COD_IDIOMA = 1
   

3. 创建复合索引
   为核心字段建立复合索引，加速数据定位：

   CREATE INDEX IDX_TREQDEVMAT_CORE ON TREQDEVMAT(COD_EMPRESA, NUM_CHAVEOP, COD_PRODUTO);
   CREATE INDEX IDX_TPRODUTO_CORE ON TPRODUTO(COD_PRODUTO, FLG_ASSISTTEC, COD_SUBFAMILIA);
   CREATE INDEX IDX_TMRP_NUM_MRP ON tmrp(NUM_MRP, NUM_CHAVE);
   CREATE INDEX IDX_TORDEMPROD_CHMRP ON tordemprod(NUM_CHMRP, NUM_CHAVE);
   

4. 优化函数过滤逻辑
   避免在WHERE条件中使用TRIM导致索引失效，可建立函数索引：

   CREATE INDEX IDX_TMRP_TRIM_MRP ON tmrp(TRIM(NUM_MRP));
   

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