Smarticen Notes

join如何使用

8 min read

有关join的两个问题

关于join的使用，一般会集中在一下两类：

  1. DBA不让使用join，使用join有什么问题
  2. 两个大小不同的表做join，应该用哪个表做驱动表。

创建两个一样的表t1，t2，其中t2插入1000行记录，t1插入100行记录

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)

为了让join的按照指定的语句执行，而不是让优化器优化，使用下面的语句

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

使用explain分析此语句，可以看到，被驱动表使用了索引a。整个语句的执行流程是这样的：

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

驱动表t1做了全表扫描，被驱动表t2使用索引a走树搜索过程。

现在先看看第一个问题：能不能使用join？ 那么不使用join，上面的逻辑用单表实现如下：

  1. 执行select * from t1，查出t1的所有数据，这里有100行。
  2. 循环遍历这100行数据，从每一行取出字段a的值$R，执行select * from t2 where a=$R.a，把返回的结果和R构成结果集的一行。 虽然也是扫描了200行，但是执行了101条语句，比直接join多了100此交互，显然还不如执行join好。所以join合适的时候就可以使用。

第二个问题：怎么选择驱动表？ 使用小表驱动大表。

上面的结论都是针对可以使用被驱动表的索引。

如果不能够使用被驱动表上的索引，那么被驱动表每次都要进行一次全表扫描，对于这两个100和1000的小表，总共就要扫描10万行，实在不可取。所以被驱动表上没有可用的索引，算法的流程是这样的：

  1. 把表t1的数据读入线程内存join_buffer中，这里语句中是select *，所以把整个t1都放入了内存。
  2. 扫描t2，把表t2中的每一行取出来，跟join_bufer中的数据做对比，满足条件的作为结果集的一部分返回。

首先t1和t2都要进行一次全表扫描，所以是1100扫描行，并且由于join_buffer是无需数组的方式组织的，对于t2中的每一行都要做100次判断，在内存中做判断的次数是：100 * 1000 = 10万次。与前面似乎相同，但是这10万次判断都是在内存中进行的，性能要好很多。

对于这种方式，小表驱动还是大表驱动都没有什么区别。

如果表的数据很大，join_buffer放不下的话，就会进行分段放，先放一部分，判断处理完成之后，再读取另一端继续判断。如果驱动表大的话这个分段数就多，所以综合考虑，就应该==让小表当驱动表==。

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

join语句如何优化

创建两个表t1，t2，t1是1000倒序的，t2中插入100万行数据

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

Multi-Range Read优化

MRR优化的主要目的是尽量使用顺序读盘。

因为大多数的数据都是按照主键递增顺序插入得到的，所以可以认为，按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

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

上面的语句的执行流程就会变为：

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

read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果满了就会先执行2，3，清空read_rnd_buffer之后继续循环。

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

查询语句在索引a上做范围查询并且得到足够多的主键id，这样排序之后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access

前面讲过走被驱动表的索引字段的join，每次从驱动表t1中取出一行，再到驱动表t2上去做join，也就是对于表t2来说，每次都是匹配一个值，这样就无法利用MRR的优势了。

所以可以先将表t1的数据取出来一部分，先放到临时内存join_buffer中，然后一起传递给表t2. 优化后的算法BKA的流程图示如下：

要使用BKA优化算法，就需要在执行SQL语句之前，设置：

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

其中前两个参数的作用是要启用MRR，因为BKA算法依赖于MRR。

Graph View

Backlinks