SQL优化--inner、leftjoin替换in、notin、except
新系统上线，⽤户基数16万，各种查询timeout。打开砂锅问到底，直接看sql语句吧，都是泪呀，⼀⼤堆in\not in\except。这⾥总结⼀下，怎么替换掉in\not in\except。
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 _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号'
)
原查询思路
1. 查询出⽬标册本已上传数据的⽤户编号
select Customer_No
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号'
2. 查询出⽬标册本全部⽤户编号
select Customer_No
from  Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
where cs.Group_No = '册本编号'
3. 全部⽤户编号中排除已上传数据的⽤户编号，即为未上传数据的⽤户编号
全部⽤户编号 except 已抄表的⽤户编号
4. 查询出在未抄表⽤户编号集合中的⽤户信息。
select *
from Customer cs
where
cs.Group_No = '册本编号' and
cs.Customer_No in
(全部⽤户编号 except 已抄表的⽤户编号)
思路倒是没有问题，但是in+except查询效率不要太慢了，本来想测试个时间，结果执⾏了⼏分钟愣是没出结果，直接终⽌掉了
优化查询结构
其实in\not in\except这些语法在查询中使⽤，效率不⾼是公认的事实，但是可能是由于语义⽐较明显吧，很多⼈还是喜欢这样⽤。我们这⾥使⽤left join来替代in+except。这⾥就来改掉上⾯的查询：
select cs.*
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
left join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号' _no is null;
优化查询思路
1. ⽤left join代替in+except，通过left join获取⽬标册本下全部⽤户的信息，并与当天上传的抄表数据进⾏连接；
2. 连接中，右表为空即抄表数据为空的，即为当前未上传数据的客户信息；
left join on expression where expression 执⾏时，⾸先确保左表数据全部返回，然后应⽤on后指定的条件。因此，on的条件如果是对左表数据的过滤，是⽆效的；对右表数据的过滤是有效的。对左表数据的过滤条件，需要放到where条件中。
2. not in->left join
上⾯in+except的写法，可以使⽤not in简化⼀下，但是⼀样效率不⾼。这⾥想要说明的是not in也可以很⽅便的使⽤left join替换。not in结构
select *
from Customer cs
where
cs.Group_No = '册本编号' and
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 _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号'
)
left join结构
select cs.*
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
left join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号' _no is null;
3. in->inner join
查询⽬的
还是上⾯的查询背景，这⾥查询某天某个册本已经上传抄表数据的⽤户信息。
sql语句查询结果取反in结构
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
inner join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号'
)
这⾥使⽤in不够⾼效，但是我们使⽤left join是否可以呢？
left join结构
select cs.*
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
left join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号' _no is not null;
left join结构的话，这⾥需要使⽤is not null作为筛选条件。但是is not null同样⾮常低效。因此我们使⽤inner join
inner join结构
select cs.*
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号';
inner join通过连接操作，直接获取到已上传抄表数据的⽤户信息。
4. not in -> in -> inner join
前⾯的查询场景中，我们默认的条件是未上传抄表数据的⽤户，当天在meter_data表是没有记录的。现在假设我们每天凌晨初始化meter_data表，设置抄表数值默认为零，抄表数据上传默认为state=0未上传。上传后，更新抄表数值和抄表状态state=1。
这时，我们来优化上⾯的not in查询结构还有另外⼀种思路。
not in结构
select *
from Customer cs
where
cs.Group_No = '册本编号' and
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 _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 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
inner join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号' and meter.state=0
)
inner join结构
select cs.*
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
inner join Meter_data md _no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号' and meter.state=0;
5. 总结如下
上⾯的查询结构拆分出来后，⼤家可能觉得这么简单的sql怎么可能写成这个沙雕。其实真实业务系统，还有关联其他将近10张表。这⾥想说的是，在in\not in\except这种查询结构时，如果涉及到的数据量较⼤，建议坚决⽤连接替换。
... in (all except sub)... 查询结构可以转换为->left join
... not in ... 查询结构可以转换为->left join
... not in ... 查询也可以转换为 in -> inner join，这⾥需要确认转换查询条件时，是否有对应的数据
... in 查询结构可以转换为->inner join

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。