mysql - SELECT 查询偶尔会执行很长时间

Question

我的 MySQL InnoDB 数据库中有一个非常奇怪的问题。我有以下查询:

SELECT DISTINCT p.idProject AS idProject, p.name AS name, 0 AS isConfirm
FROM Projects p
    JOIN team_project tp ON (p.idProject = tp.idProject) 
    JOIN projtimes pt ON (p.idProject = pt.idProject) 
    JOIN CalledTimesTbl ctt ON (p.idProject = ctt.idProject)
    LEFT JOIN NextCalls nc ON (ctt.idCustomer = nc.idCustomer 
        AND ctt.idProject = nc.idProject) 
WHERE tp.idTeam = 158
    AND p.activated = 1 
    AND current_date >= p.projStart
    AND current_date < p.confirmStart 
    AND pt.invitesCount < pt.maxPerPresentation
    AND (nc.idCustomer IS NULL OR nc.nextCall < now())
ORDER BY p.name

通常查询运行良好，但有时 - 例如当我设置 tp.idTeam = 147 时，它运行得非常慢(比如 10 或 20 秒)。当我创建替代团队并调整适当的表值以获得具有不同 idTeam 值的相同结果时 - 查询在几分之一秒内执行。

我分析了查询并注意到当查询执行缓慢时 - 有一件事情消耗了大部分时间:

Copying to tmp table      | 12.489197

我对查询创建了一个 tmp 表感到有点惊讶，但是没关系——它会在每次查询执行时创建它——当它执行得很快时也是如此。我只是补充说数据库设计得很好，有所有需要的外键等。

如何找到执行缓慢的根源并消除它？

编辑:EXPLAIN 结果:

id   select_type   table   type     possible_keys                    key              key_len   ref                                                   rows   Extra                             
1    SIMPLE        tp      ref      unique_row,idTeam                idTeam           4         const                                                 56     Using temporary; Using filesort   
1    SIMPLE        p       eq_ref   PRIMARY,projStart,confirmStart   PRIMARY          4         xxx.tp.idProject                                      1      Using where                       
1    SIMPLE        pt      ref      uniq_projtimes                   uniq_projtimes   4         xxx.tp.idProject                                      1      Using where; Distinct             
1    SIMPLE        ctt     ref      idProject                        idProject        4         xxx.tp.idProject                                      3966   Using index; Distinct             
1    SIMPLE        nc      eq_ref   PRIMARY,idProject                PRIMARY          8         xxx.ctt.idCustomer,xxx.tp.idProject                   1      Using where; Distinct     

EDIT2:EXPLAIN EXTENDED 的结果首先用于快速查询，其次用于慢速查询。

id   select_type   table   type     possible_keys                    key              key_len   ref                                           rows    filtered   Extra                  1    SIMPLE        tp      ref      unique_row,idTeam                idTeam           4         const                                                 1       100        Using temporary         
1    SIMPLE        p       eq_ref   PRIMARY,projStart,confirmStart   PRIMARY          4         xxx.tp.idProject                              1       100        Using where             
1    SIMPLE        pt      ref      uniq_projtimes                   uniq_projtimes   4         xxx.tp.idProject                              1       100        Using where; Distinct   
1    SIMPLE        ctt     ref      idProject                        idProject        4         xxx.tp.idProject                              46199   100        Using index; Distinct   
1    SIMPLE        nc      eq_ref   PRIMARY,idProject                PRIMARY          8         xxx.ctt.idCustomer,xxx.tp.idProject           1       100        Using index; Distinct  

id   select_type   table   type     possible_keys                    key              key_len   ref                                           rows   filtered   Extra                                
1    SIMPLE        p       eq_ref   PRIMARY,projStart,confirmStart   PRIMARY          4         xxx.ctt.idProject                             1      100        Using where                          
1    SIMPLE        pt      ref      uniq_projtimes                   uniq_projtimes   4         xxx.ctt.idProject                             1      100        Using where; Distinct                
1    SIMPLE        tp      ref      unique_row,idTeam                unique_row       8         xxx.pt.idProject,const                        1      100        Using where; Using index; Distinct   
1    SIMPLE        nc      eq_ref   PRIMARY,idProject                PRIMARY          8         xxx.ctt.idCustomer,xxx.tp.idProject           1      100        Using index; Distinct  

Answer 1

试试这个调整后的查询。 (它将加入更少的行)

SELECT DISTINCT p.idProject AS idProject, p.name AS name, 0 AS isConfirm
FROM Projects p
    JOIN projtimes pt ON 
        p.idProject = pt.idProject
        AND p.activated = 1
        AND current_date >= p.projStart
        AND current_date < p.confirmStart
        AND pt.invitesCount < pt.maxPerPresentation
    JOIN team_project tp ON 
        p.idProject = tp.idProject
        AND tp.idTeam = 158
    JOIN CalledTimesTbl ctt ON (p.idProject = ctt.idProject)
    LEFT JOIN NextCalls nc ON (ctt.idCustomer = nc.idCustomer 
        AND ctt.idProject = nc.idProject) 
WHERE (nc.idCustomer IS NULL OR nc.nextCall < now())
ORDER BY p.name