JOIN语句的底层原理

 JOIN 语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环（Nested Loop Join）。如果关联表的数据量很大，则 join 关联的执行时间会非常长。在 MySQL5.5以后的版本中，MySQL 通过引入BNLJ算法来优化嵌套执行。

驱动表（主表）和被驱动表（从表）

驱动表就是主表，被驱动表就是从表、非驱动表。

• 对于内连接来说：

# 内连接的两种写法
select * from A join B on A.f1=B.f1;
select * from A inner join B on A.f1=B.f1;

A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表是驱动表，反之就是被驱动表。通过 explain 关键字可以查看。 （使用 explain 分析语句查询后，表出现在上面的是主表，下面的是从表）

• 对于外连接来说：

select * from A left join B on A.f1=B.f1;
# 或
select * from B right join A on A.f1=B.f1;

 通常大家会认为 A 就是驱动表，B 就是被驱动表。但也未必。测试如下：

create table a(f1 int,f2 int ,INDEX(f1));
create table b(f1 int,f2 int);

insert into a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

# 测试1  查询结果：b 为主表，a 为从表
explain select * from a left join b on a.f1=b.f1 where a.f2=b.f2;

# 测试2  查询结果：a 为主表，b 为从表
explain select * from a left join b on a.f1=b.f1 and a.f2=b.f2; 

# 测试3 查询结果：b 为主表，a 为从表
explain select * from a inner join b on a.f1=b.f1 where a.f2=b.f2;
explain select * from a       join b on a.f1=b.f1 where a.f2=b.f2;

 总结

      使用上面的外连接语句测试时，大家通常会认为 A 为主表，B为从表。结果并非如此。实际情况是，查询优化器帮我们把外连接改造成了内连接。那么谁作为主表、从表，查询优化器说的算。那么对于外连接来说，前面那张表不一定是主表（驱动表）。

JOIN语句原理-——Simple Nested-Loop Join （简单嵌套循环）  

      算法相当简单，从表 A 中取出一条数据 1，遍历表 B,将匹配到的数据放到result。以次类推，驱动表 A 中的每一条记录与被驱动表 B 的记录进行判断。

      当使用JOIN语句查询时，从主表表 A 中取出一条数据，然后把从表表 B 的所有数据加载到内存中。表 A 中的这条数据与内存中表 B 的所有数据匹配一遍，匹配完后清空内存中表B的数据。以次类推，取出表A中第二条数据，把表B的所有数据加载到内存中，表A的第二条数据与内存中表B的所有数据匹配一遍，匹配完了清空内存中表 B 的数据。以此类推.....

JOIN语句原理-——Block Nested-Loop Join（块嵌套循环连接） 

      SNLJ 简单嵌套循环，大大增加了IO的次数，为了减少从表（被驱动表）的IO次数，就引入了Block Nested-Loop Join（块嵌套循环连接） 。

      不再是逐条获取主表的数据，而是一块一块的获取，引入了 join buffer 缓存区,将主表 join 相关的部分数据（列大小受 join buffer 的限制）缓存到 join buffer中，然后全表扫描从表，从表的每一条记录一次性和 join buffer 中的所有主表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合成一次，降低了从表的访问频率。

 join_buffer_size：

主表能不能一次性加载完，要看 join buffer 能不能存储所有数据，默认情况下 join_buffer=256k。

加载不完的话，会分开加载，再开一个新的块保存剩下的主表数据。

JOIN 小结

• 永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是 表行数 * 每行大小）

select t1.b,t2.* from t1 straight_join t2 on t1.b=t2.b where t2.id<=100;#推荐 
select t1.b,t2.* from t2 straight_join t1 on t1.b=t2.b where t2.id<=100;#不推荐 

straight_join ：就是按照你写的 表的位置来作为主表、从表。不根据查询优化器来判断主从表。
                那么第一条SQL，主表是 t1,从表是 t2

为什么两条SQL语句差不多，只有表的位置不一样，却推荐使用第一条SQL？

select t1.b,t2.* from... 中，表 t1 只查询了字段 b,而 t2 查询了所有字段。那么根据“小结果集驱动大结果集”原则，选表 t1 作为主表查询效率最好。

假如表 t1 有100条数据，t2 有1000条数据，主从表选择？

那么选择表 t1 作为主表合适

假如表 t1 有100条数据，t2 有1000条，加了条件 where t2.id<=50 过滤只有50条数据符合条件。主从表选择？

这种情况，虽然 t1表的数据100条，表 t2 数据1000，两表关联后 t2 的数据量经过条件过滤后，只有50条符合条件数据。这个时候 选择 表 t2 为主表，效率高。

• 为从表匹配的条件增加索引（减少内层表的循环匹配次数）
• 增大 join buffer size 的大小 （一次缓存的数据越多，那么内层包的扫描次数就越少）
• 减少主表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）