join的优化

join的优化

multi_range read优化(mrr)

• 大多数的数据都是按照顺序来新增的,如果按照顺序对主键进行访问,接近于磁盘的顺序读,提高性能
  • 根据索引定位到满足条件的记录,将id放入read_rnd_buffer
  • 将read_rnd_buffer的id进行递增排序
  • 排序后的id数组,依次到主键查记录,作为结果返回
    稳定性的使用mrr,要用 set optimizer_switch=“mrr_cost_based=off”
    用explain时,在extra有using MRR,就是使用到了

Batchd key access(对NLj的优化,Index Nasted-Loop join)5.6之后

• 也就是在mrr的基础上加了join buffer(依赖MRR)
  要使用BKA的话,要在执行sql之前,执行下面这命令

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

Block Nasted-Loop join(BLN)的缺点

• 可能会多次扫描被驱动表,占用io资源
• 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
• 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率

优化

• 在被驱动表的join字段上面加索引,把BLN转成BKA
• 如果在被驱动表的join字段不好加索引,可以引入临时表,把被驱动表符合条件的数据放入临时表,然后给临时表join的字段加索引(为了触发BKA),降低了被驱动表的数据量,也优化了

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

hash join的优化

• 如果join buffer里面不是无序数组,而是hash的话,就能精准匹配了,所以可以在业务系统里面自己加
• 把驱动表符合要求的数据放到业务系统中,并维护一个hash,然后从被驱动表中获取符合要求的数据,在来和hash进行匹配和淘汰,数据量大的情况比临时表来的快