Mysql表连接的执行流程详解_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表连接 内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:MySQL中基本的多表连接查询教程MySQL的LEFT JOIN表连接的进阶学习教程MySQL中对表连接查询的简单优化教程mysql delete 多表连接删除功能mysql三张表连接建立视图mysql多表连接查询实例讲解浅谈Mysql多表连接查询的执行细节MySQL多表连接查询详解MySql数据库单表查询与多表连接查询效率对比

  • 本文相关:
  • win2008下mysql8.0.11升级mysql8.0.17版本详细步骤
  • mysql解决use db堵塞详解
  • 新手如何安装mysql(亲测有效)
  • centos7下 mysql定时自动备份的实现方法
  • table ‘xxx’ is marked as crashed and should be repaired 错误解决
  • 浅谈mysql存储引擎选择 innodb与myisam的优缺点分析
  • mysql exists 和in 详解及区别
  • mysql8.0.21安装步骤及出现问题解决方案
  • mysql实现快速删除所有表而不删除数据库的方法
  • mysql在debian系统中不能插入中文的终极解决方案
  • 求教:dbeaver执行mysql查询,突然需要添加用户名且表名需大写...
  • 如果要实现PHP与MYSQL的连接,利用phpMyAdmin建立数据库...
  • mysql内连接 比默认笛卡尔积
  • MYSQL数据表名称修改
  • 在PHP程序中,执行Mysql命令操作的语句是??
  • 有关mysql中sql的执行顺序的小问题
  • MySQL中对表连接查询的简单优化教程
  • redis怎样为第三方程序连接mysql的情况作缓存
  • Mysql逻辑架构详解
  • mysql jdbc连接步骤及常见参数
  • mysql免安装版配置步骤详解分享
  • Java程序远程连接mysql数据库失败是怎么回事?
  • Java程序远程连接mysql数据库失败是怎么回事?
  • MySQL的mysqldump工具用法详解
  • SQL语句执行深入讲解(MySQL架构总览->查询执行流程->SQL...
  • MySQL执行状态的查看与分析
  • Mysql InnoDB引擎的索引与存储结构详解
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql中基本的多表连接查询教程mysql的left join表连接的进阶学习教程mysql中对表连接查询的简单优化教程mysql delete 多表连接删除功能mysql三张表连接建立视图mysql多表连接查询实例讲解浅谈mysql多表连接查询的执行细节mysql多表连接查询详解mysql数据库单表查询与多表连接查询效率对比win2008下mysql8.0.11升级mysql8.0.17版本详细步骤mysql解决use db堵塞详解新手如何安装mysql(亲测有效)centos7下 mysql定时自动备份的实现方法table ‘xxx’ is marked as crashed and should be repaired 错误解决浅谈mysql存储引擎选择 innodb与myisam的优缺点分析mysql exists 和in 详解及区别mysql8.0.21安装步骤及出现问题解决方案mysql实现快速删除所有表而不删除数据库的方法mysql在debian系统中不能插入中文的终极解决方案mysql安装图解 mysql图文安装教程(详细说明)can""""t connect to mysql servwindows下mysql5.6版本安装及配置过程附有截图和mysql字符串截取函数substring的用法说明mysql提示:the server quit withoumysql之timestamp(时间戳)用法详解mysql创建用户与授权方法mysql——修改root密码的4种方法(以windows为mysql查看版本号的几种方式mysql日期数据类型、时间类型使用总结mysql 8.0.17 解压版安装配置方法图文教程mysql5.7.11编译安装及修改root密码的方法小结mysql中取系统当前时间,当前日期方便查询判定的代码解析数据库分页的两种方法对比(row_number()over()和top的mysql数据库表修复 myisammysql中find_in_set()函数的使用详解mysql索引是啥?不懂就问mysql show操作简单示例mysql实现行列转换非常实用的mysql函数全面总结详解示例分析教程
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved