网站首页 > 技术文章 正文
前言
在日常开发中，对于 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 语句。
猜你喜欢
- 2024-11-16 从阿里手册引出的Join查询思考（阿里技术手册）
- 2024-11-16 Python中join()方法——字符串、路径拼接的常用方法
- 2024-11-16 oracle 多表连接查询 join(一)（oracle多表联查效率优化）
- 2024-11-16 让程序员头疼的微服务下数据聚合join(一)
- 2024-11-16 神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程你是否知道?
- 2024-11-16 MySQL的Join到底能不能用，一文搞懂它
- 2024-11-16 一文看懂Mycat跨分片Join实现--全局表、ER分片、HBT、sharejoin
- 2024-11-16 为什么大厂不建议使用多表join?（为什么要多表查询）
- 2024-11-16 SQL Server 2012 高级用法(三)之Join
- 2024-11-16 LINUX中JOIN用法（linux jour）
- 标签列表
-
- content-disposition (47)
- nth-child (56)
- math.pow (44)
- 原型和原型链 (63)
- canvas mdn (36)
- css @media (49)
- promise mdn (39)
- readasdataurl (52)
- if-modified-since (49)
- css ::after (50)
- border-image-slice (40)
- flex mdn (37)
- .join (41)
- function.apply (60)
- input type number (64)
- weakmap (62)
- js arguments (45)
- js delete方法 (61)
- blob type (44)
- math.max.apply (51)
- js (44)
- firefox 3 (47)
- cssbox-sizing (52)
- js删除 (49)
- js for continue (56)
- 最新留言
-