NOT IN子查询 - MySQL中超时

Question

我在MySQL中有一个子查询问题。查询运行到超时：

SELECT mac FROM cc_btdata WHERE DATE(time)="2014-09-29" AND mac NOT IN (
SELECT mac FROM cc_btdata WHERE DATE(time) BETWEEN ("2014-09-29" - INTERVAL 60 DAY) AND ("2014-09-29" - INTERVAL 1 DAY) GROUP BY mac) GROUP BY mac 

当我逐个调用查询时，它的工作原理。但是，当我将它们称为子查询时不行。我也在我的开发环境中尝试了MariaDB中的查询，但它的工作原理却不在MySQL中。

这个查询有什么问题？

谢谢！

Answer 1

你可以这样写查询：

SELECT mac 
FROM cc_btdata b 
WHERE NOT EXISTS (SELECT 1 
        FROM cc_btdata b2 
        WHERE b.mac = b2.mac AND 
         time >= '2014-09-29' - INTERVAL 60 DAY AND 
         time < '2014-09-29' - INTERVAL 1 DAY 
       ) AND 
     time >= '2014-09-29' and time < '2014-09-29' + interval 1 day; 

出于性能考虑，你想对cc_btdata(mac, time)的索引。我重写了没有date()的日期比较，因此MySQL将能够利用索引。

你也可以这样写：

select mac 
from cc_btdata 
where time >= '2014-09-29' - INTERVAL 60 DAY 
group by mac 
having sum(date(time) = '2014-09-29') > 0 and 
     sum(date(time) between '2014-09-29' - interval 60 DAY and '2014-09-29' - interval 1 day) = 0; 

（我使用的原始表达式为having子句，因为MySQL不能利用的having条款索引。）第一个版本可能会有更好的表现。

Answer 2

我看不出有任何需要GROUP BY mac，你也可以考虑与NOT EXISTS像

SELECT mac 
FROM cc_btdata 
WHERE NOT EXISTS (
SELECT 1 FROM cc_btdata 
WHERE DATE(time) BETWEEN ('2014-09-29' - INTERVAL 60 DAY) 
AND ('2014-09-29' - INTERVAL 1 DAY) 
) 
AND DATE(time)='2014-09-29' 

您可能还喜欢发布查询执行计划更换NOT IN（OR）解释计划。

Answer 3

我相外部联接这种情况下会工作，并能避免因子查询：

SELECT DISTINCT mac 
FROM cc_btdata 
LEFT JOIN cc_btdata cc_btdata_check ON 
cc_btdata.mac = cc_btdata_check.mac 
AND DATE(cc_btdata_check.time) 
BETWEEN ("2014-09-04" - INTERVAL 60 DAY) AND ("2014-09-04" - INTERVAL 1 DAY) 
WHERE DATE(cc_btdata.time)="2014-09-04" 
AND cc_btdata_check IS NULL