join的优化
MRR
Multi-Range Read优化(MRR)。这个优化的主要目的是尽量使用顺序读盘。
大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。这就是MRR优化的设计思路。
如果你想要稳定地使用MRR优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用MRR，把mrr_cost_based设置为off，就是固定使用MRR了。）
MRR能够提升性能的核心在于范围查询,这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
Batched Key Access
MySQL在5.6版本后开始引入的Batched Key Acess(BKA)算法了。这个BKA算法，其实就是对NLJ算法的优化。
算法内容：
从驱动表取出所需字段的值，将其存入临时内存join_buffer。然后一个join_buffer大小批次的进行比对。优化一行行进行比对的过程。（从驱动表里一次性地多拿些行出来，一起传给被驱动表。）
使用：
要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
前两个参数的作用是要启用MRR,因为BKA算法的优化要依赖于MRR。
BNL算法对系统的影响主要包括三个方面：
大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
-
可能会多次扫描被驱动表，占用磁盘IO资源；
-
判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
-
可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。
BNL转BKA
- 一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成BKA算法了。
- 一些不适合在被驱动表上建索引的情况，可以考虑采用临时表，大致思路：
- 把表t2中满足条件的数据放在临时表tmp_t中；
- 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
- 让表t1和tmp_t做join操作。
- 对应语句：
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);
- 不管是临时表还是加索引 ，整体思路都是让join语句能够用上被驱动表上的索引，来触发BKA算法，提升查询性能。
扩展-hash join
如果join_buffer里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是10亿次判断，而是100万次hash查找。这样的话，整条语句的执行速度就快很多。但是mysql不支持hash join
总结：
- BKA优化是MySQL已经内置支持的，建议你默认使用；
- BNL算法效率低，建议你都尽量转成BKA算法。优化的方向就是给被驱动表的关联字段加上索引；
- 基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的；
- 尽量让每一次参与join的驱动表的数据集，越小越好，因为这样我们的驱动表就会越小。
