MySQL 中 Join 的用例与原理

left join / right join / inner join对比

区别：

• left join:保全左表数据和右表中联结字段相等的记录；如果右表没相关数据，会显示null
• right join:保全右表数据和左表中联结字段相等的记录；如果左表没相关数据，会显示null
• inner join:只返回两个表中联结字段相等的行，都不是null才返回

用例

基础表数据：


left join:
查询所有考生在不同场考试中的答题信息。左表的一条记录可能对应多条右表的记录

right join:
可以看到此处使用的 right join 与上面的 left join 效果一样

（inner） join:
筛选出参加过考试的学生

MySQL的 join 算法原理

  三种算法 Simple Nested-Loop Join 、Index Nested-Loop Join 、 Block Nested-Loop Join，后两者均是从 Simple Nested-Loop Join 中， 从减少嵌套的循环次数来提高处理效率的。某些商业的数据库可以支持哈希链接和合并连接等。

Simple Nested-Loop Join（简单嵌套循环连接）

时间复杂度为 驱动表数据条数 * 非驱动表数据条数。
该方法对非驱动表访问次数多，数据库压力大，效率低。

Index Nested-Loop Join（索引嵌套循环连接）

时间复杂度为 驱动表的行数 * 非驱动表索引树的高度。
通过匹配条件直接与非驱动表索引（即 ~ Join ~ ON 后的字段上的索引）进行匹配，减少了对非驱动表的匹配次数。

Block Nested-Loop Join（缓存块嵌套循环连接）

时间复杂度为 N（N<=驱动表的行数，与设置的缓存大小有关）* 非驱动表数据行数。
查找的时候MySQL会将所有需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列，然后批量与非驱动表进行比较，减少了对非驱动表的访问次数。

join_buffer 默认为256K。

MySQL对 join 算法的选择

Block Nested-loop是开启的（默认情况），如果关联列上有索引，会使用 Index Nested-loop；关联列上没有索引时，才会使用Block Nested-loop。
Block Nested-loop关闭，如果关联列上有索引，会使用 Index Nested-loop；关联列上没有索引时，使用 Simple Nested-loop 。

MySQL优化器对驱动表的选择

MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。

• 如果关联列上有索引，驱动表一般会选择 sql 语句中的第一张表
• 当有 where 筛选条件时，会选择数据量少的作为驱动表

join 算法底层原理及实例参考

如何提高 Join 查询的效率？

1. 为关联列添加索引

2. 若关联列添加索引的意义不大，则可以增大join_buffer_size的大小 或 减少不必要的字段查询（字段越少，join buffer 所缓存的数据条数就越多，非驱动表的循环次数就越少）