MySQL 將 not in select 用 left join 改寫以提昇性能

今天在幫MySQL 做些性能的查詢 發現有些語句開發人員寫好就沒有人再看過 其實都有優化的空間

看了幾個  cost 高的 statement, 這邊做個記錄

  1. 改寫 not in select,重點在粗體字中 用  left join 改寫 性能自42s -> 1.17s原查詢
    SELECT s.detail_prod_no as prod_no,SUM(s.qty) AS num
    FROM order_dd AS s
    LEFT JOIN order_m AS m ON s.ord_no=m.ord_no
    LEFT JOIN product_m AS p ON p.prod_no=s.detail_prod_no
    WHERE m.io_kind=2 and m.jit_no=’01’ and m.ord_date between ’09xxxxxxxx’ and ‘1616687999’ and no_order_send=’0′ and s.detail_prod_no=’DM006-14′
    and s.ord_no not in(select ord_no_io2 from io2_order_m where order_status=1)
    GROUP BY s.detail_prod_no
    ORDER BY s.detail_prod_no;

    改查詢
    SELECT s.detail_prod_no as prod_no,SUM(s.qty) AS num
    FROM order_dd AS s

    left join io2_order_m as o ON o.ord_no_io2=s.ord_no and o.order_status=1

    LEFT JOIN order_m AS m ON s.ord_no=m.ord_no
    LEFT JOIN product_m AS p ON p.prod_no=s.detail_prod_no
    WHERE m.io_kind=2 and m.jit_no=’01’ and m.ord_date between ’09xxxxxxxx’ and ‘1616687999’ and no_order_send=’0′ and s.detail_prod_no=’DM006-14′

    and o.ord_no_io2 is NULL

    GROUP BY s.detail_prod_no
    ORDER BY s.detail_prod_no;

 

Author: jerryw1974

learning and focus on computer science, cloud infrastructure, virtualization and information security, technical, networking,platform system and cyber-security related topic.