Mysql中的join语句优化

skyemax
838 阅读5分钟

上一篇文章我们介绍了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();

创建t1,t2两张表,索引为字段a,t1表1000行,a字段逆序,t2表100W行,a字段正序

Multi-Range Read 优化

实现介绍一下MRR,这个优化的目的主要是为了顺序读盘。

select * from t1 where a>=1 and a<=100;

先看一下这个语句,innodb在普通索引a先查到主键id,再根据主键id去主键索引查到整行数据(回表),流程如下:

这个过程是一行行查主键索引的,随着a值的增量,id变成逆序,也就是随机访问,性能相对较差,虽然按行查这个机制不能改,但是调整查询的顺序,还是能够有加速的。 因为大多数的数据都是按主键递增顺序插入的，所以我们可以认为：如果按照主键递增顺序查询的话，比较接近顺序读，能够提升性能
MRR优化的执行流程:

  1. 根据索引a查到id,放到read_rnd_buffer;
  2. 将read_rnd_buffer按照id递增排序;
  3. 一行行查主键索引,返回记录;

执行流程:

需要通过如下语句开启mysql的MRR优化:

set optimizer_switch="mrr_cost_based=off"

explain结果:

由于对id做了排序,所以我们查询结果也是按照id顺序排列。

总结:MRR提升性能的核心:在索引上做范围查询,能够得到足够多的主键id,然后对id排序编程顺序读,提升性能。

Batched Key Access

理解了MMR,我们看一下mysql5.6引入的Batched Key Access(BKA)算法,其实就是对NLJ的优化。 先看下上一篇NLJ的流程：

我们从驱动表t1中一行行的拿出a到t2中比较，对于t2来说，每次匹配的都是一个值，这样就用不到MRR优化了。
我们可以每次从t1中拿出一部分数据放到临时内存中，这个临时内存正式join_buffer,join_buffer在BNL中暂存驱动表的数据,但NLJ并没有用到,我们刚好可以服用join_buffer到BKA算法中。 流程如下：

在join_buffer中只放入需要查询的字段，批量与t2匹配，得到一批id，然后t2表就可以用到MRR
启用BKA:

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

explain结果:

BNL算法的优化

如果我们的被驱动表是一个大数据量的冷表,如果我们使用BNL,除了会对IO有影响,还会有什么问题?

Innodb对Buffer Pool的LRU算法优化:第一次从磁盘读入的内存页,先放在old区,如果1s后不再对这条数据访问,就不会被移到头部,对Buffer Pool命中率影响不大

如果多次扫描一个冷表,并且1s后没有返回结果,在此扫描冷表就会把数据移至LRU头部
大表join操作虽然对IO有影响,但是语句执行结束后对IO的影响就结束了,但是对缓存Buffer Pool的影响确实永久性的,需要依靠后续的查询请求慢慢恢复缓存命中率。 为了减少这种影响，可以考虑加大join_buffer_size,减少被驱动表的扫描次数

总结一下BNL的缺点:

  1. 多次扫描被驱动表,占用磁盘IO资源;
  2. 需要执行M*N次对比,占用CPU资源;
  3. 可能会导致Buffer Pool热数据被淘汰,影响内存命中率;

优化的常见做法是给被驱动表加索引,BNL转为BKA,但是有些情况下不适合建索引:

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

这个语句中t2需要join的只有2000行,同时如果这个语句低频的时候,建个索引就很浪费,使用BNL的流程:

  1. 取出t1的所有字段放到join buffer,共1000行;
  2. 扫描t2所有行
    • 不满足t1.b = t2.b ,跳过
    • 满足,再判断t2.b>=1 and t2.b<=2000 t2总共100万行,总的判断次数1000*100W

这个时候,我们可以考虑使用临时表:

  1. 创建临时表tmp_b,加上索引b,插入符合条件的2000条数据
  2. t1和tmp进行join 对应的sql:
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. 插入数据过程中做了全表扫描,100W
  2. t1和tmp进行join,1000*log100W

扩展 -hash join

如果join buffer中维护的不是一个无序数组,而是一个hash表,那么查找就可以降到O(1),之前的1000*100W就可以将到100W,但是mysql并不支持,我们可以自己通过代码在业务端实现:

  1. select * from t1;结果放到map
  2. select* from t2 where b>=1 and b<=2000;
  3. 那2查到的值去map里面查找

理论上这种方式的效果要好于临时表。

Mysql中的join语句篇完结!

avatar
文章
阅读
粉丝