百度360必应搜狗淘宝本站头条
当前位置:网站首页 > IT技术 > 正文

什么,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++指针和数组的区别)

指针和数组是密切相关的。事实上,指针和数组在很多情况下是可以互换的。例如,一个指向数组开头的指针,可以通过使用指针的算术运算或数组索引来访问数组。今天我们就来聊一聊数组和指针千丝万缕的关系;一维数组与...