优化3个Eloquent请求(请求持续时间1.5秒…)

Question

我想优化这3个查询，使我能够根据日期标准(当前周，上周，从开始以来)显示在我们网站上发布最多照片的用户。

在调试栏中，查询时间是1.5秒，它真的很长!你知道如何优化它吗?

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_at', [Carbon::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,
]);
}

Answer 1

首先，您已经就图片关系致电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查询:

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

:

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)
-- no second where exists clause
and `users`.`deleted_at` is null
order by `pictures_count` desc
limit 10

现在，你在where子句中只有一个条件。它选择在指定日期范围内拥有图片的用户。看起来好多了，对吧?

---

但是，您已经使用封闭的withCount，因此您只计算日期范围内的图片。如果条件不匹配怎么办?它返回0。既然您是按计数进行反向排序的，那么对whereHas的另一个调用也可以进行。

$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
-- no where exists clauses at all any more
order by `pictures_count` desc
limit 10

它应该运行得更快。这在一定程度上改变了你的数据;结果集合将始终包含10个项目，即使其中一些为零。如果你不想让排行榜上出现零，那就把它们从集合中过滤掉。