一、MySQL JOIN分类

  • INNER JOIN，内连接，返回左右表互相匹配的所有行
  • LEFT JOIN，左外连接，返回左表的所有行，若某些行在右表里没有相对应的匹配行，则将右表的列在新表中置为NULL
  • RIGHT JOIN，右外连接，返回右表的所有行，若某些行在左表里没有相对应的匹配行，则将左表的列在新表中置为NULL
  • FULL JOIN，MySQL不支持，可以使用左外连接和右外连接的联合查询
  • CROSS JOIN，交叉连接

二、JOIN顺序

inner join驱动顺序由优化器自己指定，如果优化器选择有误可以使用straight_join自己指定驱动顺序以达到优化的目的

left join驱动顺序是固定的，left join左边的表为驱动表，右边为匹配表，RIGHT JOIN则刚好相反

下面这两个SQL是等价的吗？

select count(*) from t_user_log a left join t_user b on a.uid = b.uid and b.city = 78; 
select count(*) from t_user_log a left join t_user b on a.uid = b.uid where b.city = 78;

不等价，因为前者在匹配表中加了过滤条件，而后者在关联结果中加了过滤条件，前者不影响驱动表检索出来的数据（与匹配表无法匹配的数据依然会检索出来，只是匹配表字段部分值等于NULL），后者影响驱动表检索出来的数据（因为在结果集中直接被过滤掉了）。

三、MySQL原理介绍

场景： t1表插入100行，t2表插入1000行数据

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

Index Nested-Loop Join

连接字段a上有索引，straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join，不会自己选择。在这个语句里，t1 是驱动表，t2 是被驱动表

执行流程：先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。并且可以用上被驱动表的索引。驱动表是走全表扫描，而被驱动表是走树搜索

  • 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好
  • 如果使用 join 语句的话，需要让小表做驱动表，因为小表是驱动表，走的是全表扫描，表越小，扫描的数据越少，性能越好

Simple Nested-Loop Join

被驱动表上没有可用的索引

因为t2没有索引，所以需要全表扫描。总共需扫描100*1000行，数据量大的时候性能可想而知，为此MySQL采取Block Nested-Loop Join策略优化。

Block Nested-Loop Join

流程：

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

Block Nested-Loop Join算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好，并且只需要扫描两次表即可

假设小表的行数是 N，大表的行数是 M，那么在这个算法里：

  1. 两个表都做一次全表扫描，所以总的扫描行数是 M+N；
  2. 内存中的判断次数是 M*N。

这里无所谓驱动表和被驱动表

join_buffer放不下

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1的所有数据话，策略很简单，就是分段放

驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是M，K 表示为λ*N

在这个算法的执行过程中：

  1. 扫描行数是 N+λNM；
  2. 内存判断 N*M 次

内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在 M 和N 大小确定的情况下，N 小一些，整个算式的结果会更小。在数据量大的情况下，如果你的 join 语句很慢，可以把join_buffer_size 改大 ，这样分段数小一点

N 越大，分段数 K 越大。那么，N 固定的时候，什么参数会影响 K 的大小呢？（也就是λ的大小）

答案是 join_buffer_size。join_buffer_size 越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少

结论：join_buffer放不下驱动表时，驱动表越小，扫描被驱动表的次数越少，总的扫描行数越少

能不能使用 join 语句？
推荐使用join字段上加索引方式，没有索引的话性能较差。所以在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样 ，出现的话最好不要使用

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

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

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

参考《MySQL45讲》