PHP PDO在SELECT上的速度太慢，有些联接 - PHP PDO too slow on SELECT with some joins

我在PHP PDO中执行select时遇到性能问题。

使用stackoverflow上的脚本（评测PHP脚本的最简单方法），我确定了问题所在，但还没有找到解决方案。

我选择的问题是：

SELECT REDACAO.ID_REDACAO AS ID_REDACAO, 
    DATE_FORMAT(REDACAO.DATA,'%d/%m/%Y') AS DATAE, 
    ALUNO.ID_ALUNO AS ID_ALUNO, 
    (SELECT IFNULL((DATEDIFF(DATE_ADD((SELECT MAX(DATA) FROM REDACAO WHERE ID_ALUNO = ALUNO.ID_ALUNO AND ID_REDACAO NOT IN (SELECT ID_REDACAO FROM CORRECAO)), INTERVAL 7 DAY), now())),NULL) as DATA FROM REDACAO LIMIT 1) AS ULTIMA, 
    ALUNO.NOME as ALUNO, 
    REDACAO.ID_TEMA AS ID_TEMA,
    TEMA.TITULO as TEMA,
    TEMA.MOTIVACIONAIS AS MOTIVACIONAIS, 
    REDACAO.TEXTO AS TEXTO, 
    REDACAO.ID_STATUS AS STATUS,
    B.NOTA as NOTA, 
    B.RCORRIGIDA AS CORRIGIDA,
    B.NOTA1,
    B.COMENTARIO1,
    B.NOTA2,
    B.COMENTARIO2,
    B.NOTA3,
    B.COMENTARIO3,
    B.NOTA4,
    B.COMENTARIO4,
    B.NOTA5,
    B.COMENTARIO5,
    B.COMENTARIO6,
    C.COMENTARIO AS COMENTARIO 
FROM REDACAO 
    LEFT OUTER JOIN (SELECT SUM(CORRECAO.C1+CORRECAO.C2+CORRECAO.C3+CORRECAO.C4+CORRECAO.C5) AS NOTA, RCORRIGIDA AS RCORRIGIDA, CORRECAO.C1 as NOTA1, CORRECAO.COM1 as COMENTARIO1, CORRECAO.C2 as NOTA2, CORRECAO.COM2 as COMENTARIO2, CORRECAO.C3 as NOTA3, CORRECAO.COM3 as COMENTARIO3, CORRECAO.C4 as NOTA4, CORRECAO.COM4 as COMENTARIO4, CORRECAO.C5 as NOTA5, CORRECAO.COM5 as COMENTARIO5, CORRECAO.COMGERAL AS COMENTARIO6, CORRECAO.ID_REDACAO FROM CORRECAO GROUP BY CORRECAO.ID_REDACAO) B 
    ON B.ID_REDACAO = REDACAO.ID_REDACAO 
    JOIN ALUNO ON ALUNO.ID_ALUNO = REDACAO.ID_ALUNO 
    JOIN TEMA ON TEMA.ID_TEMA = REDACAO.ID_TEMA 
    LEFT OUTER JOIN (SELECT (COUNT(COMENTARIO.ID_COMENTARIO)) AS COMENTARIO, COMENTARIO.ID_REDACAO FROM COMENTARIO GROUP BY COMENTARIO.ID_REDACAO) C 
    ON C.ID_REDACAO = REDACAO.ID_REDACAO 
WHERE REDACAO.ID_PROFESSOR = $CodProfessor 
    and REDACAO.ID_STATUS != 6 
ORDER BY (CASE WHEN REDACAO.ID_STATUS = 4 THEN 1 ELSE 0 END) DESC

我正在使用（PDO:：FETCH_ASSOC）来获取数据。有些列的响应时间不到1秒，而另一些则超过20秒。

你知道问题出在哪里以及如何解决吗？

您的查询包含以下内容，这些内容会减慢它的速度：

多个联接
许多子选择
不带位置的选择
函数，如COUNT、isnull、datediff、sum。（其中一些可能会取消索引）
当
订购人
分组依据

根据索引、表的连接方式以及表的大小，这最终会变得非常缓慢。

请尝试使用"explain"命令，并尽可能简化查询。

解释输出

一个关于解释的好视频

PDO没有故障；查询很复杂。而且可能缺少索引。

将其转换为LEFT JOIN（因为IN (SELECT...)优化较差。

AND  ID_REDACAO NOT IN (  SELECT  ID_REDACAO FROM  CORRECAO)

升级至5.6；它有一些改进。

你有两个JOIN ( SELECT ... )。在5.6之前，它会被严重优化。将其中一个移到临时表中，在其中添加合适的索引。

在其中一个子查询中，GROUP BY CORRECAO.ID_REDACAO似乎是不必要的。

需要这些索引（或PRIMARY KEYs）：

CORRECAO：（ID_REDACAO）
REDACAO:（ID_REDACAO），（ID_PROFESSOR）
阿鲁诺：（ID_ALUNO）
TEMA:（ID_TEMA）
COMENTARIO：（ID_REDAAO，ID_COMENTARIO）（"化合物索引"）

如果这些建议还不够帮助，请为每个表返回SHOW CREATE TABLE。