innerjoinon加条件和where加条件_MySQL实战:Join的使⽤
技巧和优化
join⽤于多表中字段之间的联系,在数据库的DML (数据操作语⾔,即各种增删改查操作)中有着重要的作⽤。
合理使⽤Join语句优化SQL有利于:
1. 增加数据库的处理效率,减少响应时间;
2. 减少数据库服务器负载,增加服务器稳定性;
3. 减少服务器通讯的⽹络流量;
1. Join的分类:
内连接 Inner Join
全外连接 FULL Outer Join
左外连接 Left Outer Join
右外连接 Right Outer Join
交叉连接 Cross Join
连接的分类
每种连接的区别作为基础内容,这⾥就不再展开说明,请读者⾃⼰参看其他⽂章了解,⽐如Mysql Join语法以及性能优化
需要说明的是,⽬前MySQL不⽀持全连接,需要使⽤UNION关键字进⾏联合。
Union:对两个结果集进⾏并集操作,不包括重复⾏,同时进⾏默认规则的排序;
Union All:对两个结果集进⾏并集操作,包括重复⾏,不进⾏排序;
3. Join使⽤的注意事项
下⾯进⾏本⽂重点,Join的使⽤注意事项和技巧,⾸先给出要使⽤的表结构:
-- auto-generated definitionCREATE TABLE customer( id INT AUTO_INCREMENT PRIMARY KEY, cust_name VARCHAR(50) NOT NULL CHARSET utf8,
customer表中数据,代表客户的信息
faculty表中的数据,代表职⼯的信息
2.1 显式连接 VS 隐式连接
所谓显式连接,即如上显⽰使⽤inner Join关键字连接两个表,
select * fromtable a inner join table bon a.id = b.id;
⽽隐式连接即不显⽰使⽤inner Join关键字,如:
select a.*, b.*from table a, table bwhere a.id = b.id;
⼆者在功能上没有差别,实现的性能上也⼏乎⼀样。只不过隐式连接是SQL92中的标准内容,⽽在SQL99中显式连接为标准,虽然很多⼈还在⽤隐私连接,但是它已经从标准中被移除。从使⽤的⾓度来说,还是推荐使⽤显⽰连接,这样可以更清楚的显⽰出多个表之间的连接关系和连接依赖的属性。
2.2 On VS Where
ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)⽤来决定如何从 B 表中检索数据⾏。如果 B 表中没有任何⼀⾏数据匹配 ON 的条件,将会额外⽣成⼀⾏所有列为 NULL 的数据,在匹配阶段 WHERE
⼦句的条件都不会被使⽤。仅在匹配阶段完成以后,WHERE ⼦句条件才会被使⽤。ON将从匹配阶段产⽣的数据中检索过滤。
所以我们要注意:在使⽤Left (right) join的时候,⼀定要在先给出尽可能多的匹配满⾜条件,减少Where的执⾏。尽可能满⾜ON的条件,⽽少⽤Where的条件,从执⾏性能来看也更加⾼效。
3 Join的技巧
3.1 如何更新使⽤过虑条件中包括⾃⾝的表
假设现在要将是职⼯中的消费者的“over”属性设置为"优惠",直接如下更新会报错:
这是由于Mysql不⽀持这种查询后更新(这其实是标准SQL中⼀项要求,Oracle、SQL Server中都是可
以的)。
为了解决这种更新的过虑条件中包含要更新的表的情况,可以把带过滤条件的查询结果当做⼀个新表,在新表上,执⾏更新操作。UPDATE (faculty f INNER JOIN customer c on user_name=cust_name)set c.over = "优惠";
更新成功
3.2 Join优化⼦查询
group by的用法及原理详解嵌套的⼦查询是⽐较低效地,因为每⼀条记录都要进⾏匹配,如果记录长度⽐较⼤的话,那么我们的查询就有可能⾮常的耗时。我们应该尽
量避免使⽤⼦查询,⽽⽤表连接。如下⾯的这个⼦查询就可以转化为等价的连接查询
SELECT user_name, over ,(SELECT over FROM customer c where user_name=cust_name) as over2from faculty f;SELECT user_name, f.over , c.over as over2f
3.3 使⽤Join优化聚合查询
为了说明这个问题 ,我们在添加⼀个⼯作量的表,记录每个职⼯每天的⼯作量
-- auto-generated definitionCREATE TABLE tasks( id SMALLINT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY, facult_id SMALLINT(5) UNSIGNED NULL, ti
tasks记录职⼯的⼯作量
⽐如我们想查询每个员⼯⼯作量最多是哪⼀天,通过⼦查询可以这样实现:
select a.user_name ,b.timestr,b.workloadfrom faculty a join tasks b on a.id = b.facult_idwhere b.workload = ( select max(c.workload) from tasks c where c.facult_i
查询结果
使⽤表连接优化之后:
SELECT user_name, t.timestr, t.workloadFROM faculty f JOIN tasks t ON f.id = t.facult_id JOIN tasks t2 ON t2.facult_id = t.facult_idGROUP BY user_name,t.times
这⾥额外的再连接了⼀个task表中内容,在这个“额外表”中通过聚合计算出⼯作量的最⼤值,然后再过虑(HAVING)出⼯作量最⼤的⽇
期。
因为聚合函数通过作⽤于⼀组数据⽽只返回⼀个单个值,因此,在SELECT语句中出现的元素要么为⼀个聚合函数的输⼊值,要么为
GROUP BY语句的参数,否则会出错。
但是mysql的group by做过扩展了,select之后的列允许其不出现在group by之后,MySQL在执⾏这类查询语句时,它会默认理解
为,没写到GROUP BY⼦句的列,其列值是唯⼀的,如果GROUP BY省略的列值其实并不唯⼀,将会默认取第⼀个获得的值,这样
就会指代不明,那么最好不要使⽤这项功能。
3.4 如何实现分组查询
要获取每个员⼯完成⼯作量最多的两天。这个也可以通过Join来完成。
select d.user_name,c.timestr,workloadFROM ( select facult_id,timestr,workload, (SELECT COUNT(*) FROM tasks b WHERE b.facult_id=a.facult_id AND a.worklo
其中,内部的查询结果cnt表⽰对于tasks表中某个给定记录,相同员⼯的⼯作⾥记录⽐其⼤的数量有多少。
内部查询的结果如下:
select facult_id,timestr,workload, (SELECT COUNT(*) FROM tasks b WHERE b.facult_id=a.facult_id AND a.workload<=b.workload) AS cnt FROM tasks a GROUP
内部查询的结果
即每个⼯作量记录信息和同⼀员⼯的⼯作量排名。
cnt <= 2就代表该记录是某位员⼯的⼯作量最⼤两天之⼀。
每个员⼯完成⼯作量最多的两天
4. join的实现原理
join的实现是采⽤Nested Loop Join算法,就是通过驱动表的结果集作为循环基础数据,然后⼀条⼀条的通过该结果集中的数据作为过滤
条件到下⼀个表中查询数据,然后合并结果。如果有多个join,则将前⾯的结果集作为循环数据,再⼀次作为循环条件到后⼀个表中查询数
据。
⽐如我们以如下SQL语句为例:
EXPLAIN SELECT C.id, cust_name,T.workloadFROM customer C INNER JOIN faculty F ON C.cust_name = F.user_name INNER JOIN tasks T ON T.facult_id = F
EXPLAIN 连接查询
从explain的输出看出,MySQL选择C作为驱动表,
⾸先通过Using Where和Using join buffer来匹配F中的内容,然后在其结果的基础上通过主键的索引PRIMARY,faculty_id_uindex匹配
到T表中的内容。
其过程类似于三次次嵌套的循环。
需要说明的是,C作为驱动表,通过Using Where和Using join buffer来匹配F,是因为C.cust_name ,F.user_name都没有加索
引,要获取具体的内容只能通过对全表的数据进⾏where过滤才能获取,⽽Using join buffer是指使⽤到了Cache(只有当join类型为
ALL,index,rang或者是index_merge的时候才会使⽤join buffer),记录已经查询的结果,提⾼效率。
⽽对于T和F之间通过T的主键T.id连接,所以join类型为eq_ref,也不⽤使⽤Using join buffer。
5. join语句的优化原则
1. ⽤⼩结果集驱动⼤结果集,将筛选结果⼩的表⾸先连接,再去连接结果集⽐较⼤的表,尽量减少join语句中的Nested Loop的循环总
次数;
2. 优先优化Nested Loop的内层循环(也就是最外层的Join连接),因为内层循环是循环中执⾏次数最多的,每次循环提升很⼩的性能都
能在整个循环中提升很⼤的性能;
3. 对被驱动表的join字段上建⽴索引;
4. 当被驱动表的join字段上⽆法建⽴索引的时候,设置⾜够的Join Buffer Size。
参考⽂章
1. MySQL数据库对GROUP BY⼦句的功能扩展(1)
2. SQL中GROUP BY语句与HAVING语句的使⽤
3. Mysql Join语法以及性能优化
4. mysql join的实现原理及优化思路
5. Explicit vs implicit SQL joins
6.
7. Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论