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 甚至可以完全移除：
$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 子句的执行。
通过以上优化，我们成功地将 Laravel Eloquent 查询中的冗余条件移除，利用 withCount 的特性简化了 SQL，从而大幅提升了用户排行榜功能的查询效率。在编写 Eloquent 查询时，深入理解各个方法的内部机制及其生成的 SQL 是优化性能的关键。
