通过在特定表上添加联接，SQL查询速度非常慢。

Question

首先，我不太确定我是否应该在这里张贴我的问题。

我有一个带有多个联接的简单SQL查询。

SELECT
    ho.id_host AS "ID",
    ho.Hostname AS "Nom",
    CONCAT(COUNT(DISTINCT ip.id_ip), " IP") AS "IP",
    GROUP_CONCAT(ip.IP_Address SEPARATOR ", ") AS "IPList",
    ho.OS,
    ho.Version AS "Version OS",
    CONCAT(COUNT(DISTINCT us.id_user), " utilisateur(s)") AS "Utilisateurs",
    GROUP_CONCAT(us.Nom_user SEPARATOR ", ") AS "UtilisateursList",
    CONCAT(COUNT(DISTINCT co.id_composant), " composant(s)") AS "Composants",
    GROUP_CONCAT(co.Nom_composant SEPARATOR ", ") AS "ComposantsList"
FROM
    Host ho
LEFT OUTER JOIN
    IP ip ON ho.id_host = ip.id_host
LEFT OUTER JOIN
    User us ON ho.id_host = us.id_host
LEFT OUTER JOIN
    Composant co ON us.id_user = co.id_user
GROUP BY
    ho.id_host;

这个查询需要0.250~0.300秒才能完成。如果我添加所需的最后一个联接，则查询需要9~12秒才能完成。我也不知道为什么。

LEFT OUTER JOIN
    Informations inf ON ho.id_host = inf.id_host

最后一个表是数据库中最小的表之一，只有172行。JOIN的ON子句比较两个int而不是两个varchar。如果删除除此之外的每个联接，则查询只需0,072秒即可完成。

我想知道，为什么在向最小的表中添加一个连接时，这个查询还需要10秒才能完成。此外，我还有一些查询，在更大的表上有更多的联接，它们运行得更快。

编辑： EXPLAIN给出如下内容：

+----+-------------+-------+------+---------------+---------+---------+-----------------+------+---------------------------------+
| id | select_type | table | type | possible_keys |   key   | key_len |       ref       | rows |              Extra              |
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+---------------------------------+
|  1 |      SIMPLE |    ho |  ALL |          NULL |    NULL |    NULL |            NULL |  352 | Using temporary; Using filesort |
|  1 |      SIMPLE |    ip |  ref |       id_host | id_host |       5 | cmdb.ho.id_host |   11 |                                 |
|  1 |      SIMPLE |    us |  ref |       id_host | id_host |       5 | cmdb.ho.id_host |   32 |                                 |
|  1 |      SIMPLE |    co |  ref |       id_user | id_user |       5 | cmdb.us.id_user |   11 |                                 |
|  1 |      SIMPLE |   inf |  ALL |          NULL |    NULL |    NULL |            NULL |  172 |                                 |
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+---------------------------------+

Answer 1

对于那些有同样问题的人，@Madhur Bhaiya和@Gordon Linoff帮助我将时间从10秒缩短到0，4秒。

我添加了ORDER BY NULL (-2s)，并在Informations(id_host) (-7,6s)上创建了一个索引。最后的SQL查询如下所示：

SELECT
    ho.id_host AS "ID",
    ho.Hostname AS "Nom",
    CONCAT(COUNT(DISTINCT ip.id_ip), " IP") AS "IP",
    GROUP_CONCAT(ip.IP_Address SEPARATOR ", ") AS "IPList",
    ho.OS,
    ho.Version AS "Version OS",
    CONCAT(COUNT(DISTINCT us.id_user), " utilisateur(s)") AS "Utilisateurs",
    GROUP_CONCAT(us.Nom_user SEPARATOR ", ") AS "UtilisateursList",
    CONCAT(COUNT(DISTINCT co.id_composant), " composant(s)") AS "Composants",
    GROUP_CONCAT(co.Nom_composant SEPARATOR ", ") AS "ComposantsList",
    inf.Jumelle,
    inf.Baie
FROM
    Host ho
LEFT OUTER JOIN
    IP ip ON ho.id_host = ip.id_host
LEFT OUTER JOIN
    User us ON ho.id_host = us.id_host
LEFT OUTER JOIN
    Composant co ON us.id_user = co.id_user
LEFT OUTER JOIN
    Informations inf ON ho.id_host = inf.id_host
GROUP BY
    ho.id_host
ORDER BY
    NULL;

Answer 2

你可以看看执行计划。但是根据您的描述，您可能需要一个表上的索引：

create table idx_composant_id_user on composant(id_user)