基于Mysql优化解释 [英] Mysql optimization based on explain

问题描述

我有以下查询需要很长时间才能执行。我需要加快速度，但我对于使用什么技术感到很失望。这是查询：

I have the following query that takes a really long time to execute. I need to speed it up, but I'm at a lost as to what technique to use. This is the query:

SELECT 
    `User`.`id`, 
    `User`.`username`, 
    `User`.`password`, 
    `User`.`role`, 
    `User`.`created`, 
    `User`.`modified`, 
    `User`.`email`, 
    `User`.`other_user_id`, 
    `User`.`first_name`, 
    `User`.`last_name`, 
    `User`.`place_id`, 
    `Resume`.`id`, 
    `Resume`.`user_id`, 
    `Resume`.`other_resume_id`, 
    `Resume`.`other_user_id`, 
    `Resume`.`file_extension`, 
    `Resume`.`created`, 
    `Resume`.`modified`, 
    `Resume`.`is_deleted`, 
    `Resume`.`has_file`, 
    `Resume`.`is_stamped`, 
    `Resume`.`is_active` 
FROM 
    `streetofwalls`.`users` AS `User` 
    LEFT JOIN `my_database`.`attempts` AS `Attempt` 
        ON (`Attempt`.`user_id` = `User`.`id` AND `Attempt`.`test_id` != 5) 
    LEFT JOIN `my_database`.`reports` AS `Resume` 
        ON (`Resume`.`user_id` = `User`.`id`) 
WHERE 
    `Attempt`.`test_id` = 8 
    AND `Attempt`.`score` > 60 
    AND `User`.`id` IN (
        SELECT 
            `User1`.`id` 
        FROM 
            `my_database`.`users` AS User1 
            LEFT JOIN `my_database`.`tags_users` AS TagUser 
                ON (`User1`.`id`= `TagUser`.`user_id`) 
            LEFT JOIN `my_database`.`tags` AS Tag 
                ON (`TagUser`.`tag_id`= `Tag`.`id`) 
        WHERE `Tag`.`id` = (8) ) 
    AND `User`.`id` NOT IN (
        SELECT 
            `User1`.`id` 
        FROM 
            `my_database`.`users` AS User1 
            LEFT JOIN `my_database`.`tags_users` AS TagUser 
                ON (`User1`.`id`= `TagUser`.`user_id`) 
            LEFT JOIN `my_database`.`tags` AS Tag 
                ON (`TagUser`.`tag_id`= `Tag`.`id`) 
        WHERE `Tag`.`id` = (3) ) 
    AND `Resume`.`has_file` = 1 
GROUP BY `User`.`id` 
ORDER BY `Attempt`.`score` DESC;

此查询生成以下解释：

This query generates the following explain:

正如您所看到的，我在此查询中有几个索引。目前只有简历表无法编入索引。是否可以在此查询的上下文中索引此表？有没有其他方法来加快这个我没想过的查询？它的预期功能令人望而却步，而且我没有想法。感谢任何能提供帮助的人。如果需要任何其他信息，请告诉我。

As you can see, I have several indexes on this query. At the moment only the resume table is not able to be indexed. Is is possible to index this table in the context of this query? Is there some other way to speed this query up that I have not thought of? Its prohibitively slow for its intended function and I'm out of ideas. Thank you to anyone who can help. Please let me know if any other information is needed.

推荐答案

尝试内部联接而不是子查询
它是默认为引导查询而不运行数据，但可能跟随查询将帮助您。

try inner join instead of sub-query it is default to guide query without running on data,but may be following the query will help you.

SELECT 用户。 id ，用户。用户名，用户。密码，用户。角色，用户。已创建，用户。 已修改，用户。电子邮件，用户。 other_user_id ，用户。 first_name ，用户。 last_name ，用户。 place_id ，恢复。 id ，恢复。 user_id ，恢复。 other_resume_id ，恢复。 other_user_id ，恢复。 file_extension ，恢复。已创建，恢复。已修改，恢复。 is_deleted ，恢复。 has_file ，恢复。 is_stamped ，恢复。 is_active

SELECT User.id, User.username, User.password, User.role, User.created, User.modified, User.email, User.other_user_id, User.first_name, User.last_name, User.place_id, Resume.id, Resume.user_id, Resume.other_resume_id, Resume.other_user_id, Resume.file_extension, Resume.created, Resume.modified, Resume.is_deleted, Resume.has_file, Resume.is_stamped, Resume.is_active

FROM
streetofwalls 。用户 AS 用户

LEFT JOIN my_database 。尝试 AS 尝试 ON（尝试。 user_id = 用户。 id AND 尝试。 test_id ！= 5）
LEFT JOIN my_database 。报告 AS 恢复 ON（恢复。 user_id = 用户。 id ）

FROM streetofwalls.users AS User
LEFT JOIN my_database.attempts AS Attempt ON (Attempt.user_id = User.id AND Attempt.test_id != 5) LEFT JOIN my_database.reports AS Resume ON (Resume.user_id = User.id)

， my_database 。 users AS User1

, my_database.users AS User1

LEFT JOIN my_database 。 tags_users AS TagUser on（ User1 。 id = TagUser 。 user_id ）

LEFT JOIN my_database.tags_users AS TagUser on (User1.id= TagUser.user_id)

LEFT JOIN my_database 标签 AS标签ON（ TagUser 。 tag_id = 标签。 id ）

LEFT JOIN my_database.tags AS Tag ON (TagUser.tag_id= Tag.id)

WHERE
用户。 id = User1 。 id
AND 尝试。 test_id = 8
AND 尝试。得分> 60

AND 恢复。 has_file = 1
AND 标记。 id ='8' AND 标记。 id ！='3'
GROUP BY 用户。 id
ORDER BY 尝试。得分 DESC;

WHERE User.id = User1.id AND Attempt.test_id = 8 AND Attempt.score > 60
AND Resume.has_file = 1 AND Tag.id = '8' AND Tag.id != '3' GROUP BY User.id ORDER BY Attempt.score DESC;

这篇关于基于Mysql优化解释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！