MySQL两表关联查询分析和算法解析

表关联查询通常有2种算法：NLJ(嵌套循环连接算法)和BNL(基于块的嵌套循环连接算法)

NLJ(嵌套循环连接算法)
一行一行的取第一张表(驱动表)中读取行，然后拿关联字段取第二张表（被驱动表）中取数据，然后取出两张表的结果合集。我们用以下sql为例：t1表2条数据,t2表11行数据
EXPLAIN SELECT t1.* FROM t1 INNER JOIN t2 ON t1.name=t2.name t1和t2表的name都有索引。

通过执行计划可以看出
1、先执行t1表数据，
2、然后拿t1表的每一行数据通过关联字段name去t2表查询，因为我们的t22表字段name建有索引，所以我们每次只会在t2表中扫一行数据。
扫描的总条数，t1表全表查询5条数据+扫描t2表5条数据=10条数据

BNL(基于块的嵌套循环连接算法)
把驱动表的数据读入到join_buffer中，然后扫描被驱动表，把被驱动表的每一行拿出来跟join_buffer中的数据比较。EXPLAIN SELECT t1.* FROM t1 INNER JOIN t2 ON t1.name=t2.name t1和t2表的name都没有索引。

可以看到用的是BNL算法。

1、先把驱动表t1中的5条数据全部读取出来，然后加入到join_buffer(默认256kb)中
2、把t2表中的每一行数据读取出来，然后放到join_buffer中进行比较
3、返回满足条件的数据

使用BNL需要扫描的行数=5+11=16，因为join_buffer中的数据是无序的，所以t2表中每一行数据都需要与join_buffer中的数据比较11*5=55次，所以总的是16次扫描+55次内存中数据判断。
join_buffer默认是256kb，如果表数据放不下，则进行分段放，比如有100行数据，第一次放8行，第二次清空之后放2条。

从上面两个例子可以看出，mysql的查询算法跟两表关联字段是否有索引有关系的 。这是什么道理呢？如果我们两个关联的字段没有索引也走NLJ算法呢，会是什么结果呢。我们可以算一下。t1表5条数据，因为t2表关联的查询字段没有索引，所以需要全表扫描，一共需要扫5*11=55次，相比较我们上面使用BNL算法的16次，明显会慢很多。