Mysql6连接及其优化

文章目录

• 前言
• join语句的基本操作
• join语句的优化
• 参考

前言

在这之前，需要连接以下两个链接内容：

1. explain的解释
2. MySQL性能优化神器Explain使用分析

join语句的基本操作

交叉连接

mysql> SELECT * FROM player CROSS JOIN team;

explain解析：

mysql> explain SELECT * FROM player CROSS JOIN team\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: team
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: player
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37
     filtered: 100.00
        Extra: Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

从explain的显示可知：

1. 将表team的所有字段取出来，存入join_buffer中。
2. 扫描表player，取出每一行跟join_buffer中的数据拼接，作为结果集返回。

自然连接

自然连接：它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

SQL92标准——等值连接

mysql> SELECT player_id, a.team_id, player_name, height, team_name FROM player as a, team as b WHERE a.team_id = b.team_id;

SQL99标准

mysql> SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team;

explain解析:

mysql> explain SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: team
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: player
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

从explain的显示可知：

1. 将表team的所有字段取出来，存入join_buffer中。
2. 扫描表player，取出每一行跟join_buffer中的数据对比
   1. a.team_id = b.team_id 就加入结果集；
   2. a.team_id = b.team_id 不满足，则跳过。

ON连接

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id

exlpain解析：

mysql> explain SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: team
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: player
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

这条语句的执行流程与上一条类似；

外连接

左外连接

mysql> SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id;

explain解析

mysql> explain SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: player
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: team
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

从上可知执行流程如下：

1. 将player表记录，读入join_buffer中。
2. 扫描team表，取出每一行跟join_buffer中的数据对比
   1. a.team_id = b.team_id 就加入结果集；
   2. a.team_id = b.team_id 不满足，则跳过。

右外连接

mysql> SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id;

explain解析：

mysql> explain SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: team
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: player
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

从上可知：

1. 将表team的所有字段取出来，存入join_buffer中。
2. 扫描表player，取出每一行跟join_buffer中的数据对比
   1. a.team_id = b.team_id 就加入结果集；
   2. a.team_id = b.team_id 不满足，则跳过。

sql之left join、 right join、 inner join区别

全外连接

全外连接在mysql不支持。但： 全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

自连接

mysql> SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height;
+---------------------------+--------+
| player_name               | height |
+---------------------------+--------+
| 安德烈-德拉蒙德           |   2.11 |
| 索恩-马克                 |   2.16 |
| 扎扎-帕楚里亚             |   2.11 |
| 亨利-埃伦森               |   2.11 |
| 多曼塔斯-萨博尼斯         |   2.11 |
| 迈尔斯-特纳               |   2.11 |
+---------------------------+--------+
6 rows in set (0.00 sec)

explain解析

mysql> explain SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: const
possible_keys: player_name
          key: player_name
      key_len: 767
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37
     filtered: 33.33
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

解析：

1. 从表a中通过索引player_name读入 player_name='布雷克-格里芬'这一行数据，记为R；
2. 从数据行R中，取出字段height到表b里查找；
3. 取出表b中满足条件的行，和R组成一行，作为结果集的一部分；
4. 重复2， 3直到表b的末尾结束。

join语句的优化

Index Nested-Loop Join(NLJ)

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

以上语句中t1是驱动表，t2是被驱动表。假设被驱动表t2的字段a上有索引，join过程中用到该索引，此语句的执行流程如下：

result;
for r1 in t1：
	c := extract(r.a)
	for r2 in t2:
		if (extract(r2.a) == extract(r1.a)):
			result.add(concat(r1, r2))
return result;

以上过程，首先遍历表t1，然后根据从表t1中取出每行数据中的a值，再去表t2找满足条件的记录。以上过程是遍历的镶嵌，并且用到索引，因此称为NLJ；

复杂度：

假设t1有M条记录，t2有N条记录，使用t2上的索引，因此复杂度为：

• 遍历t1的复杂度为 O(M)
• 根据索引在t2上找记录复杂度为 O(logN)
• 找到索引后，需要回表，复杂度为O(logn)

故复杂度为 O(NLJ) = O(M) + O(M) * 2O(logN)，从这个式子可以看出，复杂度主要在O(M)这一项，因此， NLJ这种情况下，我们应该用小表作为驱动 表，大表作为被驱动表，这样可以使用大表的索引。

Simple Nested-Loop Join

NLJ中，join的时候利用了被驱动表的索引，如果 表均没有索引，流程是怎样的呢？

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

还是这条语句，SNLJ的算法和NLJ类似，只是在匹配t2图的时候，没有使用索引，因此复杂度为：

O（SNLJ） = O（M） + O(M*N)

假设N =100, M = 1000， 那这里需要扫面 100 + 100 * 1000行。因此效率太低，BNL就是针对这种情况进行优化的。

Block Nested-Loop Join(BNL)

算法流程是这样的：

1. 将驱动表t1读入内存中，也就是join_buffer中
2. 扫描t2，把表t2的每一行取出来，和join_buffer中的数据做对比，满足条件，就作为结果集的一部分返回。

以上，t1，t2均全表扫描，因此总共100 + 1000行，当时同样在内存做判断的次数是：100*1000次。

将以上步骤抽象化，假设小表t1= N, 大表t2 = M， 如果join_buffer足够大，那么算法：

1. 两表都做一次全表扫苗，总的扫描函数是M + N
2. 内存中判断的次数为：N * M

假设join_buffer不足，驱动表需要分K段才能完成算法流程，（其实这里K表示join_buffer能容纳记录的条数更为合适），那么 λ = K / N ​ \lambda = K/N​ λ=K/N​ ，其取值范围为(0, 1);

所以，算法执行过程为：

1. 扫描行数为 N + λ ∗ N ∗ M N + \lambda * N*M N+λ∗N∗M 或者 N + k ∗ M N + k*M N+k∗M
2. 内存判断为 N ∗ M N*M N∗M

有第一个公式不难看出，N占的比重较大，因为k的取值范围是[0, 1]呀。

所以综上可知，不管那种情况，都是将小表作为驱动表。

现在有个问题，究竟什么是小表？

小表的定义

首先给出定义啊。小表就是，两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是小表，应该作为驱动表。

**比如1，**表t1有100条记录，t2有1000条记录，以下两条语句，谁的效率更高？

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

按照小表的定义啊，只将t2表的前50行加入到join_buffer中，这个相对来说是一个小表。因此，t2做驱动表效果更好，也就是B方案更好。

Multi-Range Read 优化

MRR优化的目的是尽量使用顺序读盘。在谈论MRR优化之前，先看个例子：

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

常规的，这个语句的执行顺序是：

1. 通过索引找到主键id
2. 通过主键id获取数据行，加入到结果集中。

这里就有个问题，如果索引递增的情况下，主键id是随机的，那么读盘的时候，就是随机访问。那能不能将利用磁盘的顺序读呢？

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

通过以上特性，MRR优化的设计思路如下：

1. 根据索引，获取满足条件的主键id，并将主键id放入read_rnd_buffer中；
2. 然后对read_rnd_buffer中的id递增排序；
3. 最后依据主键id查记录，作为结果返回。

综上，MRR优化，就是对索引进行范围查询，获取多个主键id，再对主键id排序。这样再获取磁盘的记录时，利用顺序性。

Batched Key Access(BKA)

BKA就是对NLJ算法的优化。再介绍BKA之前，先说下NLJ算法。NLJ首先遍历驱动表，对于每个记录，再通过索引，访问被驱动表。结合前文的MRR优化，我们就会联想到，这里是否可以对 主键id进行排序，从而利用顺序读的特性呢？

BKA算法对NLJ优化如下：

1. 在NLJ算法中，每次在驱动表中获取一条记录，而在这里，我们获取多条记录，并将起暂存在临时内存join_buffer中。
2. 这样，多条记录，就可以在t2表中匹配多个t2表的索引了呀。
3. 到这一步，就和上文MRR优化一致了。将t2表的多个主键id保存在read_rnd_buffer中，递增排序；
4. 最后依据主键id查记录，作为结果返回。

我们在来看看BNL如何利用BKA进行优化。首先给定一个例子，t1 = 1000， t2 = 100w如下：

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

这条语句的执行流程如下：

1. 把表 t1 的所有字段取出来，存入 join_buffer 中。这个表只有 1000 行，join_buffer_size 默认值是 256k，可以完全存入。
2. 扫描表 t2，取出每一行数据跟 join_buffer 中的数据进行对比，
   1. 如果不满足 t1.b=t2.b，则跳过；
   2. 如果满足 t1.b=t2.b, 再判断其他条件，也就是是否满足 t2.b 处于[1,2000]的条件，如果是，就作为结果集的一部分返回，否则跳过。

可见上述例子中，扫描行数为：1000 + 100 w，比较次数为：1000*100w；

如果我们将t2符合条件的记录提取出来，作一个临时表，然后再这个临时上对b作索引，如下：

A：create temporary table temp_t(id int primary key, a int, b int, index(b)) engine=innodb;
B:insert into temp_t select * from t2 where b>=1 and b<=2000;
C:select * from t1 join temp_t on (t1.b=temp_t.b);

上述代码，扫描行数：

• B行代码，扫描行数为100w
• 那么接下来，就是BKA对NLJ的优化了。

hash join

再次回到，t1=1000, t2=100w 利用BNL实现连接，需要比较10亿次。我们可以将t1表的记录读入，然后存在一个hash结构中，那么，这个时候，只需要100w次hash查询辣。hash join已经在Mysql8中实现辣。我们来看看业务端的实现流程：

1. select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。
2. select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。
3. 把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行

参考

1. 极客时间Mysql45讲
2. 极客时间Mysql必知必会
3. epexplain的解释
4. MySQL性能优化神器Explain使用分析
5. sql之left join、 right join、 inner join区别