MSQL系列(十) Mysql实战-Join驱动表和被驱动表区分

Mysql实战-Join驱动表和被驱动表区分

前面我们讲解了Mysql的查询连接Join的算法原理, 我发现大家都知道小表驱动大表,要让小表作为驱动表, 现在有2个问题

  • 查询多表, 到底哪个是驱动表?哪个是被驱动表, 如何区分?
  • 索引如何优化,到底是加在驱动表上,还是被驱动表上?

今天我们来讨论下这两个问题的答案

文章目录

      • Mysql实战-Join驱动表和被驱动表区分
        • 1.什么是驱动表和被驱动表？
        • 2.Explain命令区分 驱动表及被驱动表
        • 3. left join 左表可能不是驱动表
        • 4. left join 没where 查询 驱动表, 左表才是驱动表
        • 4. left join where 查询条件的表就是驱动表的错误说法
        • 5.left join where查询驱动表判断

1.什么是驱动表和被驱动表？

在join连接查询中，驱动表在SQL语句执行的过程中总是先被读取。而被驱动表在SQL语句执行的过程中总是后被读取。

在读取驱动表数据后，放入到join_buffer后，再去读取被驱动表中的数据来和驱动表中的数据进行匹配。如果匹配成功,就返回结果,否则该丢弃, 继续匹配下一条

为什么要小表驱动大表?
从上面的查询过程中,我们就知道了 , 因为小表查的少, 大大的减少了I/O 次数, join_buffer容量也有限, 表越小, 越少次数匹配, 越容易查结果,所以 我们必须区分 哪个是驱动表, 哪个是被驱动表

现在我们先创建2个表结构, 插入数据,作为测试数据

drop table user_info;
CREATE TABLE `user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
  `age` int(10)  DEFAULT NULL COMMENT '员工年龄',
  `address` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

drop table order_info;
CREATE TABLE `order_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '订单id',
  `user_id` bigint(20) NOT NULL COMMENT '用户user表主键id',
`goods` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '商品',
`production` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '产地',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表'

INSERT INTO `order_info` (order_id, user_id, goods, production) VALUES (CONCAT("uuid",1), 2, "衣服", "上海贸易");


#插入3条用户数据
INSERT INTO `user_info` (user_name, age, address) VALUES ("张三", 10, "北京");
INSERT INTO `user_info` (user_name, age, address) VALUES ("李四", 20, "上海");
INSERT INTO `user_info` (user_name, age, address) VALUES ("王五", 30, "广州");

#插入2条 张三的 订单记录
INSERT INTO `order_info` (order_id, user_id, goods, production) VALUES ("uuid1", 1, "衣服", "北京三里屯");
INSERT INTO `order_info` (order_id, user_id, goods, production) VALUES ("uuid2", 1, "鞋子", "北京三里屯");
#插入1w条 李四的订单记录, 用存储过程执行

#先创建存储过程
CREATE PROCEDURE test() #创建存储函数；
BEGIN
DECLARE i INT DEFAULT 100;

WHILE i < 10100 DO
	INSERT INTO `order_info` (order_id, user_id, goods, production) VALUES (CONCAT("uuid",i), 2, "书本", "上海贸易");
	SET i = i+1;
end WHILE;

END;
#然后执行 存储过程
CALL test();#调用存储函数

我们可以看下数据是否插入成功
user_info 3条数据
order_info 10002条数据
在这里插入图片描述

2.Explain命令区分 驱动表及被驱动表

对于已有的SQL语句,我们可以直接通过Explain 命令来判断 驱动表与被驱动表, explain命令查看一下SQL语句的执行计划。

输出的执行计划中，首先出现的排在第一行的表是驱动表，排在第二行的表是被驱动表,比如下面的语句

#查看驱动表 第一行就是驱动表
explain
select * from user_info 
left join order_info
on user_info.id = order_info.user_id;

查看执行结果

  • 第一行 user_info表 ,所以驱动表是 user_info
  • 第二行 order_info表, 被驱动表示 order_info
  • 此刻都没有索引信息, type=ALL
  • 即使双方连接字段是 id~user_id, user_info表的id是主键, user_info表也没有走索引
  • 所以驱动表有索引, 也不一定走
    在这里插入图片描述

这里虽然左表示 user_info 是驱动表, 而且是 left_join 查询, 那么我们可以得出结论 left join 左表一定是驱动表么 ?

不能, 重要事情说三遍
!!! left join 左表 不一定是驱动表
!!! left join 左表 不一定是驱动表
!!! left join 左表 不一定是驱动表

3. left join 左表可能不是驱动表

下面我们来验证下 left join 左表不是驱动表的逻辑

我们看下 下面的 查询语句, 也是用 left join 查询, 可以看到 左表是 order left join user_info
那么我们看下 explain 到底哪个是驱动表

#left join 左表不一定是 驱动表
explain
select * from order_info 
left join user_info
on user_info.id = order_info.user_id
where user_info.id = 1;

执行结果

  • left join 左表是 order_info
  • 但是 驱动表是user_info
  • 所以 并不是 left_join 左表就是驱动表
  • 同理 right_join 右表也不一定是驱动表
    在这里插入图片描述

那么 什么情况下? left join 左表示驱动表呢?

4. left join 没where 查询 驱动表, 左表才是驱动表

当SQL查询语句没有 where 查询条件时

  • 没有 where 查询条件时 left join 左表是驱动表, 右表是被驱动表
  • 没有 where 查询条件时 right join 右表是驱动表, 左表示被驱动表
  • 没有 where 查询条件时 inner join 也就是join, mysql自动选择 小表作为驱动表, 大表作为被驱动表,进行底层优化

先说结论, 下面我们验证下这个逻辑

  • 没有 where 查询条件时 left join 左表是order_info, explain 驱动表就是 order_info
  • 没有 where 查询条件时 left join 左表示驱动表, 不管查询表位置如何交换
  • 没有 where 查询条件时 join查询, 不管 左右表顺序, mysql自己优化选择小表作为驱动表

1.没有 where 查询条件时 left join 左表是order_info, explain 驱动表就是 order_info

#没where 查询 左表才是驱动表, 左表是order
explain
select * from order_info 
left join user_info
on user_info.id = order_info.user_id

执行结果, 符合预期
在这里插入图片描述

换下位置,看看是否 依旧如此
2.没有 where 查询条件时 left join 左表是user_info, explain 驱动表就是 user_info

#没where 查询  左表才是驱动表, 换位置 左表是user
 explain
select * from user_info 
left join order_info
on order_info.user_id = user_info.id

执行结果, 符合预期
在这里插入图片描述
3.没有 where 查询条件时 , 不管 左右表顺序, join 驱动表是mysql自己优化选择的,小表 user_info就是驱动表, user_info 3条数据, order_info 1w多条数据

#join查询, mysql选择小表作为驱动表
 explain
select * from user_info 
left join order_info
on order_info.user_id = user_info.id

#join'查询, 换下 user_info 和 order_info 的位置
explain
select * from order_info 
join user_info
on user_info.id = order_info.user_id

user_info不论左侧右侧, 都是小表作为驱动表
执行结果 符合预期
在这里插入图片描述

4. left join where 查询条件的表就是驱动表的错误说法

有where 查询语句时, 驱动表的判断规则是另一种情况
有一种 说法 where查询中只有一个表结构, 那么该表就是驱动表 ?

这种说法是错误的,重要事情说三遍
!!! 有where查询的, where条件的表 就是驱动表 这是错误的
!!! 有where查询的, where条件的表 就是驱动表 这是错误的
!!! 有where查询的, where条件的表 就是驱动表 这是错误的

#带where 查询表, where的表 不是驱动表, 验证错误语法
explain
select * from user_info 
left join order_info
on user_info.id = order_info.user_id
where order_info.user_id = 1;

这是有where 查询条件的, 而且where查询中只有一个表 order_info, 我们来执行下 explain
执行结果, 有where查询条件, order_info,但是 explain的驱动表是 user_info表
在这里插入图片描述
所以上面的说法是靠不住的

5.left join where查询驱动表判断

上面我们验证了 where 查询表就是驱动表这种说法的错误性, 那么 带where查询条件到底哪个是驱动表呢?

我们先说结论,然后验证,结论如下

  • where 查询字段没索引, 那就是谁是左表,用谁
  • where 查询字段有索引, 那就用where表作为驱动表

1.where 查询表字段没索引, 谁是左表,用谁做驱动表
在这里插入图片描述

2.where 查询字段有索引, 那就用where表作为驱动表
在这里插入图片描述

至此, 我们已经了解了 join 语法驱动表及被驱动表的判断,这对于我们进行SQL优化至关重要, 只有知道了被驱动表我们才能进行针对索引进行优化,磨刀不误砍柴工


http://www.niftyadmin.cn/n/5131447.html

相关文章

HPV感染的风险：闫会宁主任分析酒店环境中的常见因素

HPV感染的风险：闫会宁主任分析酒店环境中的常见因素

人类乳头瘤病毒(HPV)是一种普遍存在的病毒&#xff0c;其存在和传播方式多种多样。近年来&#xff0c;人们对于HPV的认识不断深入&#xff0c;知道其在酒店环境中的传播风险。本文将探讨哪些情况下在酒店可能感染HPV。 一、HPV的传播方式 HPV主要通过直接接触传播&#xff0c…
阅读更多...
H5游戏分享-全民找房祖名qmxzfzm

H5游戏分享-全民找房祖名qmxzfzm

H5游戏分享-全民找房祖名qmxzfzm 一开始就比较简单 后面就会越来越难&#xff0c;而且也有时间限制 游戏的源码 <!DOCTYPE html> <html><head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width,ini…
阅读更多...
wiresharak捕获DNS

wiresharak捕获DNS

DNS解析&#xff1a; 过滤项输入dns&#xff1a; dns查询报文 应答报文&#xff1a; 事务id相同&#xff0c;flag里 QR字段1&#xff0c;表示响应&#xff0c;answers rrs变成了2. 并且响应报文多了Answers 再具体一点&#xff0c;得到解析出的ip地址&#xff08;最底下的add…
阅读更多...
logback-classic包中ThrowableProxy递归缺陷StackOverflowError解析

logback-classic包中ThrowableProxy递归缺陷StackOverflowError解析

logback-classic&#xff08;<1.2.12版本&#xff09;ThrowableProxy类中存在递归缺陷&#xff0c;会导致java.lang.StackOverflowError。改缺陷在1.2.12以上版本(包含该版本)中已修复。 如何复现&#xff1a; 两个异常彼此设置casue&#xff1a; 运行后报以下错误 以上写…
阅读更多...
二、BurpSuite Decoder解码器

二、BurpSuite Decoder解码器

一、编码解码 解释&#xff1a;BurpSuite 可以用这个模块来轻松进行编码解码&#xff0c;下面是支持的类型 URL HTML Base64 ASCIIhex Hex Octal Binary Gzip 注意&#xff1a;特别注意的是URL编码&#xff0c;一般的在线网站都无法对比如‘abc’的文本编码&#xff0c;burps…
阅读更多...
详解 DES加密技术 | 凯撒密码 | 栅栏密码

详解 DES加密技术 | 凯撒密码 | 栅栏密码

目录 密码学 恺撒密码 栅栏密码 消息和加密 密码的三个特性 算法和密钥 对称算法 公开密钥算法 DES对称加密技术 DES算法的安全性 DES算法的原理 DES算法的实现步骤 IP置换表和IP-1逆置换表 函数f 子密钥ki S盒的工作原理 DES算法的应用误区 密码学 密码学是一门…
阅读更多...
io测试【FPGA】

io测试【FPGA】

按钮&#xff1a; 按钮是区分输入输出的&#xff0c; LED配置成输入&#xff0c;是不会亮的。 //timescale 1s/1ns // 【】是预编译&#xff0c;类似C语言的#include // 这是FPGA原语 //晶振时钟 1ns//类型声明 module LED //跟PLC的FB功能块一样&#xff0c;使用前需要实…
阅读更多...
【python练习】在棋盘上收集奖品，跟着书本理思路

【python练习】在棋盘上收集奖品，跟着书本理思路

在棋盘上收集奖品 Description在棋盘上收集奖品。假设有一个m x n的棋盘&#xff0c;每个格子里有一个奖品&#xff08;每个奖品的价值在10到1000之间&#xff09;&#xff0c;现在要求从左上角开始到右下角结束&#xff0c;每次只能往右或往下走一个格子&#xff0c;所经过的格…
阅读更多...
最新文章

Copyright @ 2022~2023