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

Eloquent 查询优化：提升关联计数与排序性能

心靈之曲

发布： 2025-07-29 14:44:23

原创

291人浏览过

Eloquent 查询优化：提升关联计数与排序性能

本文旨在深入探讨并优化 Laravel Eloquent 中常见的关联查询性能问题，特别是涉及 whereHas 和 withCount 的组合使用。通过逐步分析冗余代码并充分利用 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)
    -- ...

登录后复制

优化代码：

知我AI

一款多端AI知识助理，通过一键生成播客/视频/文档/网页文章摘要、思维导图，提高个人知识获取效率；自动存储知识，通过与知识库聊天，提高知识利用效率。

101

查看详情

我们移除冗余的 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 查询性能提升的关键原则：

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