MySQL高阶讲座之：`MySQL`的`Correlated Subquery`：其性能陷阱与`JOIN`重写。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

OK，各位亲爱的码农朋友们，大家好！今天咱们来聊聊MySQL里一个让人又爱又恨的小妖精——关联子查询（Correlated Subquery）。说它小妖精，是因为它看起来挺简单，用起来也挺方便，但是一不小心，就会让你掉进性能的大坑里。

咱们今天就来扒一扒它的底裤，看看它到底有什么能耐，又有什么弱点，以及，怎么才能驯服它，让它乖乖地为我们服务。

第一幕：关联子查询是个啥？

简单来说，关联子查询就是指在子查询中引用了外部查询的列。这就像是两个人聊天，一个人说：“我喜欢那个…（停顿）… 你昨天说的那个！” “你昨天说的那个” 就是引用了外部的信息，必须结合上下文才能知道具体指什么。

举个栗子：

假设我们有两个表：Customers（客户表）和 Orders（订单表）。

Customers 表结构：

Column Name	Data Type
customer_id	INT
name	VARCHAR
city	VARCHAR

Orders 表结构：

Column Name	Data Type
order_id	INT
customer_id	INT
order_date	DATE
amount	DECIMAL

现在，我们要找出所有订单金额高于该客户平均订单金额的客户信息。用关联子查询可以这么写：

SELECT c.customer_id, c.name, c.city
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
    AND o.amount > (
        SELECT AVG(amount)
        FROM Orders
        WHERE customer_id = c.customer_id
    )
);

这段代码的意思是：

外层查询遍历 Customers 表的每一行。
对于 Customers 表的每一行（也就是每个客户），内层子查询计算该客户的平均订单金额。
再内层的 EXISTS 子查询用来判断是否存在该客户的订单金额大于平均订单金额。如果存在，则返回该客户的信息。

看到了吗？内层的子查询 WHERE customer_id = c.customer_id 引用了外层查询的 c.customer_id，这就是关联子查询。

第二幕：关联子查询的性能陷阱

关联子查询虽然写起来直观，但它的性能往往让人头疼。原因就在于，它通常需要对外部查询的每一行执行一次子查询。这就像你每和一个妹子聊天，都要先问一遍她的基本信息。如果妹子不多还好，如果妹子成千上万… 你就等着累死吧！

具体来说，关联子查询的执行计划通常是这样的：

外层查询驱动。
对于外层查询的每一行，都要执行一次内层子查询。

这种执行方式被称为 "row-by-row processing"，也就是一行一行地处理。如果外层查询的数据量很大，那么子查询的执行次数就会非常多，导致性能急剧下降。

更糟糕的是，MySQL 的优化器有时候并不会对关联子查询进行很好的优化。它可能会选择一种非常低效的执行方式，比如全表扫描。

第三幕：如何避免性能陷阱？—— JOIN 重写

既然关联子查询性能不好，那我们该怎么办呢？答案是：尽量用 JOIN 来重写关联子查询。JOIN 可以把多个表连接在一起，然后一次性地处理所有数据，避免了 "row-by-row processing"。

咱们还是用上面的例子，用 JOIN 重写一下：

SELECT c.customer_id, c.name, c.city
FROM Customers c
JOIN (
    SELECT customer_id, AVG(amount) AS avg_amount
    FROM Orders
    GROUP BY customer_id
) AS avg_orders ON c.customer_id = avg_orders.customer_id
JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.amount > avg_orders.avg_amount;

这段代码的意思是：

首先，计算每个客户的平均订单金额，并将结果存储在一个临时表 avg_orders 中。
然后，将 Customers 表和 avg_orders 表连接起来，找到每个客户的平均订单金额。
最后，将 Customers 表、avg_orders 表和 Orders 表连接起来，筛选出订单金额大于平均订单金额的客户信息。

通过 JOIN 重写，我们将关联子查询转化成了一个连接查询。MySQL 的优化器通常能够对连接查询进行更好的优化，比如使用索引、选择合适的连接顺序等，从而提高查询性能。

第四幕：一些更复杂的例子和注意事项

EXISTS 型关联子查询的 JOIN 重写：

假设我们要找出所有下过订单的客户信息。用关联子查询可以这么写：

SELECT c.customer_id, c.name, c.city
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

用 JOIN 重写：

SELECT DISTINCT c.customer_id, c.name, c.city
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id;

这里使用了 DISTINCT 来去重，因为一个客户可能下过多个订单。

NOT EXISTS 型关联子查询的 JOIN 重写：

假设我们要找出所有没有下过订单的客户信息。用关联子查询可以这么写：

SELECT c.customer_id, c.name, c.city
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

用 LEFT JOIN 和 WHERE IS NULL 重写：

SELECT c.customer_id, c.name, c.city
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

这里使用了 LEFT JOIN，它会返回左表（Customers）的所有行，以及右表（Orders）中与左表匹配的行。如果右表中没有与左表匹配的行，则右表的列的值为 NULL。通过 WHERE o.customer_id IS NULL，我们可以筛选出所有没有下过订单的客户。

IN 型关联子查询的 JOIN 重写：

假设我们要找出所有来自 "New York" 或 "Los Angeles" 的客户的订单信息。用关联子查询可以这么写：

SELECT o.order_id, o.customer_id, o.order_date, o.amount
FROM Orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM Customers c
    WHERE c.city IN ('New York', 'Los Angeles')
);

用 JOIN 重写：

SELECT o.order_id, o.customer_id, o.order_date, o.amount
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.city IN ('New York', 'Los Angeles');

一些注意事项：

并非所有关联子查询都可以直接用 JOIN 重写。 有些复杂的关联子查询可能需要更复杂的 JOIN 结构，或者需要使用临时表、窗口函数等技巧。
JOIN 重写并不总是能提高性能。 在某些情况下，如果 JOIN 的条件非常复杂，或者表的数据量非常小，那么 JOIN 的性能可能还不如关联子查询。因此，我们需要根据实际情况进行测试和评估。
关注执行计划。 使用 EXPLAIN 命令可以查看 MySQL 的执行计划。通过分析执行计划，我们可以了解 MySQL 如何执行查询，并找出性能瓶颈。
索引是关键。 合适的索引可以大大提高查询性能。在 JOIN 查询中，确保连接列上有索引。

第五幕：实战演练

咱们来做一个稍微复杂一点的例子。假设我们有一个 Products 表（产品表）和一个 Sales 表（销售表）。

Products 表结构：

Column Name	Data Type
product_id	INT
name	VARCHAR
category	VARCHAR
price	DECIMAL

Sales 表结构：

Column Name	Data Type
sale_id	INT
product_id	INT
sale_date	DATE
quantity	INT

现在，我们要找出每个类别中，销量最高的产品的名称和销量。

用关联子查询可以这么写：

SELECT p.category, p.name, SUM(s.quantity) AS total_quantity
FROM Products p
JOIN Sales s ON p.product_id = s.product_id
GROUP BY p.category, p.name
HAVING SUM(s.quantity) = (
    SELECT MAX(total_quantity)
    FROM (
        SELECT SUM(quantity) AS total_quantity, product_id
        FROM Sales
        JOIN Products ON Sales.product_id = Products.product_id
        WHERE Products.category = p.category
        GROUP BY product_id
    ) AS subquery
);

这个查询有点复杂，我们来分解一下：

最外层查询计算每个类别中每个产品的总销量。
内层子查询计算每个类别中每个产品的总销量，并找到该类别中销量最高的产品的总销量。
HAVING 子句用来筛选出销量等于该类别中最高销量的产品。

这个查询的性能会比较差，因为它使用了关联子查询，并且子查询中还使用了 GROUP BY。

用 JOIN 和窗口函数重写：

WITH CategorySales AS (
    SELECT
        p.category,
        p.name,
        SUM(s.quantity) AS total_quantity,
        ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(s.quantity) DESC) AS rn
    FROM Products p
    JOIN Sales s ON p.product_id = s.product_id
    GROUP BY p.category, p.name
)
SELECT category, name, total_quantity
FROM CategorySales
WHERE rn = 1;

这段代码的意思是：

使用 WITH 子句定义一个名为 CategorySales 的公共表表达式 (CTE)。
CategorySales CTE 计算每个类别中每个产品的总销量，并使用 ROW_NUMBER() 窗口函数为每个类别中的产品按照销量降序排序，并分配一个行号 rn。
最后，从 CategorySales CTE 中筛选出 rn = 1 的行，也就是每个类别中销量最高的产品的名称和销量。

这个查询使用了窗口函数，它可以避免关联子查询，并且能够更有效地计算每个类别中销量最高的产品的总销量。

第六幕：总结

关联子查询是一个功能强大的工具，但它的性能往往让人头疼。为了避免性能陷阱，我们应该尽量用 JOIN 来重写关联子查询。在重写过程中，需要仔细分析查询的逻辑，并选择合适的 JOIN 类型和条件。此外，我们还需要关注执行计划，并使用索引来提高查询性能。

记住，没有银弹！选择哪种方式，最终还是要看实际情况，多测试，多分析，才能找到最佳方案。

好了，今天的讲座就到这里。希望大家以后在使用关联子查询的时候，能够更加谨慎，避免掉进性能的大坑里。祝大家编码愉快！

发表回复 取消回复

发表回复取消回复