Laravel多表分组数据查询性能优化方案咨询

首先得说，你的现有代码存在几个核心性能瓶颈：重复查询数据库、在PHP内存中处理海量数据、没用到Nestedset的高效查询特性，这些在数据量上来后肯定会拖慢速度。下面是针对性的优化方案，核心思路是让数据库完成所有聚合计算，大幅减少PHP的内存消耗和查询次数。

问题分析 #

1. 重复查询浪费资源：你对Summaries和Stores表各做了3次查询，每次只筛选一个类型，完全可以一次查询搞定所有类型的聚合。
2. 内存处理聚合效率低：把大量原始数据加载到Collection后再做whereIn和sum，远不如数据库原生聚合函数高效。
3. 未利用Nestedset特性：Kalnoy Nestedset提供的lft和rgt字段能让你用一次SQL高效获取用户的所有后代，而不是依赖Eloquent集合的descendants（可能带来N+1问题或内存过载）。

优化方案 #

我们可以通过一次SQL查询直接生成所需报表数据，利用Nestedset的范围查询和数据库聚合函数完成所有计算：

步骤1：确定需要展示的用户列表 #

先获取报表中要显示的用户：选中的用户本身 + 它的role_id=3直接子节点。

$selectedUser = User::find($parentId);

// 收集报表展示用户：选中用户 + 其role_id=3的直接子节点
$reportUsers = collect([$selectedUser])
    ->merge($selectedUser->children()->where('role_id', 3)->get());

步骤2：一次性查询所有报表数据 #

利用数据库的JOIN和聚合函数，直接计算每个用户及其role_id=4后代的各类数据总和：

$reportUserIds = $reportUsers->pluck('id')->toArray();

$reportData = DB::table('users as u')
    ->select([
        'u.id',
        'u.name as username',
        // 汇总Summaries各类型数据
        DB::raw('COALESCE(SUM(CASE WHEN s.type = 1 THEN s.value ELSE 0 END), 0) as sum_type_1'),
        DB::raw('COALESCE(SUM(CASE WHEN s.type = 2 THEN s.value ELSE 0 END), 0) as sum_type_2'),
        DB::raw('COALESCE(SUM(CASE WHEN s.type = 3 THEN s.value ELSE 0 END), 0) as sum_type_3'),
        // 汇总Stores各类型数据
        DB::raw('COALESCE(SUM(CASE WHEN st.type = 1 THEN st.value ELSE 0 END), 0) as store_type_1'),
        DB::raw('COALESCE(SUM(CASE WHEN st.type = 3 THEN st.value ELSE 0 END), 0) as store_type_3'),
    ])
    // 关联当前用户的所有后代（含自身），筛选自身或role_id=4的用户
    ->leftJoin('users as descendants', function ($join) {
        $join->on('descendants.lft', '>=', 'u.lft')
             ->on('descendants.rgt', '<=', 'u.rgt')
             ->where(function ($query) {
                 $query->where('descendants.id', '=', 'u.id')
                       ->orWhere('descendants.role_id', '=', 4);
             });
    })
    // 关联Summaries表
    ->leftJoin('summaries as s', 's.user_id', '=', 'descendants.id')
    // 关联Stores表
    ->leftJoin('stores as st', 'st.user_id', '=', 'descendants.id')
    // 只查询需要展示的用户
    ->whereIn('u.id', $reportUserIds)
    // 按用户分组聚合
    ->groupBy('u.id', 'u.name')
    ->get();

return response()->json([
    'userCollection' => $reportData,
]);

步骤3：添加索引优化（可选但强烈推荐） #

为了让查询速度再上一个台阶，给相关字段添加索引：

-- 用户表：优化Nestedset范围查询和role_id筛选
CREATE INDEX idx_users_lft_rgt ON users(lft, rgt);
CREATE INDEX idx_users_role_id ON users(role_id);

-- Summaries表：优化按user_id和type的查询
CREATE INDEX idx_summaries_user_id_type ON summaries(user_id, type);

-- Stores表：优化按user_id和type的查询
CREATE INDEX idx_stores_user_id_type ON stores(user_id, type);

为什么这个方案更高效？ #

1. 单次查询搞定所有：所有计算都在数据库层面完成，只需要一次查询就能获取所有报表数据，替代原来的多次重复查询。
2. 数据库聚合更高效：数据库的SUM+CASE聚合逻辑比PHP在内存中处理Collection快得多，数据量越大优势越明显。
3. 高效的后代查询：利用Nestedset的lft和rgt字段，通过范围查询快速获取用户的所有后代，避免了Eloquent集合可能带来的内存占用问题。
4. 大幅减少内存消耗：不需要把大量原始数据加载到PHP内存中，直接获取聚合后的结果，内存占用骤降。

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