带连接子查询的SQL查询优化：多门店销售统计语句性能问题

嘿，这个场景我太熟了——单条子查询跑起来嗖嗖的，一关联四家门店的数据就直接卡到两分钟，完全是SQL调优里的经典“坑”！咱们一步步拆解，看看能怎么把速度拉回来：

你说连接前得先处理每个子查询，那首先得确保每个门店的子查询只返回你真正需要的东西：

• 别把没用的字段塞进子查询，比如那些后续连接、汇总都用不上的商品描述、冗余计算列，能砍就砍
• 把WHERE条件提前放到每个子查询里（比如限定销售日期、只取已完成的订单），别等连接后再过滤——理论上优化器会自动推条件，但有时候它就是“犯傻”，提前过滤能大幅缩小中间结果集
• 给每个子查询的结果加清晰的别名，连接时只用到精准的关联键（比如product_id+store_id这种唯一组合）

举个优化前后的例子：
原来的子查询可能是：

SELECT * FROM sales WHERE store_id = 1

优化后：

SELECT 
  store_id, 
  product_id, 
  SUM(sales_amount) AS total_sales, 
  COUNT(order_id) AS order_count
FROM sales 
WHERE store_id = 1 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY store_id, product_id

如果用JOIN关联四家子查询，得确认你的关联条件是精准匹配：

• 能不用LEFT JOIN就不用（除非你确实要保留无销售的商品），LEFT JOIN有时候会让优化器放弃使用索引，被迫全表扫描
• 关联键必须有索引！比如product_id和store_id如果没建联合索引，赶紧补上——单条查询可能靠单字段索引撑着，但关联的时候没联合索引就会直接拉胯
• 别用模糊的关联条件，明确写ON a.product_id = b.product_id（你说调过ON clauses，这条可能已经做了，但再仔细核对下有没有漏条件）

既然是四家门店的汇总数据，说不定反其道而行之——先合并四家的子查询结果，再统一汇总，比分别汇总再连接效率高得多：

SELECT 
  store_id,
  product_id,
  SUM(total_sales) AS total_sales,
  SUM(order_count) AS order_count
FROM (
  -- 门店1的子查询
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 1 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
  UNION ALL
  -- 门店2的子查询
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 2 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
  UNION ALL
  -- 门店3的子查询
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 3 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
  UNION ALL
  -- 门店4的子查询
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 4 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
) AS all_stores
GROUP BY store_id, product_id;

这个方法的核心是：UNION ALL只是线性合并结果，不会像JOIN那样产生大量中间交叉数据，尤其是当四家门店的商品重叠度很高时，JOIN会做很多无用的匹配，而UNION ALL直接把数据堆在一起就完事。

有时候数据库优化器会错误地选择先连接再过滤/汇总，这时候可以用子查询物化的方式，让数据库先把每个子查询的结果存成临时表，再进行连接：
比如PostgreSQL里可以这么写：

WITH store1_sales AS MATERIALIZED (
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 1 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
),
store2_sales AS MATERIALIZED (
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 2 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
),
store3_sales AS MATERIALIZED (
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 3 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
),
store4_sales AS MATERIALIZED (
  SELECT store_id, product_id, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
  FROM sales WHERE store_id = 4 AND sale_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY store_id, product_id
)
SELECT 
  COALESCE(s1.product_id, s2.product_id, s3.product_id, s4.product_id) AS product_id,
  COALESCE(s1.total_sales, 0) AS store1_sales,
  COALESCE(s2.total_sales, 0) AS store2_sales,
  COALESCE(s3.total_sales, 0) AS store3_sales,
  COALESCE(s4.total_sales, 0) AS store4_sales
FROM store1_sales s1
FULL OUTER JOIN store2_sales s2 ON s1.product_id = s2.product_id
FULL OUTER JOIN store3_sales s3 ON s1.product_id = s3.product_id
FULL OUTER JOIN store4_sales s4 ON s1.product_id = s4.product_id;

MySQL里也可以用临时表先存每个子查询的结果，再连接临时表，效果类似。

把最后一道关守好：

• 给sales表建(store_id, sale_date, product_id)的联合索引，这个索引能让每个子查询直接定位到目标数据，不用全表扫描
• 如果是汇总操作，(store_id, product_id)的联合索引能加速GROUP BY，因为数据库可以直接按索引顺序分组，省去排序的开销

如果以上方法都试过还是慢，建议跑个EXPLAIN看看执行计划，比如是不是某个连接步骤在做全表扫描，或者中间结果集太大——从执行计划里能直接找到瓶颈所在。

内容的提问来源于stack exchange，提问作者Bee S.