本文共 4660 字,大约阅读时间需要 15 分钟。
SQL优化--inner、left join替换in、not in、except
新系统上线,用户基数16万,各种查询timeout。打开砂锅问到底,直接看sql语句吧,都是泪呀,一大堆innot inexcept。这里总结一下,怎么替换掉innot inexcept。根据
客户表(Customer,按照站点、册本划分,16万数据)
水表表(Meter,16万数据)水表抄表数据表(Meter_Data,远传表每天更新,27万数据)关联查询,查询某天某个册本下水表未上传抄表数据的用户。原查询结构
select *
from Customer cswhere cs.Group_No = '册本编号' andcs.Customer_No in (select Customer_No from Customer csleft join Meter me on cs.Customer_No = me.Customer_Nowhere cs.Group_No = '册本编号'exceptselect Customer_Nofrom Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'where cs.Group_NO='册本编号'
)
原查询思路查询出目标册本已上传数据的用户编号
select Customer_Nofrom Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner 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 csleft join Meter me on cs.Customer_No = me.Customer_Nowhere cs.Group_No = '册本编号'全部用户编号中排除已上传数据的用户编号,即为未上传数据的用户编号全部用户编号 except 已抄表的用户编号查询出在未抄表用户编号集合中的用户信息。select * from Customer cswhere cs.Group_No = '册本编号' andcs.Customer_No in (全部用户编号 except 已抄表的用户编号)思路倒是没有问题,但是in+except查询效率不要太慢了,本来想测试个时间,结果执行了几分钟愣是没出结果,直接终止掉了优化查询结构
其实innot inexcept这些语法在查询中使用,效率不高是公认的事实,但是可能是由于语义比较明显吧,很多人还是喜欢这样用。我们这里使用left join来替代in+except。这里就来改掉上面的查询:
select cs.*
from Customer csleft join Meter me on cs.Customer_No = me.Customer_Noleft 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条件中。not in结构
select *
from Customer cswhere cs.Group_No = '册本编号' andcs.Customer_No not in (select Customer_Nofrom Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'where cs.Group_NO='册本编号'
)
left join结构select cs.*
from Customer csleft join Meter me on cs.Customer_No = me.Customer_Noleft 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;还是上面的查询背景,这里查询某天某个册本已经上传抄表数据的用户信息。
in结构
select *
from Customer cswhere cs.Group_No = '册本编号' andcs.Customer_No in (select Customer_Nofrom Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'where cs.Group_NO='册本编号'
)
这里使用in不够高效,但是我们使用left join是否可以呢?left join结构
select cs.*
from Customer csleft join Meter me on cs.Customer_No = me.Customer_Noleft 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 not null;left join结构的话,这里需要使用is not null作为筛选条件。但是is not null同样非常低效。因此我们使用inner joininner join结构
select cs.*
from Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner join Meter_data md on me.meter_no = md.meter_no and md.date = '2019-04-09'where cs.Group_NO='册本编号';inner join通过连接操作,直接获取到已上传抄表数据的用户信息。这时,我们来优化上面的not in查询结构还有另外一种思路。
not in结构
select *
from Customer cswhere cs.Group_No = '册本编号' andcs.Customer_No not in (select Customer_Nofrom Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner 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
)
in结构通过筛选条件取反,变换not in->in
select *
from Customer cswhere cs.Group_No = '册本编号' andcs.Customer_No in (select Customer_Nofrom Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner 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
)
inner join结构select cs.*
from Customer csleft join Meter me on cs.Customer_No = me.Customer_Noinner 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;... in (all except sub)... 查询结构可以转换为->left join
... not in ... 查询结构可以转换为->left join... not in ... 查询也可以转换为 in -> inner join,这里需要确认转换查询条件时,是否有对应的数据... in 查询结构可以转换为->inner join原文地址转载地址:http://hzbxa.baihongyu.com/