MySQL怎样优化子查询 MySQL子查询改写与性能对比测试-mysql教程-PHP中文网

优化mysql子查询最有效的策略是将其改写为join或exists操作，以提升执行效率；2. 对于非关联子查询，尤其是使用in的情况，应改写为inner join或使用exists，避免生成临时表和全表扫描；3. 对于关联子查询，优先使用exists或not exists判断存在性，因其只需找到一个匹配即可停止；4. 当需要消除重复记录时，可采用distinct或派生表方式与join结合；5. 在需要获取子查询中额外数据时，必须改写为join操作以支持聚合和字段提取；6. 对于not exists场景，可改写为left join配合is null条件，以提升可读性和执行性能；7. 改写的核心在于理解查询语义、利用索引、减少临时表使用，并通过explain分析执行计划进行实测验证，最终选择最优方案。

MySQL怎样优化子查询 MySQL子查询改写与性能对比测试

MySQL优化子查询的核心在于理解其执行机制并进行改写，通常通过将子查询转换为连接（JOIN）操作或合理利用EXISTS/NOT EXISTS等，能显著提升查询性能，避免不必要的全表扫描或多次执行。

解决方案

优化MySQL子查询，我个人觉得最直接有效的策略就是“改写”。很多时候，子查询的效率低下并非其本身设计有问题，而是MySQL优化器在处理某些特定模式时，可能无法像处理JOIN那样高效。

首先，对于非关联子查询（即子查询的执行不依赖于外部查询的任何列），特别是那些使用

IN

登录后复制

操作符的：

例如，我们想找出所有在订单表中有记录的用户：

SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders);

登录后复制

这种情况下，MySQL可能会先执行内部子查询，生成一个临时表，然后再对外部查询进行匹配。如果

orders

登录后复制

表非常大，或者

user_id

登录后复制

没有合适的索引，这个过程可能会很慢。

我的经验是，这种场景下，将其改写为

JOIN

登录后复制

通常能带来立竿见影的效果：

SELECT u.* FROM users u JOIN orders o ON u.user_id = o.user_id;

登录后复制

或者，如果你只是想确认存在性，并且不关心重复的用户记录：

SELECT u.* FROM users u JOIN (SELECT DISTINCT user_id FROM orders) o ON u.user_id = o.user_id;

登录后复制

甚至更简洁，直接使用

EXISTS

登录后复制

，在某些情况下，MySQL的优化器对

EXISTS

登录后复制

的处理可能会更智能，因为它只需要找到一个匹配项就停止：

SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

登录后复制

对于关联子查询（即子查询的执行依赖于外部查询的列），

EXISTS

登录后复制

通常是首选，因为它在逻辑上更符合“是否存在”的判断。但即使是

EXISTS

登录后复制

，在某些极端情况下，如果外部查询返回的行数巨大，每次执行子查询的开销累积起来也可能成为瓶颈。

我发现，有时候将关联子查询转换为

LEFT JOIN

登录后复制

并结合

IS NOT NULL

登录后复制

或

IS NULL

登录后复制

来判断存在性或不存在性，也能提供不错的性能。

例如，找出没有下过订单的用户：

SELECT u.* FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

登录后复制

改写为：

SELECT u.* FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.user_id IS NULL;

登录后复制

这种改写的好处在于，

LEFT JOIN

登录后复制

通常可以更好地利用索引，并且避免了子查询可能带来的行级处理开销。当然，具体效果还得看数据量、索引情况以及MySQL版本和优化器的能力。

为什么MySQL子查询性能常常不尽如人意？

这问题问得很好，也是我工作中经常会遇到的一个痛点。在我看来，MySQL子查询性能不佳，主要有几个深层原因。首先，优化器对子查询的处理策略相对保守。很多时候，特别是早期版本，MySQL对子查询的处理方式是“先执行子查询，再将结果传递给外部查询”。这听起来很直接，但如果子查询的结果集很大，或者子查询本身就是关联的（需要对外部查询的每一行都执行一次），那么这种“串行”或“嵌套循环”式的执行方式就会导致大量的I/O和CPU开销。

其次，临时表的生成和管理。当子查询的结果集无法直接传递给外部查询时，MySQL可能会创建一个内部临时表来存储子查询的结果。这个临时表可能在内存中，也可能因为数据量过大而被写入磁盘。无论是哪种情况，创建、填充和读取临时表都会引入额外的开销。特别是当临时表没有合适的索引时，外部查询对它的访问效率会非常低。我记得有一次，一个简单的

IN

登录后复制

子查询导致了磁盘临时表的生成，直接让查询时间从几毫秒飙升到几十秒，简直是灾难。

再者，索引利用的局限性。虽然MySQL的优化器在不断进步，但它在处理某些子查询模式时，可能无法像处理JOIN操作那样充分利用现有的索引。比如，一个

IN

登录后复制

子查询，即使内部查询的列有索引，外部查询在匹配时可能也无法有效利用这个索引，因为它在处理的是一个“列表”，而不是一个明确的连接条件。这就像你给了优化器一把瑞士军刀，但它在某些场景下，只用它来削铅笔，而没有发挥其多功能性。

最后，缺乏对子查询的“下推”优化。理想情况下，数据库优化器应该能够将外部查询的某些条件“下推”到子查询内部，从而减少子查询返回的行数。但在某些复杂的子查询结构中，MySQL可能无法做到这一点，导致子查询返回了过多的不必要数据，增加了后续处理的负担。所以，理解这些“坑”，才能更好地避开它们。

如何将IN子查询改写为JOIN操作？

将

IN

登录后复制

子查询改写为

JOIN

登录后复制

操作，是提升查询性能的经典手段，也是我个人在优化SQL时最常用的技巧之一。它的核心思想是：将子查询的结果集视为一个独立的表，然后通过连接操作将它与外部表关联起来。

我们来看一个具体的例子。假设我们有两个表：

products

登录后复制

（产品信息，包含

product_id

登录后复制

name

登录后复制

等）和

orders_items

登录后复制

（订单详情，包含

order_id

登录后复制

product_id

登录后复制

quantity

登录后复制

等）。现在，我们想找出所有已经被下过订单的产品信息。

原始的

IN

登录后复制

子查询可能是这样的：

SELECT p.product_id, p.name
FROM products p
WHERE p.product_id IN (SELECT oi.product_id FROM orders_items oi);

登录后复制

这条SQL的意图很明确：从

products

登录后复制

表中选出

product_id

登录后复制

存在于

orders_items

登录后复制

表中的所有产品。

现在，我们将其改写为

JOIN

登录后复制

操作。最直接的方式是使用

INNER JOIN

登录后复制

：

SELECT p.product_id, p.name
FROM products p
INNER JOIN orders_items oi ON p.product_id = oi.product_id;

登录后复制

等等，这里有个小问题。如果一个产品被下了多次订单，那么

INNER JOIN

登录后复制

会导致

products

登录后复制

表中的同一行被重复返回多次。这显然不是我们想要的，因为我们只是想知道“哪些产品被下过订单”，而不是“每个产品被下过几次订单”。

蓝心千询

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

查看详情

为了解决重复行的问题，我们有几种改写方式：

使用
```
DISTINCT
```
登录后复制
关键字：
```
SELECT DISTINCT p.product_id, p.name
FROM products p
INNER JOIN orders_items oi ON p.product_id = oi.product_id;
```
登录后复制
这种方式很直观，通过
```
DISTINCT
```
登录后复制
来消除重复。MySQL在执行时可能会先进行JOIN，然后对结果集进行去重。
将子查询结果作为派生表（Derived Table）进行JOIN：
```
SELECT p.product_id, p.name
FROM products p
INNER JOIN (SELECT DISTINCT product_id FROM orders_items) AS distinct_products_in_orders
ON p.product_id = distinct_products_in_orders.product_id;
```
登录后复制
这种方式在逻辑上更接近原始的
```
IN
```
登录后复制
子查询。它先从
```
orders_items
```
登录后复制
中找出所有不重复的
```
product_id
```
登录后复制
，形成一个临时的“表”，然后再与
```
products
```
登录后复制
表进行连接。我个人更倾向于这种写法，因为它让意图更清晰，而且在某些情况下，MySQL优化器可能能更好地处理这个派生表。
使用
```
EXISTS
```
登录后复制
替代（如果只是判断存在性）：
```
SELECT p.product_id, p.name
FROM products p
WHERE EXISTS (SELECT 1 FROM orders_items oi WHERE oi.product_id = p.product_id);
```
登录后复制
虽然这又回到了子查询，但
```
EXISTS
```
登录后复制
在语义上更贴合“是否存在”的判断，而且MySQL对
```
EXISTS
```
登录后复制
的优化通常比
```
IN
```
登录后复制
更好。它不需要将子查询的所有结果都加载到内存中，只要找到一个匹配就返回真。在性能对比上，
```
EXISTS
```
登录后复制
和
```
JOIN
```
登录后复制
有时会互有胜负，具体取决于数据分布和索引情况。

选择哪种改写方式，取决于你的具体需求和对性能的考量。在我的经验里，对于大数据量，

INNER JOIN

登录后复制

配合

DISTINCT

登录后复制

或者派生表的方式，通常比原始的

IN

登录后复制

子查询表现更好，因为它允许MySQL优化器更好地利用索引，甚至进行哈希连接或合并连接等更高级的优化策略。

何时应该使用EXISTS子查询，何时考虑改写？

这是一个很关键的问题，因为它涉及到对查询意图和数据库优化器行为的深刻理解。我个人在处理

EXISTS

登录后复制

子查询时，通常会遵循一个原则：当你的核心需求是“是否存在”某个匹配项，而不是“获取匹配项的具体数据”时，
EXISTS
登录后复制
往往是更自然、更高效的选择。

何时使用

EXISTS

登录后复制

子查询：

判断存在性：这是
```
EXISTS
```
登录后复制
最典型的应用场景。例如，你想找出所有至少有一个订单的用户，你并不关心他们下了多少个订单，也不关心订单的具体内容，只关心“有没有”：
```
SELECT u.user_id, u.username
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);
```
登录后复制
在这种情况下，
```
EXISTS
```
登录后复制
非常高效，因为一旦子查询找到一个匹配的
```
order
```
登录后复制
，它就会立即停止执行并返回
```
TRUE
```
登录后复制
，而不需要扫描所有匹配的行。
处理关联子查询：当子查询需要引用外部查询的列时（即关联子查询），
```
EXISTS
```
登录后复制
通常比
```
IN
```
登录后复制
更优。因为
```
IN
```
登录后复制
在处理关联子查询时，可能需要将外部查询的每一行都带入子查询中执行，并且每次执行都可能产生一个结果集，然后与外部的列表进行匹配。而
```
EXISTS
```
登录后复制
只需要判断是否存在即可。
处理
```
NOT EXISTS
```
登录后复制
来查找“不存在”的记录：
```
SELECT u.user_id, u.username
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);
```
登录后复制
这用于查找那些没有下过订单的用户。
```
NOT EXISTS
```
登录后复制
同样高效，它会尝试在子查询中找到一个匹配，如果找不到，则外部查询的条件为真。

何时考虑将

EXISTS

登录后复制

子查询改写：

尽管

EXISTS

登录后复制

在很多场景下表现优秀，但并非万能药。在某些特定情况下，将其改写为

JOIN

登录后复制

操作可能会带来更好的性能，这通常发生在：

优化器限制或数据分布特殊：尽管MySQL对
```
EXISTS
```
登录后复制
有优化，但在某些复杂的查询或特定的数据分布下，优化器可能无法充分利用索引。例如，如果外部表非常小，而内部子查询涉及的表非常大，或者关联条件涉及的列没有合适的索引，那么
```
JOIN
```
登录后复制
可能会有更好的执行计划。
需要获取子查询中的其他数据：如果除了判断存在性之外，你还需要从子查询涉及的表中获取一些额外的信息，那么
```
JOIN
```
登录后复制
就成了必然的选择。例如，你想找出所有下过订单的用户，并且显示他们最近一次订单的日期。这时，
```
EXISTS
```
登录后复制
就无法满足需求了，你需要
```
JOIN
```
登录后复制
，并且可能需要结合
```
GROUP BY
```
登录后复制
和
```
MAX()
```
登录后复制
函数。
```
-- 原始EXISTS (只判断存在)
SELECT u.user_id FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

-- 改写为JOIN (获取额外信息，并去重)
SELECT u.user_id, MAX(o.order_date) AS latest_order_date
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;
```
登录后复制
这里，
```
INNER JOIN
```
登录后复制
不仅判断了存在性，还允许我们通过
```
GROUP BY
```
登录后复制
和聚合函数获取了每个用户的最新订单日期。
当
```
LEFT JOIN
```
登录后复制
结合
IS [NOT] NULL
登录后复制
语义更清晰或性能更优时：对于
```
NOT EXISTS
```
登录后复制
的场景，将其改写为
```
LEFT JOIN ... WHERE column IS NULL
```
登录后复制
有时会更直观，并且在某些情况下，优化器对
```
LEFT JOIN
```
登录后复制
的优化可能更到位。
```
-- 原始NOT EXISTS
SELECT u.user_id FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

-- 改写为LEFT JOIN
SELECT u.user_id FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.user_id IS NULL;
```
登录后复制
我发现，对于
```
NOT EXISTS
```
登录后复制
，
```
LEFT JOIN
```
登录后复制
的改写方式在可读性上并不逊色，而且在实际测试中，性能也往往不错。