MySQL子查询优化与改写为JOIN的最佳实践

一、啥是 MySQL 子查询和 JOIN

在 MySQL 里，子查询和 JOIN 都是用来从数据库里取数据的方法。子查询就是在一个 SQL 语句里再嵌套另一个 SQL 语句。比如说，咱们有两个表，一个是 orders 表，存着订单信息；另一个是 customers 表，存着客户信息。要是我们想找出那些下过订单的客户的名字，就可以用子查询。

示例（MySQL 技术栈）

-- 从 customers 表中选择客户名字
SELECT name 
FROM customers 
WHERE customer_id IN (
    -- 子查询，从 orders 表中选择所有订单的客户 ID
    SELECT customer_id 
    FROM orders
);

在这个例子里，括号里的 SELECT customer_id FROM orders 就是子查询，它会先执行，找出所有下过订单的客户 ID，然后外面的查询再根据这些 ID 从 customers 表中找出对应的客户名字。

JOIN 呢，就是把多个表连接起来，根据表之间的关系把数据整合到一起。还是上面那两个表，用 JOIN 来实现同样的功能可以这样写：

示例（MySQL 技术栈）

-- 使用 INNER JOIN 连接 customers 表和 orders 表
SELECT customers.name 
FROM customers
INNER JOIN orders 
ON customers.customer_id = orders.customer_id;

这里的 INNER JOIN 会把 customers 表和 orders 表根据 customer_id 这个字段连接起来，然后选出 customers 表中的 name 字段。

二、应用场景

子查询的应用场景

子查询适合在需要先筛选一部分数据，再根据这部分数据去查询其他信息的时候用。比如说，我们想找出那些订单金额大于平均订单金额的客户。就可以先算出平均订单金额，再用这个结果去筛选客户。

示例（MySQL 技术栈）

-- 从 customers 表中选择客户名字
SELECT name 
FROM customers 
WHERE customer_id IN (
    -- 子查询，从 orders 表中选择订单金额大于平均订单金额的客户 ID
    SELECT customer_id 
    FROM orders 
    WHERE order_amount > (
        -- 子查询，计算所有订单的平均金额
        SELECT AVG(order_amount) 
        FROM orders
    )
);

在这个例子里，最里面的子查询先算出所有订单的平均金额，中间的子查询根据这个平均金额筛选出订单金额大于它的客户 ID，最外面的查询再根据这些 ID 从 customers 表中找出对应的客户名字。

JOIN 的应用场景

JOIN 适合在需要把多个表的数据整合到一起的时候用。比如说，我们想找出每个客户的订单数量和总订单金额。就可以把 customers 表和 orders 表连接起来，然后进行分组统计。

示例（MySQL 技术栈）

-- 使用 LEFT JOIN 连接 customers 表和 orders 表
SELECT customers.name, COUNT(orders.order_id) AS order_count, SUM(orders.order_amount) AS total_amount
FROM customers
LEFT JOIN orders 
ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id;

这里的 LEFT JOIN 会把 customers 表和 orders 表根据 customer_id 这个字段连接起来，然后用 GROUP BY 对客户进行分组，最后用 COUNT 和 SUM 函数统计每个客户的订单数量和总订单金额。

三、子查询和 JOIN 的优缺点

子查询的优缺点

优点

• 逻辑清晰：子查询可以把复杂的查询拆分成多个小查询，每个小查询的逻辑都比较简单，容易理解。
• 灵活性高：子查询可以嵌套多层，根据不同的需求进行灵活组合。

缺点

• 性能较差：子查询通常会执行多次，尤其是嵌套子查询，会导致性能下降。
• 可读性差：嵌套子查询的代码会比较复杂，阅读和维护起来比较困难。

JOIN 的优缺点

优点

• 性能好：JOIN 只需要执行一次，效率比子查询高。
• 可读性高：JOIN 的代码比较简洁，容易理解和维护。

缺点

• 逻辑复杂：当表之间的关系比较复杂时，JOIN 的条件可能会比较难写。
• 灵活性低：JOIN 只能根据表之间的关系进行连接，不能像子查询那样灵活组合。

四、子查询优化与改写为 JOIN 的方法

优化子查询

减少子查询的嵌套层数

尽量把嵌套子查询拆分成多个简单的查询，避免多层嵌套。比如说，上面找出订单金额大于平均订单金额的客户的例子，可以拆分成两个查询。

示例（MySQL 技术栈）

-- 先计算所有订单的平均金额
SELECT AVG(order_amount) INTO @avg_amount 
FROM orders;

-- 再根据平均金额筛选出订单金额大于它的客户 ID
SELECT name 
FROM customers 
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    WHERE order_amount > @avg_amount
);

这里先把平均订单金额计算出来，存到一个变量 @avg_amount 里，然后再用这个变量去筛选客户，这样就减少了子查询的嵌套层数。

使用 EXISTS 代替 IN

当子查询使用 IN 时，MySQL 会先执行子查询，然后把结果存到一个临时表中，再用这个临时表去匹配主查询。而使用 EXISTS 时，MySQL 会直接检查子查询是否有结果，不需要创建临时表，性能会更好。

示例（MySQL 技术栈）

-- 使用 EXISTS 代替 IN
SELECT name 
FROM customers 
WHERE EXISTS (
    SELECT 1 
    FROM orders 
    WHERE orders.customer_id = customers.customer_id
);

这里用 EXISTS 代替了 IN，当 orders 表中存在与 customers 表中客户 ID 相同的记录时，就会返回结果。

改写子查询为 JOIN

简单子查询改写为 JOIN

对于一些简单的子查询，可以直接改写成 JOIN。比如说，上面用子查询找出下过订单的客户的名字的例子，可以改写成 JOIN。

示例（MySQL 技术栈）

-- 改写子查询为 JOIN
SELECT customers.name 
FROM customers
INNER JOIN orders 
ON customers.customer_id = orders.customer_id;

这里把原来的子查询改写成了 INNER JOIN，性能会更好。

复杂子查询改写为 JOIN

对于一些复杂的子查询，可能需要先对数据进行预处理，再用 JOIN 进行连接。比如说，上面找出订单金额大于平均订单金额的客户的例子，可以先计算出平均订单金额，然后用 JOIN 进行连接。

示例（MySQL 技术栈）

-- 先计算所有订单的平均金额
SELECT AVG(order_amount) INTO @avg_amount 
FROM orders;

-- 用 JOIN 连接 customers 表和 orders 表，并筛选出订单金额大于平均订单金额的客户
SELECT customers.name 
FROM customers
JOIN orders 
ON customers.customer_id = orders.customer_id
WHERE orders.order_amount > @avg_amount;

这里先把平均订单金额计算出来，存到一个变量 @avg_amount 里，然后用 JOIN 把 customers 表和 orders 表连接起来，再根据平均订单金额筛选出符合条件的客户。

五、注意事项

索引的使用

无论是子查询还是 JOIN，都要注意索引的使用。在表的连接字段和筛选条件字段上创建索引，可以提高查询的性能。比如说，在 customers 表的 customer_id 字段和 orders 表的 customer_id 字段上创建索引。

示例（MySQL 技术栈）

-- 在 customers 表的 customer_id 字段上创建索引
CREATE INDEX idx_customer_id ON customers (customer_id);

-- 在 orders 表的 customer_id 字段上创建索引
CREATE INDEX idx_customer_id_orders ON orders (customer_id);

数据类型的匹配

在使用 JOIN 时，要确保连接字段的数据类型一致。如果数据类型不一致，MySQL 会进行类型转换，这会影响查询的性能。比如说，customers 表的 customer_id 字段是 INT 类型，orders 表的 customer_id 字段也应该是 INT 类型。

避免笛卡尔积

在使用 JOIN 时，要避免出现笛卡尔积。笛卡尔积是指两个表连接时，每个表的每一行都与另一个表的每一行进行组合，会产生大量的数据。比如说，两个表都有 100 行数据，笛卡尔积会产生 10000 行数据。为了避免笛卡尔积，要确保连接条件正确。

六、文章总结

在 MySQL 中，子查询和 JOIN 都是很重要的查询方法。子查询逻辑清晰、灵活性高，但性能较差、可读性低；JOIN 性能好、可读性高，但逻辑复杂、灵活性低。在实际应用中，我们可以根据具体的需求选择合适的方法。对于一些复杂的子查询，可以通过优化和改写为 JOIN 来提高查询的性能。同时，要注意索引的使用、数据类型的匹配和避免笛卡尔积等问题，这样才能让查询更加高效。