如何优化每小时运行的百万行数据关联聚合SQL查询？

原查询的核心问题是子查询s对picked全表执行聚合操作，即便仅需处理与shipped表中该小时master_con相关的记录，这会导致百万行级的picked表被全量扫描，严重拖慢查询速度。以下是针对性的优化写法：

1. 保留单master_con明细的优化写法 #

直接过滤shipped表的小时数据后关联picked，仅对匹配的记录做聚合：

SELECT 
    f.master_con, 
    COUNT(DISTINCT p.container) AS containers
FROM shipped f
JOIN picked p ON f.master_con = p.master_con
WHERE f.start_time >= a AND f.start_time <= a + 1
GROUP BY f.master_con

这种写法避免了picked表的全表扫描，仅处理与目标小时master_con关联的记录，性能会有明显提升。

2. 直接获取总发货容器数的简化写法 #

如果最终只需要对containers求和的结果，无需保留单master_con明细，可以简化为嵌套聚合：

SELECT SUM(containers) AS total_containers
FROM (
    SELECT COUNT(DISTINCT p.container) AS containers
    FROM shipped f
    JOIN picked p ON f.master_con = p.master_con
    WHERE f.start_time >= a AND f.start_time <= a + 1
    GROUP BY f.master_con
) t

3. 索引优化建议 #

为进一步提升查询效率，建议添加以下复合索引：

• shipped(start_time, master_con)：快速过滤目标小时的master_con数据
• picked(master_con, container)：加速关联和去重统计的过程

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