MySQL实战教程之Join语句执行流程

目录
  • Join语句执行流程
  • 一、Index Nested-Loop Join
  • 二、Simple Nested-Loop Join
  • 三、Block Nested-Loop Join
  • 四、总结

Join语句执行流程

Hi,我是阿昌,今天学习记录的是关于Join语句执行流程的内容。

在实际生产中,关于 join 语句使用的问题,一般会集中在以下两类:

  • 不让使用 join,使用 join 有什么问题呢?
  • 如果有两个大小不同的表做 join,应该用哪个表做驱动表呢?

创建两个表 t1t2 来说明。

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)

可以看到,这两个表都有一个主键索引 id 和一个索引 a,字段 b 上无索引。

存储过程 idata() 往表 t2 里插入了 1000 行数据,在表 t1 里插入的是 100 行数据。

一、Index Nested-Loop Join

如果直接使用 join 语句,MySQL 优化器可能会选择表 t1 或 t2 作为驱动表,这样会影响分析 SQL 语句的执行过程。

所以,为了便于分析执行过程中的性能问题,改用 straight_join 让 MySQL 使用固定的连接方式执行查询,这样优化器只会按照指定的方式去 join。

来看一下这个语句:

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

在这个语句里,t1 是驱动表,t2 是被驱动表。

现在,来看一下这条语句的 explain 结果。

可以看到,在这条语句里,被驱动表 t2 的字段 a 上有索引,join 过程用上了这个索引,因此这个语句的执行流程是这样的:

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

这个过程是先遍历表 t1,然后根据从表 t1 中取出的每行数据中的 a 值,去表 t2 中查找满足条件的记录。

在形式上,这个过程就跟写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以称之为“Index Nested-Loop Join”,简称 NLJ。它对应的流程图如下所示:

在这个流程里:

对驱动表 t1 做了全表扫描,这个过程需要扫描 100 行;而对于每一行 R,根据 a 字段去表 t2 查找,走的是树搜索过程。由于构造的数据都是一一对应的,因此每次的搜索过程都只扫描一行,也是总共扫描 100 行;所以,整个执行流程,总扫描行数是 200。

能不能使用 join?

假设不使用 join,那就只能用单表查询。

看看上面这条语句的需求,用单表查询怎么实现。

  • 执行select * from t1,查出表 t1 的所有数据,这里有 100 行;
  • 循环遍历这 100 行数据:
    • 从每一行 R 取出字段 a 的值 $R.a;
    • 执行select * from t2 where a=$R.a;
    • 把返回的结果和 R 构成结果集的一行。

可以看到,在这个查询过程,也是扫描了 200 行,但是总共执行了 101 条语句,比直接 join 多了 100 次交互。

除此之外,客户端还要自己拼接 SQL 语句和结果。

显然,这么做还不如直接 join 好。

怎么选择驱动表?

在这个 join 语句执行过程中,==驱动表是走全表扫描,而被驱动表是走树搜索。==假设被驱动表的行数是 M。

每次在被驱动表查一行数据,要先搜索索引 a,再搜索主键索引。

每次搜索一棵树近似复杂度是以 2 为底的 M 的对数,记为 log2M,所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是 N,执行过程就要扫描驱动表 N 行,然后对于每一行,到被驱动表上匹配一次。

因此整个执行过程,近似复杂度是 N + N*2*log2M

显然,N 对扫描行数的影响更大,因此应该让小表来做驱动表

如果没觉得这个影响有那么“显然”, 可以这么理解:
N 扩大 1000 倍的话,扫描行数就会扩大 1000 倍;
而 M 扩大 1000 倍,扫描行数扩大不到 10 倍。

小结一下,通过上面的分析得到了两个结论:

  • 使用 join 语句,性能比强行拆成多个单表执行 SQL 语句的性能要好;
  • 如果使用 join 语句的话,需要让小表做驱动表。

但是,需要注意,这个结论的前提是“可以使用被驱动表的索引”。

二、Simple Nested-Loop Join

再看看被驱动表用不上索引的情况。

现在,把 SQL 语句改成这样:

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

由于表 t2 的字段 b 上没有索引,因此再用图 2 的执行流程时,每次到 t2 去匹配的时候,就要做一次全表扫描。

你可以先设想一下这个问题,继续使用图 2 的算法,是不是可以得到正确的结果呢?

如果只看结果的话,这个算法是正确的,而且这个算法也有一个名字,叫做“Simple Nested-Loop Join”。

但是,这样算来,这个 SQL 请求就要扫描表 t2 多达 100 次,总共扫描 100*1000=10 万行。

这还只是两个小表,如果 t1 和 t2 都是 10 万行的表(当然了,这也还是属于小表的范围),就要扫描 100 亿行,这个算法看上去太“笨重”了。

三、Block Nested-Loop Join

当然,MySQL 也没有使用这个 Simple Nested-Loop Join 算法,而是使用了另一个叫作“Block Nested-Loop Join”的算法,简称 BNL。

这时候,被驱动表上没有可用的索引,算法的流程是这样的:

  • 把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,因此是把整个表 t1 放入了内存;
  • 扫描表 t2,把表 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回。

这个过程的流程图如下:

对应地,这条 SQL 语句的 explain 结果如下所示:

可以看到,在这个过程中,对表 t1 和 t2 都做了一次全表扫描,因此总的扫描行数是 1100。

由于 join_buffer 是以无序数组的方式组织的,因此对表 t2 中的每一行,都要做 100 次判断,总共需要在内存中做的判断次数是:100*1000=10 万次。

前面我们说过,如果使用 Simple Nested-Loop Join 算法进行查询,扫描行数也是 10 万行。因此,从时间复杂度上来说,这两个算法是一样的。但是,Block Nested-Loop Join 算法的这 10 万次判断是内存操作,速度上会快很多,性能也更好。

在这种情况下,应该选择哪个表做驱动表。

假设小表的行数是 N,大表的行数是 M,那么在这个算法里:

  • 两个表都做一次全表扫描,所以总的扫描行数是 M+N;
  • 内存中的判断次数是 M*N。

可以看到,调换这两个算式中的 M 和 N 没差别,因此这时候选择大表还是小表做驱动表,执行耗时是一样的。

这个例子里表 t1 才 100 行,要是表 t1 是一个大表,join_buffer 放不下怎么办呢?

join_buffer 的大小是由参数 join_buffer_size 设定的,默认值是 256k。如果放不下表 t1 的所有数据话,策略很简单,就是分段放

join_buffer_size 改成 1200,再执行:

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

执行过程就变成了:

  • 扫描表 t1,顺序读取数据行放入 join_buffer 中,放完第 88 行 join_buffer 满了,继续第 2 步;
  • 扫描表 t2,把 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回;
  • 清空 join_buffer;
  • 继续扫描表 t1,顺序读取最后的 12 行数据放入 join_buffer 中,继续执行第 2 步。

执行流程图也就变成这样:

图中的步骤 4 和 5,表示清空 join_buffer 再复用。

这个流程才体现出了这个算法名字中“Block”的由来,表示“分块去 join”。

可以看到,这时候由于表 t1 被分成了两次放入 join_buffer 中,导致表 t2 会被扫描两次。

虽然分成两次放入 join_buffer,但是判断等值条件的次数还是不变的,依然是 (88+12)*1000=10 万次。

在这种情况下驱动表的选择问题。

假设,驱动表的数据行数是 N,需要分 K 段才能完成算法流程,被驱动表的数据行数是 M。

注意,这里的 K 不是常数,N 越大 K 就会越大,因此把 K 表示为λ*N,显然λ的取值范围是 (0,1)。

所以,在这个算法的执行过程中:

  • 扫描行数是 N+λNM;
  • 内存判断 N*M 次。

显然,内存判断次数是不受选择哪个表作为驱动表影响的。

而考虑到扫描行数,在 M 和 N 大小确定的情况下,N 小一些,整个算式的结果会更小。所以结论是,应该让小表当驱动表。

在 N+λNM 这个式子里,λ才是影响扫描行数的关键因素,这个值越小越好。

刚刚我们说了 N 越大,分段数 K 越大。那么,N 固定的时候,什么参数会影响 K 的大小呢?(也就是λ的大小)答案是 join_buffer_size

join_buffer_size 越大,一次可以放入的行越多,分成的段数也就越少,对被驱动表的全表扫描次数就越少。

如果你的 join 语句很慢,就把 join_buffer_size 改大。

第一个问题:能不能使用 join 语句?

  • 如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;
  • 如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。

所以在判断要不要使用 join 语句时,就是看 explain 结果里面,Extra 字段里面有没有出现“Block Nested Loop”字样。

第二个问题是:如果要使用 join,应该选择大表做驱动表还是选择小表做驱动表?

  • 如果是 Index Nested-Loop Join 算法,应该选择小表做驱动表;
  • 如果是 Block Nested-Loop Join 算法:
    • 在 join_buffer_size 足够大的时候,是一样的;
    • 在 join_buffer_size 不够大的时候(这种情况更常见),应该选择小表做驱动表。

所以,这个问题的结论就是,总是应该使用小表做驱动表

当然了,这里我需要说明下,什么叫作“小表”。

如果我在语句的 where 条件加上 t2.id<=50 这个限定条件,再来看下这两条语句:

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;

注意,为了让两条语句的被驱动表都用不上索引,所以 join 字段都使用了没有索引的字段 b。

但如果是用第二个语句的话,join_buffer 只需要放入 t2 的前 50 行,显然是更好的。

所以这里,“t2 的前 50 行”是那个相对小的表,也就是“小表”。

再来看另外一组例子:

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;

这个例子里,表 t1 和 t2 都是只有 100 行参加 join。

但是,这两条语句每次查询放入 join_buffer 中的数据是不一样的:

  • 表 t1 只查字段 b,因此如果把 t1 放到 join_buffer 中,则 join_buffer 中只需要放入 b 的值;
  • 表 t2 需要查所有的字段,因此如果把表 t2 放到 join_buffer 中的话,就需要放入三个字段 id、a 和 b。

应该选择表 t1 作为驱动表。也就是说在这个例子里,“只需要一列参与 join 的表 t1”是那个相对小的表。

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

四、总结

  • 如果可以使用被驱动表的索引,join 语句还是有其优势的;
  • 不能使用被驱动表的索引,只能使用 Block Nested-Loop Join 算法,这样的语句就尽量不要使用;
  • 在使用 join 的时候,应该让小表做驱动表。

使用 Block Nested-Loop Join 算法,可能会因为 join_buffer 不够大,需要对被驱动表做多次全表扫描。如果被驱动表是一个大表,并且是一个冷数据表,除了查询过程中可能会导致 IO 压力大以外,觉得对这个 MySQL 服务还有什么更严重的影响吗?

如果被驱动表是一个大表(因为不论用BNL还是ILJ算法) 都是优先让被参与join的总的字段量较大的一张表作为一个被驱动表。

但是由于关联的时候被驱动表的数据会频繁被走索引数, 所以根据MYSQL 的LRU算法 其实冷数据也会被提到链表的前部 ,造成冷数据的前移,其余业务数据被淘汰。 造成内存命中率降低。 请求响应变慢,业务可能造成阻塞。

到此这篇关于Join语句执行流程 -MySQL实战的文章就介绍到这了,更多相关mysql Join语句内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL中union和join语句使用区别的辨析教程

    union和join是需要联合多张表时常见的关联词,具体概念我就不说了,想知道上网查就行,因为我也记不准确. 先说差别:union对两张表的操作是合并数据条数,等于是纵向的,要求是两张表字段必须是相同的(Schema of both sides of union should match.).也就是说如果A表中有三条数据,B表中有两条数据,那么A union B就会有五条数据.说明一下union 和union all的差别,对于union如果存在相同的数据记录会被合并,而union all不会合

  • MySQL中join语句怎么优化

    目录 Simple Nested-Loop Join Block Nested-Loop Join Index Nested-Loop Join 如何选择驱动表? Simple Nested-Loop Join 我们来看一下当进行 join 操作时,mysql是如何工作的.常见的 join 方式有哪些? 如图,当我们进行连接操作时,左边的表是驱动表,右边的表是被驱动表 Simple Nested-Loop Join 这种连接操作是从驱动表中取出一条记录然后逐条匹配被驱动表的记录,如果条件匹配则将

  • MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

    在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦. 在优化这个语句之前,我们先了解下SQL查询的基本执行过程: 1.应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析 2.检查权限.MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(quer

  • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

  • mysql查询语句join、on、where的执行顺序

    目录 一.典型SELECT语句完整的执行顺序 二.from 三.on 四.on 条件与where 条件 1.使用位置 2.使用对象 3.选择与使用 五.join 流程 MySQL 的执行顺序 一.典型SELECT语句完整的执行顺序 1)from子句组装来自不同数据源的数据: 2)使用on进行join连接的数据筛选 3)where子句基于指定的条件对记录行进行筛选: 4)group by子句将数据划分为多个分组: 5)cube, rollup 6)使用聚集函数进行计算: 7)使用having子句筛

  • MySQL中join语句的基本使用教程及其字段对性能的影响

    join语句的基本使用 SQL(MySQL) JOIN 用于根据两个或多个表中的字段之间的关系,从这些表中得到数据. JOIN 通常与 ON 关键字搭配使用,基本语法如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1 通常称为左表,table2 称为右表.ON 关键字用于设定匹配条件,用于限定在结果集合中想要哪些行.如果需要指定其他条件,后面可以加上 WHERE 条件 或者 LIMIT 以限制记录返回数目等.

  • 在MySQL中使用JOIN语句进行连接操作的详细教程

    到目前,我们已经学习了从一个表中获取数据.这是简单的需要,但在大多数现实MySQL的使用,经常需要将数据从多个表中的一个单一的查询. 可以使用多个表中的单一SQL查询.在MySQL中联接(join)行为是指两个或多个表到一个表中可以使用连接在SELECT,UPDATE和DELETE语句中加入MySQL表.我们将看到一个例子LEFT JOIN简单的MySQL连接. 在命令提示符使用联接: 假设我们两个表的教程tcount_tbl和tutorials_tbl的完整列表如下: 例子: 试试下面的例子:

  • MySQL实战教程之Join语句执行流程

    目录 Join语句执行流程 一.Index Nested-Loop Join 二.Simple Nested-Loop Join 三.Block Nested-Loop Join 四.总结 Join语句执行流程 Hi,我是阿昌,今天学习记录的是关于Join语句执行流程的内容. 在实际生产中,关于 join 语句使用的问题,一般会集中在以下两类: 不让使用 join,使用 join 有什么问题呢? 如果有两个大小不同的表做 join,应该用哪个表做驱动表呢? 创建两个表 t1 和 t2 来说明.

  • MySQL基础教程之DML语句详解

    目录 DML 语句 1.插入记录 2.更新记录 3.简单查询记录 4.删除记录 5.查询记录详解(DQL语句) 5.1.查询不重复的记录 5.2.条件查询 5.3.聚合查询 5.4.排序查询 5.5.limit查询 5.6.连表查询 5.7.子查询 5.8.记录联合 5.9.select语句的执行顺序 6.总结 DML 语句 DML(Data Manipulation Language)语句:数据操纵语句. 用途:用于添加.修改.删除和查询数据库记录,并检查数据完整性. 常用关键字:insert

  • 掌握SQL Server实战教程之SQL Server的安装指南

    目录 前言 一. 数据库的介绍 1.1 数据库的分类 1.2 MS SQL介绍 二. MS SQL的安装 2.1 从网站下载安装包 2.2 开始安装 选择基本版本 2.3 安装SSMS 三. 连接数据库 3.1 数据库的连接 3.2 创建数据库 总结 前言 本文沐风晓月带你来了解一下sqlserver数据库的安装及简单使用,本文的主要任务: 安装SQL Server并能够成功的远程链接,然后执行几条简单的SQL语句进行测试即可 一. 数据库的介绍 1.1 数据库的分类 数据库的种类有很多,根据存

  • mysql存储过程之case语句用法实例详解

    本文实例讲述了mysql存储过程之case语句用法.分享给大家供大家参考,具体如下: 除了if语句,mysql提供了一个替代的条件语句CASE. mysql的 CASE语句使代码更加可读和高效.废话不多说,我们先来看下简单case语句的语法: CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands ... ELSE commands END CASE; 我们可以使

  • mysql存储过程之if语句用法实例详解

    本文实例讲述了mysql存储过程之if语句用法.分享给大家供大家参考,具体如下: mysql中的 IF语句允许我们根据表达式的某个条件或值结果来执行一组SQL语句,所以我们要在MySQL中形成一个表达式,可以结合文字,变量,运算符,甚至函数来组合.表达式可以返回TRUE,FALSE或NULL,这三个值之一.来看下语法结构: IF expression THEN statements; END IF; 如果上述表达式(expression)计算结果为TRUE,那么将执行statements语句,否

  • vue3实战教程之axios的封装和环境变量

    目录 axios 基本使用 配置 封装 请求时添加loading 环境变量 总结 axios axios: ajax i/o system. 一个可以同时在浏览器和node环境进行网络请求的第三方库 功能特点: 在浏览器中发送 XMLHttpRequests 请求 在 node.js 中发送 http请求 支持 Promise API 拦截请求和响应 转换请求和响应数据 等等 基本使用 get请求 // 导入的axios是一个实例对象 import axios from 'axios' // a

  • 根据mysql慢日志监控SQL语句执行效率

    根据mysql慢日志监控SQL语句执行效率 启用MySQL的log-slow-queries(慢查询记录). 在Linux环境下先要找到my.cnf文件(一般在/etc/mysql/),然后可能会发现该文件修改后无法保存,原因是你没有相应的权限,可以从属性中看到该文件的所有者是root,这时要先以root的身份打开它: sudo nautilus /etc/mysql 接着再打开my.cnf文件然后找到[mysqld]标签在下面加上: log-slow-queries=/path/slow.lo

  • java基本教程之join方法详解 java多线程教程

    本章涉及到的内容包括:1. join()介绍2. join()源码分析(基于JDK1.7.0_40)3. join()示例 1. join()介绍join() 定义在Thread.java中.join() 的作用:让"主线程"等待"子线程"结束之后才能继续运行.这句话可能有点晦涩,我们还是通过例子去理解: 复制代码 代码如下: // 主线程public class Father extends Thread {    public void run() {     

  • MYSQL事务教程之Yii2.0商户提现功能

    前言 我是一个半路出家的PHP程序员,到目前为止,不算在培训班学习的时间,已经写代码整整两年了.可能由于工作业务的原因,在这两年中我没有用到过MySQL事务.就在昨天有个关于支付宝转账的业务不得不使用MySQL事务来完成,别人说了很多,还是不明白MySQL事务到底是个啥,于是就开始了新一轮的补课,出来混,欠下的知识账总是要还的. 先简单说一下我昨天遇到的那个业务,我要在移动端发起一个支付宝提现的业务,这个业务我之前是分三步写的: 第一步: 首先提现的话,我先要在提现表里写入一条提现记录,再更新一

随机推荐