mysql - SQL请求优化

我有一个SQL请求，该请求在运行时占用了我的VM CPU的100％。我想知道如何优化它:

SELECT g.name AS hostgroup
     , h.name AS hostname
     , a.host_id
     , s.display_name AS servicename
     , a.service_id
     , a.entry_time AS ack_time
     , (  SELECT ctime
          FROM logs
          WHERE logs.host_id = a.host_id
          AND logs.service_id = a.service_id
          AND logs.ctime < a.entry_time
          AND logs.status IN (1, 2, 3)
          AND logs.type = 1
          ORDER BY logs.log_id DESC
          LIMIT 1) AS start_time
     , ar.acl_res_name AS timeperiod
     , a.state AS state
     , a.author
     , a.acknowledgement_id AS ack_id
FROM centstorage.acknowledgements a
LEFT JOIN centstorage.hosts h ON a.host_id = h.host_id
LEFT JOIN centstorage.services s ON a.service_id = s.service_id
LEFT JOIN centstorage.hosts_hostgroups p ON a.host_id = p.host_id
LEFT JOIN centstorage.hostgroups g ON  g.hostgroup_id = p.hostgroup_id
LEFT JOIN centreon.hostgroup_relation hg ON a.host_id = hg.host_host_id
LEFT JOIN centreon.acl_resources_hg_relations hh ON hg.hostgroup_hg_id = hh.hg_hg_id
LEFT JOIN centreon.acl_resources ar ON hh.acl_res_id = ar.acl_res_id
WHERE ar.acl_res_name != 'All Resources'
AND YEAR(FROM_UNIXTIME( a.entry_time )) = YEAR(CURDATE())
AND MONTH(FROM_UNIXTIME( a.entry_time )) = MONTH(CURDATE())
AND a.service_id is not null
ORDER BY a.acknowledgement_id ASC

问题出在这部分:

(SELECT ctime FROM logs
 WHERE logs.host_id = a.host_id
   AND logs.service_id = a.service_id
   AND logs.ctime < a.entry_time
   AND logs.status IN (1, 2, 3)
   AND logs.type = 1
 ORDER BY logs.log_id DESC
 LIMIT 1) AS start_time

该表的日志确实很大，有些 friend 告诉我要使用缓冲表/数据库，但是我对此很了解，我也不知道该怎么做。

该查询有一个EXPLAIN EXTENDED:

看来他只会检查表日志中的两行，所以为什么要花这么多时间？ (表日志中有560000行)。

这是这些表的所有索引:

centstorage.acknowledgements:

centstorage.hosts:

centstorage.services:

centstorage.hosts_hostgroups:

centstorage.hostgroups:

centeron.hostgroup_relation:

centreon.acl_resources_hg_relations:

centreon.acl_resources:

最佳答案

对于SQL Server，可以使用MAXDOP定义查询的最大并行度

例如，您可以在查询末尾定义

option (maxdop 2)

我很确定MySql中有一个等效项。

如果执行时间不相关，则可以尝试解决这种情况。

关于mysql - SQL请求优化，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30367460/