Eloquent查询优化：提升关联数据统计性能-php教程-PHP中文网

Eloquent查询优化：提升关联数据统计性能

心靈之曲

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

原创

397人浏览过

Eloquent查询优化：提升关联数据统计性能

本文深入探讨了如何优化Laravel Eloquent中涉及关联模型数据统计的慢查询问题。通过分析whereHas和withCount的冗余用法，逐步演示了如何精简查询逻辑，消除不必要的数据库操作，从而显著提升查询性能。教程强调了理解Eloquent底层SQL的重要性，并提供了具体的优化策略和代码示例，帮助开发者构建更高效的数据库交互。

1. 原始问题分析

在laravel应用开发中，当需要统计关联模型的数据并进行排序时，不当的eloquent查询写法可能导致严重的性能瓶颈。一个常见的场景是，我们需要查询在特定时间范围内（如本周、上周、总计）发布照片最多的用户列表。以下是一个典型的、但效率低下的查询示例：

public function show()
{
    // 查询本周发布照片最多的用户
    $currentWeek = User::whereHas('pictures') // 冗余的whereHas
        ->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
        ->whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()]))
        ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()])])
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    // 查询总计发布照片最多的用户
    $overall = User::whereHas('pictures') // 冗余的whereHas
        ->whereHas('pictures') // 冗余的whereHas
        ->withCount('pictures')
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

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

登录后复制

上述代码在实际运行时可能耗时1.5秒甚至更久，主要原因是查询中存在冗余的whereHas调用。例如，对于$currentWeek查询，它生成类似如下的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 exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `pictures`.`deleted_at` is null) -- 第一个whereHas
    and exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null) -- 第二个whereHas
    and `users`.`deleted_at` is null
    order by `pictures_count` desc
    limit 10

登录后复制

whereHas方法会生成一个EXISTS子查询来判断是否存在关联记录。当存在多个whereHas调用，或者whereHas的条件与withCount的条件重复时，就会产生不必要的数据库查询，显著降低性能。

2. 优化策略一：消除冗余的whereHas

仔细观察原始代码，可以发现每个查询都调用了两次whereHas。例如，->whereHas('pictures') 是一个无条件的检查，它只判断用户是否有任何照片。而紧随其后的 ->whereHas('pictures', fn ($q) => $q->whereBetween(...)) 则是在特定日期范围内检查照片。

由于第二个whereHas已经包含了更具体的条件，并且如果用户在指定日期范围内没有照片，那么他们也不会满足第一个无条件whereHas的要求。因此，第一个无条件的whereHas是完全冗余的。

移除这个冗余调用后的代码如下：

$currentWeek = User::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();

登录后复制

此时生成的SQL将只包含一个EXISTS子查询：

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 exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)
    and `users`.`deleted_at` is null
    order by `pictures_count` desc
    limit 10

登录后复制

这已经是一个改进，但仍有进一步优化的空间。

3. 优化策略二：深入理解withCount与whereHas的协同

withCount方法不仅可以统计关联模型的数量，还可以通过闭包传入条件来限制统计范围。更重要的是，它会将统计结果作为一个新的字段（例如pictures_count）添加到主模型中，并且这个字段可以直接用于排序。

考虑到我们的目标是获取按照片数量排序的用户列表，即使某个用户在指定时间范围内没有照片，其pictures_count也会是0。由于我们最终会按pictures_count降序排序并限制结果数量，那些照片数量为0的用户自然会排在后面，甚至不会出现在前10名中。

网龙b2b仿阿里巴巴电子商务平台

本系统经过多次升级改造，系统内核经过多次优化组合，已经具备相对比较方便快捷的个性化定制的特性，用户部署完毕以后，按照自己的运营要求，可实现快速定制会费管理，支持在线缴费和退费功能财富中心，管理会员的诚信度数据单客户多用户登录管理全部信息支持审批和排名不同的会员级别有不同的信息发布权限企业站单独生成，企业自主决定更新企业站信息留言、询价、报价统一管理，分系统查看分类信息参数化管理，支持多样分类信息，

查看详情

这意味着，whereHas的判断（用户是否存在满足条件的照片）在很多情况下是多余的。因为withCount已经完成了计数，并且我们依赖这个计数进行排序和筛选。如果一个用户在指定时间范围内没有照片，withCount会返回0，这与whereHas排除该用户达到的效果是等效的（因为0会使其在降序排列中靠后）。

基于此理解，我们可以完全移除whereHas调用，仅依赖withCount进行计数和排序：

public function show()
{
    // 查询本周发布照片最多的用户 (优化后)
    $currentWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])])
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    // 查询上周发布照片最多的用户 (优化后)
    $lastWeek = User::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::withCount('pictures')
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

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

登录后复制

现在，生成的SQL将变得非常简洁，不再包含任何EXISTS子查询：

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
order by `pictures_count` desc
limit 10

登录后复制

通过移除where exists子句，数据库查询的复杂性大大降低，执行效率会得到显著提升。

注意事项

这种优化确实改变了结果集。原始查询只返回那些在指定时间范围内至少有一张照片的用户。而优化后的查询会返回10个用户，即使其中一些用户的pictures_count为0（即他们在指定时间内没有照片）。

如果你的业务逻辑要求只显示有照片的用户，你可以选择在获取结果后进行过滤：

$currentWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])])
    ->orderBy('pictures_count', 'DESC')
    ->limit(10)
    ->get()
    ->filter(fn ($user) => $user->pictures_count > 0); // 过滤掉照片数为0的用户

登录后复制

然而，对于排行榜这类场景，通常直接显示前N名（即使某些用户照片数为0）并无大碍，甚至可以帮助识别活跃度较低的用户。

4. 总结与最佳实践

理解底层SQL： Eloquent的便利性有时会掩盖其生成的实际SQL。使用Laravel Debugbar等工具检查生成的SQL语句是优化查询的关键第一步。理解whereHas如何转换为EXISTS子查询，以及withCount如何转换为子查询或JOIN，有助于识别性能瓶颈。
避免冗余条件： 当withCount或withSum等聚合方法已经包含了所需的过滤条件，并且你主要关心聚合结果的排序时，whereHas可能不再是必需的。仔细分析业务逻辑，避免重复的筛选条件。
索引优化： 确保数据库表中相关的列（如pictures.created_at和pictures.user_id）有合适的索引。这对于whereBetween和关联查询的性能至关重要。
按需加载： 对于不需要的关联数据，避免使用with或load方法，以减少内存消耗和不必要的JOIN操作。
批量操作： 对于需要多次执行类似查询的场景，考虑是否可以合并为一次更复杂的查询，或者使用数据库视图、存储过程等方式来优化。