1、驱动表和被驱动表

连接查询中，第一个需要查询的表称为驱动表，从驱动表中拿出来每条记录，去与被驱动表的所有记录进行匹配。

对于内连接来说
驱动表和被驱动表可以互换
对于外连接来说（内连接在某些特殊情况下可以被优化为内连接）
左外连接：选取左侧的表为驱动表
右外连接：选取右侧的表为驱动表

2、外连接消除

对于外连接的驱动表记录来说，如果无法在被驱动表中找到匹配ON后过滤条件的记录，那么该驱动表的记录任然会被加到结果集中。
但是如果在where搜索条件中指定被驱动表的列不为null（或者类似搜索条件，隐含驱动表的列不为null），那么此时就和内连接没有区别，驱动表和被驱动表就可以互换了。

语句1：select * from student LEFT JOIN teacher on student.teacher_id = teacher.id

语句2：select * from student LEFT JOIN teacher on student.teacher_id = teacher.id where teacher.name is not null

语句3：select * from student JOIN teacher on student.teacher_id = teacher.id

其中语句2和语句3的结果是相同的，也就是在where搜索条件中指定被驱动表的列不为null时外连接可以转换为内连接

消除外连接带来的好处就是可以选择最优的驱动表方案

3、连接的原理

3.1、Index Nested-Loop Join 索引嵌套循环连接

条件：连接时被驱动表使用索引

例如：
如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，straight_join 让 MySQL 使用固定的连接方式执行查询，当前sql指定
t1 是驱动表，t2 是被驱动表。

select * from t1 straight_join t2 on (t1.a=t2.a);

被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引，语句的执行流程为：

从表 t1 中读入一行数据 R；
从数据行 R 中，取出 a 字段到表 t2 里去查找；
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

被驱动表可使用索引的连接如何选择驱动表呢？
驱动表是走全表扫描，而被驱动表是走树搜索。
假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是 N + N2log2M

显然，N 对扫描行数的影响更大，因此被驱动表可使用索引的连接时应该让小表来做驱动表

3.2、Block Nested-Loop Join 基于块的循环嵌套连接

条件：连接时被驱动表上没有可用的索引

例如：当前sql指定t1 是驱动表，t2 是被驱动表，此时t2表上a字段无索引。

select * from t1 straight_join t2 on (t1.a=t2.a);

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k，join_buffer是按线程分配的

此时分为两种情况，一种是join_buffer能够放下t1表全部数据，一种是join_buffer放不下t1表全部数据

（1）join_buffer能够放下驱动表全部数据

流程：

把表 t1 的数据读入线程内存 join_buffer 中，由于语句是 select *，因此是把整个表 t1 放入了内存；
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

被驱动表不可使用索引且join_buffer能够放下驱动表全部数据的连接如何选择驱动表呢？

两个表都做一次全表扫描，所以总的扫描行数是 M+N，内存中的判断次数是 MN，*因此被驱动表不可使用索引且join_buffer能够放下驱动表全部数据的连接时选择大表还是小表做驱动表成本是一样的

（2）join_buffer放不下驱动表全部数据

如果放不下表 t1 的所有数据话，策略很简单，就是分段放，假设t1有100行数据，而join_buffer只能放88行，其执行流程为：

扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
清空 join_buffer；
继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

被驱动表不可使用索引且join_buffer不能放下驱动表全部数据的连接如何选择驱动表呢？

假设驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M。K 不是常数，N 越大 K 就会越大，因此把 K 表示为λ*N，显然λ的取值范围是 (0,1)。

扫描行数是 N+λNM，内存判断 NM 次，*因此被驱动表不可使用索引且join_buffer不能放下驱动表全部数据的连接时选择小表作为驱动表。

4、连接查询的建议

能不能使用 join 语句？
1、如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
2、如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源，所以这种 join 尽量不要用。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
如果是 Block Nested-Loop Join 算法：
在 join_buffer_size 足够大的时候，是一样的；
在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以问题的结论就是，总是应该使用小表做驱动表

小表是怎么定义的？

小表并不是单纯的说哪个表的行数少哪个就是小表，要考虑两个方面，一是过滤后参与连接的行数，二是参与 join 的各个字段的总数据量

例1：
假设两个表b字段都没有索引，t1，t2都有100行数据

#t1为驱动表 t2为被驱动表
select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;

select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

语句1，join_buffer 需要放入t1的100行数据，而语句2，join_buffer 只需要放入 t2 的前 50 行，“t2 的前 50 行”是那个相对小的表，也就是“小表”

例2：
假设两个表b字段都没有索引，t1，t2都有100行数据

#t1为驱动表 t2为被驱动表
select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
#t2为驱动表 t1为被驱动表
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

表 t1 和 t2 都是只有 100 行参加 join。但是，这两条语句每次查询放入 join_buffer 中的数据是不一样的：

表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，则 join_buffer 中只需要放入 b 的值；
表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字段 id、a 和 b。

这里，我们应该选择表 t1 作为驱动表，“只需要一列参与 join 的表 t1”是那个相对小的表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

mysql