➤ Join中的驱动表概念:
- left join: 左表为驱动表
- right join: 右表为驱动表
➤ InnoDB是如何实现Join的?
Mysql的JOIN是通过 Nested-Loop Join 算法实现的(嵌套循环, 如同名字, 循环外层表也即驱动表, 外层表每一行数据再去内层表查找符合条件的数据 ), 有三种:
- Simple Nested-Loop Join 算法效率最低(Mysql并不使用这种Join算法), 可以认为是: 遍历外层所有符合条件的列, 每一行外层数据都再去遍历内层表, 依次比较, 如果外层表有N行(符合条件的)数据, 内层表有M行数据, 总共需要
N*M次比较; - Index Nested-Loop Join (索引嵌套循环): 需要内层表的条件列有索引, 依然循环外层表所有符合条件的行, 但是由于内层表的条件列有索引, 并不需要对内层表进行全表扫描. (当内层表建立了索引, Mysql优先使用这种方式)
- Block Nested-Loop Join (缓存块嵌套循环): 如果内层表没有索引会使用这种方式, 思路就是减少外层循环次数, 具体做法是: 外层表查出多条数据, 放入 join buffer, 然后以 join buff里的多条数据作为条件, 对内层表进行全表扫描. 能有效减少外层表的循环次数(也减少了内层表进行全表扫描的次数)
➤ 如何让Mysql使用 Block Nested-Loop Join:
- 设置 optimizer_switch的值为
block_nested_loop=on - 设置 join_buffer_size大小
➤ How to 优化 Join:
- 用小结果集驱动大结果集(减少外循环次数)
- 为内层表的条件列增加索引(避免内层全表扫描)
- 增大join buffer size的大小（一次缓存的数据越多，那么外层表循环的次数就越少）
- 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少）
- 合理使用覆盖索引, 减少回表次数
@ref: