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小时内删除。