MySQL 连接

这篇文章还是学习极客时间专栏《MySQL实战 45 讲》的笔记，我们都听说过 JOIN 可能是个十分消耗资源的操作，《阿里巴巴 JAVA 开发手册》中也禁止 JOIN 三张以上的表，那究竟为什么 JOIN 操作让人如此「恐惧」呢？下面结合专栏中关于 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 if exists 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);

上面的 SQL 语句是直接从专栏中复制的：首先创建了表 t2，其中 a 字段上面有索引，然后使用存储过程插入 1000 行数据；创建结构相同的表 t1，将 t2 的前 100 行数据插入 t1。

Index Nested-Loop Join（NLJ）

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

id            | 1           | 1
select_type   | SIMPLE      | SIMPLE
table         | t1          | t2
partitions    | <null>      | <null>
type          | ALL         | ref
possible_keys | a           | a
key           | <null>      | a
key_len       | <null>      | 5
ref           | <null>      | test.t1.a
rows          | 100         | 1
filtered      | 100.0       | 100.0
Extra         | Using where | <null>

上面的 JOIN 语句使用 straight_join 强制把 t1 作为驱动表，从执行计划可以看出 JOIN 的过程使用了 t2 表 a 字段上的索引，执行流程大概是：遍历表 t1 的每一行，根据 a 字段的值到表 t2 中查询满足条件的行，这个过程类似嵌套循环，而且能用到被驱动表上的索引，所以称为 Index Nested-Loop Join。

在能够使用被驱动表索引的情况下，假设驱动表的行数是 N，被驱动表的行数是 M，由于有回表操作，所以从被驱动表查询一行的时间复杂度是 2logM；由于需要对驱动表做全表扫描，且每行都要去被驱动表查一次；所以总的时间复杂度是：N + 2NlogM。显然，N 对整体的时间复杂度影响较大，所以应该将小表（行数较少的表）作为驱动表。

Batched Key Access（BKA）

MySQL 有一种 Multi-Range Read（MRR）优化，思路是尽量顺序读取磁盘，以提升读性能。但是 MRR 并不一定会被优化器采用，如果想要强制使用 MRR 的话，需要将 mrr_cost_based 配置关掉：

set optimizer_switch='mrr_cost_based=off';

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

id            | 1
select_type   | SIMPLE
table         | t2
partitions    | <null>
type          | range
possible_keys | a
key           | a
key_len       | 5
ref           | <null>
rows          | 100
filtered      | 100.0
Extra         | Using index condition; Using MRR

从执行计划的 Extra 中可以看出使用了 MRR，这时的查询流程大概是：根据索引 a 定位大满足条件的行，将对应的 id 值放入 read_cnd_buffer 中，并按照 id 递增的顺序排序，然后按照这个顺序依次到主键索引树中查询结果。因为大多数数据都是按照主键递增的顺序插入的，所以我们可以认为：如果按照主键递增的顺序查询，对磁盘的读比较接近顺序读，能够提升读性能。

基于 MRR，MySQL 引入了 BKA 对 NLJ 进行优化，我们知道 NLJ 的执行逻辑是：从驱动表 t1 中逐行取出 a 值，再到被驱动表 t2 中搜索，即对于表 t2 每次都只匹配一个值，MRR 的优势就用不上了。而 BKA 是一次从驱动表 t1 中取出多行，放到 join_buffer 中，然后根据这些 a 值到表 t2 中搜索，这时就能利用 MRR 加速回表的过程了。使用 MRR 需要先执行下面的设置，然后再执行之前的 JOIN 语句：

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

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

id            | 1           | 1
select_type   | SIMPLE      | SIMPLE
table         | t1          | t2
partitions    | <null>      | <null>
type          | ALL         | ref
possible_keys | a           | a
key           | <null>      | a
key_len       | <null>      | 5
ref           | <null>      | test.t1.a
rows          | 100         | 1
filtered      | 100.0       | 100.0
Extra         | Using where | Using join buffer (Batched Key Access)

Block Nested-Loop Join（BNL）

进行 JOIN 操作时如果能用上被驱动表的索引，性能其实还不错，那如果用不上索引，上面的 JOIN 语句是不是要对被驱动表 t2 做 100 次全表扫描呢？这种算法被称为 Simple Nested-Loop Join，MySQL 其实没有用这种算法，还是针对这种场景做了一些优化，使用的是 Block Nested-Loop Join。

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

id            | 1           | 1
select_type   | SIMPLE      | SIMPLE
table         | t1          | t2
partitions    | <null>      | <null>
type          | ALL         | ALL
possible_keys | a           | <null>
key           | <null>      | <null>
key_len       | <null>      | <null>
ref           | <null>      | <null>
rows          | 100         | 1000
filtered      | 100.0       | 10.0
Extra         | <null>      | Using where; Using join buffer (Block Nested Loop)

我们稍微修改一下 JOIN 的条件，让查询不能使用被驱动表 t2 的索引，可以看到这个查询对 t1 和 t2 都做了一次全表扫描，整个执行流程大概是：把表 t1 的数据读入内存 join_buffer 中，扫描表 t2，将表 t2 中的每一行取出来，与内存 join_buffer 中的数据逐行对比，返回满足条件的行。

这里 join_buffer 足够大，能够放得下表 t1，要是放不下就只能分块存放了，假设 join_buffer 只能放 L 条记录，驱动表的行数是 N（N > L），被驱动表的行数是 M，则需要扫描的总行数是 N + MN/L，内存中比较次数是 MN。在 L、M 和 N 确定的情况下，内存中的比较次数是确定的，N 如果小一点，扫描的总行数会少一些，所以需要把小表作为驱动表。

关于小表的定义，这里需要再进一步说明一下：

select * from t1 straight_join t2 on (t1.b = t2.b) where t2.id <= 50;
select * from t2 straight_join t1 on (t1.b = t2.b) where t2.id <= 50;

这两个查询最终返回的结果是相同的，但是把 t2 作为驱动表的第二条语句相对更合理一写，虽然 t2 的总行数要比 t1 多，但是通过 where 过滤的只有 50 行，小于 t1 的总函数，在这个场景中 t2 是小表。

select t1.b, t2.* from t1 straight_join t2 on (t1.b = t2.b) where t2.id <= 100;
select t1.b, t2.* from t2 straight_join t1 on (t1.b = t2.b) where t2.id <= 100;

这次通过 where 过滤后，t1 和 t2 参与 JOIN 的行数都是 100，但是针对 t1 只需要返回一个字段 b，而需要返回 t2 的所有字段，在这个场景下 t1 是所谓的小表。

所以，更准确的说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤之后，再计算参与 JOIN 的各字段的总数据量，数据量小的那个表才是小表，应该作为驱动表。