MySQL中高效统计两级关联表分类型昨日交易总额的最优方案

咱们直接用一次关联查询+条件聚合就能搞定，不需要分两次查询，也不用嵌套那些低效的子查询，能大幅减少数据库的IO开销和网络往返次数，性能提升非常明显。

最终SQL语句 #

SELECT
    a.id AS affiliate_id,
    COALESCE(SUM(CASE WHEN ut.type = 'deposit' THEN ut.amount ELSE 0 END), 0) AS deposit_amount,
    COALESCE(SUM(CASE WHEN ut.type = 'withdraw' THEN ut.amount ELSE 0 END), 0) AS withdraw_amount
FROM affiliates a
LEFT JOIN users u ON a.id = u.affiliate_id
LEFT JOIN user_transactions ut 
    ON u.id = ut.user_id 
    AND DATE(ut.deposit_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) -- 自动取昨日日期，无需手动传参
GROUP BY a.id;

为什么这是最高效的？ #

• 单次查询完成全逻辑：避免了两次ORM查询的网络往返，减少了数据库连接的额外开销。
• 避免重复扫描表：原方案的子查询会对每个用户执行两次SUM计算，相当于对user_transactions表做N*2次扫描（N为用户数量）；新方案只扫描一次相关数据就完成所有聚合。
• 保证数据完整性：用LEFT JOIN确保即使某个联盟商旗下没有用户，或用户昨日无交易，依然会返回该联盟商的记录，COALESCE将NULL值转为0，贴合业务需求。

关键优化：加对索引才能跑更快 #

一定要给这两个表加联合索引，不然性能还是上不去：

• user_transactions表：创建(user_id, deposit_time, type, amount)联合索引
  • 这个索引能让数据库直接定位到某个用户昨日的指定类型交易，完全不需要全表扫描。
• users表：创建(affiliate_id, id)联合索引
  • 加速从联盟商到旗下用户的关联查询，减少关联时的查找时间。

对比原方案的优势 #

原方案的嵌套子查询属于「相关子查询」，每次执行都依赖外部表的用户ID，会重复触发查询；而新方案的聚合是在关联后一次性完成的，随着用户和交易数量的增长，性能差距会越来越显著。

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