博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
SQL优化--inner、left join替换in、not in、except
阅读量:6303 次
发布时间:2019-06-22

本文共 4660 字,大约阅读时间需要 15 分钟。

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 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_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查询效率不要太慢了,本来想测试个时间,结果执行了几分钟愣是没出结果,直接终止掉了

优化查询结构

其实innot inexcept这些语法在查询中使用,效率不高是公认的事实,但是可能是由于语义比较明显吧,很多人还是喜欢这样用。我们这里使用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条件中。

  1. 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_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 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;

  1. in->inner join
    查询目的

还是上面的查询背景,这里查询某天某个册本已经上传抄表数据的用户信息。

in结构

select *

from Customer cs
where
cs.Group_No = '册本编号' and
cs.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 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通过连接操作,直接获取到已上传抄表数据的用户信息。

  1. 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_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 cs
where
cs.Group_No = '册本编号' and
cs.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 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;

  1. 总结如下
    上面的查询结构拆分出来后,大家可能觉得这么简单的sql怎么可能写成这个沙雕。其实真实业务系统,还有关联其他将近10张表。这里想说的是,在innot inexcept这种查询结构时,如果涉及到的数据量较大,建议坚决用连接替换。

... in (all except sub)... 查询结构可以转换为->left join

... not in ... 查询结构可以转换为->left join
... not in ... 查询也可以转换为 in -> inner join,这里需要确认转换查询条件时,是否有对应的数据
... in 查询结构可以转换为->inner join
原文地址

转载地址:http://hzbxa.baihongyu.com/

你可能感兴趣的文章
Flink入坑指南第五章 - 语法糖 view
查看>>
centos 7.4 使用 pgxc_ctl 安装与使用
查看>>
Redis 单key值过大 优化方式
查看>>
【数据库】表分区
查看>>
nutz-sqltpl 1.3.4.RELEASE 发布,在 Nutz 项目中“解决 Java 拼接 SQL”问题
查看>>
城市 | 800个地铁站数据透析的京沪白领图鉴:隐形土豪、无产中产阶级和猪猪女孩...
查看>>
前端脚本!网站图片素材中文转英文
查看>>
linux的常用易忘命令
查看>>
PHP 分割字符串
查看>>
java 基于QRCode、zxing 的二维码生成与解析
查看>>
关于职业规划的一些思考
查看>>
img垂直水平居中与div
查看>>
Fabrik – 在浏览器中协作构建,可视化,设计神经网络
查看>>
防恶意注册的思考
查看>>
http2-head compression
查看>>
C# 命名空间
查看>>
订餐系统之同步美团商家订单
查看>>
使用ArrayList时设置初始容量的重要性
查看>>
Java Web-----JSP与Servlet(一)
查看>>
Maven搭建SpringMVC+Mybatis项目详解
查看>>