44-一些常见问题

2020-03-09

字数统计: 2.1k字 | 阅读时长: 7分

× 文章目录

1. 1. join的用法
2. 2. Simple Nested Loop Join 的性能问题

学习来源：极客时间-MySQL实战45讲，本人购买课程后依据图文讲解汇总成个人见解。

1. join的用法

在 第35篇文章《join语句怎么优化？》 中，在介绍join执行顺序的时候，用的都是straight_join。有人在文后提出了两个问题：

如果用left join的话，左边的表一定是驱动表吗？
如果两个表的join包含多个条件的等值匹配，是都要写到on里面呢，还是只把一个条件写到on里面，其他条件写到where部分？

为了同时回答这两个问题，我来构造两个表a和b：

create table a(f1 int, f2 int, index(f1))engine=innodb; 
create table b(f1 int, f2 int)engine=innodb; 
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

表a和b都有两个字段f1和f2，不同的是表a的字段f1上有索引。然后，我往两个表中都插入了6条记录，其中在表a和b中同时存在的数据有4行。

第二个问题，其实就是下面这两种写法的区别：

1 2	select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /Q1/ select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/Q2/

把这两条语句分别记为Q1和Q2。

首先，需要说明的是，这两个left join语句的语义逻辑并不相同。我们先来看一下它们的执行结果。

可以看到：

语句Q1返回的数据集是6行，表a中即使没有满足匹配条件的记录，查询结果中也会返回一行，并将表b的各个字段值填成NULL。
语句Q2返回的是4行。从逻辑上可以这么理解，最后的两行，由于表b中没有匹配的字段，结果集里面b.f2的值是空，不满足where 部分的条件判断，因此不能作为结果集的一部分。

接下来，我们看看实际执行这两条语句时，MySQL是怎么做的。

我们先一起看看语句Q1的explain结果：

可以看到，这个结果符合我们的预期：

驱动表是表a，被驱动表是表b；
由于表b的f1字段上没有索引，所以使用的是Block Nexted Loop Join（简称BNL）算法。

看到BNL算法，你就应该知道这条语句的执行流程其实是这样的：

把表a的内容读入join_buffer 中。因为是select * ，所以字段f1和f2都被放入join_buffer了。
顺序扫描表b，对于每一行数据，判断join条件（也就是a.f1=b.f1 and a.f2=b.f2)是否满足，满足条件的记录, 作为结果集的一行返回。如果语句中有where子句，需要先判断where部分满足条件后，再返回。
表b扫描完成后，对于没有被匹配的表a的行（在这个例子中就是(1,1)、(2,2)这两行），把剩余字段补上NULL，再放入结果集中。

对应的流程图如下：

可以看到，这条语句确实是以表a为驱动表，而且从执行效果看，也和使用straight_join是一样的。

你可能会想，语句Q2的查询结果里面少了最后两行数据，是不是就是把上面流程中的步骤3去掉呢？我们还是先看一下语句Q2的expain结果吧。

可以看到，这条语句是以表b为驱动表的。而如果一条join语句的Extra字段什么都没写的话，就表示使用的是Index Nested-Loop Join（简称NLJ）算法。

因此，语句Q2的执行流程是这样的：顺序扫描表b，每一行用b.f1到表a中去查，匹配到记录后判断a.f2=b.f2是否满足，满足条件的话就作为结果集的一部分返回。

那么，为什么语句Q1和Q2这两个查询的执行流程会差距这么大呢？其实，这是因为优化器基于Q2这个查询的语义做了优化。

为了理解这个问题，我需要再和你交代一个背景知识点：在MySQL里，NULL跟任何值执行等值判断和不等值判断的结果，都是NULL。这里包括， select NULL = NULL 的结果，也是返回NULL。

因此，语句Q2里面where a.f2=b.f2就表示，查询结果里面不会包含b.f2是NULL的行，这样这个left join的语义就是“找到这两个表里面，f1、f2对应相同的行。对于表a中存在，而表b中匹配不到的行，就放弃”。

这样，这条语句虽然用的是left join，但是语义跟join是一致的。

因此，优化器就把这条语句的left join改写成了join，然后因为表a的f1上有索引，就把表b作为驱动表，这样就可以用上NLJ 算法。在执行explain之后，你再执行show warnings，就能看到这个改写的结果，如图5所示。

这个例子说明，即使我们在SQL语句中写成left join，执行过程还是有可能不是从左到右连接的。也就是说，使用left join时，左边的表不一定是驱动表。

这样看来，如果需要left join的语义，就不能把被驱动表的字段放在where条件里面做等值判断或不等值判断，必须都写在on里面。那如果是join语句呢？

这时候，我们再看看这两条语句：

1 2	select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /Q3/ select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/Q4/

我们再使用一次看explain 和 show warnings的方法，看看优化器是怎么做的。

可以看到，这两条语句都被改写成：

1	select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

执行计划自然也是一模一样的。

也就是说，在这种情况下，join将判断条件是否全部放在on部分就没有区别了。

2. Simple Nested Loop Join 的性能问题

我们知道，join语句使用不同的算法，对语句的性能影响会很大。

虽然BNL算法和Simple Nested Loop Join 算法都是要判断M*N次（M和N分别是join的两个表的行数），但是Simple Nested Loop Join 算法的每轮判断都要走全表扫描，因此性能上BNL算法执行起来会快很多。

为了便于说明，我还是先为你简单描述一下这两个算法。

BNL算法的执行逻辑是：

首先，将驱动表的数据全部读入内存join_buffer中，这里join_buffer是无序数组；
然后，顺序遍历被驱动表的所有行，每一行数据都跟join_buffer中的数据进行匹配，匹配成功则作为结果集的一部分返回。

Simple Nested Loop Join算法的执行逻辑是：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。

有人提出问题，Simple Nested Loop Join算法，其实也是把数据读到内存里，然后按照匹配条件进行判断，为什么性能差距会这么大呢？

解释这个问题，需要用到MySQL中索引结构和Buffer Pool的相关知识点：

在对被驱动表做全表扫描的时候，如果数据没有在Buffer Pool中，就需要等待这部分数据从磁盘读入；
从磁盘读入数据到内存中，会影响正常业务的Buffer Pool命中率，而且这个算法天然会对被驱动表的数据做多次访问，更容易将这些数据页放到Buffer Pool的头部（请参考 第35篇文章 中的相关内容)；
即使被驱动表数据都在内存中，每次查找“下一个记录的操作”，都是类似指针操作。而join_buffer中是数组，遍历的成本更低。

所以说，BNL算法的性能会更好。