mysql - 优化 SQL 查询多个 JOIN

Question

我有一个带有嵌套连接的 SQL 查询:

SELECT rh.host, rh.report, COUNT(results.id), COUNT(results_2.id), COUNT(results_3.id), COUNT(results_4.id)
FROM report_hosts rh
INNER JOIN report_results rr ON rh.report = rr.report
LEFT OUTER JOIN results ON rr.result = results.id AND results.type =  'Hole' AND results.host = rh.host
LEFT OUTER JOIN results results_2 ON rr.result = results_2.id AND results_2.type =  'Warning' AND results_2.host = rh.host
LEFT OUTER JOIN results results_3 ON rr.result = results_3.id AND results_3.type =  'Note' AND results_3.host = rh.host
LEFT OUTER JOIN results results_4 ON rr.result = results_4.id AND results_4.type =  'Log' AND results_4.host = rh.host
GROUP BY rh.host

按原样查询大约需要 5 秒，其中 99.7% 复制到临时表。完整查询的 EXPLAIN 显示为:

+----+-------------+-----------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
| id | select_type | table     | type   | possible_keys | key     | key_len | ref               | rows | Extra                           |
+----+-------------+-----------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
|  1 | SIMPLE      | rr        | ALL    | report        | NULL    | NULL    | NULL              | 3139 | Using temporary; Using filesort |
|  1 | SIMPLE      | rh        | ref    | report        | report  | 5       | openvas.rr.report |  167 | Using where                     |
|  1 | SIMPLE      | results   | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
|  1 | SIMPLE      | results_2 | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
|  1 | SIMPLE      | results_3 | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
|  1 | SIMPLE      | results_4 | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
+----+-------------+-----------+--------+---------------+---------+---------+-------------------+------+---------------------------------+

当我删除 LEFT JOIN 时，查询会在大约 1 秒内执行，每个 LEFT JOIN 都会增加大约一秒的执行时间。

我的问题:谁能解释一下，如果有更多 LEFT JOIN，为什么一个连接的复制到临时表任务需要更长的时间？ MySQL 是否为每个 JOIN 多次复制临时表？

我怎样才能避免这种情况？我缺少索引吗？

我打算完成的事情:我有一个表，其中包含多个主机的扫描结果。每个结果都按类型(“孔”、“警告”、“注意”或“日志”)分类。我想选择每个主机以及相应数量的漏洞、警告、注释和日志。作为一个“限制”，我有一个事实，即并非每个主机都有每种类型的结果。

Answer 1

您要多次连接一个表，这实际上就像连接多个表。您应该能够使用一些 case 语句和 where 子句来处理它。 (事实上​​你可能不需要 where 子句。)

SELECT rh.host, rh.report, 
 COUNT(CASE WHEN results.type = 'Hole' THEN 1 ELSE NULL END) as Holes, 
 COUNT(CASE WHEN results.type = 'Warning' THEN 1 ELSE NULL END) as Warnings,
 COUNT(CASE WHEN results.type = 'Note' THEN 1 ELSE NULL END) as Notes, 
 COUNT(CASE WHEN results.type = 'Log' THEN 1 ELSE NULL END) as Logs
FROM 
 report_hosts rh
INNER JOIN 
 report_results rr 
ON 
 rh.report = rr.report
LEFT OUTER JOIN 
 results 
ON 
 rr.result = results.id 
 AND results.host = rh.host
WHERE
 results.type = 'Hole' 
 OR results.type = 'Warning' 
 OR results.type = 'Note' 
 OR results.type = 'Log'
GROUP BY rh.host, rh.report

Case 语句、IME 并不是最出色的性能，但您的数据因许多连接而膨胀可能会抵消这一点并提供更好的性能。