MySql为null与非null性能

Question

我有一个查询，我基本上是在做一个左外部联接，并检查连接值是否为null。

select count(T1.code) 
from ( select code 
       from asset 
       where type = 'meter' 
       and creation_time <= '2022-04-29 00:00:00' 
       and (deactivation_time > '2022-04-28 00:00:00' or deactivation_time is null ) 
       group by code
       ) as T1 
left join ( select asset_code 
            from amr_midnight_data 
            where server_time between '2022-04-28 00:00:00' and '2022-04-29 00:00:00' 
            group by asset_code
         ) as T2 on T1.code = T2.asset_code 
Where T2.asset_code is null;

这个查询需要3秒才能执行，但如果我将is null替换为not null，则所需时间少于1秒。为什么这里存在性能差异，我有哪些替代方法可以使我的原始查询更快呢？

Answer 1

看看解释。一个猜测..。通过更改为IS NOT NULL，优化器可以将LEFT JOIN更改为JOIN，这使优化器可以从amr_midnight_data开始，这可能会更好地优化。

我认为LEFT JOIN ( SELECT ... ) .. IS [NOT] NULL可以用

WHERE [NOT] EXISTS ( SELECT 1 FROM amr_midnight_data
              WHERE asset_code = T1.code
                AND server_time >= '2022-04-28'
                AND server_time  < '2022-04-28' + INTERVAL 1 DAY )

想要INDEX(asset_code, server_time)

EXISTS比SELECT .. GROUP BY更快，因为只要找到一个匹配行，它就可以停止。

asset可能会受益于INDEX(type, creation_time)或(使其“覆盖”)：

INDEX(time, creation_time, deactivation_time, code)

如果您想进一步讨论，请为两个表提供SHOW CREATE TABLE，为每个SELECT提供EXPLAIN。