35 怎么优化join
上一篇介绍了join的两种算法：nlj和bnl
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优化
Multi-range read（MRR），优化的主要目的是尽量使用顺序读盘。
在介绍innodb的索引结构时，提到了”回表”的概念，回表是指，在innodb普通索引a上查到主键id的值后，再根据一个个主键id的值到主键索引上去查询整行数据的过程。
但是回表的过程是一行行的查数据还是批量的查询数据呢？
select * from t1 where a>=1 and a<=100;
主键索引是一个B+tree，在这个树上，每次只能根据一个主键id查到一行数据，因此，回表肯定是一行行搜索主键索引的
随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能相对较差，虽然”按行查”这个机制不能改，但是调整查询的顺序还是能够加速的。
因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
这就是mrr的优化设计的思路，语句的执行流程：
--1 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中
--2 将read_rnd_buffer中的id进行递增排序
--3 排序后的id数组，依次到主键id索引中查询记录，并作为结果返回。
这里read_rnd_buffer的大小由read_rnd_buffer_size参数控制，如果read_rnd_buffer放满了，就会先执行步骤2和3，然后清空read_rnd_buffer，之后继续找索引a的下个记录，并继续循环。--如果主键是uuid类型，排序就没有必要了，也没有必要使用MRR。
如果要稳定的使用MRR优化，需要设置set optimizer_switch='mrr_cost_based=off';
使用mrr的流程
explain
可以看到extra字段多了MRR，表示的是用上了mrr优化，而且，由于我们在read_rnd_buffer中按照id做了排序，所以最后得到的结果集也是按照主键id递增顺序的，
MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出”顺序性”的优势。
Bathed Key Access
在mysql 5.6版本开始引入了BKA算法，其实是对NLJ算法的优化。
在看一下上一篇提到的NLJ算法的流程
NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2上去做join，也就是说，对于表t2，每次都是匹配一个值，这时，mrr的优势就用不上了。
现把表t1的数据取出来一部分，先放到一个临时内存，这个临时内存就是join_buffer
上面NLJ算法优化后
图中，在join_buffer中放入的数据是R1-R100，表示只会取查询需要的字段，当然join_buffer中放不下R1-R100的所有数据，就会把这100行数据分成多个段执行
开启BKA算法
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
BNL算法的性能问题
如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1秒，就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部。
如果这个冷表很大，就会出现另外一种情况，业务正常访问的数据页，没有机会进入young区域，由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒再次被访问到，但是，由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了，这样，就会导致这个mysql实例的buffer pool在这段时间内，young区域的数据页每页被合理淘汰掉。
大表join操作虽然对io有影响，但是在语句执行结束后，对io的影响也就结束了。但是，对buffer pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
为了减少这种影响，可以考虑增大join_buffer_size值，减少对被驱动表的扫描次数。
也就是说，BNL算法对系统的影响：
--1 可能会多次扫描被驱动表，占用磁盘io资源
--2 判断join条件需要执行M*N次对比（M,N分别是两张表的行数），如果是大表就会占用非常多的cpu资源
--3 可能会导致buffer pool的热数据被淘汰，影响内存命中率。
在执行语句之前，需要通过理论分析和查看explain结果的方式，确认是否使用BNL算法，如果确认优化器会使用BNL算法，就需要做优化，优化的常见做法是，给被驱动表的join字段上加索引，把BNL算法转换成BKA算法。
create index idx_t2_b on t2(b);
BNL转BKA
一些情况下，我们可以直接在被驱动表上建立索引，这时就可以直接转成BKA算法，但是，有时候确实会碰到一些不适合在被驱动表上建立索引的情况
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
在t2表中插入了100w行数据，但是经过where条件过滤后，需要参与join的只有2000行数据，如果这条语句同时是一个低频的sql语句，那么再为这个语句在表t2的字段b上建立一个索引就很浪费了。
使用BNL算法来join的话，这个语句的执行流程
--1 把表t1的所有字段取出来，存入join_buffer，这个表只有1000行，join_buffer_size默认256k，可以完全存入
--2 扫描表t2，取出每一行数据跟join_buffer中的数据进行对比
---如果不满足t1.b=t2.b则跳过
---如果满足t1.b=t2.b，在判断其他条件，也就是是否满足t2.b处于[1,2000]的条件，如果是，就作为结果集的一部分返回，否则跳过。
对于表t2的每一行，判断join是否满足的时候，都需要遍历join_buffer中的所有行，因此判断等值的次数1000*100万次，
查询结果耗时
在explain 中看到使用了BNL算法
在表t2的字段b上创建索引会浪费资源，但是不创建索引的话这个语句的等值判断要消耗1000*100w次
这时候，可以考虑使用临时表，大致思路：
--1 把表t2中满足条件的数据放在临时表tmp_t中
--2 为了让join使用BKA算法，给临时表tmp_t的字段b上加上索引
--3 让表t1和tmp_t做join操作
耗时
相比前面的BNL算法，性能提升很高。
总体来说，不论是在原表上加索引，还是用索引的临时表，我们的思路是让join语句能够使用被驱动表上的索引，来触发BKA算法，提升查询性能。
扩展-hash join
由于mysql的优化器和执行器不支持hash join，可以自己在业务端实现，大致流程
--1 select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构，比如C++里面的set，php的数组这样的数据结构
--2 select * from t2 where b>=1 and b<=2000；获取t2中满足条件的2000行数据
--3 把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据，满足匹配的条件的这行数据，就作为结果集的一行。
小结：
介绍了NLJ和BNL的优化方法
在这些优化方法中：
--1 BKA优化是mysql已经内置支持的，建议默认使用
--2 BNL算法效率低，建议尽量转换成BKA算法，优化的方向就是给被驱动表的管理字段上加上索引
--3 基于临时表的改进方案，对于能够提前过滤小数据的join来说，效果还是很好的
--4 mysql目前的版本还不支持hash join，可以配合应用端模拟出来