Mysql 子查询

MySQL 的子查询（Subquery）是指 嵌套在其他 SQL 查询中的查询语句，它允许在一个查询中使用另一个查询的结果作为条件或数据源。子查询可以出现在 SELECT、FROM、WHERE、HAVING 甚至 INSERT/UPDATE/DELETE 语句中。以下是子查询的详细分类、使用场景和优化建议。

一、子查询的分类

1. 按返回结果类型

2. 按是否依赖外层查询

-- 非相关子查询
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

-- 相关子查询
SELECT name FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

二、子查询的使用场景

1. 在 WHERE 子句中过滤数据

-- 标量子查询（查找价格高于平均价的商品）
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- IN/NOT IN 子查询（查找有订单的用户）
SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- EXISTS/NOT EXISTS 子查询（查找有订单的用户）
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

2. 在 SELECT 子句中作为计算字段

-- 为每个用户统计订单数量
SELECT 
    id, 
    name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

3. 在 FROM 子句中作为派生表（Derived Table）

-- 统计每个部门的平均工资
SELECT d.department_name, tmp.avg_salary
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY department_id
) AS tmp ON d.id = tmp.department_id;

4. 在 HAVING 子句中过滤分组

-- 筛选订单数超过 10 的用户
SELECT user_id, COUNT(*) AS order_count 
FROM orders 
GROUP BY user_id 
HAVING order_count > (SELECT AVG(order_count) FROM (
    SELECT COUNT(*) AS order_count FROM orders GROUP BY user_id
) AS tmp);

三、子查询的性能优化

1. 避免相关子查询

相关子查询对外层查询的每一行执行一次，效率低下。
优化方法：改用 JOIN 或临时表。

-- 原查询（相关子查询）
SELECT name FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 优化后（使用 JOIN）
SELECT DISTINCT u.name 
FROM users u 
JOIN orders o ON u.id = o.user_id;

2. 用 JOIN 替代 IN 子查询

IN 子查询在 MySQL 中可能被优化为 JOIN，但显式使用 JOIN 更可控。

-- 原查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 优化后
SELECT u.* 
FROM users u 
JOIN (SELECT DISTINCT user_id FROM orders) AS o ON u.id = o.user_id;

3. 使用索引加速子查询

确保子查询中的字段有索引：

-- 对 orders.user_id 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 加速子查询：SELECT user_id FROM orders

4. 限制子查询返回的数据量

避免子查询返回大量数据，使用 LIMIT 或条件过滤。

SELECT * FROM products 
WHERE price > (SELECT price FROM discounts WHERE product_id = 100 LIMIT 1);

四、子查询的限制与注意事项

1. MySQL 对子查询的优化限制

   • 旧版本（如 MySQL 5.5 之前）对子查询优化较差，建议升级到 5.7+ 或 8.0+。
   • 复杂嵌套子查询可能生成低效的执行计划。

2. 避免多层嵌套子查询
   嵌套过深会降低可读性和性能，建议拆分为多个临时表或 CTE（Common Table Expressions）。

3. 注意 NULL 值的影响
   当子查询可能返回 NULL 时，使用 IN 或 NOT IN 需谨慎：

   -- 如果子查询返回 NULL，整个条件结果为 NULL
   SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
   

五、高级用法：CTE（公共表表达式）

MySQL 8.0+ 支持 CTE，可替代部分子查询场景，提高可读性：

-- 使用 CTE 替代派生表
WITH order_counts AS (
    SELECT user_id, COUNT(*) AS cnt 
    FROM orders 
    GROUP BY user_id
)
SELECT u.name, oc.cnt 
FROM users u 
JOIN order_counts oc ON u.id = oc.user_id;

六、总结

合理使用子查询可以简化复杂逻辑，但需结合执行计划分析（EXPLAIN）和索引优化，避免性能陷阱。