mysql的join分析与优化

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

 

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 DEFAULT CHARSET=latin1;

 

一、当驱动表和被驱动表的join键均为索引键时，（NLJ）

select * from t1 straight_join t2 on (t1.a=t2.a);强制指定驱动表为t1，方便后续叙述

执行过程为，索引树全表扫描t1，一行一行取出数据，拿字段a到t2中查询，因为t2.a是索引字段，所以走t2

的索引，快速命中a索引树的数据行，取出来id，然后回表从id索引树取数据，合并放到结果集。

 

二、驱动表非索引字段，被驱动表为索引字段，(NLJ)，。

 

三、当驱动表为索引键，但是被驱动表为普通字段时(BNL)

select * from t1 straight_join t2 on (t1.a=t2.b);

如果没有joinbuffer

那么就会全表扫描t1,循环取出t1每一行数据,和t2的每一行进行匹配。

有joinbuffer

如果joinbuffer足够大

驱动表的数据会被全部load到joinbuffer中，然后被驱动表的每一条数据取出来和驱动表的每一行数据匹配，只不过这个匹配过程发生在joinbuffer，所以相对来说，节省了另一个表循环取数据的过程。

如果joinbuffer不够大，那么驱动表就会分段放入joinbuffer，这样相对于足够大的情况，就会多几次取数据放入join buffer的过程

四、驱动表和被驱动表都没有索引的情况，同三。

 

结论一，如果能用上索引，那么最好让被驱动表用上索引，驱动表有索引。

结论二，驱动表一定是小表。

小表和大表

如果没有用上索引，那么请对表加索引，或者换一个join条件。

如果两表都用上了索引，那么就选择数据量小的作为驱动表

小表，数据量小的表，有where条件的话是join前后的表在各自where条件过滤后数据量小的表，

此外还需要考虑放入的整体大小，比如

explain SELECT t1.*,t2.b FROM `t1` STRAIGHT_JOIN t2 on t1.b = t2.b where t2.id<100;

explain SELECT t1.*,t2.b FROM `t2` STRAIGHT_JOIN t1 on t1.b = t2.b  where t2.id<100;

这两条语句的执行计划看起来差别不大，但是第二句sql放入join buffer的只有t2的b字段（返回结果和join条件），所以相对来说，单次可以放入的数据量能代表更多的数据行，就能减少分段的次数。

 

 

 

优化

Multi-Range Read 优化----MRR

回表-非主键索引命中后，会一条条的进行主键索引的搜索，然后在主键索引取出字段，放到结果集中。

MRR主要是对要回表的操作，在read_rnd_buffer中，将非主键索引命中的主键id有序排列，然后再一条条回表，

虽然还是一条条的，但是磁盘io就从随机的变成了顺序的，毕竟通常数据是按照id自增插入的。

这，就是 MRR 优化的设计思路。

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

1-根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;

2-将 read_rnd_buffer 中的 id 进行递增排序；

3-排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。

如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。

之后继续找索引 a 的下个记录，并继续循环。

另外需要说明的是，如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。

（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

 

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），

可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

 

Batched Key Access 优化----BAK

在NLJ方式中，是驱动表数据一条一条的拿出来对被驱动表进行遍历匹配，那其实可以优化一下，将驱动表数据放到join-buffer中，然后整批的与被驱动表进行join

如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

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

 

BNL算法是有问题的，有可能会对冷表扫描，导致正常业务的数据还没有在old区呆到1s就被大量的新插入的冷表数据替换调，虽然不会对young区域已有的热点数据造成影响，

但是old区域中本来可以进入young区的数据却会在一段时间内无法缓存到young区域。

 

BNL转BAK，其实就是对被驱动表的join键加索引就好，例如这个语句

但是有时候，可能加索引并不划算，可能这个索引就只有这个sql用到，加索引的化或许就很浪费

例如 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 操作。此时，对应的 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);