MySQL Join原理

Join的类型

1. left join，以左表为驱动表，以左表作为成果集根底，衔接右表的数据补齐到成果会集

2. right join，以右表为驱动表，以右表作为成果集根底，衔接左表的数据补齐到成果会集

3. inner join，成果集取两个表的交集

4. full join，成果集取两个表的并集

1. 1. mysql没有full join，union取代
   2. union与union all的区别为，union会去重

5. cross join 笛卡尔积

1. 1. 如果不运用where条件则成果集为两个相关表行的乘积
   2. 与，的区别为，cross join树立成果集时会依据on条件过滤成果调集

7. straight_join

1. 1. 严厉依据SQL顺序指定驱动表，左表是驱动

Join原理

本质上能够理解为嵌套循环的操作，驱动表作为外层for循环，被驱动表作为内层for循环。依据衔接组成数据的策略能够分为三种算法。

Simpe Nested-Loop Join

1. 衔接比如有A表，B表，两个表JOIN的话会拿着A表的连表条件一条一条在B表循环，匹配A表和B表相同的id 放入成果集，这种功率是最低的。

Index Nested-Loop Join

1. 执行流程（磁盘扫描）

1. 1. 从表t1中读入一行数据 R；
   2. 从数据行R中，取出a字段到表t2里进行树搜索查找；
   3. 取出表t2中满意条件的行，跟R组成一行，作为成果集的一部分；
   4. 重复执行过程1到3，直到表t1的末尾循环结束。

2. 而关于每一行R，依据a字段去表t2查找，走的是树搜索过程。

Block Nested-Loop Join

1. mysql运用了一个叫join buffer的缓冲区去减少循环次数，这个缓冲区默许是256KB，能够经过指令show variables like ‘join_%’检查
2. 其详细的做法是，将第一表中符合条件的列一次性查询到缓冲区中，然后遍历一次第二个表，并逐一和缓冲区的所有值比较，将比较成果参加成果会集
3. 只有当JOIN类型为ALL，index，rang或者是index_merge的时候才会运用join buffer，能够经过explain检查SQL的查询类型。

Join优化

1. 为了优化join算法选用Index nested-loop join算法，在衔接字段上树立索引字段
2. 运用数据量小的表去驱动数据量大的表
3. 增大join buffer size的巨细（一次缓存的数据越多，那么外层表循环的次数就越少）
4. 留意衔接字段的隐式转化与字符编码，防止索引失效