SQL優化--inner、left join替換in、not in、except

SQL優化--inner、left join替換in、not in、except

新系統上線，使用者基數16萬，各種查詢timeout。打開砂鍋問到底，直接看sql語句吧，都是淚呀，一大堆innot inexcept。這裡總結一下，怎麼替換掉innot inexcept。

1. in/except->left join

   查詢目的：

根據

客戶表(Customer，按照站點、冊本劃分，16萬資料)

水表表(Meter，16萬資料)

水表抄表資料表(Meter_Data，遠傳表每天更新，27萬資料)

關聯查詢，查詢某天某個冊本下水表未上傳抄表資料的使用者。

原查詢結構

select *

from Customer cs

where

cs.Group_No = '冊本編号' and

cs.Customer_No in

(

select Customer_No 
from  Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
where cs.Group_No = '冊本編号'
except
select Customer_No
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='冊本編号'           

)

原查詢思路

查詢出目标冊本已上傳資料的使用者編号

select Customer_No

left join Meter me on cs.Customer_No = me.Customer_No

inner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'

where cs.Group_NO='冊本編号'

查詢出目标冊本全部使用者編号

select Customer_No

from Customer cs

where cs.Group_No = '冊本編号'

全部使用者編号中排除已上傳資料的使用者編号，即為未上傳資料的使用者編号

全部使用者編号 except 已抄表的使用者編号

查詢出在未抄表使用者編号集合中的使用者資訊。

(全部使用者編号 except 已抄表的使用者編号)

思路倒是沒有問題，但是in+except查詢效率不要太慢了，本來想測試個時間，結果執行了幾分鐘愣是沒出結果，直接終止掉了

優化查詢結構

其實innot inexcept這些文法在查詢中使用，效率不高是公認的事實，但是可能是由于語義比較明顯吧，很多人還是喜歡這樣用。我們這裡使用left join來替代in+except。這裡就來改掉上面的查詢：

select cs.*

left join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'

where cs.Group_NO='冊本編号' and md.meter_no is null;

優化查詢思路

用left join代替in+except，通過left join擷取目标冊本下全部使用者的資訊，并與當天上傳的抄表資料進行連接配接；

連接配接中，右表為空即抄表資料為空的，即為目前未上傳資料的客戶資訊；

left join on expression where expression 執行時，首先確定左表資料全部傳回，然後應用on後指定的條件。是以，on的條件如果是對左表資料的過濾，是無效的；對右表資料的過濾是有效的。對左表資料的過濾條件，需要放到where條件中。

1. not in->left join

   上面in+except的寫法，可以使用not in簡化一下，但是一樣效率不高。這裡想要說明的是not in也可以很友善的使用left join替換。

not in結構

cs.Customer_No not in

select Customer_No
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='冊本編号'           

left join結構

1. in->inner join

   查詢目的

還是上面的查詢背景，這裡查詢某天某個冊本已經上傳抄表資料的使用者資訊。

in結構

select Customer_No
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='冊本編号'           

這裡使用in不夠高效，但是我們使用left join是否可以呢？

where cs.Group_NO='冊本編号' and md.meter_no is not null;

left join結構的話，這裡需要使用is not null作為篩選條件。但是is not null同樣非常低效。是以我們使用inner join

inner join結構

where cs.Group_NO='冊本編号';

inner join通過連接配接操作，直接擷取到已上傳抄表資料的使用者資訊。

1. not in -> in -> inner join

   前面的查詢場景中，我們預設的條件是未上傳抄表資料的使用者，當天在meter_data表是沒有記錄的。現在假設我們每天淩晨初始化meter_data表，設定抄表數值預設為零，抄表資料上傳預設為state=0未上傳。上傳後，更新抄表數值和抄表狀态state=1。

這時，我們來優化上面的not in查詢結構還有另外一種思路。

select Customer_No
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='冊本編号' and meter.state=1           

通過篩選條件取反，變換not in->in

select Customer_No
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='冊本編号' and meter.state=0           

where cs.Group_NO='冊本編号' and meter.state=0;

1. 總結如下

   上面的查詢結構拆分出來後，大家可能覺得這麼簡單的sql怎麼可能寫成這個沙雕。其實真實業務系統，還有關聯其他将近10張表。這裡想說的是，在innot inexcept這種查詢結構時，如果涉及到的資料量較大，建議堅決用連接配接替換。

... in (all except sub)... 查詢結構可以轉換為->left join

... not in ... 查詢結構可以轉換為->left join

... not in ... 查詢也可以轉換為 in -> inner join，這裡需要确認轉換查詢條件時，是否有對應的資料

... in 查詢結構可以轉換為->inner join

原文位址

https://www.cnblogs.com/zhangdk/p/notintoleftjoin.html