【mysql 详解】join关联查询详解

多表连表关联查询

• 交叉连接（cross jooin）
• 内连接(inner join)
• 外连接（left join、right join）
• 联合查询(union、union all)
• 全连接(full join)

join流程

驱动表、被驱动表分析

a left join b:表示a是驱动表，b是被驱动表；

a right join b :表示b是驱动表，a是被驱动表；

a inner join b：mysql会自动优化将a b两个表，小表作为驱动表，大表作为被驱动表

索引使用分析（t1表 100条数据， B表1000条数据）

Index Nested-Loop Join

    select * from t1 left join t2 on t1.a = t2.a;

分析：
1.驱动表t1做全表扫描，扫描100行；
2.根据拿到数据行T的字段a，去表t2中匹配，因为表t2中字段a创建了索引，这个查询每次是走树搜索，基本扫描一行数据搞定，所以也是一共扫描就是100行；
3.因此总共扫描200行数据。

Simple Nested-Loop Join

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

分析：
1.表t1走全表扫描，扫描100行
2.从t1表中取出字段，到t2去匹配，因为表t2的字段b上没有索引，那么会走全表扫描，这个过程扫描的行数为100*1000，也就是10万行数据；

Block Nested-Loop Join

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

分析：
1.从表t1中，全表扫描，取出所有数据100条放入join_buffer中，因为是select * 所以是把全部数据放入缓存；
2.因为表t2上字段b没有索引，把表t2拿出来的数据与join_buffer中的数据进行对比，满足join条件的放入结果集中；
3.表t1和表t2都是全表所描总计1100行，因为join_buffer中的数据是无须的，因此每次从表t2中拿出一条数据进行比较需要比较100次，因此总比较次数也是100*1000次，但是这个比较是在内存中进行的，速度上会快很多，性能上也会更好。

总结：

• 计算公式：如果驱动表的行数为N，被驱动表的行数为M，驱动表走全表扫描，被驱动表走的是索引数的查找，那么驱动表在查找一条数据后，在被驱动表上走普通索引a，在根据普通索引上的主键回表查询数据，走一次树搜索的时间复杂度为log2M，回表一次就是2log2M，驱动表全表扫描，扫描行数为N，那么总扫描行数为N+N2*log2M，那么可以总结出，驱动表的数据越小，整个过程扫描的行数就越小，因此我们应该使用小表作为驱动表，大表作为被驱动表。
• 这个使用我们来总结一下使用什么表：做驱动表会更好，如果小表行数为N，大表行数为M，因为没有索引总扫描行数为N+M，在内存中比较的次数为N*M，从中可以看出来，无论谁是驱动表，并不影响其性能。

参考

mysql多表查询详解: 交叉连接、内连接、外链接、左连接、右连接、联合查询、全连接

关联查询join的流程以及优化