3条Eloquent查询优化咨询：当前查询总耗时达1.5秒

现有代码的核心问题 #

• 重复冗余查询：每个统计维度都调用了2次whereHas，额外生成了不必要的EXISTS子查询，和后续withCount的条件完全重复
• 关联方向不合理：从User表反向关联pictures表统计，本质是对全量用户做遍历判断，数据量大时效率极低
• 三次独立查询，重复IO开销
• 大概率缺少必要的数据库索引，导致关联、条件过滤、排序都走全表扫描

第一步：优化SQL逻辑降低查询耗时 #

方案1：精简现有写法（改动最小） #

直接删除冗余的whereHas调用，withCount统计后过滤pictures_count>0即可，同时把重复的时间区间抽成变量避免重复计算：

public function show()
{
    $now = Carbon::now();
    $currentWeekStart = $now->startOfWeek();
    $currentWeekEnd = $now->endOfWeek();
    $lastWeekStart = $currentWeekStart->copy()->subWeek();
    $lastWeekEnd = $currentWeekEnd->copy()->subWeek();

    $currentWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [$currentWeekStart, $currentWeekEnd])])
        ->where('pictures_count', '>', 0)
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    $lastWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [$lastWeekStart, $lastWeekEnd])])
        ->where('pictures_count', '>', 0)
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    $overall = User::withCount('pictures')
        ->where('pictures_count', '>', 0)
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    return view('users.leaderboard', compact('currentWeek', 'lastWeek', 'overall'));
}

方案2：调整关联方向（性能提升最明显） #

直接从pictures表做分组聚合统计，再关联用户信息，避免遍历全量用户表：

public function show()
{
    $now = Carbon::now();
    $currentWeekStart = $now->startOfWeek();
    $currentWeekEnd = $now->endOfWeek();
    $lastWeekStart = $currentWeekStart->copy()->subWeek();
    $lastWeekEnd = $currentWeekEnd->copy()->subWeek();

    // 本周统计
    $currentWeek = Picture::whereBetween('created_at', [$currentWeekStart, $currentWeekEnd])
        ->selectRaw('user_id, count(*) as pictures_count')
        ->groupBy('user_id')
        ->orderBy('pictures_count', 'desc')
        ->limit(10)
        ->with('user') // 预加载用户信息
        ->get()
        ->pluck('user'); // 提取用户对象，保留pictures_count属性

    // 上周逻辑同上，替换时间条件即可
    $lastWeek = Picture::whereBetween('created_at', [$lastWeekStart, $lastWeekEnd])
        ->selectRaw('user_id, count(*) as pictures_count')
        ->groupBy('user_id')
        ->orderBy('pictures_count', 'desc')
        ->limit(10)
        ->with('user')
        ->get()
        ->pluck('user');

    // 历史累计逻辑
    $overall = Picture::selectRaw('user_id, count(*) as pictures_count')
        ->groupBy('user_id')
        ->orderBy('pictures_count', 'desc')
        ->limit(10)
        ->with('user')
        ->get()
        ->pluck('user');

    return view('users.leaderboard', compact('currentWeek', 'lastWeek', 'overall'));
}

第二步：添加数据库索引 #

必须给pictures表加两个联合索引，大幅提升过滤、分组速度：

-- 用于时间范围筛选+分组的联合索引
ALTER TABLE pictures ADD INDEX idx_created_at_user_id (created_at, user_id);
-- 用于历史累计分组的索引
ALTER TABLE pictures ADD INDEX idx_user_id (user_id);

第三步：加缓存进一步降低开销 #

这类排行榜数据不需要实时更新，可根据更新频率设置缓存：

• 上周排行榜：数据不会再变化，缓存有效期设置为7天，每周一自动刷新
• 本周排行榜：缓存有效期设置为1~2小时
• 历史累计排行榜：缓存有效期设置为1天
  用Laravel缓存实现示例：

$currentWeek = Cache::remember('leaderboard.current_week', 3600, function () {
    // 上述本周统计逻辑
});
$lastWeek = Cache::remember('leaderboard.last_week', 86400*7, function () {
    // 上述上周统计逻辑
});
$overall = Cache::remember('leaderboard.overall', 86400, function () {
    // 上述历史累计统计逻辑
});

做完以上三步，查询耗时可以降到100ms以内，加缓存后几乎无数据库开销。

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