MySQL查询优化帮助

问题描述：

CREATE TABLE `cpuinfo` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `usagetime` datetime DEFAULT NULL, 
    `cpuusage` int(11) NOT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `id_UNIQUE` (`id`), 
    KEY `idx_usagetime` (`usagetime`), 
    KEY `idx_usage` (`cpuusage`)); 

CREATE TABLE `jobinfo` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `starttime` datetime NOT NULL, 
    `endtime` datetime DEFAULT NULL, 
    `jobname` text NOT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `id_UNIQUE` (`id`), 
    KEY `idx-startime` (`starttime`), 
    KEY `idx-endtime` (`endtime`));

使用此查询：

explain SELECT j.id, j.starttime, j.endtime, j.jobname, c.cpuusage 
    FROM (SELECT j.id, j.starttime, j.endtime, j.jobname, MAX(c.usagetime) AS usagetime 
      FROM jobinfo AS j 
     LEFT JOIN cpuinfo AS c ON c.usagetime <= j.starttime 
     GROUP BY j.id) AS j 
    JOIN cpuinfo AS c ON j.usagetime = c.usagetime 
ORDER BY j.starttime

它需要大约10分钟才能运行。

用于解释命令，我得到这个输出

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 
--------------------------------------------------------------------------- 
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,4557,"Using filesort" 
1,PRIMARY,c,ref,idx_usagetime,idx_usagetime,9,j.usagetime,1,"Using where" 
2,DERIVED,j,ALL,NULL,NULL,NULL,NULL,4557,"Using temporary; Using filesort" 
2,DERIVED,c,index,idx_usagetime,idx_usagetime,9,NULL,2880,"Using index"

你能给我一些提示，以优化这个SQL查询？

这里是我的原单后：

Mysql join with time matching

答

尝试：

SELECT ji.starttime, 
     ji.endtime, 
     ji.jobname, 
     (SELECT ci.cpuusage 
      FROM CPUINFO ci 
     WHERE ci.usagetime <= ji.endtime 
     ORDER BY ci.usagetime DESC 
     LIMIT 1) AS cpuusage 
    FROM JOBINFO ji

这里是我49年5月1日解释输出：

id select_type   table type possible_keys key key_len ref rows Extra 
------------------------------------------------------------------------------------------------ 
'1', 'PRIMARY',   'ji', 'ALL', NULL,   NULL, NULL, NULL, '12', '' 
'2', 'DEPENDENT SUBQUERY', 'ci', 'ALL', 'idx_usagetime', NULL, NULL, NULL, '6', 'Using where; Using filesort'

答

你是在一个比比较少加入：

c.usagetime <= j.starttime

这意味着，具有比工作开始时间少了usagetime每个CPU记录将被加入到工作记录中。随着时间的推移，这个查询会变得越来越慢，因为它会在几个月前加入信息，如果它存在的话。你只对工作开始前的最新条目感兴趣。

如果您确信在作业开始时间的某个时间范围内有cpuinfo记录，请将其更改为范围搜索。

c.usagetime between j.starttime and date_sub(j.starttime, interval 5 minute)

这应该大大加快。你可以使间隔越小越好。

答

你可以试试这个小窍门：

SELECT j.id, j.starttime, j.endtime, j.jobname, c.cpuusage 
FROM 
(
    SELECT j.id, j.starttime, j.endtime, j.jobname, MAX(c.usagetime) AS usagetime 
    FROM jobinfo AS j 
    LEFT JOIN cpuinfo AS c 
    ON c.usagetime <= j.starttime 
    WHERE c.usagetime > DATE_ADD(j.starttime, INTERVAL -1 DAY); 
    GROUP BY j.id 
) AS j 
JOIN cpuinfo AS c 
ON j.usagetime = c.usagetime 
ORDER BY j.starttime;

这应该会导致服务器花一点点的IT运表cpuinfo中的一部分，也不是全部或一半。

PS：尝试考虑间隔值，也许5分钟在你的情况下就足够了。

MySQL查询优化帮助

相关推荐