原文地址：https://time.geekbang.org/column/article/80147?cid=100020801

场景


create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

Multi-Range Read（MRR）优化

目的

尽量使用顺序读盘

原理

假设执行：select * from t1 where a>=1 and a<=100;
我们知道InnoDB中有回表的概念，回表时会根据主键id去查询主键索引树，如果a的值递增顺序查询，id的值就变成随机的了，回表就会涉及随机访问，性能相对顺序扫描较差，MRR优化就是将查询优化为按照主键的顺序查询，提升读性能。

优化后执行流程

根据索引a，定位到满足条件的记录，将id值放入read_rud_buffer中
将read_rnd_buffer中的id进行递增排序
排序后的id数组，依次到主键id索引中查记录，并作为结果返回

核心就是这条查询语句在索引a上做的是一个范围查询，可以得到足够多的主键id，这样通过排序以后，再去主键索引查主键，才能体现出“顺序性”的优势

read_rud_buffer由read_rnd_buffer_size参数控制，如果read_rnd_buffer放满了，就会先执行完步骤2、3，然后清空read_rnd_buffer，之后继续找索引a的下一个记录，并继续循环。

注意

由于优化器倾向于不使用MRR，需要设置set optimizer_switch="mrr_cost_based=off"，开启后explain命令中的Extra中可以看到Using MRR

Batched Key Access(BKA)

目的

优化NLJ算法

NLJ算法的缺点

在上一篇文章中我们讲过Index Nested-Loop up Join算法，这个算法是在被驱动表使用到索引时会使用的，会从驱动表中取出每一行数据去被驱动表join，对于被驱动表来说，每次都是匹配一个值，这时MRR的优势就用不上了。

因此我们想怎么才能每次多传些值给被驱动表呢？方法就是从驱动表里一次性地多拿些行出来，一起传给被驱动表。在上一篇文章中我们也讲过join_buffer，但是join_buffer只有BNL算法才会使用，那么BKA算法就是复用join_buffer。

开启BKA

先开启MRR，再开启BKA

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

BNL优化

BNL的缺点

影响Buffer Pool命中率
当使用BNL算法时，可能会对被驱动表做多次扫描，如果这个被驱动表是一个大的冷数据表，除了会导致IO压力大外，由于InnoDB中有buffer pool，并且buffer pool使用的改造后的LRU算法，
即：第一次从磁盘读入内存的数据页，优先放在old区域，如果1s后这个数据页不再被访问了，就不会被移动到LRU链表头部，这样对Buffer Pool的命中率影响就不大。

当使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1s，就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部。
如果这个冷表很大，就会出现另外一种情况：业务正确访问的数据页，没有机会进入yong区
- 由于优化机制的存在，一个正常访问的数据页要进入yong区，需要隔1s再次被访问到，由于join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1s之内就被淘汰了，这样会导致buffer pool在这段时间内，yong区域的数据没有被合理地淘汰过
  总结

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

增大join_buffer_size的值，减少对被驱动表的扫描次数
给被驱动表的join字段加索引，把BNL转成BKA算法

被驱动表上join字段建索引不合适该怎么办

临时表

思路

把表t2中满足条件的数据放在临时表tmp_t中
为了让join使用BKA算法，给临时表tmp_t的字段b加上索引
让t1和tmp_t做join操作 ```sql

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

MySQL 8.0已经指出Hash Join了

上边BNL导致查询次数过多的原因是由于join_buffer里维护的是一个无序数组，如果是一个哈希表的话，就只需要根据被驱动表的数据去哈希表中对应匹配一遍。

也可以在业务代码中实现：

select * from t1，取出表t1的全部数据，存到hashMap中
select * from t2 where b>=1 and b<=2000，获取表t2中满足条件的2000行数据
把这2000行数据一行一行的取到业务端，去hashMap中匹配满足条件的数据

join的优化

场景