百万级数据下三表关联带条件查询两表金额总和的MySQL及Laravel实现

架构老林

2026-10-2

解决方案

前置优化（百万级查询必备）

首先需要给关联表添加覆盖索引，避免回表查询，大幅提升查询效率：

balance_ins 表添加联合索引：idx_sc_type_amount(super_consumer_id, type, amount)
recharges 表添加联合索引：idx_sc_status_amount(super_consumer_id, status, amount)

原生MySQL查询语句

优先使用子查询预聚合的写法，避免直接关联产生笛卡尔积，性能远高于直接JOIN后聚合：

SELECT 
    sc.id,
    sc.name,
    IFNULL(bi.total, 0) AS total_balance_in,
    IFNULL(r.total, 0) AS total_recharge
FROM super_consumers sc
LEFT JOIN (
    SELECT super_consumer_id, SUM(amount) AS total
    FROM balance_ins
    WHERE type = 1
    GROUP BY super_consumer_id
) bi ON sc.id = bi.super_consumer_id
LEFT JOIN (
    SELECT super_consumer_id, SUM(amount) AS total
    FROM recharges
    WHERE status = 1
    GROUP BY super_consumer_id
) r ON sc.id = r.super_consumer_id;

Laravel DB Query写法

和MySQL逻辑一致，使用Laravel子查询关联语法实现：

// 余额子查询
$balanceInSub = \DB::table('balance_ins')
    ->selectRaw('super_consumer_id, SUM(amount) as total')
    ->where('type', 1)
    ->groupBy('super_consumer_id');

// 充值子查询
$rechargeSub = \DB::table('recharges')
    ->selectRaw('super_consumer_id, SUM(amount) as total')
    ->where('status', 1)
    ->groupBy('super_consumer_id');

// 主查询关联子查询
$result = \DB::table('super_consumers as sc')
    ->leftJoinSub($balanceInSub, 'bi', 'sc.id', '=', 'bi.super_consumer_id')
    ->leftJoinSub($rechargeSub, 'r', 'sc.id', '=', 'r.super_consumer_id')
    ->selectRaw('sc.id, sc.name, IFNULL(bi.total, 0) as total_balance_in, IFNULL(r.total, 0) as total_recharge')
    ->get();