Eloquent关联计数与排序优化技巧

golang学习网今天将给大家带来《Eloquent查询优化：关联计数与排序提升技巧》，感兴趣的朋友请继续看下去吧！以下内容将会涉及到等等知识点，如果你是正在学习文章或者已经是大佬级别了，都非常欢迎也希望大家都能给我建议评论哈~希望能帮助到大家！

引言：Eloquent 查询性能挑战

在 Laravel 开发中，Eloquent ORM 极大地简化了数据库交互。然而，不当的查询构造方式，尤其是在处理关联数据和聚合操作时，可能导致性能瓶颈。一个常见的场景是，我们需要统计关联模型的数量，并根据这些数量进行排序，例如，统计用户发布的图片数量并生成排行榜。当查询逻辑中存在冗余或重复的条件判断时，数据库查询时间会显著增加，影响用户体验。

考虑以下一个典型的场景，我们需要查询在当前周、上周以及总榜上发布图片最多的用户：

public function show()
{
    // 查询当前周发布图片最多的用户
    $currentWeek = User::whereHas('pictures')
        ->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();

    // 查询上周发布图片最多的用户
    $lastWeek = User::whereHas('pictures')
        ->whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek()->subWeek(), now()->endOfWeek()->subWeek()]))
        ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek()->subWeek(), 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,
    ]);
}

上述代码在执行时可能耗时较长（例如 1.5 秒），主要原因在于其生成的 SQL 语句中包含了冗余的 EXISTS 子句，导致数据库需要执行不必要的检查。

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

在上述原始查询中，以 $currentWeek 为例，我们注意到 whereHas('pictures') 被调用了两次：一次是无条件的，另一次是带有时间范围条件的。

whereHas('pictures') 的作用是确保用户至少拥有一张图片。而 whereHas('pictures', fn ($q) => $q->whereBetween(...)) 则进一步确保用户在指定时间范围内拥有图片。显然，如果用户在指定时间范围内有图片，那么他们也必然有图片。因此，无条件的 whereHas('pictures') 是多余的。

优化前生成的 SQL 片段（针对 currentWeek）：

-- ...
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)
    -- ...

优化代码：

我们移除冗余的 whereHas('pictures') 调用：

$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 片段：

-- ...
where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)
    -- 注意：不再有第二个 where exists 子句
    -- ...

通过这一步，我们减少了一个 EXISTS 子句，使得 SQL 查询更简洁，性能有所提升。

优化步骤二：充分利用 withCount 的能力

进一步分析，我们发现 $currentWeek 查询中 whereHas 的条件与 withCount 的条件是完全相同的。

• whereHas('pictures', fn ($q) => $q->whereBetween(...))：确保用户在指定时间范围内有图片。
• withCount(['pictures' => fn ($q) => $q->whereBetween(...)])：计算用户在指定时间范围内的图片数量，并将结果作为 pictures_count 字段返回。

关键在于，如果一个用户在指定时间范围内没有图片，那么 withCount 会返回 0。由于我们最终是按照 pictures_count 降序排序并取前 10 名，那些计数为 0 的用户自然会排在后面，或者根本不会进入前 10 名。这意味着，whereHas 的存在性检查在这里变得多余。withCount 已经包含了筛选和计数的功能，whereHas 只是在重复其作用。

优化代码：

我们可以完全移除 whereHas 调用，只保留 withCount：

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

优化后生成的 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

通过这一最终优化，我们完全消除了 EXISTS 子句，使得 SQL 查询变得极其高效。数据库不再需要执行额外的存在性检查，只需计算每个用户的图片数量并进行排序。

对数据结果的影响及处理：

这种优化可能会导致返回的集合中包含 pictures_count 为 0 的用户（如果这些用户在排序后仍然进入了前 10 名）。如果你的排行榜不希望显示计数为 0 的用户，可以在获取结果后进行过滤：

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

对于 overall 查询的优化：

同样的原理也适用于 overall 查询。原始的 overall 查询：

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

优化后可以简化为：

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

因为 withCount('pictures') 会计算所有图片，如果用户没有图片，计数为 0，自然会排在后面。

总结与最佳实践

本次优化案例揭示了 Eloquent 查询性能提升的关键原则：

1. 避免冗余查询条件： 仔细检查你的 whereHas 和 withCount 条件，确保没有重复或可以被更强大的子句覆盖的条件。
2. 理解 whereHas 与 withCount 的作用：
   • whereHas 用于检查关联模型是否存在，它生成 EXISTS 子句。
   • withCount 用于计算关联模型的数量，它生成子查询来计算计数。
3. 充分利用 withCount 的筛选能力： 当你需要根据关联模型的计数进行排序，并且 withCount 已经包含了必要的筛选条件时，通常可以省略 whereHas。因为 withCount 会返回 0 作为计数，这在排序中是自然处理的。
4. 检查生成的 SQL： 使用 Laravel Debugbar 或 toSql() 方法来查看 Eloquent 生成的实际 SQL 语句。这是诊断和优化查询性能最直接有效的方法。通过观察 SQL 语句中的 EXISTS 子句数量和复杂性，可以判断查询是否还有优化空间。
5. 考虑索引： 确保你的数据库表（尤其是 pictures 表的 user_id 和 created_at 字段）有合适的索引，这对于关联查询和日期范围查询至关重要。

通过遵循这些最佳实践，你将能够编写出更高效、更具可维护性的 Eloquent 查询，显著提升 Laravel 应用的整体性能。

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~