Mysql表连接的执行流程详解

目录
  • 1. 前言
    • 1.1 mysql连接的原理
    • 1.2 show warnings命令
  • 2. 准备工作
  • 3. inner join内连接on、where的区别
  • 4. left join左连接on、where的区别
    • 4.1 where驱动表过滤条件
    • 4.2 on驱动表过滤条件
    • 4.3 on被驱动表过滤条件
    • 4.4 where被驱动表过滤条件
  • 5. 总结

1. 前言

对于连接操作,驱动表和被驱动表的关联条件我们放在on后面,如果额外增加对驱动表和被驱动表的过滤条件,放到on或者where后面都不会报错,但是得到的结果集却是不一样的???

1.1 mysql连接的原理

众所周知,mysql是基于嵌套循环连接(Nested-Loop Join,暂不考虑优化算法)算法来进行表之间的连接操作的,大致过程如下:

  • 选取驱动表,使用与驱动表相关的过滤条件执行对驱动表的单表查询;
  • 对于查询到的驱动表中的每一条纪录,分别到被驱动表中查找匹配的纪录。

伪代码如下:

for each row in t1 {      // 遍历满足对t1单表查询结果集中的每一条纪录
    for each row in t2 {  // 对于某条t1纪录,遍历满足对t2单表查询结果集中的每一条纪录
        if row satisfies join conditions, send to client
    }
}

1.2 show warnings命令

我们写的sql语句,在经过优化器优化后才会交给执行器执行,而show warnings命令则可以帮助我们获得优化器优化后的sql。

2. 准备工作

表结构如下:

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  `stu_name` varchar(30) NOT NULL DEFAULT '',
  `stu_sex` varchar(10) NOT NULL DEFAULT '',
  `stu_age` int(10) NOT NULL DEFAULT '0',
  `stu_dept` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uq_stu_code` (`stu_code`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4

CREATE TABLE `course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cou_code` varchar(20) NOT NULL DEFAULT '',
  `cou_name` varchar(50) NOT NULL DEFAULT '',
  `cou_score` int(10) NOT NULL DEFAULT '0',
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_stu_code_cou_code` (`stu_code`,`cou_code`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4

表数据如下: 

3. inner join内连接on、where的区别

sql如下:

select * from student
inner join course on student.stu_code = course.stu_code
and student.stu_code >= 3 and course.cou_score >= 80;

执行explain+sql命令: 

执行show warnings命令: 

分析:从show warnings分析来看,对于inner join连接,经过优化器优化后,on连接条件会转化为where!也就是说内连接中的where和on是等价的

4. left join左连接on、where的区别

4.1 where驱动表过滤条件

sql如下:

select * from student
left join course on student.stu_code = course.stu_code
where student.stu_code >= 3;

执行explain+sql命令: 

执行show warnings命令: 

结果集: 

分析:从explain分析看出,student作为驱动表,把student.stu_code >= 3作为过滤条件进行全表扫描,然后把查询到的每条纪录的student.stu_code(也就是on条件里面的)分别作为过滤条件让被驱动表course做单表查询。

4.2 on驱动表过滤条件

sql如下:

select * from student
left join course on student.stu_code = course.stu_code
and student.stu_code >= 3;

执行explain+sql命令: 

执行show warnings命令: 

结果集: 

从结果集来看,student.stu_code >= 3并未生效,为什么?

分析:从explain分析看出,student作为驱动表,做全表扫描,然后把查询到的每条记录的student.stu_code和student.stu_code >= 3(也就是on条件里面的)分别做为过滤条件让被驱动表做单表查询;此时student.stu_code >= 3对驱动表是不过滤的,仅在连接被驱动表时生效,查询不到符合纪录而返回NULL!

4.3 on被驱动表过滤条件

sql如下:

select * from student
left join course on student.stu_code = course.stu_code
and course.cou_score >= 80;

执行explain+sql命令: 

执行show warnings命令: 

结果集: 

分析:从explain分析看出,student作为驱动表,做全表扫描,然后把查询到的每条记录的student.stu_code和course.cou_score >= 80(也就是on条件里面的)分别做为过滤条件让被驱动表做单表查询;

4.4 where被驱动表过滤条件

sql如下: 

执行explain+sql命令: 

执行show warnings命令: 

结果集: 

从show warnings分析来看?left join连接变成了inner join连接?

分析:从show warnings分析看出,如果被驱动表有过滤条件在where,那么left join会被失效,被优化成inner join连接。所以被驱动表的过滤条件应该放在on而不是where

5. 总结

其实,在内连接的基础上引入外连接的概念,就是为了解决驱动表中的纪录即使没有在被驱动表中找到匹配的纪录,仍要加入结果集的问题。所以对于外连接(外连接包括:左连接、右连接),被驱动表的过滤条件我们应该放在on!

到此这篇关于Mysql表连接的执行流程详解的文章就介绍到这了,更多相关Mysql表连接 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL中对表连接查询的简单优化教程

    在MySQL中,A LEFT JOIN B join_condition执行过程如下: · 根据表A和A依赖的所有表设置表B. · 根据LEFT JOIN条件中使用的所有表(除了B)设置表A. · LEFT JOIN条件用于确定如何从表B搜索行.(换句话说,不使用WHERE子句中的任何条件). · 可以对所有标准联接进行优化,只是只有从它所依赖的所有表读取的表例外.如果出现循环依赖关系,MySQL提示出现一个错误. · 进行所有标准WHERE优化. · 如果A中有一行匹配WHERE子句,但B中没

  • 浅谈Mysql多表连接查询的执行细节

    先构建本篇博客的案列演示表: create table a(a1 int primary key, a2 int ,index(a2)); --双字段都有索引 create table c(c1 int primary key, c2 int ,index(c2), c3 int); --双字段都有索引 create table b(b1 int primary key, b2 int); --有主键索引 create table d(d1 int, d2 int); --没有索引 insert

  • mysql多表连接查询实例讲解

    实际的项目,存在多张表的关联关系.不可能在一张表里面就能检索出所有数据.如果没有表连接的话,那么我们就需要非常多的操作.比如需要从A表找出限制性的条件来从B表中检索数据.不但需要分多表来操作,而且效率也不高.比如书中的例子: 代码如下: SELECT FId FROM T_Customer WHERE FName='MIKE' 这个SQL语句返回2,也就是姓名为MIKE 的客户的FId值为2,这样就可以到T_Order中检索FCustomerId等于2 的记录: 代码如下: SELECT FNu

  • mysql delete 多表连接删除功能

    单个表的删除: DELETE FROM tableName WHERE columnName = value; 删除表内的所有行: 即:保留表的结构.属性.索引 DELETE FROM tablename; DELETE * FROM tablename; 删除同一张表内的所有内容(删除数据.表结构) TRUNCATE customer; 无法报告删除了几行,且只能用于单一表 多表连接删除: DELETE orders,itrms FROM orders,items WHERE orders.u

  • MySQL多表连接查询详解

    目录 多表连接查询 内连接 左连接 右连接 子查询 总结 多表连接查询 表与表之间的连接分为内连接和外连接 内连接:仅选出两张表互相匹配的记录 外连接:既包括两张表匹配的记录,也包括不匹配的记录,同时外连接又分为左外连接(左连接)和右外连接(右连接) 内连接 首先准备两张表 学生student表 分数score表 内连接:在每个表中找出符合条件的共有记录 查询student表中的学生姓名和分数 第一种写法:只使用where select a.s_name, b.s_score from stud

  • MySQL的LEFT JOIN表连接的进阶学习教程

    LEFT JOIN的主表 这里所说的主表是指在连接查询里MySQL以哪个表为主进行查询.比如说在LEFT JOIN查询里,一般来说左表就是主表,但这只是经验之谈,很多时候经验主义是靠不住的,为了说明问题,先来个例子,建两个演示用的表categories和posts: CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(15) NOT NULL

  • MySQL中基本的多表连接查询教程

    一.多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用','  如: 由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢.一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN 2.   内连接INNER JOIN 在MySQL中把I SELECT * FROM table1 CROSS JOIN tabl

  • mysql三张表连接建立视图

    三张表连接·· A表的a字段 对应 B表的b字段 ,B表的b1字段对应C 表的c字段 现在 建立 一个视图,可以同时 看到三张表的 所有信息·~ create or replace view v_name as select t1.*,t2.*,t3.* from A表 t1, B表 t2, C表 t3 where t1.a=t2.b and t2.b1=t3.c 两表链接创建视图 CREATE TABLE `aa_user` ( `id` int(10) NOT NULL, `name` va

  • MySql数据库单表查询与多表连接查询效率对比

    这段时间在做项目的过程中,遇到一个模块,数据之间的联系很复杂,在建表的时候就很纠结,到底该怎么去处理这些复杂的数据呢,是单表查询,然后在业务层去处理数据间的关系,还是直接通过多表连接查询来处理数据关系呢? 通过查阅资料和阅读博客,有以下两个回答: 一.<高性能mysql>中的回答 很多高性能的应用都会对关联查询进行分解.简单地,可以对每个表进行一次单表查询,然后将结果在应用程序中进行关联.例如,下面这个查询: select * from tag join tag_post on tag_pos

  • Mysql表连接的执行流程详解

    目录 1. 前言 1.1 mysql连接的原理 1.2 show warnings命令 2. 准备工作 3. inner join内连接on.where的区别 4. left join左连接on.where的区别 4.1 where驱动表过滤条件 4.2 on驱动表过滤条件 4.3 on被驱动表过滤条件 4.4 where被驱动表过滤条件 5. 总结 1. 前言 对于连接操作,驱动表和被驱动表的关联条件我们放在on后面,如果额外增加对驱动表和被驱动表的过滤条件,放到on或者where后面都不会报

  • mysql基础:mysqld_safe 启动执行流程详解

    mysqld_safe脚本执行的基本流程:1.查找basedir和ledir.2.查找datadir和my.cnf.3.对my.cnf做一些检查,具体检查哪些选项请看附件中的注释.4.解析my.cnf中的组[mysqld]和[mysqld_safe]并和终端里输入的命令合并.5.调用parse_arguments函数解析用户传递的所有参数($@).6.对系统日志和错误日志的判断和相应处理具体可以参考附件中的注释,及选项--err-log参数的赋值.7.对选项--user,--pid-file,-

  • mysql update语句的执行过程详解

    以前有过一篇关于MySQL查询语句的执行过程,这里总结一下update语句的执行过程.由于update涉及到数据的修改,所以,很容易推断,update语句比select语句会更复杂一些. 1,准备 创建一张test表 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) NOT NULL DEFAULT '0' COMMENT '数值', PRIMARY KEY (`id`) ) ENGINE=InnoDB

  • SpringMvc框架的简介与执行流程详解

    目录 一.SpringMvc框架简介 1.Mvc设计理念 2.SpringMvc简介 二.SpringMvc执行流程 1.流程图解 2.步骤描述 3.核心组件 三.整合Spring框架配置 1.spring-mvc配置 2.Web.xml配置 3.测试接口 4.常用注解说明 四.常见参数映射 1.普通映射 2.指定参数名 3.数组参数 4.Map参数 5.包装参数 6.Rest风格参数 五.源代码地址 一.SpringMvc框架简介 1.Mvc设计理念 M:代表模型Model 模型就是数据,应用

  • ThreadPoolExecutor参数含义及源码执行流程详解

    目录 背景 典型回答 考点分析 知识拓展 execute() VS submit() 线程池的拒绝策略 自定义拒绝策略 ThreadPoolExecutor 扩展 小结 背景 线程池是为了避免线程频繁的创建和销毁带来的性能消耗,而建立的一种池化技术,它是把已创建的线程放入“池”中,当有任务来临时就可以重用已有的线程,无需等待创建的过程,这样就可以有效提高程序的响应速度.但如果要说线程池的话一定离不开 ThreadPoolExecutor ,在阿里巴巴的<Java 开发手册>中是这样规定线程池的

  • Handler消息传递机制类引入及执行流程详解

    目录 提要 1.学习路线图: 2.Handler类的引入 3.Handler的执行流程图 4.Handler的相关方法 5.Handler的使用示例 1)Handler写在主线程中 2)Handler写在子线程中 提要 本节给大家讲解的是Activity中UI组件中的信息传递Handler,相信很多朋友都知道,Android为了线程安全,并不允许我们在UI线程外操作UI:很多时候我们做界面刷新都需要通过Handler来通知UI组件更新!除了用Handler完成界面更新外,还可以使用runOnUi

  • MySQL数据库Event定时执行任务详解

    一.背景 由于项目的业务是不断往前跑的,所以难免数据库的表的量会越来越庞大,不断的挤占硬盘空间.即使再大的空间也支撑不起业务的增长,所以定期删除不必要的数据是很有必要的.在我们项目中由于不清理数据,一个表占的空间竟然达到了4G之多.想想有多可怕... 这里介绍的是用MySQL 建立一个定时器Event,定期清除掉之前的不必要事件. 二.内容 #1.建立存储过程供事件调用 delimiter// drop procedure if exists middle_proce// create proc

  • AngularJS执行流程详解

    一.启动阶段 浏览器解析HTML页面,读取到angular.js的<script>标签后会停止解析后面的DOM节点,开始执行angular.js,与此同时,Angular会设置一个事件监听器来监听DOMContentLoaded事件,当Angular监听到这个事件后,Angular就启动了. 二.初始化阶段 Angular启动后,它会查找ng-app指令,然后初始化一系列必要的组件(即$injector.$compile服务以及$rootScope),接着继续解析DOM. 三.编译.链接阶段

  • MySQL执行SQL语句的流程详解

    目录 1.通常sql执行流程 1.1 问题1:MySQL谁去处理网络请求? 1.2 问题2:MySQL如何执行sql语句? 1.3 查询解析器 1.4 查询优化器 1.5 存储引擎 1.6 执行器 2.总结 1.通常sql执行流程 用户发起请求到业务服务器,执行sql语句时,先到连接池中获取连接,然后到mysql服务器执行查询. 1.1 问题1:MySQL谁去处理网络请求? msyql服务器谁负责从这个连接中去监听这个网络请求?谁负责从网络连接里把数据读出来? 其实大家都知道,网络连接必须得分配

  • JDBC连接MySQL数据库批量插入数据过程详解

    这篇文章主要介绍了JDBC连接MySQL数据库批量插入数据过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 1.读取本地json数据 2.jdbc理解数据库 3.批量插入 maven 引入jar包: <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2

随机推荐