使用外连接优化子查询

初始表结构#

CREATE TABLE t1(
    a INT UNIQUE
);

CREATE TABLE t2(
    a INT UNIQUE
);

INSERT INTO t1 SELECT 1;
INSERT INTO t1 SELECT 2;
INSERT INTO t1 SELECT 3;

INSERT INTO t2 SELECT 2;
INSERT INTO t2 SELECT 3;

DEPENDENT SUBQUERY#

下面这条子查询的意思是，对于t1中的每一行R1，若t2表中存在与它的a列相等的行，则R1出现在结果集中：

SELECT t1.* FROM t1
WHERE EXISTS (
    SELECT * FROM t2
    WHERE t1.a=t2.a
);

在5.5版本中使用EXPLAIN解释这条语句：

第一个是对索引的一个扫描，第二个的select_type为DEPENDENT SUBQUERY。

DEPENDENT SUBQUERY即关联子查询，意思是它的执行用到了外层表中的某些字段，对于关联子查询中依赖到的外层关系列，若外层有多个行在这些列上取相同的值，那么对于这些行，内层子查询只需要查一次（比如外层有多个行的a取值为3，对于这些行，只需要复用之前查过的子查询结果即可），否则，每次都要重新执行内层子查询。

如果内层是一个较大的关系，并且内层查询没法较好的利用到索引的情况下，这种依赖子查询其实是很危险的。

我们看在MySQL8.0.29版本中这条相同的语句的执行计划

同样的一条语句，子查询被消除了，官方文档是这样说的：

Beginning with MySQL 8.0.24, the optimizer can transform a correlated scalar subquery to a derived table when the subquery_to_derived flag of the optimizer_switch variable is enabled.

从MySQL8.0.24开始，当optimizer_switch变量的subquery_to_drived标志打开时，优化器可以转换一个关联标量子查询到一个派生表。

如何优化这种情况#

先看你的需求需不需要优化，像上面的例子，数据量少不说，a列上其实是有唯一索引的，就算内层每次都要查，那也只是一个eq_ref索引等值查询，其实也没那么慢，如果缓存利用率不错或者在非机械硬盘上。

不过我们还是对上面的简单的示例进行优化，我们先看下面的查询：

SELECT t1.a, derived.a FROM t1
LEFT JOIN (
    SELECT a FROM t2
) as derived USING(a); 

这个查询以t1为标准使用列a执行左连接，那么在t2中，若存在t1中没有的a，那么结果中它的a列会是NULL

我们恰好可以通过这个NULL来过滤掉这个行，最终的SQL就是：

SELECT t1.a FROM t1
LEFT JOIN (
    SELECT a FROM t2
) as derived USING(a)
WHERE derived.a IS NOT NULL; 

请一定要注意，在内层查询的表非常大并且内层查询可以用到索引的情况下，有可能出现优化了还不如不优化的情况。因为内层本来的索引查询是非常快的，你一优化变成了全表扫描。所以具体问题还得具体分析。

一个稍微复杂点的案例#

难度要上来了！下面的语句查询2009年秋天开课与2010年春天开课的差集，即2009年秋开课且2010年春没开课的课程，来自《数据库系统概念》一书：

SELECT 
    course_id
FROM section
    WHERE semester='Fall' AND year=2009
    AND course_id NOT IN (
        SELECT course_id FROM section
        WHERE semester='Spring' AND year=2010
    );

虽然子查询并没用到外部的值，但EXPLAIN的结果显示子查询又是关联子查询，具体外层的每一行查询一次还是咋的咱也不知道。并且这次，子查询中用到的列实际上是主索引中的非最左前缀列，也就是说内层还是相当于全表扫描啊。

section表的表描述信息，可以看到(course_id, sec_id, semester, year)是复合主索引。

我们的数据太少，看不出实际的执行计划中内层是否是每次外层执行都需要跟着执行一次，不过我们是为了演示把子查询转成外连接，所以直接干它。

还是一样的套路，我们先写出个左外连接的模板，现在结果集中D.course_id为NULL的都是非2010年春季的课程：

SELECT O.*, D.* FROM section O
LEFT JOIN (
    SELECT course_id FROM section
    WHERE semester='Spring' AND year=2010
) AS D USING (course_id);

我们的需求是找到2009年秋开课但2010年春没开课的课程，2009年秋开课的条件是semester='Fall' AND year=2009，而结果集中只要D.course_id!=NULL就代表这门课在2010年春开课了，所以最终的语句就是：

SELECT O.course_id FROM section O
LEFT JOIN (
    SELECT course_id FROM section
    WHERE semester='Spring' AND year=2010
) AS D USING (course_id)
WHERE semester='Fall' AND year=2009 AND D.course_id is NULL;