我司某程序员：一个简单的查询功能对业务系统没有多大影响...

我：......

看破不说破.....

面对这样的SQL 也难起波澜了 转交给DBA

10年经验程序员写的原语句：

SELECT

mobile

FROM

actdb.act_customer_info

WHERE

(    create_date BETWEEN '2019-02-25 00:00:00'    AND '2019-02-26 00:00:00')

AND (

id NOT IN (    SELECT        customer_id    FROM        parttime.ls_bill_info    WHERE        create_date BETWEEN '2019-02-25 00:00:00'    AND '2019-02-26 00:00:00')

);

原语句执行时间68秒

DBA优化后语句：

SELECT

mobile

FROM

actdb.act_customer_info a

WHERE

create_date BETWEEN '2019-02-25 00:00:00'

AND '2019-02-26 00:00:00'

AND NOT EXISTS (

SELECT    1FROM    parttime.ls_bill_info bWHERE    a.id = b.customer_idAND b.create_date BETWEEN '2019-02-25 00:00:00'AND '2019-02-26 00:00:00'

);

DBA优化后语句执行时间31秒

最后自己再花1分钟重写一下吧 ，也是满心惆怅啊......

SELECT

a.mobile,a.id,c.customer_id

FROM

actdb.act_customer_info a

LEFT JOIN (

SELECT    customer_idFROM    parttime.ls_bill_info bWHERE    b.create_date BETWEEN '2019-02-25 00:00:00'AND '2019-02-26 00:00:00'

) c ON a.id = c.customer_id

WHERE

a.create_date BETWEEN '2019-02-25 00:00:00'

AND '2019-02-26 00:00:00'

AND c.customer_id IS NULL本质就是not in 和 not exists和left join的区别这里就不发相关详细执行计划了，简单但又不简单，相信程序员或者DBA都可以写,但偏偏上线的是最差的一条.......

任重道远，磨砺前行吧~