为什么join

1. 因为一个表没有相应的数据，需要别的表中的数据
2. 即使在一个表，如emp mgr递归特性 (MySQL不支持)，严重影响效率，换成join更好

join的特点

正常情况下使用join后 数据是相同或者减少，但是存在数据变多的情况(数据上存在多对多)

去除重复值可以使用distinct，existed和in

outer join

outer join的前提条件是必须满足join之后数据量不能有变化

有任何数据量变化的outer join都是错误的

数据量变多 说明被outer join的表里连接条件有重复值

注意点

1. outer join关键字位置不同导致的错误

   Left join时，如果过滤条件在on中，数据正常，不会发生异常。但是在where条件的话，会变成inner join

   例如，有如下两张表

join.png

分别运行如下俩个SQL，结果却不一样

join1.png

但是将上面where的条件写入on之后，结果如下

join2.png

2. outer join可以转换为subquery

   如下面这个SQL

   SELECT a.*,d.* FROM t_group a LEFT JOIN (select distinct d.dept_no,d.dept_name from dept d) d on a.dept_no = d.dept_no;
   

   结果集如下

join3.png

根据等量替换的原则可改写成如下语句

SELECT a.*,
(select distinct d.dept_no from dept d WHERE dept_no = d.dept_no) dept_no,
(select distinct d.dept_name from dept d WHERE a.dept_no = d.dept_no) dept_name 
FROM t_group a;

上面的SQL语句有两个小问题

1. 使用了distinct，目的是去除重复值，我们可以使用limit进行替换，保证语法正确

2. 重复的写了两次subquery，为了避免这种情况，我们可以使用先数据合并在拆解的方式解决

join4.png

nested loop join的特点

1. 顺序性
2. 驱动表的数据量决定连接次数
3. random access为主
4. 受到被连接条件影响较大
5. 主要用于oltp少量数据的时候

join5.png

如上图所示，把两个表的join分成9个部分，分别讲解对应部分的MySQL中的特性

1. 驱动表的索引起始位，通常在SQL中是where条件对应的列的查询条件，常数为主，上图的话对应a.FLD1 = 'AB'
2. 2表示a.FLD1的索引，对索引的查询通常有ref，range，index三种形式，当涉及pk时还有const，const属于ref一类
3. 索引查到对应的pk之后的回表过程，MySQL innodb二级索引包含索引列+pk，通过查索引之后找出对应的pk值进行回表，回表的原因是select列或者where条件中包含索引列之外的列，索引列是有序的，但是对应定的pk无须，所以会产生random access io。如果数据量较大，就会相对比较慢，为了解决这个问题减少随机io产生了MRR，但是又出现了排序错乱。解决随机io还可以使用延时join来优化
4. 驱动表查索引之后的回表部分。主要是where条件或者select列中包含索引列之外的列，例子中的index列为FLD1，where条件中有a.key1，select中还包含a表的其他字段。回表部分可能会发生二次过滤，主要表现在fiter部分，可根据fiter的大小考虑是否创建联合索引
5. 驱动表和被驱动表的连接部分。例子中a.key1值我们可以当成常数，他是根据a.FLD1 = 'AB'取出的。连接部分在含有group by的时候，通常要将group by提前，减少重复值和缩小结果集，达到优化。semi join采用同样的优化思路
6. 被驱动表的索引部分。例子中是b.key2。执行计划一般是ref，eq_ref较多。优化过程中要想使用被驱动表索引，则必须保证驱动表的连接列数据类型与被驱动表连接列的数据类型一致。当执行计划出现all时，在b表数据量较大的时候考虑是否创建索引。当数据量较小的时候，可以通过改写成不能合并的子查询，产生auto_key的方式进行优化
7. 被驱动表查索引之后的random access io。原因是select列或者where条件中包含索引列之外的列。
8. 被驱动表的回表部分。优化方式可考虑使用垂直分表，不使用select *等
9. 回表后的二次过滤。例子中由b.FIL2=10产生。优化方式可以创建联合索引