我司某程式員:一個簡單的查詢功能對業務系統沒有多大影響...
我:......
看破不說破.....
面對這樣的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優化後語句:
mobile
actdb.act_customer_info a
create_date BETWEEN '2019-02-25 00:00:00'
AND '2019-02-26 00:00:00'
AND NOT EXISTS (
SELECT
1
FROM
parttime.ls_bill_info b
WHERE
a.id = b.customer_id
AND b.create_date BETWEEN '2019-02-25 00:00:00'
AND '2019-02-26 00:00:00'
DBA優化後語句執行時間31秒
最後自己再花1分鐘重寫一下吧 ,也是滿心惆怅啊......
a.mobile,
a.id,
c.customer_id
actdb.act_customer_info a
LEFT JOIN (
SELECT
customer_id
FROM
parttime.ls_bill_info b
WHERE
b.create_date BETWEEN '2019-02-25 00:00:00'
AND '2019-02-26 00:00:00'
) c ON a.id = c.customer_id
a.create_date BETWEEN '2019-02-25 00:00:00'
AND c.customer_id IS NULL
本質就是not in 和 not exists和left join的差別
這裡就不發相關詳細執行計劃了,簡單但又不簡單,相信程式員或者DBA都可以寫,但偏偏上線的是最差的一條.......
任重道遠,磨砺前行吧~