基于explain的Mysql优化

Question

我有以下查询需要很长时间才能执行。我需要加快速度，但我不知道该使用什么技术。以下是查询：

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;

此查询生成以下解释：

​

如您所见，我对这个查询有几个索引。目前，只有简历表不能被索引。是否可以在此查询的上下文中对此表进行索引？有没有其他方法可以加速这个查询，这是我没有想到的？对于它的预期功能来说，它的速度慢得令人望而却步，我已经没有想法了。感谢任何能帮上忙的人。如果还需要任何其他信息，请告诉我。

Answer 1

尝试内部连接而不是子查询默认情况下，引导查询时不对数据运行，但跟随查询可能会对您有所帮助。

选择User。id，User。username，User。password，User。role，User。D9，D10。D11，D12。D13，D26。D15，D16。D17，D18。D19，D20。D21，D22。D23，D24。D25，D26。other_resume_id，Resume。other_user_id，D30/code，31。Resume。created，Resume。modified，Resume。is_deleted，Resume。has_file，Resume。<代码>D41，<代码>D42。<代码>D43

来自streetofwalls。users AS User

左加入my_database。attempts AS Attempt ON (Attempt.user_id = User.id和Attempt.test_id != 5)左加入D56。D57 AS D58 ON (D59.D60=D61.D62)

，my_database。users AS User1

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

左连接my_database.tags AS Tag ON (TagUser.tag_id= Tag.id)

 AND `Resume`.`has_file` = 1      AND `Tag`.`id` = '8'  AND `Tag`.`id` != '3' GROUP BY `User`.`id`  ORDER BY `Attempt`.`score` DESC;