<p>中的子查询分为标量子查询、行子查询、列子查询、表子查询、相关子查询、非相关子查询以及exists/not exists子查询，1. 标量子查询返回单个值，可用于select、where等语句中；2. 行子查询返回单行多列，常用于行比较；3. 列子查询返回单列多行，常与in、any、all结合使用；4. 表子查询作为派生表用于from子句，需有别名；5. 相关子查询依赖外部查询的每一行，易引发“n+1”性能问题；6. 非相关子查询可独立执行，仅执行一次；7. exists/not exists用于判断子查询是否返回结果，不关注具体数据。为提升性能，应优先使用join替代子查询，善用派生表，确保索引有效，避免在select中使用相关子查询，最终通过优化手段减少执行开销，提高查询效率。p>
<p>p>
<p>MySQL中的子查询，简单来说，就是嵌套在其他SQL语句（如SELECT、e-default'>INUPDATE、DELETE）内部的查询。它们可以返回单个值、一行、一列或一个表，根据其返回结果和与外部查询的关联方式，主要分为几类：标量子查询、行子查询、列子查询以及表子查询（或称派生表）。此外，从执行方式上看，还有非相关子查询和相关子查询，以及特殊用于存在性判断的EXISTSEXISTS
解决方案
<p>子查询在MySQL中扮演着举足轻重的角色，它允许我们构建更复杂、更具逻辑性的查询语句，以应对各种数据检索和操作需求。理解并熟练运用不同类型的子查询，是提升数据库操作效率和解决实际问题的关键。p>
<p>1. 标量子查询 (Scalar Subquery) 这种子查询返回单个值（一行一列）。它可以用在SELECT语句的表达式中，或者WHEREe-default'>IN
- 特点： 结果唯一，如同一个常量。
- 应用场景： 获取某个聚合值（如最大值、平均值），然后用这个值进行比较或显示。
- 示例：<
pre>e-default'>SELECTproduct_name,price
FROMproducts
WHEREprice= (SELECTMAX(price)FROMproducts
-- 找出价格最高的商品e>
<p>2. 行子查询 (Row Subquery) 行子查询返回单行多列的结果。它通常用在WHEREe-default'>IN
- 特点： 比较的是一整行数据。
- 应用场景： 查找同时满足多个条件组合的记录，例如找出部门ID和职位都匹配特定值的员工。
- 示例：<
pre>e-default'>SELECTemployee_name, department_id, job_title
FROMemployees
WHEREepartment_id, job_title) = (SELECTdepartment_id, job_titleFROMemployeesWHEREemployee_id = 101);
-- 找出和员工101部门及职位都相同的员工e>
<p>3. 列子查询 (Column Subquery) 列子查询返回单列多行的结果。它常与p>
<pre>e-default'>INe>、
<pre>NOT e-default'>INe>、
<pre>e-default'>ANYe>、
<pre>e-default'>ALLe>等操作符结合使用。
<p>p>
- 特点： 结果集是一列值。
- 应用场景： 查找某个属性值存在于另一个查询结果集中的记录。
- 示例：<
pre>SELECTcustomer_name
FROMcustomers
WHEREer_ide-default'>INSELECTcustomer_idFROMordersWHEREer_date= CURDATE());
-- 找出今天有下订单的客户pre>
<p>4. 表子查询 / 派生表 (Table Subquery / Derived Table) 表子查询返回一个完整的结果集，可以看作是一个临时表，用在FROM子句中。MySQL会先执行这个子查询，然后将结果作为外部查询的数据源。p>
- 特点： 必须有别名，外部查询可以像操作普通表一样操作它。
- 应用场景： 对聚合结果进行再次查询、简化复杂的多表连接、实现分步查询逻辑。
- 示例：<
pre>SELECTdepartment_name, avg_salary
FROM(SELECTdepartment_id, AVG(salary) AS avg_salaryFROMemployees GROUP BY department_id) AS dept_avg_salaries
JOe-default'>INepartments ON dept_avg_salaries.department_id = departments.department_id
WHERE
-- 找出平均薪资超过60000的部门及其平均薪资pre>
<p>5. 相关子查询 (Correlated Subquery) 相关子查询的执行依赖于外部查询的每一行数据。它会为外部查询的每一行都执行一次。p>
- 特点： 子查询中引用了外部查询的列。
- 应用场景： 查找与外部查询某行数据相关联的特定信息。
- 示例：<
pre>e-default'>SELECTe.employee_name,e.salary,e-default'>e.department_id
FROMemployeese
WHEREe.salary = (SELECTMAX(salary)FROMemployeesWHEREepartment_id =e-default'>e.department_id
-- 找出每个部门薪资最高的员工e><p>这里子查询中的p><pre>e-default'>e.department_ide>就引用了外部查询的<pre>epre>表的<pre>department_idpre>。<p>p>
<p>6. 非相关子查询 (Uncorrelated Subquery) 非相关子查询可以独立于外部查询执行，它只执行一次，然后将结果传递给外部查询。p>
- 特点： 子查询不依赖外部查询的任何列。
- 应用场景： 大多数标量、行、列子查询都是非相关的。
<p>7. EXISTSEXISTS
<pre>EXISTSe>操作符用于检查子查询是否返回任何行。如果子查询返回至少一行，
<pre>EXISTSe>条件就为真；否则为假。
<pre>NOT EXISTSe>则相反。
<p>p>
- 特点： 不关心子查询返回的具体数据，只关心是否有数据。
- 应用场景： 判断是否存在满足特定条件的记录，通常比<
pre>e-default'>INe>在处理大数据集时效率更高。 - 示例：<
pre>SELECTcustomer_name
FROMcustomers c
WHEREEXISTSSELECT1FROMorders oWHEREer_id = c.customer_id AND o.order_date= CURDATE());
-- 找出今天有下订单的客户（使用EXISTSe>
MySQL子查询的性能瓶颈与常见误区
<p>我个人在实际开发中，经常会遇到开发者对子查询的滥用，或者说，是不太了解其背后的一些性能陷阱。子查询虽然功能强大，但并非万能药，尤其是在处理大量数据时，不恰当的使用可能会成为性能瓶颈。p>
<p>1. 相关子查询的“N+1”问题： 这是最常见也最致命的问题。相关子查询的执行逻辑决定了它会为外部查询的每一行都执行一次。如果外部查询返回1000行，那么子查询就会执行1000次。这在数据量小的时候可能不明显，但一旦数据量上来，性能会急剧下降，就像一个循环套着另一个循环，每次迭代都去数据库跑一次查询，开销巨大。很多时候，我发现大家写出这样的代码，并不是不知道有更优解，而是觉得子查询写起来更“直观”，更符合人类的思维习惯。p>
<p>2. p>
<pre>e-default'>INe>
子查询的潜在风险： 当
<pre>e-default'>INe>子查询的结果集非常庞大时，MySQL的优化器可能无法很好地处理。虽然MySQL会尝试将
<pre>e-default'>INe>子查询优化为
<pre>JOe-default'>INe>操作，但并非所有情况都能完美转换。如果子查询返回的行数过多，可能会导致内存消耗过大，或者无法有效利用索引，最终退化为全表扫描，性能自然好不到哪里去。我记得有一次，一个同事用
<pre>e-default'>INe>查询几万个ID，结果直接把数据库搞崩了，后来发现就是这个原因。
<p>p>
<p>3. 优化器选择的不可预测性： MySQL的查询优化器很聪明，它会尝试将子查询转换为更高效的连接操作。但这种转换并非总是发生，也不是总能达到最优。有时候，你觉得一个子查询应该能被优化，但实际上，优化器可能因为某些复杂的条件或统计信息不足，选择了次优的执行计划。这就像你给一个聪明的学生布置作业，他可能会用一种你没想到的高效方法完成，也可能因为某个小细节卡住，用最笨的方法来做。p>
<p>4. 缺乏索引支持： 无论是外部查询还是子查询，如果涉及的列没有合适的索引，那么无论查询结构多么精巧，都可能导致全表扫描，性能自然会受到影响。这不仅仅是子查询的问题，但子查询因为其嵌套特性，更容易暴露索引缺失带来的问题。p>
如何优化MySQL子查询：实用技巧与替代方案
<p>面对子查询可能带来的性能挑战，我们并非束手无策。在我看来，大多数情况下，子查询都有更高效的替代方案，或者至少有优化的空间。核心思想就是：尽量避免相关子查询，并善用连接（JOe-default'>IN
<p>1. 优先使用 JOe-default'>IN
<pre>e-default'>INe>子查询，都可以通过
<pre>e-default'>INe-default'>INe>、
<pre>LEFT JOe-default'>INe>甚至
<pre>RIGHT JOe-default'>INe>来高效实现。
<p>p>
- <
p>p><pre>e-default'>INe>子查询转<pre>JOe-default'>INe>：<p>p><pre>-- 原始e-default'>IN
SELECTcustomer_nameFROMcustomersWHEREer_ide-default'>INSELECTcustomer_idFROMordersWHEREer_date= CURDATE());-- 优化后：使用
e-default'>INe-default'>IN
SELECTDISTe-default'>INer_name
FROMcustomers c
e-default'>INe-default'>INers o ON c.customer_id = o.customer_id
WHEREer_date= CURDATE();pre><p>p><pre>JOe-default'>INe>通常能更好地利用索引，并且优化器在处理<pre>JOe-default'>INe>时有更多的优化策略。<p>p> - <
p>p><pre>EXISTSe>子查询转<pre>JOe-default'>INe>： 虽然<pre>EXISTSe>在某些场景下性能优于<pre>e-default'>INe>，但很多时候，<pre>JOe-default'>INe>仍然是更清晰和高效的选择。<p>p><pre>-- 原始EXISTS
SELECTc.customer_nameFROMcustomers cWHEREEXISTSSELECT1FROMorders oWHEREer_id = c.customer_id AND o.order_date= CURDATE());-- 优化后：使用
e-default'>INe-default'>IN
SELECTDISTe-default'>INer_name
FROMcustomers c
e-default'>INe-default'>INers o ON c.customer_id = o.customer_id
WHEREer_date= CURDATE();pre><p>对于查找“存在”的情况，p><pre>e-default'>INe-default'>INe>通常就足够了。如果需要查找“不存在”的情况，可以考虑<pre>LEFT JOe-default'>INWHEREe>。<p>p> - <
p>相关子查询转p><pre>JOe-default'>INe>（特别是聚合类）：<p>p><pre>-- 原始相关子查询：找出每个部门薪资最高的员工
SELECTe.employee_name,e.salary,e-default'>e.department_id
FROMemployeese
WHEREe.salary = (SELECTMAX(salary)FROMemployeesWHEREepartment_id =e-default'>e.department_id-- 优化后：使用JO
e-default'>INe-default'>INe-default'>IN
SELECTe.employee_name,e.salary,e-default'>e.department_id
FROMemployeese
e-default'>INe-default'>INSELECTdepartment_id, MAX(salary) AS max_dept_salaryFROMemployees GROUP BY department_id) AS max_salaries_per_dept
ONe-default'>e.department_ides_per_dept.department_id ANDe.salary = max_salaries_per_dept.max_dept_salary;pre><p>这种方式将子查询变成了派生表，它只执行一次，然后与主表进行连接，大大减少了执行次数。p>
<p>2. 善用派生表（Derived Table）： 当子查询返回一个结果集，并需要在此结果集上进行进一步操作时，将其作为派生表（在p>
<pre>FROMpre>子句中使用）是一个非常好的选择。它能让查询逻辑更清晰，也更容易被优化器处理。
<p>p>
<pre>-- 找出平均薪资超过60000的部门及其平均薪资
SELECT d.department_name, ds.avg_salary
FROM departments d
JOe-default'>INSELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVe-default'>INds
ON d.department_id = ds.department_id;pre>
<p>这里，子查询p>
<pre>dspre>先计算出各部门的平均薪资并过滤，然后结果作为临时表与
<pre>departmentspre>表连接。
<p>p>
<p>3. 确保索引的有效性： 无论你用子查询还是p>
<pre>JOe-default'>INe>，索引都是性能的基石。确保子查询中用于连接或过滤的列，以及外部查询中用于条件判断的列都有合适的索引。特别是那些在
<pre>WHEREe>子句中频繁出现的列，或者
<pre>JOe-default'>INe>条件中的列。
<p>p>
<p>4. 考虑使用p>
<pre>UNIONe-default'>ALLe>
或<pre>UNIONe>： 在某些需要合并不同子查询结果的场景，
<pre>UNIONe-default'>ALLe>或
<pre>UNIONe>可能比复杂的子查询更直观且高效。
<p>p>
<p>5. 避免在p>
<pre>SELECTe>
列表中使用相关子查询： 如果
<pre>SELECTpre>列表中的子查询是相关子查询，那么它会为外部查询的每一行都执行一次，这是性能杀手。如果可能，尝试将其转换为
<pre>JOe-default'>INe>或预计算。
<p>p>
MySQL子查询实战案例解析
<p>理论讲了这么多，不如直接看几个实际的例子，看看子查询是怎么用的，以及我们又该如何去优化它们。p>
<p>案例一：查找每个部门薪资最高的员工p>
<p>这是个非常经典的面试题，也是展示相关子查询和其优化方式的好例子。p>
<p>蓝心千询是vivo推出的一个多功能AI智能助手p>
<p>原始子查询写法（相关子查询）：p>
<pre>SELECT e.employee_name, e.salary, e-default'>e.department_id
FROM employees e
WHEREe.salary = (SELECT MAX(salary) FROM employees WHEREepartment_id = e-default'>e.department_ide>
<p>分析： 这个查询对于每一位员工，都会去子查询一次，看看他所在部门的最高薪资是多少，然后比较。如果员工数量很多，部门也很多，那这个子查询的执行次数会非常可观。p>
<p>优化方案（使用 JOe-default'>IN
<pre>SELECT e.employee_name, e.salary, e-default'>e.department_id
FROM employees e
e-default'>INe-default'>IN
SELECT department_id, MAX(salary) AS max_salary_in_dept
FROM employees
GROUP BY department_id
) AS dept_max_salaries
ON e-default'>e.department_idept_max_salaries.department_id AND e.salary = dept_max_salaries.max_salary_in_dept;pre>
<p>分析： 这里的优化思路是，先通过一个派生表（p>
<pre>dept_max_salariespre>）一次性计算出每个部门的最高薪资，这个派生表只执行一次。然后，将这个派生表与原始的
<pre>employeespre>表进行连接，找出那些薪资恰好等于其所在部门最高薪资的员工。这样，子查询的开销就被大大降低了。
<p>p>
<p>案例二：找出所有购买过特定商品类别（例如“电子产品”）的客户p>
<p>原始子查询写法（使用 e-default'>IN
<pre>SELECT c.customer_name
FROM customers c
WHEREer_id e-default'>IN
SELECT o.customer_id
FROM orders o
JOe-default'>INer_items oi ON o.order_id = oi.order_id
JOe-default'>INproductsp ON oi.product_id = p.product_id
WHEREp.category = '电子产品'
);pre>
<p>分析： 这个查询会先找出所有购买过“电子产品”的客户ID列表，然后外部查询再判断每个客户的ID是否在这个列表中。如果购买“电子产品”的客户数量非常庞大，p>
<pre>e-default'>INe>列表会很长，可能导致性能问题。
<p>p>
<p>优化方案（使用 e-default'>INe-default'>IN
<pre>SELECT DISTe-default'>INer_name
FROM customers c
e-default'>INe-default'>INers o ON c.customer_id = o.customer_id
e-default'>INe-default'>INer_items oi ON o.order_id = oi.order_id
e-default'>INe-default'>INproductsp ON oi.product_id = p.product_id
WHEREp.category = '电子产品';pre>
<p>分析： 直接通过多个p>
<pre>e-default'>INe-default'>INe>将所有相关的表连接起来，然后通过
<pre>WHEREe>条件过滤出“电子产品”类别，最后用
<pre>DISTe-default'>INe>确保客户名称不重复。这种方式通常更高效，因为MySQL优化器在处理
<pre>JOe-default'>INe>操作时有更多的优化空间，并且能更好地利用索引。
<p>p>
<p>案例三：更新数据时，根据另一张表的数据进行更新p>
<p>原始子查询写法：p>
<pre>UPDATE productsp
SET p.price = p.price * 1.10
WHEREp.product_id e-default'>IN
SELECT oi.product_id
FROM order_items oi
WHERE
);pre>
<p>分析： 这个查询会找出所有销售数量超过100的商品ID，然后更新这些商品的p>
<pre>pricepre>。这里子查询返回的ID列表如果过大，同样可能面临
<pre>e-default'>INe>子查询的性能问题。
<p>p>
<p>优化方案（使用 JOe-default'>IN
<pre>UPDATEpre>，这是一种非常实用的技巧。
<p>p>
<pre>UPDATE productsp
e-default'>INe-default'>INer_items oi ON p.product_id = oi.product_id
SET p.price = p.price * 1.10
WHEREe>
<p>分析： 直接将p>
<pre>productse>表和
<pre>order_itemspre>表连接起来，然后通过
<pre>WHEREe>条件过滤，并直接更新
<pre>ppre>表的
<pre>pricepre>字段。这种方式更加直接和高效，避免了子查询的开销。
<p>p>
<p>这些案例希望能让你对子查询的实际应用和优化有更直观的理解。记住，虽然子查询很方便，但在追求性能的场景下，多思考一下是否能用p>
<pre>JOe-default'>INe>来替代，往往能带来意想不到的提升。
<p>p>
<p>以上就是MySQL中常用的子查询类型有哪些 MySQL子查询优化与实战全攻略的详细内容，更多请关注php中文网其它相关文章！p>
