一、join的优化：
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();
在t1中插入了1000行数据，每一行的a=1001-id的值。即表t1中字段a是逆序的。在表t2中插入了100万行数据。
1、Multi-Range Read(MRR)优化回表操作：
对于语句：
select * from t1 where a>=1 and a<=100;
<1>、优化前：
(1)、执行过程：
主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据。回表时是一行行搜索主键索引的。如果随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能相对较差。
(2)、基本回表流程：
![]()
<2>、优化后：
(1)、优化目的：
尽量使用顺序读盘。
(2)、设计思路：
因为大多数的数据都是按照主键递增顺序插入得到的，所以可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
(3)、执行流程：
- 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;
- 将read_rnd_buffer中的id进行递增排序；
- 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。
注意：
read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的。如果步骤1中，read_rnd_buffer放满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。之后继续找索引a的下个记录，并继续循环。
如果想要稳定地使用MRR优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用MRR，把mrr_cost_based设置为off，就是固定使用MRR了。）
(4)、判断是否使用MRR优化方法：
可以从explain结果中看到Extra字段多了Using MRR，表示的是用上了MRR优化。而且，由于我们在read_rnd_buffer中按照id做了排序，所以最后得到的结果集也是按照主键id递增顺序的。
<3>、MRR能够提升性能的原因：
这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
2、Batched Key Access(BKA)算法----对Index Nested-Loop Join的优化：
<1>、使用方法：
在执行SQL语句之前，先设置
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
其中，前两个参数的作用是要启用MRR。这么做的原因是，BKA算法的优化要依赖于MRR。
<2>、优化方法：
把NLJ算法执行的逻辑“从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。”优化成“从表t1里一次性地多拿些行出来，一起传给表t2。既然如此，我们就把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是join_buffer。”
3、Block Nested-Loop Join(BNL)算法的优化：
<1>、BNL的性能问题：
(1)、大表join操作对IO的影响：
使用Block Nested-Loop Join(BNL)算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，会导致IO压力大。
(2)、大表join操作对Buffer Pool的影响：
由于InnoDB对Bufffer Pool的LRU算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在old区域。如果1秒之后这个数据页不再被访问了，就不会被移动到LRU链表头部，这样对Buffer Pool的命中率影响就不大。
但是，如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1秒，就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部。这种情况对应的是冷表的数据量小于整个Buffer Pool的3/8，能够完全放入old区域的情况。
如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入young区域。
由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但是由于join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。这样，就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。
所以，大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。为了减少这种影响，可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数。
(3)、BNL算法对系统的影响：
- 可能会多次扫描被驱动表，占用磁盘IO资源；
- 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
- 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。
<2>、对BNL算法的优化：
(1)、BNL转BKA：
一些情况下，可以直接在被驱动表上建索引，这时就可以直接转成BKA算法了。
-
如果这条语句同时是一个低频的SQL语句，那么再为这个语句在表t2的字段b上创建一个索引就很浪费了。此时可以考虑使用临时表。使用临时表的大致思路是：
- 把表t2中满足条件的数据放在临时表tmp_t中；
- 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
- 让表t1和tmp_t做join操作。
(2)、扩展-hash join：
①、MySQL的优化器和执行器不支持哈希join。
②、将hash join实现在业务端的思路：
- select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构，比如C++里的set、PHP的dict这样的数据结构。
- select * from t2 where b>=1 and b<=2000; 获取表t2中满足条件的2000行数据。
- 把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。
4、总结：
BKA优化是MySQL已经内置支持的，建议默认使用；
BNL算法效率低，建议都尽量转成BKA算法。优化的方向就是给被驱动表的关联字段加上索引；
基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的；
MySQL目前的版本还不支持hash join，但可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。
