10个案例告诉你mysql不使用子查询的原因

大家好，我是V哥。上周末与一位技术同好聚餐，不出所料，我们很快便深入探讨起技术话题。其中，关于数据库中避免使用子查询的讨论特别引人入胜。回家后，我整理了以下10个案例，展示如何在不使用子查询的情况下解决问题，现在与大家分享。

首先，让我们探讨在MySQL中为何不推荐使用子查询和JOIN，主要原因如下：

1. 性能考量：执行子查询时，MySQL需创建临时表以存储内层查询结果，查询结束后再删除这些表，这无疑增加了CPU和IO的负担，可能导致查询速度变慢。同样，JOIN操作在处理大规模数据时，性能也难以得到保证。

2. 索引失效风险：子查询可能导致索引失效，因为MySQL可能会将查询转换为联接操作，这使得子查询无法优先执行，若外表数据量庞大，性能将受到影响。

3. 查询优化器复杂性：子查询可能干扰查询优化器的决策，导致执行计划不够优化。而联表查询则更易于被优化器理解和处理。

4. 数据传输成本：子查询可能引起大量不必要的数据传输，因为每个子查询都需要将结果返回给主查询，而联表查询可以通过一次性查询返回所有所需数据，减少数据传输。

5. 维护难度：使用JOIN编写的SQL语句在修改数据库结构时较为复杂，尤其在大型系统中，维护成本较高。

针对上述问题，我们可以考虑以下解决方案：

1. 应用层关联：在业务层先进行单表查询，然后将结果作为条件传递给下一个单表查询，以此减轻数据库层的负担。

2. 使用IN替代子查询：若子查询结果集较小，可考虑使用“IN”操作符进行查询，这在数据量不大时，查询效率更高。

3. 使用WHERE EXISTS：WHERE EXISTS是比“IN”更优的方案，它检查子查询是否返回结果集，能显著提升查询速度。

4. 改写为JOIN：使用JOIN查询替代子查询，无需建立临时表，查询速度更快，且若查询中使用索引，性能更佳。

接下来，V哥将通过10个案例直观展示这些优化策略。

案例1：查询所有有库存的商品信息

原始查询（使用子查询）：

SELECT * FROM products WHERE id IN (SELECT product_id FROM inventory WHERE stock > 0);

此查询可能导致查询速度缓慢，影响用户体验。

优化方案（使用EXISTS）：

SELECT * FROM products WHERE EXISTS (SELECT 1 FROM inventory WHERE inventory.product_id = products.id AND inventory.stock > 0);

此优化方案能显著提升查询速度，改善用户体验。

案例2：使用EXISTS优化子查询

原始查询：

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

优化方案：

SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM customers WHERE orders.customer_id = customers.customer_id AND customers.country = 'USA');

使用EXISTS替代IN子查询，减少回表查询次数，提高查询效率。

案例3：使用JOIN代替子查询

原始查询：

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

优化方案：

SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.country = 'USA';

使用JOIN代替子查询，减少子查询开销，更易利用索引。

案例4：优化子查询以减少数据量

原始查询：

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers);

优化方案：

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE active = 1);

限制子查询返回的数据量，减少主查询需要检查的行数，提高查询效率。

案例5：使用索引覆盖

原始查询：

SELECT customer_id FROM customers WHERE country = 'USA';

优化方案：

CREATE INDEX idx_country ON customers(country);
SELECT customer_id FROM customers WHERE country = 'USA';

为country字段创建索引，使子查询能在索引中直接找到数据，避免回表查询。

案例6：使用临时表优化复杂查询

原始查询：

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE last_order_date > '2023-01-01');

优化方案：

```sql
CREATE TEMPORARY TABLE temp_customers AS SELECT customer_id