子查询与join

为什么开发同学这么喜欢写子查询？

我工作这么多年，发现相当多的开发同学喜欢写子查询，而不是传统的 JOIN 语句。举一个简单的例子，如果让开发同学“找出1993年，没有下过订单的客户数量”，大部分同学会用子查询来写这个需求，比如：

SELECT
    COUNT(c_custkey) cnt
FROM
    customer
WHERE
    c_custkey NOT IN (
        SELECT
            o_custkey
        FROM
            orders
        WHERE
            o_orderdate >=  '1993-01-01'
            AND o_orderdate <  '1994-01-01'
	);

从中可以看到，子查询的逻辑非常清晰：通过 NOT IN 查询不在订单表的用户有哪些。

不过上述查询是一个典型的 LEFT JOIN 问题（即在表 customer 存在，在表 orders 不存在的问题）。所以，这个问题如果用 LEFT JOIN 写，那么 SQL 如下

SELECT
    COUNT(c_custkey) cnt
FROM
    customer
        LEFT JOIN
    orders ON
            customer.c_custkey = orders.o_custkey
            AND o_orderdate >= '1993-01-01'
            AND o_orderdate < '1994-01-01'
WHERE
    o_custkey IS NULL;

可以发现，虽然 LEFT JOIN 也能完成上述需求，但不容易理解，因为 LEFT JOIN 是一个代数关系，而子查询更偏向于人类的思维角度进行理解。

所以，大部分人都更倾向写子查询，即便是天天与数据库打交道的 DBA 。

不过从优化器的角度看，LEFT JOIN 更易于理解，能进行传统 JOIN 的两表连接，而子查询则要求优化器聪明地将其转换为最优的 JOIN 连接。

我们来看一下，在 MySQL 8.0 版本中，对于上述两条 SQL，最终的执行计划都是：

可以看到，不论是子查询还是 LEFT JOIN，最终都被转换成了 Nested Loop Join，所以上述两条 SQL 的执行时间是一样的。

即，在 MySQL 8.0 中，优化器会自动地将 IN 子查询优化，优化为最佳的 JOIN 执行计划，这样一来，会显著的提升性能。

子查询 IN 和 EXISTS，哪个性能更好？

除了“为什么开发同学都喜欢写子查询”，关于子查询，另一个经常被问到的问题是：“ IN 和EXISTS 哪个性能更好？”要回答这个问题，我们看一个例子。

针对开篇的 NOT IN 子查询，你可以改写为 NOT EXISTS 子查询，重写后的 SQL 如下所示

SELECT
    COUNT(c_custkey) cnt
FROM
    customer
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            orders
        WHERE
            o_orderdate >=  '1993-01-01'
            AND o_orderdate <  '1994-01-01'
            AND c_custkey = o_custkey
    );

你要注意，千万不要盲目地相信网上的一些文章，有的说 IN 的性能更好，有的说 EXISTS 的子查询性能更好。你只关注 SQL 执行计划就可以，如果两者的执行计划一样，性能没有任何差别。

接着说回来，对于上述 NOT EXISTS，它的执行计划如下图所示：

你可以看到，它和 NOT IN 的子查询执行计划一模一样，所以二者的性能也是一样的。讲完子查询的执行计划之后，接下来我们来看一下一种需要对子查询进行优化的 SQL：依赖子查询。

总结

• 子查询相比 JOIN 更易于人类理解，所以受众更广，使用更多；

• 当前 MySQL 8.0 版本可以“毫无顾忌”地写子查询，对于子查询的优化已经相当完备；