php - Laravel 的 whereHas 表现不佳

Question

我想对关系应用where 条件。这是我的做法:

Replay::whereHas('players', function ($query) {
    $query->where('battletag_name', 'test');
})->limit(100);

它生成以下查询:

select * from `replays` 
where exists (
    select * from `players` 
    where `replays`.`id` = `players`.`replay_id` 
      and `battletag_name` = 'test') 
order by `id` asc 
limit 100;

在 70 秒内执行。如果我像这样手动重写查询:

select * from `replays` 
where id in (
    select replay_id from `players` 
    where `battletag_name` = 'test') 
order by `id` asc 
limit 100;

它在 0.4 秒内执行。如果 where exists 这么慢，为什么它是默认行为？有没有一种方法可以使用查询构建器生成正确的where in 查询，或者我是否需要注入(inject)原始 SQL？也许我完全做错了什么？

replays 表有 4M 行，players 有 40M 行，所有相关列都已索引，数据集不适合 MySQL 服务器内存。

更新:发现可以生成正确的查询为:

Replay::whereIn('id', function ($query) {
    $query->select('replay_id')->from('players')->where('battletag_name', 'test');
})->limit(100);

还有一个问题，为什么 exists 表现如此糟糕，为什么它是默认行为

Answer 1

laravel has(whereHas) 有时慢的原因是用 where exists 语法实现的。

例如:

// User hasMany Post
User::has('posts')->get();
// Sql: select * from `users` where exists (select * from `posts` where `users`.`id`=`posts`.`user_id`)

'exists'语法是循环到外部表，然后每次都查询内部表(subQuery)。

但是当users表有大量数据时会出现性能问题，因为上面的sql select * from 'users' where exists... 无法使用索引。

它可以在这里使用where in而不是where exists而不破坏结构。

// select * from `users` where exists (select * from `posts` where `users`.`id`=`posts`.`user_id`)
// =>
// select * from `users` where `id` in (select `posts`.`user_id` from `posts`)

这将大大提高性能!

我推荐你试试这个包hasin ，在上面的例子中，你可以使用hasin代替has。

// User hasMany Post
User::hasin('posts')->get();
// Sql: select * from `users` where `id` in (select `posts`.`user_id` from `posts`)

与框架has相比，hasin只是只使用了where in语法，而不是where exists，但是其他地方都是一样的，比如参数和调用方式，甚至是代码实现，都可以放心使用。