MySQL深入31-join语句优化
# MySQL深入31-join语句优化
相关文章：
MySQL深入30-join使用 (opens new window)
join语句有两种算法，分别是Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。
其中NLJ算法效果还是不错，比通过应用层拆分成多个语句然后再拼接查询结果更方便，而且性能也不会差。但是BNL算法在大表join的时候性能就差很多了，比较次数等于两个表参与join的行数的乘积，很消耗CPU资源。
# Multi-Range Read优化
Multi-Range Read 优化 (MRR)：这个优化的主要目的是尽量使用顺序读盘。
InnoDB索引结构会涉及到“回表”，回表是指，InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据的过程。
select * from t1 where a>=1 and a<=100;
主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据，回表肯定是一行行搜索主键索引的。
如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。但是如果我们回表查询能够按照主键的递增顺序查询的话，那么磁盘的读比较接近顺序读，能够提升读性能。在MRR优化之后，语句的执行流程如下：
- 根据索引 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能够提升性能的核心在于，得到的非主键索引列表排序后再去主键索引差数据，体现出“顺序性”的优势。
# Batched Key Access
首先我们来回顾一下NLJ算法的流程：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join，对于表t2来说，每次相当于是匹配一个值。
如果要使用MRR对其优化，就需要一次性多传值给表t2，也就是说表t1并不能和之前那样每次只取一个值而是要一次性取多行出来，一起传给表t2。t1的数据取出来的那部分存放到一个临时内存，这个临时内存就是join buffer。
join_buffer 在 BNL 算法里的作用，是暂存驱动表的数据。但是在 NLJ 算法里并没有用。
当join buffer放不下一定数量的数据的时候，就会把这一定数量的数据分成多段执行上图的流程。
# BNL算法优化
# BNL算法性能问题
BNL算法无论是对驱动表还是对被驱动表都会进行全表扫描，这样会导致IO压力过多。同时还存在下面一个问题：
由于 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 区域的数据页没有被合理地淘汰。
总结来说，BNL算法对系统的影响主要包括三个方面：
- 可能会多次扫描被驱动表，占用磁盘 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 算法，提升查询性能。