在Laravel中表现欠佳的地方 [英] Poor whereHas performance in Laravel

问题描述

我想对关系应用where条件.这是我的工作:

I want to apply a where condition to relation. Here's what I do:

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秒内执行.如果我这样手动重写查询:

Which executes in 70 seconds. If I manually rewrite query like this:

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查询，还是我需要注入原始SQL?也许我完全做错了什么?

It executes in 0.4 seconds. Why where exists is the default behavior if it's so slow? Is there a way to generate the correct where in query with query builder or do I need to inject raw SQL? Maybe I'm doing something wrong altogether?

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

replays table has 4M rows, players has 40M rows, all relevant columns are indexed, dataset doesn't fit into MySQL server memory.

更新:发现可以通过以下方式生成正确的查询:

Update: found that the correct query can be generated as:

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

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

Still have a question why exists performs so poorly and why it is the default behavior

推荐答案

这与mysql有关，与laravel有关.您可以同时使用 joins 和子查询这两个选项来执行上述操作. 子查询通常比加入要慢得多.

This is related to the mysql not to the laravel. You can perform the same thing you wanted from the above with the both options, joins and the subqueries. Subqueries are generally much slower than joins.

子查询是:

• 不太复杂
• 优雅
• 更容易理解
• 更容易写
• 逻辑分离

以上事实是为什么雄辩的ORM使用求购的原因. 但是速度较慢！尤其是当数据库中有很多行时.

and the above facts are why ORMs like eloquent are using suquries. but there are slower! Especially when you have many rows in the database.

查询的加入版本是这样的:

Join version of your query is something like this :

select * from `replays`
join `players` on `replays`.`id` = `players`.`replay_id` 
and `battletag_name` = 'test'
order by `id` asc 
limit 100;

但是现在您必须更改选择并添加分组依据，并在许多其他事情上要小心，但是为什么这样做却超出了答案.新查询为:

but now you must change select and add group by and be careful on many other things, but why is this so it is beyond that answer. New query would be :

select replays.* from `replays`
join `players` on `replays`.`id` = `players`.`replay_id` 
and `battletag_name` = 'test'
order by `id` asc 
group by replays.id
limit 100;

所以这就是为什么加入更加复杂的原因.

So that are the reasons why join in more complicated.

您可以在laravel中编写原始查询，但是对连接查询的雄辩支持并不很好，也没有太多可以帮助您的软件包，例如: https://github.com/fico7489/laravel-eloquent-join

You can write raw query in laravel, but eloquent support for join queries are not well supported, also there are no much packages that can help you with that, this one is for example : https://github.com/fico7489/laravel-eloquent-join

这篇关于在Laravel中表现欠佳的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！