MySQL IN查询转JOIN查询优化：结果异常问题排查

问题背景 #

原使用IN子句的查询能得到正确的求和结果：

SELECT IFNULL(SUM(bt.transactionQuantity * bpd.unitPrice), 0)
FROM batchTransaction bt
LEFT JOIN batch b ON b.id = bt.batchId
LEFT JOIN batchPricingDetails bpd ON bpd.id = b.batchPricingDetailsId
WHERE (bt.productId, bt.dateCreated) IN (
    SELECT productId, MAX(dateCreated)
    FROM batchTransaction
    WHERE organisationId = '714361434540086498'
    AND dateCreated < 1689877800000
    GROUP BY productId
);

改为JOIN查询后，求和结果偏差超过10倍：

SELECT IFNULL(SUM(bt.transactionQuantity * bpd.unitPrice), 0)
FROM batchTransaction bt
JOIN (
    SELECT bt.productId, max(bt.dateCreated)
    FROM batchTransaction bt
    WHERE bt.organisationId = '714361434540086498' AND bt.dateCreated < 1689877800000
    GROUP BY bt.productId
) AS filtered_data ON bt.productId = filtered_data.productId
LEFT JOIN batch b ON b.id = bt.batchId
LEFT JOIN batchPricingDetails bpd ON bpd.id = b.batchPricingDetailsId;

问题根源 #

原IN子句是同时匹配productId和该productId对应的最新dateCreated，仅筛选出每个productId下最新的那一笔交易参与计算。而修改后的JOIN仅关联了productId，会把该productId下所有符合organisationId和dateCreated < 1689877800000条件的交易全部纳入求和，导致总和被重复计算，结果远大于预期。

修正后的JOIN查询 #

调整JOIN的关联条件，同时匹配productId和dateCreated等于子查询中的最大值，确保只取每个productId的最新交易：

SELECT IFNULL(SUM(bt.transactionQuantity * bpd.unitPrice), 0)
FROM batchTransaction bt
JOIN (
    SELECT productId, MAX(dateCreated) AS max_dateCreated
    FROM batchTransaction
    WHERE organisationId = '714361434540086498'
      AND dateCreated < 1689877800000
    GROUP BY productId
) AS filtered_data 
    ON bt.productId = filtered_data.productId 
    AND bt.dateCreated = filtered_data.max_dateCreated
LEFT JOIN batch b ON b.id = bt.batchId
LEFT JOIN batchPricingDetails bpd ON bpd.id = b.batchPricingDetailsId;

额外优化建议 #

如果batchTransaction表数据量较大，建议给(organisationId, productId, dateCreated)建立联合索引，能大幅提升子查询的分组效率，进一步优化慢查询问题。

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