在一般的项目开发中，多表查询是必不可少的。而对于存在大量数据的情况下，简单的查询已经无法满足性能需求。这就需要对表结构和SQL进行优化。
这次我们讲SQL优化的一种方式，连接查询（Join）和联合查询（union）和 MySQL中连接查询的原理。
对于大型的数据表，我们建议尽量使用连接查询，而避免使用子查询。
但是这不是绝对的，因为连接查询并不是在所有情况下最优的，在一些情况下，配合子查询使用，会使效率提高很多。
本篇包括：
- join以及union简介
- 使用join需要注意的事项
- MySQL 使用 join查询的执行步骤
- MySQL 使用 join 的连接算法
- join 优化
Join 简介
join 用于 多表之间的连接关系，语法如下（当然也可以自连接）
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona [AND conditiona ...]
Join 按照功能，大致分为三类：
内连接（INNER JOIN）
也叫等值连接，得到的满足条件的A和B表内部的数据（必须两边都满足才查出）。如果不添加 ON条件约束的话，取得的是表的笛卡尔积。在添加了ON条件约束后，获取的是同时符合ON条件的 A表和B表数据。
inner join
左外连接（LEFT JOIN）
以左表为驱动表，取得左表全部的数据。然后右表满足条件的数据会对应在左表数据后面，作为添加的外部行列出。
如果没有满足左表的数据，则会用NULL全部填充到外部行。
如果有多条满足左表数据，那么会用左表对应的数据补足，按行逐条对应右表数据。（也就是左表数据重复生成，右表的每条记录都生成一行，然后对应同一个左表数据）
left join
左连接升级：
[left join 或者left outer join(等同于left join)] + [where B.column is null]
查询出A表数据，但是排除掉A表和B表的关联数据。
右连接升级同理。同时，也可以把左连接升级和右连接升级的结果 用 union 联合起来，就取到了A和B的消除重复项的集合。
左连接升级
右外连接（RIGHT JOIN）
跟左连接相反，会以右表为驱动表。
在MySQL 中 外连接 right outer join 和 right join 是一样的，outer可以省略。对应左连接也成立。
此外，联合查询分为两个：
联合查询 （UNION UNION ALL）UNION 操作相当于合并两个或者多个 select 结果集。
要注意，UNION 内部的每个select语句必须拥有相同的列，也必须拥有相同的数据类型，同时列的顺序也必须一致。
关于 UNION 和 UNION ALL 的区别：
1）union 会对联合后的结果集去重，而union all 不会去重，可能取得相同的数据。
2）UNION 会按照结果的第一个字段进行默认排序（重点）。UNION ALL只是简单的取集合。但是相对的 UNION ALL的效率会更高。
3）如果想自定义排序，可以在UNION 结束之后，自定义order by 条件。
使用 Join 需要注意的问题
关于 A left join B on condition 的提醒。
ON 条件：用于决定如何从 表B 中检索行，如果表B中没有任何数据匹配ON条件，则会额外生成一行全部为NULL的外部行。
WHERE条件：在匹配阶段，where 条件不会被使用到。仅在匹配阶段完成后，where 子句才会被使用。它将从匹配产生的结果中检索过滤。
Join 实现原理
Join执行过程
在MySQL中，A left join B on condition 的执行过程如下：
1）以 table_A 为驱动表，检索 table_B
2）根据 on 条件过滤 table_B 的数据，构建 table_A 结果集，并且添加外部行。
3）对结果集执行 where 条件过滤。如果A中有一行匹配 where 子句但是B中没有一行匹配on条件，则生成另一个B行，其中所有列设置为NULL。
4）执行 group by 语句分组
5）执行 having 语句对分组结果筛选
6）执行 select 出结果集。
7）执行 distinct 对结果去重
8）执行 order by 语句
9）执行 limit 语句
上面需要注意的重点是：MySQL会先进行连接查询，然后再使用where子句查询结果，再从结果执行order by。所以如果被驱动表数据过大，会造成检索行过多。可以利用子查询先查询出一个较小的结果集，然后再用连接驱动。
right join 的执行类似 left join ，只是表的角色相反。
NLJ算法
在MySQL中，只有一种 join 算法，就是 Nested Loop Join（嵌套循环连接），它没有其他很多数据库锁提供的 Hash Join，也没有Sort Merge Join。
顾名思义，Nest Loop Join 实际上就是通过驱动表的结果集，作为循环基础数据，然后逐条通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
如果还有第三个参与 Join ，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。
实现方式如下图：
nested-loop join
驱动表：
就是在嵌套循环和哈希连接中，用来最先获得数据，并以此表为依据，逐步获得其他表的数据，直至最终查询到所有安祖条件的数据的第一个表。
驱动表不一定是表，也可以是一个数据集，即由某个表中满足条件的数据行组成的子集合。（同理被驱动表也不一定非得是表，也可以是一个数据集）
索引嵌套联系由于被驱动表上有索引，所以比较的时候不再需要逐条进行比较，而可以通过拿两个表关联字段的索引来进行比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。
这种算法在连接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合值，再会标进行查询。也就是只有当匹配到索引以后才会进行回表。
根据NLJ算法可知，我们在进行连接查询的时候，应该尽可能的使用小表做为驱动表，这样可以显著减小循环的次数。
BLJ算法
即 Block Nested-Loop Join，是MySQL 自己创建的方式。将制定的外键对应的被驱动表缓存起来以提高性能。
在有索引的情况下，MySQL会尝试使用NLJ算法，但是在有些情况下，Join的列可能没有索引，那么这个时候就会使用BLJ算法。
BLJ算法 比 NLJ 算法多了一个中间处理的过程，也就是join buffer，使用 join buffer 将驱动表的连接相关列缓冲起来，然后批量与非驱动表比较，这样实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。
其实就是把驱动表的列拿出来到缓冲区，合并多条数据以后，再放到非驱动表进行比较。而NLJ是从驱动表中逐条比较的。
在MySQL中，我们可以通过参数 join_buffer_size来设置 join buffer 的值。默认情况下 join_buffer_size=256K
在查找的时候，MySQL会将所有需要的列缓存到 join buffer 中，包括 select 的列，而不仅仅是只缓存关系列。
实现如下：
block nested-loop join
Join优化
1）用小结果集驱动大结果集，尽量减少 join 语句中的Nested Loop循环总次数。
2）优先优化 Nested Loop 内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能。
3）对被驱动表的 join 字段上建立索引，并且Join ON 条件的字段应该是相同类型的。
4）当被驱动表的 join 字段上无法建立索引的时候，设置足够的 Join Buffer Size。
5）对于非主键的连接查询，如果被驱动表数据特别多，建议先使用子查询查出一个临时的结果集然后再连接。（待验证）
6）对于可以直接从一个表中取数据的情况。（例如同一个表中取交集，例如好友表，互相关注才是好友）这样的情况，使用 Join 效率是要高于子查询的。
（如果有什么错误或者建议，欢迎留言指出）
（本文内容是对各个知识点的转载整理，用于个人技术沉淀，以及大家学习交流用）
参考资料：
**MySQL优化系列——查找优化
MySQL join语法解析与性能解析
关于 Left Join 需要了解的三点
**MySQL中表连接的简单优化教程
MySQL一次千万级联表查询优化
网友评论