优化 Laravel Eloquent 查询：高效构建用户排行榜-php教程-PHP中文网

优化 Laravel Eloquent 查询：高效构建用户排行榜

DDD

发布： 2025-07-29 14:26:13

原创

589人浏览过

优化 Laravel Eloquent 查询：高效构建用户排行榜

本文旨在解决 Laravel Eloquent 查询中因冗余关联条件导致的性能瓶颈，特别是在构建用户排行榜时遇到的慢查询问题。通过逐步分析 whereHas 和 withCount 的使用，揭示了如何消除不必要的查询条件，大幅提升查询效率，从而在不牺牲功能的前提下，优化数据库操作，确保应用响应速度。

Eloquent 关联查询性能瓶颈分析

在 laravel 应用中，当需要根据关联模型的数据进行聚合统计并排序时，如统计用户发布的照片数量并生成排行榜，开发者常会遇到查询效率低下的问题。原始代码中，为了筛选出在特定时间范围内发布了照片的用户，并统计其照片数量，采用了多重 wherehas 和 withcount 的组合，这导致了冗余的数据库查询，显著增加了请求时长。

以下是原始的 Eloquent 查询代码示例，它尝试获取本周、上周和总计发布照片最多的用户：

public function show()
{
    $currentWeek = User::whereHas('pictures')
        ->whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()]))
        ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])])
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    $lastWeek = User::whereHas('pictures')
        ->whereHas('pictures', fn ($q) => $q->whereBetween('created_now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()]))
        ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()])])
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    $overall = User::whereHas('pictures')
        ->whereHas('pictures') // 此处存在冗余
        ->withCount('pictures')
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    return view('users.leaderboard', [
        'currentWeek' => $currentWeek,
        'lastWeek' => $lastWeek,
        'overall' => $overall,
    ]);
}

登录后复制

这段代码的问题在于，对于 currentWeek 和 lastWeek 的查询，whereHas('pictures') 与 whereHas('pictures', fn ($q) => ...) 同时存在。对于 overall 查询，whereHas('pictures') 被调用了两次。这些冗余的 whereHas 调用会转换为额外的 SQL EXISTS 子句，从而降低查询效率。

优化步骤一：消除冗余的 whereHas

首先，观察到 whereHas('pictures') 和 whereHas('pictures', fn ($q) => $q->whereBetween(...)) 同时出现。如果一个 whereHas 已经包含了特定的条件，那么一个不带条件的 whereHas 就变得多余了，因为它只是简单地检查是否存在任何关联记录。如果带条件的 whereHas 已经满足，那么无条件的 whereHas 也必然满足。

将查询修改为：

$currentWeek = User::whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()]))
    ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()])])
    ->orderBy('pictures_count', 'DESC')
    ->limit(10)
    ->get();

登录后复制

经过此优化，生成的 SQL 查询将减少一个 EXISTS 子句：

优化前 SQL 片段:

where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `pictures`.`deleted_at` is null)
    and exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)

登录后复制

优化后 SQL 片段:

where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)
    -- 移除了第一个无条件 where exists 子句

登录后复制

这已经是一个初步的改进，减少了数据库的额外检查。

优化步骤二：利用 withCount 的隐式过滤能力

进一步分析，我们发现 withCount 方法本身就支持传入闭包来限制计数范围。如果 withCount 的条件没有匹配到任何关联记录，它会返回 0。由于我们最终是按照 pictures_count 降序排序，那些 pictures_count 为 0 的用户自然会排在后面。这意味着，我们不需要单独使用 whereHas 来过滤用户，withCount 已经能够间接地实现这一目的。

因此，whereHas 甚至可以完全移除：

Boomy

AI音乐生成工具，创建生成音乐,与世界分享.

272

查看详情

$currentWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()])])
    ->orderBy('pictures_count', 'DESC')
    ->limit(10)
    ->get();

$lastWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek()->subWeek(), now()->endOfWeek()->subWeek()])])
    ->orderBy('pictures_count', 'DESC')
    ->limit(10)
    ->get();

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

登录后复制

经过此最终优化，生成的 SQL 查询将不再包含任何 EXISTS 子句：

最终优化后 SQL 片段:

select `users`.*, (
    select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null
) as `pictures_count`
from `users`
where `users`.`deleted_at` is null
    -- 不再有任何 where exists 子句
order by `pictures_count` desc
limit 10

登录后复制

这种方法大大简化了 SQL 查询，减少了数据库执行的复杂性，从而显著提升了查询速度。

注意事项与总结

数据结果变化: 移除 whereHas 后，查询结果中可能会包含 pictures_count 为 0 的用户。这是因为 withCount 即使没有匹配的关联记录也会返回 0，而不再是直接排除这些用户。如果排行榜不希望显示计数为 0 的用户，可以在获取结果后，使用 filter 方法进行二次过滤：
```
$currentWeek = User::withCount(...)
    ->orderBy(...)
    ->limit(10)
    ->get()
    ->filter(fn ($user) => $user->pictures_count > 0);
```
登录后复制
然而，考虑到通常排行榜会按照计数降序排列，并且 limit 10 会优先选取计数最高的，因此在大多数情况下，计数为 0 的用户不会出现在前10名中，除非活跃用户不足10名。
索引的重要性: 为了进一步提升查询性能，务必确保 pictures 表的 user_id 和 created_at 字段上存在合适的数据库索引。例如：
```
ALTER TABLE `pictures` ADD INDEX `pictures_user_id_created_at_index` (`user_id`, `created_at`);
```
登录后复制
这将极大加速 JOIN 和 WHERE 子句的执行。