如何优化SQL，去除子查询从关联表查询每笔采购的最大参数值

你可以用条件聚合+左连接的方式替换原本的相关子查询，只需要扫描一次Items表即可完成统计，执行效率远高于原写法，优化后的SQL如下：

SELECT
    p.Id,
    p.CustomerName,
    MAX(CASE WHEN i.ParameterName = 'Price' THEN i.ParameterValue END) AS BiggestPrice,
    MAX(CASE WHEN i.ParameterName = 'PercentOff' THEN i.ParameterValue END) AS BiggestPercentOff
FROM Purchases p
LEFT JOIN Items i ON p.Id = i.PurchaseId
GROUP BY p.Id, p.CustomerName

优化说明 #

• 原写法属于关联子查询，Purchases表每有一行数据，就会分别执行两次子查询扫描Items表，数据量越大性能损耗越明显
• 优化后先通过左连接一次性关联所有匹配的Items记录，再用条件聚合分别统计两种参数的最大值，全程仅需扫描一次Items表，兼容性也更强，适配绝大多数主流SQL dialect
• 若要进一步提升性能，可以给Items表创建联合覆盖索引 (PurchaseId, ParameterName, ParameterValue)，查询时无需回表即可拿到所有需要的字段

执行后返回的结果和你期望的完全一致，缺失对应参数的采购记录会自动返回NULL。

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