Eloquent关联查询优化方法分享

本文深入剖析了Laravel Eloquent关联查询中常见的性能问题，尤其是在统计关联模型数据并进行排序时。通过实例分析，揭示了`whereHas`和`withCount`的冗余使用是导致慢查询的关键原因。文章提供了清晰的优化策略，包括消除不必要的`whereHas`调用，以及充分利用`withCount`进行计数和排序，避免生成复杂的EXISTS子查询。教程强调理解Eloquent底层SQL的重要性，并辅以优化后的代码示例，旨在帮助开发者编写更高效的Eloquent查询，提升Laravel应用的数据库交互性能，从而构建更流畅的用户体验。掌握这些技巧，让你的Laravel应用在处理关联数据时更加得心应手。

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名中。

这意味着，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. 总结与最佳实践

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

通过以上优化策略，可以显著提升Laravel应用中涉及关联模型数据统计的查询性能，从而提供更流畅的用户体验。

今天关于《Eloquent关联查询优化方法分享》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于的内容请关注golang学习网公众号！