SQL优化--inner、left join替换in、not in、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 on me.meter_no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号'
)
原查询思路
- 查询出目标册本已上传数据的用户编号
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
from Customer cs
left join Meter me on cs.Customer_No = me.Customer_No
where cs.Group_No = '册本编号'
- 全部用户编号中排除已上传数据的用户编号，即为未上传数据的用户编号
全部用户编号 except 已抄表的用户编号
- 查询出在未抄表用户编号集合中的用户信息。
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 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条件中。
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 on me.meter_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 on me.meter_no = md.meter_no and md.date = '2019-04-09'
where cs.Group_NO='册本编号' and md.meter_no is null;
3. in->inner join
查询目的
还是上面的查询背景，这里查询某天某个册本已经上传抄表数据的用户信息。
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 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 cs
left join Meter me on cs.Customer_No = me.Customer_No
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 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 on me.meter_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 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 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 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 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;
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
