mysql join原理

Mysql 是只支持一种join 算法Nested-Loop Join(嵌套循环连接)，并不支持幻想连接和合并连接，不过在mysql中包含多种变化，能够帮助mysql提高join执行效率。

simple Nested-Loop Join

select * from t1,t2;

这个算法很简单了，就是t1中每列去配合t2的所有列，匹配次数 t1（列数）*t2（列数） 。

Index Nested-Loop Join

1. 索引嵌套联系由于非驱动表上有索引，所以比较的时候不需要一条条记录进行比较，而可以通过缩影来减少比较，从而加快查询速度。这也是平时我们在做关联查询的时候必须要求关联字段有索引的主要原因。
2. 这种算法在连接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到符合的值，再回表进行查询，也就是只有匹配到索引以后才会进行回表。驱动表的选择，一般是选择记录数量少的作为驱动表，但是sql很复杂的时候也会选择失败。
3. 如果非驱动表的关联键是主键的话，性能就会很高。如果不是主键，关联返回行数会很多，这样效率就很低，因为要多次进行回表操作。

Block Nested-Loop Join

1. 在有索引的情况下，会优先使用Index Nested-Loop Join 算法。没有索引的情况下才会使用Block Nested-Loop Join算法。
2. Block Nested-Loop Join对比simple Nested-Loop Join多了一个中间处理的过程，就是join buffer 。
3. 会将驱动表中所有相关列都先缓存，然后匹配非驱动表将一种和多种合并为一次，减低非驱动表访问频率。
4. 默认join buffer size =256k，这个设置，缓存驱动表的关联列和select的查询列。
5. 一个查询语句中有n个join 就会有n-1 join buffer 缓存

mysql面试总结