什么,LEFT JOIN 会变成 JOIN?
wptr33 2024-11-21 22:05 19 浏览
前言
在日常开发中,对于 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 语句。
相关推荐
- SpringBoot 3 + Flutter3 实战低代码运营管理-10章
-
获课》aixuetang.xyz/5075/三天构建运营管理系统:SpringBoot3+Flutter3高效开发方法论...
- SpringBoot探针实现:从零构建应用健康监控利器
-
SpringBoot探针实现:从零构建应用健康监控利器声明本文中的所有案例代码、配置仅供参考,如需使用请严格做好相关测试及评估,对于因参照本文内容进行操作而导致的任何直接或间接损失,作者概不负责。本文...
- Spring Batch中的JobRepository:批处理的“记忆大师”是如何工作
-
一、JobRepository是谁?——批处理的“档案馆”JobRepository是SpringBatch的“记忆中枢”,负责记录所有Job和Step的执行状态。它像一位严谨的档案管理员,把任务执...
- 还在为 Spring Boot3 技术整合发愁?一文解锁大厂都在用的实用方案
-
你在使用SpringBoot3开发后端项目时,是不是常常陷入这样的困境?想提升项目性能和功能,却不知道该整合哪些技术;好不容易选定技术,又在配置和使用上频频踩坑。其实,这是很多互联网大厂后端开发...
- 一文吃透!Spring Boot 项目请求日志记录,这几招你绝对不能错过!
-
在互联网应用开发的高速赛道上,系统的稳定性、可维护性以及安全性是每一位开发者都必须关注的核心要素。而请求日志记录,就如同系统的“黑匣子”,能够为我们提供排查故障、分析用户行为、优化系统性能等关键信息...
- spring-boot-starter-actuator简单介绍
-
SpringBootActuator是SpringBoot的一个功能强大的子项目,它提供了一些有用的监控和管理SpringBoot应用程序的端点。SpringBootActuat...
- 使用SpringBoot钩子或Actuator实现优雅停机
-
服务如何响应停机信号在java中我们可以直接利用通过Runtime...
- 28-自定义Spring Boot Actuator指标
-
上篇我们学习了《27-自定义SpringBootActuator健康指示器》,本篇我们学习自定义SpringBootActuator指标(Metric)。...
- 如何在Spring Boot中整合Spring Boot Actuator进行服务应用监控?
-
监控是确保系统稳定性和性能的关键组成部分,而在SpringBoot中就提供了默认的应用监控方案SpringBootActuator,通过SpringBootActuator提供了开箱即用的应...
- 「Spring Boot」 Actuator Endpoint
-
Actuator官网地址:https://docs.spring.io/spring-boot/docs/2.5.6/reference/html/actuator.html目的监控并管理应用程序...
- Spring Boot Actuator监控功能全面剖析
-
SpringBootActuator监控功能全面剖析在现代企业级Java开发中,SpringBoot以其轻量化、高效率的特性深受开发者青睐。而作为SpringBoot生态系统的重要组成部分,S...
- 1000字彻底搞懂SpringBootActuator组件!
-
SpringBootActuator组件SpringBootActuator通过HTTPendpoints或者JMX来管理和监控SpringBoot应用,如服务的审计、健康检查、指标统计和...
- JavaScript数据类型(javascript数据类型介绍)
-
基本数据类型BooleanNullNumberStringSymbolUndefined对象数据类型ObjectArray定义:JavaScript数组是内置的对象之一,它可以用一个变量来存储多个同种...
- 能运行,不代表它是对的:5 个潜伏在正常功能下的 JavaScript 错误
-
JavaScript的动态性和复杂性意味着,代码虽然表面上正常运行,但一些深层次、隐蔽的陷阱往往让人意想不到,梳理了几个JavaScript开发中难以发现的隐蔽错误,旨在帮助我们写出更健壮、更可...
- 一周热门
-
-
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
-
- 最近发表
-
- SpringBoot 3 + Flutter3 实战低代码运营管理-10章
- SpringBoot探针实现:从零构建应用健康监控利器
- Spring Batch中的JobRepository:批处理的“记忆大师”是如何工作
- Github霸榜的SpringBoot全套学习教程,从入门到实战,内容超详细
- 还在为 Spring Boot3 技术整合发愁?一文解锁大厂都在用的实用方案
- 一文吃透!Spring Boot 项目请求日志记录,这几招你绝对不能错过!
- spring-boot-starter-actuator简单介绍
- 使用SpringBoot钩子或Actuator实现优雅停机
- 28-自定义Spring Boot Actuator指标
- 如何在Spring Boot中整合Spring Boot Actuator进行服务应用监控?
- 标签列表
-
- 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)