join基本语法
MySQL中常见的有三种用法：
select*fromtable1innerjointable2onconditionselect*fromtable1leftjointable2onconditionselect*fromtable1rightjointable2oncondition
innerjoin：内连接（等值连接）leftjoin：左连接rightjoin：右连接
下面用例子来解释这三种用法，假设有两张表，用户表user和部门表depart：
innerjoin
selectuser.name,user.age,depart.departmentfromuserinnerjoindepartonuser.name=depart.name;
innerjoin获取同时符合两张表的数据并组合起来。
在这个例子中，就是在user表和depart表中找到name相同的行记录，并组合起来
来看实际的执行结果：
需要注意的是，如果不指定on条件进行过滤的话，取得的结果就是两张表的笛卡尔积
什么是笛卡尔积？
举个例子，两个集合A{1,2,3}和B{a,b,c}，则两个集合的笛卡尔积为{1a,1b,1c,2a,2b,2c,3a,3b,3c}
selectuser.name,user.age,depart.departmentfromuserinnerjoindepart;
光看这个例子大伙儿可能觉得笛卡尔积没啥用，其实还是很常见的，举个例子：如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积就表示这个学校所有可能的选课情况（梦回大一被数据库支配的恐惧）。
另外，多提一嘴，可能有小伙伴还见过crossjoin，在MySQL中crossjoin与innerjoin的作用是一样的。并且，可以直接省略掉cross或者inner关键字：
leftjoin
selectuser.name,user.age,depart.departmentfromuserleftjoindepartonuser.name=depart.name;
leftjoin取得左表的所有记录，即使右边这个表没有对应的匹配记录(如果没有相匹配的话，用null代替)。
在这个例子中，就是获取user表的所有记录，然后根据on条件去depart表中查询，如果有相同的name，就组合起来，如果depart表中找不到和user表具有相同name的记录，就用NULL代替。
来看实际的执行结果：
rightjoin
selectuser.name,user.age,depart.departmentfromuserrightjoindepartonuser.name=depart.name;
和leftjoin相反，rightjoin取得右表的所有记录，即使左边这个表没有对应的匹配记录(如果没有相匹配的话，用null代替)。
在这个例子中，就是获取depart表的所有记录，然后根据on条件去user表中查询，如果有相同的name，就组合起来，如果user表中找不到和depart表具有相同name的记录，就用NULL代替。
来看实际的执行结果：
fulljoin
回顾下上面三张图，小伙伴们不知道有没有感觉缺了点什么，为啥没有把两个圆都填满的？
把两个圆都填满的学名叫作fulljoin，But，MySQL中并没有fulljoin的语法，需要借助union关键字来实现：
selectuser.name,user.age,depart.departmentfromuserleftjoindepartonuser.name=depart.nameunionselectuser.name,user.age,depart.departmentfromuserrightjoindepartonuser.name=depart.name;
针对join语句该如何建立索引、如何选择驱动表
先来解释下驱动表的概念，以leftjoin为例：
selectuser.name,user.age,depart.departmentfromuserleftjoindepartonuser.name=depart.name;
就是获取user表的所有记录，然后根据on条件去depart表中查询，如果有相同的name，就组合起来，如果depart表中找不到和user表具有相同name的记录，就用NULL代替。
在这个例子中，驱动表就是user，是主动发起查询的表，被驱动表是depart，是根据on条件被查询的表。
当然了，MySQL优化器其实会对驱动表有一个选择的过程，并不会固定说就是user或者就是depart，为了便于下面的分析，我们可以用straight_join来固定驱动表。
select*fromuserstraight_joindepartonuser.name=depart.name;
现在，user表是驱动表，depart表是被驱动表
假设，我们往user表中插入了行数据，depart表中插入了0行数据。下面开始分析
IndexNested-LoopJoin
如果我们在被驱动表depart的字段name上建立索引，那么，上面这条语句的执行流程就是这样的：
从user表中读入一行数据R从数据行R中，取出name字段到表depart的name索引树上去找并取得对应的主键根据主键回表查询，取出depart表中满足条件的行，然后跟R组成一行，作为结果集的一部分重复执行步骤1到3，直到user表的末尾则循环结束
文字看不太懂？没关系，来看下面代码的表示：
这条语句的执行过程就跟我们写程序时的嵌套查询(Nested)类似，并且可以用上被驱动表depart的索引(Index)，所以我们称之为IndexNested-LoopJoin，简称NLJ
来分析下这套流程的时间复杂度：
首先，遍历了一遍user表，一共扫描了行；然后，对这每一行都去depart表中根据name字符进行搜索，由于depart表上建立了name字段的索引，所以每次搜索只需要扫描一行就行了（假设depart表中的name是unique，无重复），这样，depart表上一共只需要扫描行。
所以，这套流程总共需要扫描+=行。
各位小伙伴们再来思考一下，如果这条语句不用join，该怎么实现。
流程大概是这样的：
执行select*fromuser，查出表user的所有数据，这里有行，对吧
循环遍历这行数据：
从每一行R取出字段age的值R.a；
执行select*fromdepartwherea=R.a
把返回的结果和R组合构成结果集的一行
可以看到，这套流程一共需要扫描的行数其实也是行
但是！这套流程里，select*fromuser执行了1次，select*fromdepartwherea=R.a执行了次，总共需要和数据库进行次交互，而使用join的那套流程只需要1次交互就可以了。
高下立见。
上面的语句是基于straight_join来固定驱动表的，现在我们来分析下，我们具体该如何选取驱动表呢？
在这个join语句执行过程中，驱动表走的是全表扫描，而被驱动表由于用上了索引，所以走的是B+索引树的搜索
假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。时间复杂度N假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索name的辅助索引树，然后再回表搜索主键索引树，搜索一棵树的近似复杂度是log2M，所以在被驱动表上查一行的时间复杂度就是2*log2M
因此整个执行过程，近似复杂度是N+N*2*log2M
显然，驱动表行数N对扫描行数的影响更大，因此应该让小表来做驱动表。
多提一嘴，并不是哪个表的行数少哪个表就是“小表”，需要结合过滤条件来判断，计算参与join的各个字段的总数据量，数据量小的那个表，才是“小表”
But，需要注意的是，这个结论的前提是“可以使用被驱动表的索引”
接下来，我们再看看被驱动表用不上索引的情况
SimpleNested-LoopJoin
假设depart表上并没有name字段的索引，再来看这条语句的执行流程：
select*fromuserstraight_joindepartonuser.name=depart.name;
由于表depart的字段name上没有索引，因此每次到depart去匹配的时候，就要做一次全表扫描，整个执行流程是这样的：
从user表中读入一行数据R从数据行R中，取出name字段到表depart上做全表查询，并取得对应的主键根据主键回表查询，取出depart表中满足条件的行，然后跟R组成一行，作为结果集的一部分重复执行步骤1到3，直到user表的末尾则循环结束
显然，这条语句要全表扫描depart表多达次，总共扫描*0=10万行
。。。。。。
这个笨重的算法也有个名字，叫SimpleNested-LoopJoin，简称SNL
当然了，这么**的算法一定是不会被MySQL使用的，而是使用了另一个叫作BlockNested-LoopJoin的算法，简称BNL
BlockNested-LoopJoin
BNL引入了join_buffer的概念（别慌，很简单），感觉不需要多作解释，我们直接来看BNL的执行流程：
把表user中的数据读入线程内存join_buffer中，由于我们这个语句中写的是select*，因此是把整个表user都放入了内存扫描表depart，把表depart中的每一行取出来，跟join_buffer中的数据做对比，满足on条件的，就作为结果集的一部分返回
上张图看一下：
需要注意的是，join_buffer中的数据都是无序存储的。基于这点，我们来分析下时间复杂度：
首先，扫描user表全部数据并加入join_buffer，一共行；然后，对表depart中的每一行，取出来跟join_buffer中的数据分别做判断，每行数据都需要做次判断，因此，总共需要做的判断次数是：*0=10万次
这么一分析好像和SNL的时间复杂度差不多了？
确实，But，这个操作是在join_buffer也就是内存中做的，所以速度上会快很多
不过，看到这里，我想大伙儿还是没明白，这个BlockNested-Loop中的Block体现在哪里呢?
很简单，join_buffer的大小肯定是有限的啊，它是由参数join_buffer_size设定的，默认值是k，如果表user中的数据比较大，join_buffer一次性放不下怎么办？
分块！！！或者说，分块去join
假设我们调小了join_buffer_size，使得user表在存入第60行数据的时候join_buffer就存不下了，来看整个的执行流程是什么样的：
扫描表user，顺序读取数据行放入join_buffer中，放完第60行时join_buffer满了，执行下一步扫描表depart，把depart中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回清空join_buffer（重点就是这一步）继续扫描表user，顺序读取最后的40行数据放入join_buffer中，然后继续执行第2步
这样，总共需要做的判断次数仍然是(60+40)*0=10万次。
我们再来看下，在这种情况下该如何选择驱动表：
假设，驱动表的数据行数是N，join_buffer被分成了K段，被驱动表的数据行数是M：
内存判断N*M次。显然，内存判断次数是不受选择哪个表作为驱动表影响的
扫描行数是N+K*M。另外，N越大K就越大，所以可以把K表示为λ*N，λ取值范围是0~1，也即扫描行数是N+λ*N*M
在M和N大小确定的情况下，N越小，整个算式的结果越小。所以结论是，应该让小表当驱动表。
另外，λ作为式子的参数其实也非常重要，这个值越小就代表分的段越少，即一次可以放入join_buffer的行越多，这样，对被驱动表的全表扫描次数就越少。所以，调大join_buffer_size也是一个明智的选择。
小结
小结一下，可以看到，对于join语句来说，最好的情况就是可以用上被驱动表的索引，这样用的就是INL算法，比不用join语句的普通嵌套查询性能要好。而如果用不上被驱动表的索引话，无论是SNL还是使用join_buffer的BNL，其实代价都是比较大的，都会占用大量的系统资源。
至于join语句的驱动表问题，我们总是应该使用小表做驱动表（并不是哪个表的行数少哪个表就是“小表”，需要结合过滤条件来判断，计算参与join的各个字段的总数据量，数据量小的那个表，才是“小表”）。