一文详解MySQL—Join的使用优化（mysqljoin）速看

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

目录MySQL JOIN类型MySQL JOIN 算法Nested-Loop Join 算法执行流程工作原理时间复杂度分析Block Nested-Loop Join 算法执行流程工作原理时间复杂度分析Hash Join 算法执行流程build 构建probe 探测阶段如何使用时间复杂度分析NLJ算法优化BNL算法优化Hash Join算法优化

支持多种类型，下面是每种类型的简要概述:

：将两个表中符合条件的行组合在一起。返回的结果集只包含满足连接条件的行，即两个表中都存在的行。一般简写成

：返回左表中的所有行以及符合条件的右表中的行。如果右表中没有匹配的行，则用填充。

：返回右表中的所有行以及符合条件的左表中的行。如果左表中没有匹配的行，则用填充

：返回左表和右表中的所有行，如果一个表中没有匹配的行，则用填充。

：返回两个表中的所有可能的组合，也称为笛卡尔积。

在了解完类型概念之后，我们来了解算法，在之前的版本只支持这一种算法，在之后支持算法。

执行流程

假设两个表一个用户表，一个订单表，需要查询用户的所有订单信息，表结构如下:

CREATE TABLE `user` (
`id` int(11) NOT NULL COMMENT ‘主键id’,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘用户名称’,
`age` int(255) DEFAULT NULL COMMENT ‘年龄’,
`user_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘用户编号’,
PRIMARY KEY (`id`),
KEY `idx_user_code` (`user_code`) USING BTREE COMMENT ‘用户编号索引’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’用户表’;

CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键id’,
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘订单名称’,
`user_code` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘用户编号’,
PRIMARY KEY (`id`),
KEY `idx_user_code` (`user_code`) USING BTREE COMMENT ‘用户编号索引’
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT=’订单表’;

其中是连接字段，也是索引。如下：

mysql> SELECT
u.`name`,
u.user_code,
o.order_name
FROM
`user` u
JOIN `order` o ON u.user_code=o.user_code;
+——+———–+————+
| name | user_code | order_name |
+——+———–+————+
| 李 | 002 | 订单1 |
| 李 | 002 | 订单2 |
| 李 | 002 | 订单3 |
| 李 | 002 | 订单4 |
| 李 | 002 | 订单5 |
| 李 | 002 | 订单6 |
| 李 | 002 | 订单7 |
| 李 | 002 | 订单8 |
| 李 | 002 | 订单9 |
+——+———–+————+
9 rows in set (0.08 sec)

看一下这条语句的结果

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

从表中读入一行数据；从数据行中，取出字段到表里去查找；表根据索引找到满足条件的行字段，跟上面的数据行组成一行；重复执行步骤1到3，直到表的末尾循环结束。
工作原理

这个过程就跟我们写程序时的嵌套查询，一般称为，是一种最基本的算法，它通过对两个表进行嵌套循环来查找匹配的行。具体来说，它从一个表中取出一行，然后在另一个表中扫描所有行，查找与之匹配的行。类似于这样:

for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
}

时间复杂度分析

这个过程将会对每一行进行一次比较，因此它的时间复杂度为：O(m∗n)O(m*n)O(m∗n)，其中和分别是两个表的行数。

假设被驱动表的行数是。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是logMlog MlogM，所以在被驱动表上查一行的时间复杂度是 2∗logM2*log M2∗logM。

假设驱动表的行数是，执行过程就要扫描驱动表行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是 N+N∗2∗logMN + N*2*log MN+N∗2∗logM。对扫描行数的影响更大，因此应该让小表来做驱动表。对于大型数据集来说，它的性能会变得非常慢，因为它需要对一个表的每一行都扫描另一个表。

执行流程

接下来，我们删除掉索引，再看看被驱动表用不上索引的情况

ALTER TABLE `user` DROP INDEX `idx_user_code`;
EXPLAIN SELECT
u.`name`,
u.user_code,
o.order_name
FROM
`user` u
JOIN `order` o ON u.user_code=o.user_code

再看一下这条语句的结果,多了一个

这个时候语句的执行流程如下：

从表中读入字段数据放到线程内存中

扫描表表，把表中的每一行取出来，跟中的数据做对比，满足条件的，作为结果集的一部分返回。

工作原理

是一种内存缓存，并在查询完成后释放,它可以在执行时缓存的中间结果。的大小是由参数设定的，默认值是256k。

那如果中放不下表的所有数据话会怎么处理呢？策略很简单，就是分段，每次清空；

for each row in t1 matching range {
store used columns from t1 in join buffer
## 如果buffer满了
if buffer is full {
for each row in t2 {
for each t1 combination in join buffer {
## 如果行满足连接条件，则发送到客户端
if row satisfies join conditions, send to client
}
}
## 清空buffer
empty join buffer
}

}

if buffer is not empty {
for each row in t2 {
for each t1 combination in join buffer {
if row satisfies join conditions, send to client
}
}
}

时间复杂度分析

可以看到，在这个过程中，对表和都做了一次全表扫描。因此它的时间复杂度为：O(M+N)O(M+N)O(M+N)。由于是以无序数组的方式组织的，因此对表中的每一行，都要做判断。假设小表的行数是，大表的行数是，那么在这个算法里：

两个表都做一次全表扫描，所以总的扫描行数是M+NM+NM+N；

内存中的判断次数是M∗NM*NM∗N

是一种优化的算法，通过将一个表分成多个块（），然后逐个块与另一个表进行操作，从而减少了不必要的重复扫描和比较。它可以提高在处理大数据集时的性能,但是会占用过多的资源。会多次扫描被驱动表，占用磁盘资源。

Beginning with MySQL 8.0.20, support for block nested loop is removed, and the server employs a hash join wherever a block nested loop would have been used previously.(dev.mysql.com/doc/refman/…)

从开始，删除了算法，使用了算法替代。

执行流程

我们以下面官方例子为准：

SELECT
given_name,country_name
FROM?persons
JOIN countries
ON persons.country_id=countries.country_id;

分为两个阶段; 构建阶段和探测阶段。

build 构建

在构建阶段，使用字段作为哈希表，在内存中构建表。

正常情况会选择结果集较小（以字节为单位，而不是行数）的去构建。比如上面会对进行计算：然后将值放入内存中的相应位置。将所有行存储在哈希表中后，构建阶段就完成了。

probe 探测阶段

在探测阶段，逐行遍历被驱动表，然后计算条件的值，并在表中查找，如果匹配，则输出给客户端，否则跳过。所有内表记录遍历完，则整个过程就结束了。

比如上面遍历表中每行数据，然后取出字段的值进行计算；，然后去内存中中进行查找，匹配成功会发送给客户端。

内存中hash表的大小是由参数控制的，但是，如果构建内存大于可用内存，会发生什么情况？

当内存在构建阶段变满时，服务器会将其余的构建写入磁盘上的多个文件中。同时会设置文件的数量，确保最大的文件的大小与一致。

每行数据写入哪个块文件是通过计算属性的哈希来确定的。

在探测阶段，服务器对于表每一行数据使用同样的算法进行分区。这样，我们就可以确定两个输入之间的匹配行将位于同一对文件中。

探测阶段完成后，开始从磁盘读取文件。首先会将构建阶段的第一个文件，也就是下图中的左边的文件，加载到内存中哈希表中。这就解释了为什么希望最大的文件大小与内存一致，接着读取在探测阶段生成的文件，下图中右边的文件，在内存哈希表中进行匹配，就像之前内存操作一样。处理第一个文件后，移动到下一块文件，继续，直到处理完所有文件。

到这里也明白了，如果我们对两个操作使用相同的哈希函数，那么在将构建文件加载到哈希表中时，我们会得到一个非常糟糕的哈希表，因为同一个文件中的所有行都将哈希为相同的值。

如何使用

在之前，使用来查看是否将使用算法。

mysql> EXPLAIN FORMAT=tree SELECT
u.`name`,
u.user_code,
o.order_name
FROM
`user` u
JOIN `order` o ON u.user_code=o.user_code;
+————————————————————-+
| EXPLAIN
+————————————————-+
| -> Inner hash join (u.user_code=o.user_code) (cost=7.50 rows=7)
-> Table scan on o (cost=0.05 rows=9)
-> Hash
-> Table scan on u (cost=0.95 rows=7)
+———————————————————–+
1 row in set (0.04 sec)

整体上对驱动表遍历1次(驱动表有行记录)，被驱动表遍历1次(被驱动表有行记录)。因此它的时间复杂度为：O(M+N)O(M+N)O(M+N)。它通常比嵌套循环算法更有效，尤其是在其中一个结果集可以完全放入内存的情况下。

为了优化的性能，尽可能减少语句中的的循环总次数，就是让驱动表的结果集尽可能的小。对于很多表的关联通过应用层拆分成多个语句然后再拼接查询结果更方便，而且性能也不会差。

在的时候明确知道哪张表是小表的时候,可以用写法固定连接驱动方式

使用算法时，可能会对被驱动表做多次扫描,占用磁盘资源，并且需要执行M∗NM*NM∗N次对比但是会占用过多的资源。

优化的常见做法是，给被驱动表的字段加上索引，把算法转成算法。

无法设置索引的情况可以通过设置参数来控制的大小，以减少分段查询次数

算法在从以后才会用到，也是为了替代上面的算法。

当哈希表所需的内存量超过大小，会使用磁盘的文件进行处理，所以增加值避免生成文件可以极大提升查询速度。

以上就是一文详解MySQL—Join的使用优化的详细内容，更多关于MySQL Join使用优化的资料请关注脚本之家其它相关文章！

您可能感兴趣的文章:MYSQL Left Join优化(10秒优化到20毫秒内)MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法MySQL优化之使用连接(join)代替子查询探究MySQL优化器对索引和JOIN顺序的选择浅析Mysql Join语法以及性能优化MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍