Laravel 中缓慢的 MySQL 查询在其他地方很快
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31225703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Slow MySQL query in Laravel is fast elsewhere
提问by Christoffer
I'm using Eloquent in a Laravel 4.2 application. One of my controller comes back with a view after about 6000ms, which is way too long. It processes data from a table called responses. It works pretty ok with a couple of thousand rows, but when I'm over 30000 it starts getting really slow.
我在 Laravel 4.2 应用程序中使用 Eloquent。我的一个控制器在大约 6000 毫秒后返回一个视图，这太长了。它处理来自名为 的表中的数据responses。它在几千行的情况下工作得很好，但是当我超过 30000 行时，它开始变得非常慢。
Here's the weird part.
If I check my DB::getQueryLog();I can see this query:
这是奇怪的部分。如果我检查我的DB::getQueryLog();我可以看到这个查询：
[1] => Array
(
[query] => select * from `response` where `survey_id` = ? and `question_id` = ? and `resp_group` in (?, ?)
[bindings] => Array
(
[0] => 48
[1] => 25
[2] => a
[3] => b
)
[time] => 11
)
which I assume would result in this actual query:
我认为这会导致这个实际的查询：
select * from `response`
where `survey_id` = 48
and `question_id` = 25
and `resp_group` in ('a', 'b');
As you can see the time is pretty high there. And the log tells me I run queries like this about 40 times with time between 7 and 11.
如您所见，那里的时间相当长。日志告诉我，我运行了大约 40 次这样的查询，时间在 7 到 11 之间。
But if I run the same query in HediSQL I get 0,000 sec and 0.0010 in PHPMyAdmin. I have to run 20 of them to get 0,015 seconds.
但是如果我在 HediSQL 中运行相同的查询，我会在 PHPMyAdmin 中得到 0,000 秒和 0.0010。我必须运行 20 次才能获得 0,015 秒。
Explain gives this:
解释给出了这个：
select_type: SIMPLE
table: response
possible_keys:
question_id_4,
resp_group_survey_id,
survey_id,
resp_group_question_id_survey_id
key: question_id_4
key_len: 8
ref: const,const
rows: 949
Extra: Using where
So there are indexes. How come the same query is so slow when Eloquent performs it and so much faster when I run it directly on the MySQL Server?
所以有索引。为什么同样的查询在 Eloquent 执行时如此之慢，而当我直接在 MySQL 服务器上运行它时却如此之快？
(I'm running on my Local WAMP server)
（我在我的本地 WAMP 服务器上运行）
回答by jedrzej.kurylo
The time in DB::getQueryLog()is reported in milliseconds so it is more or less the same to what PHPMyAdmin reports.
DB::getQueryLog() 中的时间以毫秒为单位报告，因此它或多或少与 PHPMyAdmin 报告的相同。
The reason why controller method takes 6 seconds to execute is probably related to the amount of data (30000 rows) that Eloquent needs to process after it fetches the data from the database.
控制器方法执行需要 6 秒的原因可能与 Eloquent 从数据库中获取数据后需要处理的数据量（30000 行）有关。
If you want to make that faster use DB class instead.
如果您想加快速度，请改用 DB 类。
DB::table('response')
->whereSurveyId($survey_id)
->whereQuestionId($question_id)
->whereIn('resp_group', $groups)
->get();
It might be also helpful to provide a list of required columns to get()so that you fetch less data from the database.
为get()提供所需列的列表也可能会有所帮助，以便您从数据库中获取更少的数据。
