如何提高查询性能(使用explain命令结果f.e.) [英] How to improve query performance (using explain command results f.e.)

问题描述

我当前正在运行此查询.但是，在phpMyAdmin外部运行时，会导致504超时错误.我认为这与查询返回或访问的行数的效率有关.

I'm currently running this query. However, when run outside phpMyAdmin it causes a 504 timeout error. I'm thinking it has to do with how efficient the number of rows is returned or accessed by the query.

我对MySQL的经验不是很丰富，所以这是我能做的最好的事情:

I'm not extremely experienced with MySQL and so this was the best I could do:

SELECT
                    s.surveyId,
                    q.cat,
                    SUM((sac.answer_id*q.weight))/SUM(q.weight) AS score,
                    user.division_id,
                    user.unit_id,
                    user.department_id,
                    user.team_id,
                    division.division_name,
                    unit.unit_name,
                    dpt.department_name,
                    team.team_name
                FROM survey_answers_cache sac
                    JOIN surveys s ON s.surveyId = sac.surveyid
                    JOIN subcluster sc ON s.subcluster_id = sc.subcluster_id
                    JOIN cluster c ON sc.cluster_id = c.cluster_id
                    JOIN user ON user.user_id = sac.user_id
                    JOIN questions q ON q.question_id = sac.question_id
                    JOIN division ON division.division_id = user.division_id
                    LEFT JOIN unit ON unit.unit_id = user.unit_id
                    LEFT JOIN department dpt ON dpt.department_id = user.department_id
                    LEFT JOIN team ON team.team_id = user.team_id
                WHERE c.cluster_id=? AND sc.subcluster_id=? AND s.active=0 AND s.prepare=0
                GROUP BY user.team_id, s.surveyId, q.cat
                ORDER BY s.surveyId, user.team_id, q.cat ASC

此查询的问题是，当我返回正确的结果时，它会快速运行(例如+ -500ms)，但是当结果的行数是原来的两倍时，则需要5分钟以上的时间，然后导致504暂停. 另一个问题是我自己没有创建这个数据库，所以我没有自己设置索引.我正在考虑改进这些功能，因此我使用了explain命令:

The problem I get with this query is that when I get a correct result returned it runs quickly (let's say +-500ms) but when the result has twice as much rows, it takes more than 5 minutes and then causes a 504 timeout. The other problem is that I didn't create this database myself, so I didn't set the indices myself. I'm thinking of improving these and therefore I used the explain command:

我看到了很多主键和几个双索引，但是我不确定这是否会极大地影响性能.

I see a lot of primary keys and a couple double indices, but I'm not sure if this would affect the performance this greatly.

编辑:这段代码占用了所有执行时间:

This piece of code takes up all the execution time:

$start_time = microtime(true);
$stmt = $conn->query($query); //query is simply the query above.
while ($row = $stmt->fetch_assoc()){
    $resultSurveys["scores"][] = $row;
}
$stmt->close();
$end_time = microtime(true);
$duration = $end_time - $start_time; //value typically the execution time #reallyHigh...

我的问题 :是否有可能(通过改变数据库密钥来(极大地)提高查询性能)还是应该将查询分为多个较小的查询?

So my question: Is it possible to (greatly?) improve the performance of the query by altering the database keys or should I divide my query into multiple smaller queries?

推荐答案

EXPLAIN结果显示出问题的迹象

The EXPLAIN result is showing signs of problem

使用临时；使用文件排序:ORDER BY需要创建临时表来进行排序.

Using temporary;using filesort: the ORDER BY needs to create temporary tables to do the sorting.

用户表type的第三行为ALL，key和ref为NULL:表示每次需要扫描整个表以检索结果.

On 3rd row for user table type is ALL, key and ref are NULL: means that it needs to scan the whole table each time to retrieve results.

建议:

1. 在user.cluster_id以及ORDER BY和GROUP BY子句中涉及的所有字段上添加索引.请记住，用户表似乎在changein数据库下(跨数据库查询).
2. 在与JOIN相关的用户列上添加索引.
3. 将索引添加到s.survey_id
4. 如果可能，请为GROUP BY和ORDER BY子句保留相同的顺序
5. 根据此问题的已接受答案将用户表上的JOIN移到联接队列中的第一位置.
6. 请仔细阅读官方文档 .您可能需要优化服务器配置.

1. add indexes on user.cluster_id and all fields involved on the ORDER BY and GROUP by clauses. Keep in mind that user table seems to be under changein database (cross database query).
2. Add indexes on user columns involved on JOINs.
3. Add index to s.survey_id
4. If possible, keep the same sequence for GROUP BY and ORDER BY clauses
5. According to the accepted answer in this question move the JOIN on user table to the first position in the join queue.
6. Carefully read this official documentation. You may need to optimize the server configuration.

PS:查询优化是一门需要耐心和辛勤工作的艺术.没有银弹. 欢迎使用优化MySQL的精湛技巧！

PS: query optimization is an art that requires patience and hard work. No silver bullet for that. Welcome to the fine art of optimizing MySQL!

这篇关于如何提高查询性能(使用explain命令结果f.e.)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！