MySQL中常用的子查询类型有哪些 MySQL子查询优化与实战全攻略

mysql中的子查询分为标量子查询、行子查询、列子查询、表子查询、相关子查询、非相关子查询以及exists/not exists子查询，1. 标量子查询返回单个值，可用于select、where等语句中；2. 行子查询返回单行多列，常用于行比较；3. 列子查询返回单列多行，常与in、any、all结合使用；4. 表子查询作为派生表用于from子句，需有别名；5. 相关子查询依赖外部查询的每一行，易引发“n+1”性能问题；6. 非相关子查询可独立执行，仅执行一次；7. exists/not exists用于判断子查询是否返回结果，不关注具体数据。为提升性能，应优先使用join替代子查询，善用派生表，确保索引有效，避免在select中使用相关子查询，最终通过优化手段减少执行开销，提高查询效率。

MySQL中的子查询，简单来说，就是嵌套在其他SQL语句（如SELECT、INSERT、UPDATE、DELETE）内部的查询。它们可以返回单个值、一行、一列或一个表，根据其返回结果和与外部查询的关联方式，主要分为几类：标量子查询、行子查询、列子查询以及表子查询（或称派生表）。此外，从执行方式上看，还有非相关子查询和相关子查询，以及特殊用于存在性判断的EXISTS/NOT EXISTS子查询。它们是SQL语言强大表达能力的一部分，能帮助我们处理很多复杂的数据逻辑。

解决方案

子查询在MySQL中扮演着举足轻重的角色，它允许我们构建更复杂、更具逻辑性的查询语句，以应对各种数据检索和操作需求。理解并熟练运用不同类型的子查询，是提升数据库操作效率和解决实际问题的关键。

1. 标量子查询 (Scalar Subquery) 这种子查询返回单个值（一行一列）。它可以用在SELECT语句的表达式中，或者WHERE、HAVING子句的比较操作符右侧。

• 特点： 结果唯一，如同一个常量。
• 应用场景： 获取某个聚合值（如最大值、平均值），然后用这个值进行比较或显示。
• 示例：

  SELECT product_name, price
  FROM products
  WHERE price = (SELECT MAX(price) FROM products);
  -- 找出价格最高的商品

  登录后复制

2. 行子查询 (Row Subquery) 行子查询返回单行多列的结果。它通常用在WHERE或HAVING子句中，与另一个行构造器进行比较。

• 特点： 比较的是一整行数据。
• 应用场景： 查找同时满足多个条件组合的记录，例如找出部门ID和职位都匹配特定值的员工。
• 示例：

  SELECT employee_name, department_id, job_title
  FROM employees
  WHERE (department_id, job_title) = (SELECT department_id, job_title FROM employees WHERE employee_id = 101);
  -- 找出和员工101部门及职位都相同的员工

  登录后复制

3. 列子查询 (Column Subquery) 列子查询返回单列多行的结果。它常与

IN

NOT IN

ANY

ALL

• 特点： 结果集是一列值。
• 应用场景： 查找某个属性值存在于另一个查询结果集中的记录。
• 示例：

  SELECT customer_name
  FROM customers
  WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date = CURDATE());
  -- 找出今天有下订单的客户

  登录后复制

4. 表子查询 / 派生表 (Table Subquery / Derived Table) 表子查询返回一个完整的结果集，可以看作是一个临时表，用在FROM子句中。MySQL会先执行这个子查询，然后将结果作为外部查询的数据源。

• 特点： 必须有别名，外部查询可以像操作普通表一样操作它。
• 应用场景： 对聚合结果进行再次查询、简化复杂的多表连接、实现分步查询逻辑。
• 示例：

  SELECT department_name, avg_salary
  FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg_salaries
  JOIN departments ON dept_avg_salaries.department_id = departments.department_id
  WHERE avg_salary > 60000;
  -- 找出平均薪资超过60000的部门及其平均薪资

  登录后复制

5. 相关子查询 (Correlated Subquery) 相关子查询的执行依赖于外部查询的每一行数据。它会为外部查询的每一行都执行一次。

• 特点： 子查询中引用了外部查询的列。
• 应用场景： 查找与外部查询某行数据相关联的特定信息。
• 示例：

  SELECT e.employee_name, e.salary, e.department_id
  FROM employees e
  WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);
  -- 找出每个部门薪资最高的员工

  登录后复制

  这里子查询中的

  e.department_id

  登录后复制
  就引用了外部查询的

  e

  登录后复制
  表的

  department_id

  登录后复制
  。

6. 非相关子查询 (Uncorrelated Subquery) 非相关子查询可以独立于外部查询执行，它只执行一次，然后将结果传递给外部查询。

• 特点： 子查询不依赖外部查询的任何列。
• 应用场景： 大多数标量、行、列子查询都是非相关的。

7. EXISTS / NOT EXISTS 子查询

EXISTS

EXISTS

NOT EXISTS

• 特点： 不关心子查询返回的具体数据，只关心是否有数据。
• 应用场景： 判断是否存在满足特定条件的记录，通常比

  IN

  登录后复制
  在处理大数据集时效率更高。
• 示例：

  SELECT customer_name
  FROM customers c
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date = CURDATE());
  -- 找出今天有下订单的客户（使用EXISTS）

  登录后复制

MySQL子查询的性能瓶颈与常见误区

我个人在实际开发中，经常会遇到开发者对子查询的滥用，或者说，是不太了解其背后的一些性能陷阱。子查询虽然功能强大，但并非万能药，尤其是在处理大量数据时，不恰当的使用可能会成为性能瓶颈。

1. 相关子查询的“N+1”问题： 这是最常见也最致命的问题。相关子查询的执行逻辑决定了它会为外部查询的每一行都执行一次。如果外部查询返回1000行，那么子查询就会执行1000次。这在数据量小的时候可能不明显，但一旦数据量上来，性能会急剧下降，就像一个循环套着另一个循环，每次迭代都去数据库跑一次查询，开销巨大。很多时候，我发现大家写出这样的代码，并不是不知道有更优解，而是觉得子查询写起来更“直观”，更符合人类的思维习惯。

2.

IN

IN

IN

JOIN

IN

3. 优化器选择的不可预测性： MySQL的查询优化器很聪明，它会尝试将子查询转换为更高效的连接操作。但这种转换并非总是发生，也不是总能达到最优。有时候，你觉得一个子查询应该能被优化，但实际上，优化器可能因为某些复杂的条件或统计信息不足，选择了次优的执行计划。这就像你给一个聪明的学生布置作业，他可能会用一种你没想到的高效方法完成，也可能因为某个小细节卡住，用最笨的方法来做。

4. 缺乏索引支持： 无论是外部查询还是子查询，如果涉及的列没有合适的索引，那么无论查询结构多么精巧，都可能导致全表扫描，性能自然会受到影响。这不仅仅是子查询的问题，但子查询因为其嵌套特性，更容易暴露索引缺失带来的问题。

如何优化MySQL子查询：实用技巧与替代方案

面对子查询可能带来的性能挑战，我们并非束手无策。在我看来，大多数情况下，子查询都有更高效的替代方案，或者至少有优化的空间。核心思想就是：尽量避免相关子查询，并善用连接（JOIN）操作。

1. 优先使用 JOIN 替代子查询： 这几乎是我在优化SQL时最常用的手段。很多能用子查询解决的问题，特别是那些相关子查询和

IN

INNER JOIN

LEFT JOIN

RIGHT JOIN

• IN

  登录后复制
  子查询转

  JOIN

  登录后复制
  ：

  -- 原始IN子查询
  SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date = CURDATE());
  
  -- 优化后：使用INNER JOIN
  SELECT DISTINCT c.customer_name
  FROM customers c
  INNER JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_date = CURDATE();

  登录后复制

  JOIN

  登录后复制
  通常能更好地利用索引，并且优化器在处理

  JOIN

  登录后复制
  时有更多的优化策略。

• EXISTS

  登录后复制
  子查询转

  JOIN

  登录后复制
  ： 虽然

  EXISTS

  登录后复制
  在某些场景下性能优于

  IN

  登录后复制
  ，但很多时候，

  JOIN

  登录后复制
  仍然是更清晰和高效的选择。

  -- 原始EXISTS子查询
  SELECT c.customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date = CURDATE());
  
  -- 优化后：使用INNER JOIN
  SELECT DISTINCT c.customer_name
  FROM customers c
  INNER JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_date = CURDATE();

  登录后复制

  对于查找“存在”的情况，

  INNER JOIN

  登录后复制
  通常就足够了。如果需要查找“不存在”的情况，可以考虑

  LEFT JOIN ... WHERE ... IS NULL

  登录后复制
  。

• 相关子查询转

  JOIN

  登录后复制
  （特别是聚合类）：

  -- 原始相关子查询：找出每个部门薪资最高的员工
  SELECT e.employee_name, e.salary, e.department_id
  FROM employees e
  WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);
  
  -- 优化后：使用JOIN和派生表（或者更常见的JOIN + GROUP BY + HAVING）
  SELECT e.employee_name, e.salary, e.department_id
  FROM employees e
  INNER JOIN (SELECT department_id, MAX(salary) AS max_dept_salary FROM employees GROUP BY department_id) AS max_salaries_per_dept
  ON e.department_id = max_salaries_per_dept.department_id AND e.salary = max_salaries_per_dept.max_dept_salary;

  登录后复制

  这种方式将子查询变成了派生表，它只执行一次，然后与主表进行连接，大大减少了执行次数。

2. 善用派生表（Derived Table）： 当子查询返回一个结果集，并需要在此结果集上进行进一步操作时，将其作为派生表（在

FROM

-- 找出平均薪资超过60000的部门及其平均薪资
SELECT d.department_name, ds.avg_salary
FROM departments d
JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 60000) AS ds
ON d.department_id = ds.department_id;

这里，子查询

ds

departments

3. 确保索引的有效性： 无论你用子查询还是

JOIN

WHERE

JOIN

4. 考虑使用

UNION ALL

UNION

UNION ALL

UNION

5. 避免在

SELECT

SELECT

JOIN

MySQL子查询实战案例解析

理论讲了这么多，不如直接看几个实际的例子，看看子查询是怎么用的，以及我们又该如何去优化它们。

案例一：查找每个部门薪资最高的员工

这是个非常经典的面试题，也是展示相关子查询和其优化方式的好例子。

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

34

查看详情

原始子查询写法（相关子查询）：

SELECT e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);

分析： 这个查询对于每一位员工，都会去子查询一次，看看他所在部门的最高薪资是多少，然后比较。如果员工数量很多，部门也很多，那这个子查询的执行次数会非常可观。

优化方案（使用 JOIN 和派生表）：

SELECT e.employee_name, e.salary, e.department_id
FROM employees e
INNER JOIN (
    SELECT department_id, MAX(salary) AS max_salary_in_dept
    FROM employees
    GROUP BY department_id
) AS dept_max_salaries
ON e.department_id = dept_max_salaries.department_id AND e.salary = dept_max_salaries.max_salary_in_dept;

分析： 这里的优化思路是，先通过一个派生表（

dept_max_salaries

employees

案例二：找出所有购买过特定商品类别（例如“电子产品”）的客户

原始子查询写法（使用 IN）：

SELECT c.customer_name
FROM customers c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.category = '电子产品'
);

分析： 这个查询会先找出所有购买过“电子产品”的客户ID列表，然后外部查询再判断每个客户的ID是否在这个列表中。如果购买“电子产品”的客户数量非常庞大，

IN

优化方案（使用 INNER JOIN）：

SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE p.category = '电子产品';

分析： 直接通过多个

INNER JOIN

WHERE

DISTINCT

JOIN

案例三：更新数据时，根据另一张表的数据进行更新

原始子查询写法：

UPDATE products p
SET p.price = p.price * 1.10
WHERE p.product_id IN (
    SELECT oi.product_id
    FROM order_items oi
    WHERE oi.quantity > 100
);

分析： 这个查询会找出所有销售数量超过100的商品ID，然后更新这些商品的

price

IN

优化方案（使用 JOIN 更新）： MySQL支持多表

UPDATE

UPDATE products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
SET p.price = p.price * 1.10
WHERE oi.quantity > 100;

分析： 直接将

products

order_items

WHERE

p

price

这些案例希望能让你对子查询的实际应用和优化有更直观的理解。记住，虽然子查询很方便，但在追求性能的场景下，多思考一下是否能用

JOIN

以上就是MySQL中常用的子查询类型有哪些 MySQL子查询优化与实战全攻略的详细内容，更多请关注php中文网其它相关文章！