MySQL深入——24

Join语句到底可不可用

join语句用于两个或多个表当中检索数据。

INNER JOIN或者JOIN 当两个表当中有匹配的值时，返回行

LEFT JOIN或者LEFT OUTER JOIN  返回所有左边的行，即使右表当中没有相匹配的行

RIGHT JOIN或者RIGHT OUTER JOIN  返回所有右边的行，即使左表当中没有相匹配的行

FULL JOIN或FULL OUTER JOIN  左表或右表有匹配的行时，返回行。

一般在日常生活当中，Join语句通常集中于下面这几个问题：1.DBA（DataBase Administer数据库管理员）不让使用join，使用join有什么问题？2.若有两个大小不同的表，选择哪个表作为驱动？

join语句的执行流程

创建两个表t1，t2，都有主键索引id和索引a，给t1插入100行数据，给t2插入1000条数据。

若是直接使用join语句，MySQL的优化器可能会选择t1或t2作为驱动表，为了方便分析，便限定出驱动表是什么。

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

使用straight_join让MySQL使用固定的方式执行查询，t1作为驱动表，t2作为被驱动表。

通过查看explain语句，知道了执行流程为：

1.从表t1中读取一行数据R 

2.从数据页R当中取出字段到表t2中去查找满足的行，当满足的时候作为结果集的一部分返回

4.在t1中再读取一行进行对比。

5.直到t1被取完。

这个流程当中，对t1进行全表扫描，对于每一个行R，根据a索引去t2查找，走的是树查找过程，t1是全表遍历，总计扫描200行。这个算法称之为：Index Nested-Loop Join简称为NLJ算法。

那么假设不使用join，使用单表查询来做，首先得select * from t1  取出t1所有的值，共计100行。然后取出a的值进入t2进行遍历查询，返回结果构成一个结果集。我们可以看到不仅多了100多次交互，而且我们还得自己拼接结果集，所以使用join更好。

那么该如何选择驱动表呢？

通过分析得知，join语句执行过程中，驱动表走全表查询，被驱动表走树查询，可知驱动表越小越好。但是前提是我们可以使用被驱动表的索引下，若果被驱动表不使用索引会是什么情况。

将select语句改为select * from t1 straight_join t2 on（t1.a=t2.b）

b上无索引，所以每次匹配都要做一个全表扫描，这样子看上去十分繁重，所以InnoDB使用了一个Block Nested-Loop Join算法简称BNL算法，实现如下

因为select * 所以我们将t1全表放入到线程内存Join_buffer当中，扫描t2，将t2的每一行与Join_buffe当中的值作比较，由于Join_Buffer是无序的，所以对于t2的每一行，都要走一遍全表判断，故在内存做判断数为10万次。

因为是在内存当中操作，所以会更快。

而且这里是无所谓选什么表作为驱动表的，因为扫描的行数始终为一个表的行数乘上另外一个表的行数。

这里其实还存在着一个问题，要是表的数据过大，join_buffer无法放下一整个表该如何呢？
join_buffer的大小由join_buffer_size决定，默认为256k

处理方法其实很简单，就是分段放，假设join_buffer只能容纳下t1的一半，那么我们先放一半进去，然后扫描t2，将t2当中每一行取出来与t1在join_buffer当中的数据作比较，满足Join条件的返回结果集，最后判断结束后，清空join_buffer，将剩下的join_buffer放入继续判断。

这种情况下的驱动表选择是有说法的，驱动表的行数为n，被驱动表为m，n改为x*n(x*n是能放入join_buffer的大小）

1.扫描：n+x*n*m

2.内存判断：n*m次

我们可以看到x是越小越好的，join_buffer的大小固定，表越小x越小，所以选择小表作为驱动表。

那么什么是小表，定义是什么呢？

可以让两个表都不用索引，按照自己的条件过滤，运行完成之后计算join的各个字段数据量，数据量少的即为小表作为驱动表。