mysql - 将嵌套查询优化为单个查询

Question

我有一个 (MySQL) 表，其中包含上次扫描主机的日期以及报告 ID:

+--------------+---------------------+--------+
| host         | last_scan           | report |
+--------------+---------------------+--------+
| 112.86.115.0 | 2012-01-03 01:39:30 |      4 |
| 112.86.115.1 | 2012-01-03 01:39:30 |      4 |
| 112.86.115.2 | 2012-01-03 02:03:40 |      4 |
| 112.86.115.2 | 2012-01-03 04:33:47 |      5 |
| 112.86.115.1 | 2012-01-03 04:20:23 |      5 |
| 112.86.115.6 | 2012-01-03 04:20:23 |      5 |
| 112.86.115.2 | 2012-01-05 04:29:46 |      8 |
| 112.86.115.6 | 2012-01-05 04:17:35 |      8 |
| 112.86.115.5 | 2012-01-05 04:29:48 |      8 |
| 112.86.115.4 | 2012-01-05 04:17:37 |      8 |
+--------------+---------------------+--------+

我想选择所有主机的列表，其中包含上次扫描的日期和相应的报告 ID。我已经构建了以下嵌套查询，但我确信它可以在单个查询中完成:

SELECT rh.host, rh.report, rh.last_scan
FROM report_hosts rh
WHERE rh.report = (
    SELECT rh2.report
    FROM report_hosts rh2
    WHERE rh2.host = rh.host
    ORDER BY rh2.last_scan DESC
    LIMIT 1
)
GROUP BY rh.host

是否可以使用单个非嵌套查询来完成此操作？

Answer 1

不，但您可以在查询中执行 JOIN

SELECT x.*
FROM report_hosts x
INNER JOIN (
    SELECT host,MAX(last_scan) AS last_scan FROM report_hosts GROUP BY host
) y ON x.host=y.host AND x.last_scan=y.last_scan

您的查询正在进行文件排序，效率非常低。我的解决方案没有。强烈建议在此表上创建索引

ALTER TABLE `report_hosts` ADD INDEX ( `host` , `last_scan` ) ;

否则您的查询将执行两次文件排序。