mysql Join算法学习记录

通过此次学习可以了解到join算法的种类和原理，从而达到优化join语句

Join算法分类

Nested-Loop Join 中文意思为“ 嵌套循环链接”。

在mysql的实现中，Nested_Loop Join有3种实现的算法：

• Simple Nested-Loop Join:SNLJ,简单嵌套循环连接
• Index Nested-Loop Join:INLJ,索引嵌套循环连接
• Block Nested-Loop Join:BNLJ,缓存块嵌套循环连接
  有先级：
  Index Nested-Loop Join> Simple Nested_Loop Join > Simple Nested-Loop Join

Simple Nested-Loop

1. 简单嵌套循环链接：假如table1有1万条数据，table2有1万条数据，那么数据循环次数 = 1万*1万 = 1亿次，查询效率慢

Index Nested-Loop Join (减少匹配表数据的匹配次数)

2. 索引嵌套循环连接是基于索引进行连接的算法，索引是指非驱动表（被驱动表）的索引，在查询时，驱动表会根据关联字段的索引(被驱动表的索引)进行查询，当在索引上找到符合的值，再回表进行查询。如果驱动表的关联键是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，性能上比索引是主键要慢。

原来的匹配次数 = 驱动表行数 * 被驱动表行数
优化后的匹配次数 = 驱动表索引的行数 * 被驱动表的索引高度

3. 使用场景：只用匹配表 join 的列有索引时，才会使用Index Nested-Loop Join
4. 如果索引是辅助索引而且返回的数据还包括匹配表的其他数据，那么会回匹配表查询，回增加IO操作。
   

Block Nested_Loop Join（减少被驱动表数据的循环次数）

1.缓存块嵌套循环链接是同过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer 里面的数据批量与被驱动表的数据进行匹配，从而减少了被驱动表循环的次数（遍历一次被驱动表就可以批量匹配一次join buffer里面的驱动表数据）。
2.当不使用Index Nested-Loop Join 时，默认使用 Block Nested-Loop Join.

3.什么是Join Buffer?
1. Join Buffer 会缓存 所有参与查询的列而不是只有Join的列。
2. 可以通过调整join_buffer_size来调节缓存大小
3. join_buffer_size 的默认值为256K，在5.1.22版本之前最大值是4G-1，之后才能在64位操作系统下申请大于4G
4. 使用Block Nested-Loop Join 算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on,默认开启

如何优化Join速度

1. 用小的结果集驱动大的结果集，减少外层循环的数量：
   如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候驱动表的循环次数越少，join的速度越快，大结果集的索引不会用到
   2. 为匹配的条件添加索引
   3. 当使用BNLJ时，加大join_buffer_size,一次缓存的数据越多，外层表循环的次数就越少
   4. 减少不必要的字段查询：
   1. 当使用BNLJ时，字段越少，join buffer所缓存的数据就越多，外层表的循环次数就越少
   2. 当使用INLJ时，如果可以不回表查，即利用覆盖索引，那也可以提高查询速度

如何确定驱动表

通常来说就是explain分析之后结果的第一个表就是驱动表

1. 当使用left join时，左表是驱动表，右表为匹配表
2. 当使用right join 时，右表是驱动表
3. 当使用join 时，mysql会选择数据量较小的表为驱动表。
4. 不是主键与主键的join，不用考虑驱动表的选择