【原创】JOIN 详述 (下)

创建数据：

创建表 t1 和表 t2

CREATE table t10(id int PRIMARY key , a int , b int , INDEX(b));
CREATE table t11 like t10;

使用存储过程初始化数据

delimiter ;;
CREATE PROCEDURE load_data()
BEGIN
    DECLARE i int ;
    set i = 1;
    WHILE i <= 1000 DO
        INSERT into t10 VALUES(i,1001-i,i);
        set i = i + 1;
    END WHILE;
    
    set i = 1;
    WHILE i <= 1000000 DO
        INSERT into t11 VALUES(i,i,i);
        set i = i + 1;
    END WHILE;
end ;;
delimiter ;
CALL load_data();

在介绍优化以前，先介绍下 Multi-Range Read 优化 (MRR)，这个优化的主要目的是使用顺序读盘。
例如执行如下 SQL 语句

EXPLAIN select * FROM t10 WHERE b >= 1 and b <= 100;

正常的流程是根据 a 字段的索引树，找到对应的主键 id ，再根据主键 id 回表去主键索引树上去查找数据，虽然访问 a 字段的索引树是顺序 IO，但是回表到主键索引时却不是顺序 IO，这会导致查询的性能比较差。若是启动 MRR 优化，则流程是根据 a 字段的索引找到对应的主键 ID，把主键 ID 放入read_rnd_buffer 中（直到放不行），将 read_rnd_buffer 中的 ID 排序，然后根据排序之后的主键 ID 在主键索引树中找到对应的数据，并作为结果集返回。

NOTE:关于 MRR 比较具体的可以参考下 这篇文章.

上述 SQL 的执行计划如下图所示：

image.png

，
发现没有使用 MRR 优化，如果想要使用 MRR ，还得设置一个变量

set optimizer_switch="mrr_cost_based=off

设置完之后，执行计划如下：

image.png

利用MRR优化 NLJ 算法

NLJ 算法每次只能从驱动表中取出一行去被驱动表去 join，利用 MRR ，可以从驱动表的数据中取出一部分，先放入到临时内存中，由于 join_buffer 在 NLJ 算法中并没有用所以就可以复用 join_buffer 为临时内存，这个算法称之为 Batched Key Acces(BKA) 算法。如果要使用 BKA 算法进行优化，还需设置

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

因为 BKA 算法依赖于 MRR

优化 BNL算法

在 BNL 算法中，如果要 join 大表的话，但 join_buffer 不是很大，导致多次扫描一个冷表，而且多次扫描中的时间间隔有相邻两次的时间是小于 1 秒钟的，将导致冷表的数据页移动到 LRU 冷表头部。

NOTE:关于 LRU 的详细内容请参考这篇文章

也就是说大表 join 操作虽然对 IO 有影响，但这只是暂时的，但是对 Buffer Pool 的影响确实持久的，需要靠后续的查询请求慢慢恢复内存命中率。
怎样才能优化 BNL算法呢？一些情况，可以直接在被驱动表中建立索引，这是可以直接转成 BKA 算法，但是有些情况下在被驱动表中bu不适合建立索引。如下 SQL 语句：

select * from t10 join t11 on (t10.a=t11.a) where t11.a>=1 and t11.a<=2000;

执行计划如下：

image.png

使用 BNL 算法查询用了70多秒

image.png

如果这是一个比较低频的语句，那么在表 t11 中为字段 a 建立一个索引是不划算的。我们可以考虑建立一个临时表来存放表 t11 中满足条件的数据。

CREATE TEMPORARY TABLE temp_t12 (id int PRIMARY key , a int , b int , index(`a`));
insert into temp_t12 select * FROM t11 WHERE t11.a>=1 and t11.a<=2000;
select * FROM t10 left join temp_t12 on t10.a = temp_t12.a

查询的时间一秒不到：

image.png