MySQL 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();

MRR

Multi-Range Read 优化 (MRR) : 尽量用顺序读盘

回表流程 :

• 一行行搜索主键索引
• id 值是随机，就会出现随机访问

MRR思想 ：对主键的递增顺序查询，更接近顺序读，就能提升读性能

MRR 执行流程 :

1. 根据索引 a，查找所有 ID，把 ID 放入 read_rnd_buffer
2. 再对 read_rnd_buffer 的 id 进行排序
3. 再根据排序后的 id ，查询

稳定用 MRR 优化 :

set optimizer_switch="mrr_cost_based=off"

控制 read_rnd_buffer 大小 :

read_rnd_buffer_size

explain :

• Using MRR : 用上 MRR 优化

BKA

MySQL 5.6 后引入 Batched Key Access(BKA) 算法 : 对 NLJ 算法的优化

NLJ 算法 :

• 每次都匹配一个值 , 无法利用 MRR

BKA 流程 :

• 把 a索引 放入 join_buffer , 再批次查询 ，就能利用 MRR

启动 BKA 优化算法

• 依赖 MRR

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

BNL 优化

Block Nested-Loop Join 算法，可能会对被驱动表做多次扫描。当被驱动表是个大量冷数据表，可能导致 IO 压力大，还可能影响 Buffer Pool 的命中率

BNL 对 Buffer Pool 的俩个影响 :

• 用 BNL 的 Join ，当执行时间超过 1 秒，并再次扫描冷表时，把冷表的数据页会移到 LRU 链表头部 , 影响 young 区
• 业务正常访问的数据页，没机会进入 young 区

为了减少影响，可以考虑增大 join_buffer_size，减少对被驱动表的扫描次数

BNL 算法的影响 :

1. 多次扫描被驱动表，占用磁盘 IO 资源
2. 判断 join 要执行 M*N 次对比（M、N 分别是两张表的行数），当是大表就会占用较大的 CPU 资源
3. 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率

查看 explain 结果

• 确认是否用了 BNL 算法
• 当用了 BNL 算法，尽量优化成 BKA 算法。对被驱动表的 Join 字段加上索引

BNL 转 BKA

不适合在被驱动表上建索引情况 :

• where 后, 只有 2000 行数据
• 低频 SQL , 在 t2.b 上建索引较浪费

select * from t1 join t2 on (t1.b = t2.b) 
where t2.b >= 1 and t2.b <= 2000;

BNL 算法 Join 流程 :

1. 把表 t1 的所有字段取出来，存入 join_buffer 中 (默认 : 256k)
2. 扫描表 t2，取出每行数据与 join_buffer 的数据进行对比
   1. 当不满足 t1.b=t2.b，就跳过
   2. 当满足 t1.b=t2.b , 再判断是否满足 t2.b 在 [1,2000] 中，当是就返回，否则跳过

explain 结果 :

• Extra 用了 BNL 算法

执行时间 :

用临时表优化 :

1. 把表 t2 满足条件的数据放在临时表 tmp_t 中
2. 在临时表 tmp_t 的字段 b 加上索引 , 就能用 BKA 算法
3. 让表 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);

执行时间 : 总和 < 1 秒

过程消耗：

1. insert 插入 temp_t 表中，会对表 t2 做了全表扫描 (100 万)
2. Join 比较时，做了 1000 次带索引查询，该优化效果明显

Hash join

MySQL5 未实现 ：在 join_buffer 构建哈希数据 , 每次查询就快了 (MySQL8 实现了)

应用端实现 ：

1. select * from t1; 取表 t1 的全部数据，存入 Hash 结构
2. select * from t2 where b>=1 and b<=2000; 取表 t2 中满足条件的数据
3. 把表 t2 的每条数据，在 Hash 数据中 , 找匹配的数据