mysql - 非活跃客户mysql查询优化

Question

我有一个包含数百万条记录的客户表。客户

id | name | ..... 

我还有一个订单表

id | custID | orderDate | ....

我需要找出所有超过30天没有下单的人，还应该包括从来没有下过单的人

select name,customer.id from customer where id in 

(select custID from orders where datediff(curdate(),orders.orderDate) > 30 )

union

select name,customer.id from customer  left join orders on customer.id = orders.custID where orders.id is null

如何优化查询？

Answer 1

尝试

select name,t.id 
  from customer t where 
    not exists (
        select 1 
          from orders where 
          custID=t.id
          and
          datediff(curdate(),orders.orderDate) <= 30 )