十三.join语句的使用，join语句的优化_sql join之后要写临时的表名吗-优快云博客

Join语句的使用

在分析之间，我们先来建两个表


CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

Index Nested-Loop Join

我们来看下这个语句：

select * from t1 straight_join t2 on t1.a=t2.a;

我们可以看到，在这条语句里，被驱动表t2的字段a上有索引，join过程用上里这个索引，因此这个语句的执行流程是这样的：

从表t1中读出一行数据R
从数据行R中，取出a字段到表t2里去查找
取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分
重复执行步骤1到3，直到表t1的末尾循环结束。

在这个流程中，对驱动表t1做了全表扫描，这个过程需要扫描100行。而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。（驱动表走全表扫描，被驱动表走树搜索）。

使用join语句，在可以使用被驱动表索引的前提下，需要让小表做驱动表。

假设被驱动表有M行，驱动表有N行，这个查询的复杂度是N + N*2*log2M。显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。

Block Nested-Loop Join

如果被驱动表上没有可用的索引，算法的流程是这样的：

把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select * ，因此是把整个表t1放入了内存
扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

在这里插入图片描述

这条SQL的explain结果如下所示：
在这里插入图片描述

在这个过程中，对表t1和t2都做里一次全表扫描，因此总的扫描行数是1100；对于表t2中的每一行，都要做100次判断，因此在内存中做的判断次数是100*1000=10w次。由于是10w次内存操作，速度会快很多。

如果驱动表比较大，join_buffer一次放不下，会分成多个块，然后对每一个块进行匹配，然后清空join_buffer进行下一次操作，这也是BNL算法名字中Block的来源。

对于BNL算法，也应该使用小表做驱动表。而且，分成的断数越少，对被驱动表的全表扫描次数就越少。因此，join语句速度比较慢时，可以把join_buffer_size改大。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表

如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

Join语句优化

Multi-Range Read优化

Multi-Range Read(MRR)优化的主要目的是尽量使用顺序读盘。

对于下面这条语句：

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

在这里插入图片描述

上面这条语句，如果随着a的值递增顺序查询的话，id的值就变成随机的，回表的时候就会出现随机访问，性能比较差。因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照逐渐的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。这就是MRR优化的设计思路。

此时，语句的执行流程变成了这样：

根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中。
将read_rnd_buffer中的id进行递增排序
排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

如果read_rnd_buffer放慢里，就会先执行2，3，然后清空buffer，继续上面的操作。

需要说明的是，如果想要稳定地使用MRR优化的话，需要设置set optimizer_switch="mrr_cost_based=ofof"。（官方文档中提到，现在的优化器判断消耗的时候，会更倾向于不使用MRR）
在这里插入图片描述

MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引差数据，才能体现出“顺序读”的优势。

Batched Key Access

BKA算法，其实就是对NLJ算法的优化。NLJ算法执行的逻辑是，从驱动表t1，一行行地取取出a的值，再到被驱动表t2去做join。也就是说，对于表t2来说，每次都是匹配一个值。这时，MRR的优势就用不上了。

为了发挥MRR的优势，从表t1一次性多拿一些行来，放在join_buffer中，这时，BKA的执行流程就变成了下面这样：
在这里插入图片描述

如果要使用BKA算法，你需要在执行SQL语句之前，先设置：

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

前两个参数的作用是要启用MRR。这么做的原因是，BKA算法的优化要依赖于MRR。

BNL算法的性能问题

如果被驱动表是一个大的冷数据表，使用BNL算法会影响到Buffer Pool中的缓存，降低内存命中率，造成性能问题。

如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句的执行时间超过1秒，会造成两个问题：

一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页可能在1秒内就被淘汰了，这会导致缓存数据进入不了young区域。
如果join执行的时间超过了1s，old区域的数据就会被移动的young区域，淘汰掉原先的缓存，造成缓存命中率下降。

BNL转BKA

一些情况下，我们可以直接在驱动表啥那个建索引，这时就可以直接转成BKA算法了。

但有时候，存在一些不适合在被驱动表上建索引的情况：

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

这时，我们可以考虑使用临时表。使用临时表的大致思路是：

把表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算法，提升查询性能。