什么,LEFT JOIN 会变成 JOIN?
wptr33 2024-11-21 22:05 17 浏览
前言
在日常开发中,对于 LEFT JOIN 和 JOIN 的用法大部分应该都是一样的,如果有两个表 A,B,如果两个表的数据都想要,就使用 JOIN,如果只想要一个表的全部数据,另一个表数据可有可无,就使用 LEFT JOIN。(当然这么描述是不太准确的,但是很符合我的日常业务开发)。
在 MYSQL LEFT JOIN 详解 这篇文章中我们已经知道了,LEFT JOIN 是自己选择驱动表的,而 JOIN 是 MYSQL 优化器选择驱动标的。
那么,当我们写了一条 LEFT JOIN 语句,MYSQL 会将这条语句优化成 JOIN 语句吗?
如果会优化的话,那么什么时候会优化呢?
事实上,这正是我遇到的一个线上问题。我们一起来看一下。
问题描述
在我们线上有这么一条慢 SQL(已处理),执行时间超过 0.5 秒。
select
count(distinct order.order_id)
from order force index(shop_id)
left join `order_extend`
on `order`.`order_id` = `order_extend`.`order_id`
where `order`.`create_time` >= "2020-08-01 00:00:00"
and `order`.`create_time` <= "2020-08-01 23:59:59"
and `order`.`shop_id` = 328449726569069326
and `order`.`status` = 1
and `order_extend`.`shop_id` = 328449726569069326
and `order_extend`.`status` = 1
复制代码
explain 结果如下:
+----+-------------+--------------+------------+--------+------------------+----------+---------+------------------------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------------+--------+------------------+----------+---------+------------------------+------+-------------+
| 1 | SIMPLE | order_extend | NULL | ref | order_id,shop_id | shop_id | 8 | const | 3892 | Using where |
| 1 | SIMPLE | order | NULL | eq_ref | shop_id | shop_id | 16 | example.order.order_id | 1 | Using where |
+----+-------------+--------------+------------+--------+------------------+----------+---------+------------------------+------+-------------+
2 rows in set, 1 warning (0.00 sec)
复制代码
问题分析
通过 explain,再结合我们之前讲的 MYSQL 连接查询算法,驱动表为 order_extend,循环 3892 次,说多也不多,说少也不少,被驱动表数据查询类型为 eq_ref,所以应该不会太慢,那么问题就出现在 3892 次上面了,想办法将这个数字降下来即可。
等等!为什么驱动表是 order_extend?我明明使用的是 LEFT JOIN 啊,按理说驱动表应该是 order 表,为什么会变成了 order_extend 了。难道是 MYSQL 内部优化了?
顺着这个思路,既然驱动表变了,说明这条 SQL 变为 JOIN 语句了。
我们顺着分析 JOIN 语句的方式来分析一下这条语句。(ps:需要对 MYSQL JOIN 内部执行过程有一定的理解,如果不太熟悉,请先移步看这篇文章 → MYSQL 连接查询算法 )
MYSQL 选择 order_extend 当做驱动表,说明在 where 条件下 order_extend 查询的数据更少,MYSQL 会选择一个小的表当做驱动表。
我们来分别适用上述的 where 条件单独执行 select count(*) 语句,查看一下大致每个表都涉及到多少条 SQL 记录。
为了不影响我们的分析,我们使用 explain 语句,这样整个过程就都是估算的结果,模拟一下 MYSQL 分析的过程。
mysql> explain select
count(distinct order.order_id)
from order force index(shop_id)
where `order`.`create_time` >= "2020-08-01 00:00:00"
and `order`.`create_time` <= "2020-08-01 23:59:59"
and `order`.`shop_id` = 328449726569069326
and `order`.`status` = 1;
+----+-------------+-------+------------+------+--------------------------------+---------+---------+-------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+--------------------------------+---------+---------+-------+--------+-------------+
| 1 | SIMPLE | order | NULL | ref | PRIMARY,shop_id,create_time... | shop_id | 8 | const | 320372 | Using where |
+----+-------------+-------+------------+------+--------------------------------+---------+---------+-------+--------+-------------+
1 row in set, 1 warning (0.00 sec)
复制代码
select
count(distinct order_extend.order_id)
and `order_extend`.`shop_id` = 328449726569069326
and `order_extend`.`status` = 1
+----+-------------+--------------+------------+------+------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+------------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | order_extend | NULL | ref | order_id,shop_id | shop_id | 8 | const | 3892 | 10.00 | Using where |
+----+-------------+--------------+------------+------+------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
复制代码
可以看到,在上述 where 条件下,order_extend 表只会查询 3892 条数据,而 order 表会查询 320372 条数据,所以 order_extend 表当驱动表是完全没有问题的。
那么我们再来看看为什么 order 表会扫描这么多数据呢?在 2020-08-01 这一天可能也没有这么多数据啊。那么这个时候我们应该会很容易的想到,是强制走索引的问题,因为在上述查询语句中,我们强制走了 shop_id 索引,这个索引可能不是最优索引,我们把 force index(shop_id) 去掉再试试看
mysql> explain select
count(distinct order.order_id)
where `order`.`create_time` >= "2020-08-01 00:00:00"
and `order`.`create_time` <= "2020-08-01 23:59:59"
and `order`.`shop_id` = 328449726569069326
and `order`.`status` = 1;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+--------------------------+
| 1 | SIMPLE | order | NULL | ref | create_time | create_time | 8 | const | <3892 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
复制代码
可以看到,如果不强制走 shop_id 索引的话,走 create_time 索引的话,扫描的行数会更少,假设说 100 行,只会循环 100 次,扫描 100 x 3892 行数据,而之前的总共要循环 3892 次,扫描 3892 x 300000 行数据。
问题结论
所以最终的这条慢 SQL 的原因确定了,是因为我们强制走 shop_id 索引,导致 MYSQL 扫描的行数更多了,我们只需要去掉强制走索引即可,大多数时间 MYSQL 都会选择正确的索引,所以强制使用索引的时候一定要小心谨慎。
问题延伸
SQL 慢的问题我们已经解决了,我们再来回顾一下文章开头的问题:LEFT JOIN 会被优化为 JOIN 吗?
答案是会的。那么什么时候会出现这种情况呢?
我们再来回顾一下 MYSQL LEFT JOIN 详解 文章中的内容。
为了方便阅读,我们将部分内容粘贴出来。
mysql> select * from goods left join goods_category on goods.category_id = goods_category.category_id;
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
| 1 | 男鞋1 | 1 | 1 | 鞋 |
| 2 | 男鞋2 | 1 | 1 | 鞋 |
| 3 | 男鞋3 | 3 | 3 | 羽绒服 |
| 4 | T恤1 | 2 | 2 | T恤 |
| 5 | T恤2 | 2 | 2 | T恤 |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)
mysql> select * from goods left join goods_category on goods.category_id = goods_category.category_id;
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
| 1 | 男鞋1 | 1 | 1 | 鞋 |
| 2 | 男鞋2 | 1 | 1 | 鞋 |
| 3 | 男鞋3 | 4 | NULL | NULL |
| 4 | T恤1 | 2 | 2 | T恤 |
| 5 | T恤2 | 2 | 2 | T恤 |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)
mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤1');
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
| 1 | 男鞋1 | 1 | NULL | NULL |
| 2 | 男鞋2 | 1 | NULL | NULL |
| 3 | 男鞋3 | 4 | NULL | NULL |
| 4 | T恤1 | 2 | 2 | T恤 |
| 5 | T恤2 | 2 | NULL | NULL |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)
mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and c.category_name = 'T恤');
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
| 1 | 男鞋1 | 1 | NULL | NULL |
| 2 | 男鞋2 | 1 | NULL | NULL |
| 3 | 男鞋3 | 4 | NULL | NULL |
| 4 | T恤1 | 2 | 2 | T恤 |
| 5 | T恤2 | 2 | 2 | T恤 |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)
mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id) where c.category_name = '鞋';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
| 1 | 男鞋1 | 1 | 1 | 鞋 |
| 2 | 男鞋2 | 1 | 1 | 鞋 |
+----------+------------+-------------+-------------+---------------+
2 rows in set (0.00 sec)
mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id) where g.goods_name = 'T恤1';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
| 4 | T恤1 | 2 | 2 | T恤 |
+----------+------------+-------------+-------------+---------------+
1 row in set (0.00 sec)
mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤2') where g.goods_name = 'T恤1';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
| 4 | T恤1 | 2 | NULL | NULL |
+----------+------------+-------------+-------------+---------------+
1 row in set (0.00 sec)
复制代码
我们可以看到,当 where 条件中有被驱动表的条件时,查询结果是和 JOIN 的结果是一致的,无 NULL 值的出现。
所以,我们可以想到,LEFT JOIN 优化为 JOIN 的条件为:where 条件中有被驱动表的非空条件时,LEFT JOIN 等价于 JOIN。
这不难理解,LEFT JOIN 会返回驱动表所有数据,当有被驱动表的 where 条件时,会过滤掉 NULL 的值,此时和 JOIN 的结果一致了,那么 MYSQL 会选择将 LEFT JOIN 优化为 JOIN,这样就可以自己选择驱动表了。
实例测试
我们再来编写一个测试用例来验证一下我们的结论。
CREATE TABLE `A` (
`id` int(11) auto_increment,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100)do
insert into A (`a`) values(i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
CREATE TABLE `B` (
`id` int(11) auto_increment,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `b` (`b`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100)do
insert into B (`b`) values(i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
复制代码
我们创建了两张一模一样的表,每个表中有 100 条数据,然后我们执行一下 LEFT JOIN 语句。
mysql> explain select * from A left join B on A.id = B.id where A.a <= 100;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| 1 | SIMPLE | A | NULL | index | a | a | 5 | NULL | 100 | 100.00 | Using where; Using index |
| 1 | SIMPLE | B | NULL | eq_ref | PRIMARY | PRIMARY | 4 | example2.A.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
复制代码
mysql> explain select * from A left join B on A.id = B.id where A.a <= 100 and B.b <= 50;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| 1 | SIMPLE | B | NULL | range | PRIMARY,b | b | 5 | NULL | 50 | 100.00 | Using where; Using index |
| 1 | SIMPLE | A | NULL | eq_ref | PRIMARY,a | PRIMARY | 4 | example2.B.id | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
复制代码
mysql> explain select * from A left join B on A.id = B.id where A.a <= 100 and B.b <= 100;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| 1 | SIMPLE | A | NULL | index | PRIMARY,a | a | 5 | NULL | 100 | 100.00 | Using where; Using index |
| 1 | SIMPLE | B | NULL | eq_ref | PRIMARY,b | PRIMARY | 4 | example2.A.id | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
复制代码
从上面看,给 B 表增加了 where 条件之后,如果 B 表扫描的行数更少,那么是有可能换驱动表的,这也说明了,LEFT JOIN 语句被优化成了 JOIN 语句。
总结
上面我们分析了一条慢 SQL 的问题,分析的过程涉及到了很多知识点,希望大家可以认真研究一下。
同时我们得出了一条结论:当有被驱动表的非空 where 条件时,MYSQL 会将 LEFT JOIN 语句优化为 JOIN 语句。
相关推荐
- C++企业级开发规范指南(c++开发gui)
-
打造高质量、可维护的C++代码标准一、前言C++作为一门功能强大的系统级编程语言,被广泛应用于操作系统、游戏引擎、高性能服务器、数据库系统等领域。知名互联网公司(如Google、Microsoft、腾...
- C++|整型的最值、上溢、下溢、截断、类型提升和转换
-
整数在计算机内以有限字长表示,当超出最值(有限字长)时,需要截断(溢出,求模)操作。不同字长的整型具有不同的值域,混合运算时,需要类型提升和转换。1整形最值在<limit.h>中有整型的...
- C++|漫谈STL细节及内部原理(c++ std stl)
-
1988年,AlexanderStepanov开始进入惠普的PaloAlto实验室工作,在随后的4年中,他从事的是有关磁盘驱动器方面的工作。直到1992年,由于参加并主持了实验室主任BillWo...
- C++11新特性总结 (二)(c++11新特性 pdf)
-
1.范围for语句C++11引入了一种更为简单的for语句,这种for语句可以很方便的遍历容器或其他序列的所有元素vector<int>vec={1,2,3,4,5,6};f...
- C++ STL 漫谈(c++中的stl到底指的什么)
-
标准模板库(StandardTemplateLibrary,STL)是惠普实验室开发的一个函数库和类库。它是由AlexanderStepanov、MengLee和DavidRMusser在...
- C++学习教程_C++语言随到随学_不耽误上班_0基础
-
C++学习教程0基础学C++也可以,空闲时间学习,不耽误上班.2019年C语言新课程已经上线,随到随学,互动性强,效果好!带你征服C++语言,让所有学过和没有学过C++语言的人,或是正准备学习C++语...
- C++遍历vector元素的四种方式(c++ 遍历vector)
-
vector是相同类型对象的集合,集合中的每个对象有个对应的索引。vector常被称为容器(container)。C++中遍历vector的所有元素是相当常用的操作,这里介绍四种方式。1、通过下标访问...
- 一起学习c++11——c++11中的新增的容器
-
c++11新增的容器1:array当时的初衷是希望提供一个在栈上分配的,定长数组,而且可以使用stl中的模板算法。array的用法如下:#include<string>#includ...
- C++编程实战基础篇:一维数组应用之投票统计
-
题目描述班上有N个同学,有五位候选人“A,B,C,D,E”,请所有的同学投票并选举出班长,现在请你编写程序来他们计算候选人的得票总数,每位同学投票将以数字的形式投票“12345”分别代表五位候选人,...
- C++20 新特性(6):new表达式也支持数组大小推导
-
new表达式也支持数组大小推导在C++17标准中,在定义并初始化静态数组时,是可以忽略数组大小,然后通过初始化数据来推导数组的大小。但使用new来定义并初始化动态数组时,并不支持这种自动推导数组大...
- C++ 结构体(struct)最全详解(c++结构体用法)
-
一、定义与声明1.先定义结构体类型再单独进行变量定义structStudent{intCode;charName[20];charSex;intA...
- 自学 C++ 第 6 课 二维数组找最值
-
键盘输入一个m×n的二维数组,通过C++编程找出元素中的最大值,并输出其所在的位置坐标。例如,输入一个4×5的二维数组,数组元素分别为{{556623749},{578964563},...
- 从缺陷中学习C/C++:聊聊 C++ 中常见的内存问题
-
在写C/C++程序时,一提到内存,大多数人会想到内存泄露。内存泄露是一个令人头疼的问题,尤其在开发大的软件系统时。一个经典的现象是,系统运行了10天、1个月都好好的,忽然有一天宕机了:OOM(Out...
- C++开发者都应该使用的十个C++11特性(上)
-
在C++11新标准中,语言本身和标准库都增加了很多新内容,本文只涉及了一些皮毛。不过我相信这些新特性当中有一些,应该成为所有C++开发者的常规装备。你也许看到过许多类似介绍各种C++11特性的文章。下...
- 深度解读C/C++指针与数组(c++指针和数组的区别)
-
指针和数组是密切相关的。事实上,指针和数组在很多情况下是可以互换的。例如,一个指向数组开头的指针,可以通过使用指针的算术运算或数组索引来访问数组。今天我们就来聊一聊数组和指针千丝万缕的关系;一维数组与...
- 一周热门
-
-
C# 13 和 .NET 9 全知道 :13 使用 ASP.NET Core 构建网站 (1)
-
因果推断Matching方式实现代码 因果推断模型
-
git pull命令使用实例 git pull--rebase
-
面试官:git pull是哪两个指令的组合?
-
git 执行pull错误如何撤销 git pull fail
-
git fetch 和git pull 的异同 git中fetch和pull的区别
-
git pull 和git fetch 命令分别有什么作用?二者有什么区别?
-
git pull 之后本地代码被覆盖 解决方案
-
还可以这样玩?Git基本原理及各种骚操作,涨知识了
-
git命令之pull git.pull
-
- 最近发表
- 标签列表
-
- git pull (33)
- git fetch (35)
- mysql insert (35)
- mysql distinct (37)
- concat_ws (36)
- java continue (36)
- jenkins官网 (37)
- mysql 子查询 (37)
- python元组 (33)
- mybatis 分页 (35)
- vba split (37)
- redis watch (34)
- python list sort (37)
- nvarchar2 (34)
- mysql not null (36)
- hmset (35)
- python telnet (35)
- python readlines() 方法 (36)
- munmap (35)
- docker network create (35)
- redis 集合 (37)
- python sftp (37)
- setpriority (34)
- c语言 switch (34)
- git commit (34)