- DBA禁用join
- 若有两个大小不同的表做join，用哪个表做驱动表？
今天这篇文章，我就先跟你说说join语句到底是怎么执行的，然后再来回答这两个问题。
示例表：
- 往表t2里插入了1000行数据
- 在表t1里插入的是100行数据
可见，两表都有一个主键索引id和一个索引a
Index Nested-Loop Join
代码语言：javascript复制select *
from t1 straight_join t2 on (t1.a = t2.a);若直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表，这会影响我们分析SQL语句的执行过程。为便于分析执行过程中的性能，改用straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。所以，该语句里：
- t1 是驱动表
- t2是被驱动表
- 使用索引字段join的 explain结果
t2的字段a上有索引，join过程用了该索引，因此该语句执行流程：
- 从t1读入一行数据 R
- 从数据行R中，取出a字段到t2里查找
- 取出t2中满足条件的行，跟R组成一行，作为结果集一部分
- 重复执行步骤1到3，直到t1的末尾循环结束
这个过程是先遍历t1，然后根据从t1中取出的每行数据中的a值，去t2中查找满足条件的记录。形式上和我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，称之为“Index Nested-Loop Join”，NLJ。
- Index Nested-Loop Join算法的执行流程 TODO
该流程：
- 对驱动表t1做了全表扫描，需扫描100行
- 对于每一行R，根据a字段去t2查找，这是树搜索。由于构造数据一一对应，因此每次搜索过程都只扫描一行，共扫描100行
- 所以，整个执行流程，总扫描行数是200
所以能不能使用join?
假设不使用join，那就只能用单表查询：
代码语言：javascript复制select * from t1查出t1所有数据，这里有100行。 循环遍历这100行数据：
- 从每一行R取出字段a的值$R.a
- 执行select * from t2 where a=$R.a
- 把返回的结果和R构成结果集的一行
该查询过程，也扫描了200行，但共执行了101条语句，比join多了100次交互。而且客户端还要自己拼接SQL语句和结果。 这性能还不如直接join。
怎么选择驱动表？
该示例中，驱动表t1走全表扫描，被驱动表t2走树搜索。
假设被驱动表行数M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树的时间复杂度log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。
假设驱动表行数N，执行过程就要扫描驱动表N行，然后对每一行，到被驱动表上匹配一次。
因此整个执行过程，时间复杂度是 N N*2*log2M。N扩大1000倍，扫描行数就会扩大1000倍；而M扩大1000倍，扫描行数扩大不到10倍。
可见，N严重影响扫描行数，应该让小表做驱动表。
小结
- 使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好
- 如果使用join语句的话，需要让小表做驱动表。
这些结论的前提是“可以使用被驱动表的索引”。
若被驱动表用不上索引呢？
Simple Nested-Loop Join
代码语言：javascript复制select * from t1 straight_join t2 on (t1.a=t2.b);t2的b无索引，所以每次到t2去匹配时，就要做一次全表扫描。
但这样，该SQL就要扫描t2 100次，共扫描100*1000=10万行。若t1和t2都是10万行的表，就要扫描100亿行！
当然，MySQL也没有使用这个Simple Nested-Loop Join算法，而使用“Block Nested-Loop Join”算法，BNL。
Block Nested-Loop Join
被驱动表无可用索引时的算法流程：
- 把t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存
- 扫描t2，把t2中的每一行取出来，对比join_buffer数据，满足join条件的，作为结果集的一部分返回。
- BNL执行流程 TODO
- 不使用索引直接join的执行计划
t1、t2都做了次全表扫描，因此总扫描行数1100。由于join_buffer是以无序数组组织，因此对t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。
若使用SNL算法查询，扫描行数也是10万行。因此，时间复杂度一样的。但BNL算法的这10万次判断是内存操作，速度上会快很多，性能较好。
那么此时哪个表做驱动表呢？
假设小表的行数是N，大表的行数是M，则在该算法里：
- 两个表都做一次全表扫描，总扫描行数：
M N - 内存中判断次数
M*N
所以调换M和N无差异，所以选择哪个做驱动表，执行耗时都一样。
- 若表t1是个大表，join_buffer放不下咋办？ join_buffer的由参数join_buffer_size设定，默认256k。若放不下t1的所有数据，就会分段放。
把join_buffer_size改成1200，再执行：
代码语言：javascript复制select *
from t1 straight_join t2 on (t1.a = t2.b);执行过程如下：
- 扫描t1，顺序读取数据行放入join_buffer，放完第88行join_buffer满了，继续第2步
- 扫描t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回
- 清空join_buffer
- 继续扫描t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步
step4、5，表示清空join_buffer再复用。这也能看出该算法的确是分块join。
此时由于t1被分成两次放入join_buffer，导致t2会被扫描两次。虽然分成两次放入join_buffer，但判断等值条件的次数不变，依然是(88 12)*1000=10万次。
此时如何选择驱动表？ 假设，驱动表数据行数N，需分K（K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)）段完成，被驱动表数据行数M。 所以，该算法执行过程：
- 扫描行数 N λNM
- 内存判断 N*M次
显然，内存判断次数是不受选择影响。观察扫描行数，在M和N确定时，N越小，结果越小。 所以应该让小表当驱动表。
在N λ*N*M中，λ才是影响扫描行数的关键因素，越小越好。
N越大，分段数K越大。那么，N固定时，什么会影响K呢？（ 即λ的大小）答案是join_buffer_size： join_buffer_size越大，一次可放入行越多，分段数越少，被驱动表全表扫描次数越少
所以若你的join很慢，就把join_buffer_size加大。
综上：
能不能使用join
若使用INL，当可以用被驱动表的索引，是没问题的。 若使用BNL，扫描行数就会过多。尤其是在大表上的join，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join禁用。
所以判断要不要使用join，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”。
若使用join，大表or 小表做驱动表？
- INL： 选择小表做驱动表
- BNL：
- 在join_buffer_size足够大时，一样
- 在join_buffer_size不够大时（常见情况），选择小表做驱动表
所以，该问题最终结论：永远使用小表做驱动表。
什么叫“小表”？
若加上 where t2.id<=50：
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;使用 b 是为了让被驱动表都用不上索引。
但若用第二个语句，join_buffer只需放入t2的前50行，显然更好。所以这里“t2的前50行”是那个相对小的表，即“小表”。
再看个例子：
代码语言：javascript复制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;该例中，t1、t2都只有100行参与join。但这俩语句每次查询放入join_buffer的数据不同：
- t1只查字段b，因此若把t1放到join_buffer，只需放入b值
- t2需要查所有字段，若把t2放到join_buffer，就要放入所有字段
所以应该选择t1作为驱动表。该例中，“只需要一列参与join的t1”是相对的小表。
在决定哪个表做驱动表时，应该是两个表按各自条件过滤，过滤完后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，将其作为驱动表。
