全部标签
写点什么
OpenHarmony算法元宇宙MySQL移动开发学习方法学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

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

作者：威哥爱编程
  • 2024-12-23
    北京

  • 本文字数：2646 字

    阅读完需：约 9 分钟

大家好，我是 V 哥，上周跟一个哥们吃饭，技术人在一起，你知道的，没聊上一会儿，就转到技术问题探讨上了，其中聊到数据库子查询的问题印象深刻，回来整理了以下 10 个案例说明不使用子查询的问题，分享给大家。


首先，来说一下在 MySQL 中，不推荐使用子查询和 JOIN 的原因，主要有以下几点：


  1. 性能问题：子查询在执行时，MySQL 需要创建临时表来存储内层查询的结果，查询完毕后再删除这些临时表，这会增加 CPU 和 IO 资源的消耗，产生慢查询。JOIN 操作本身效率也是硬伤，特别是当数据量很大时，性能难以保证。

  2. 索引失效：子查询可能导致索引失效，因为 MySQL 会将查询强行转换为联接来执行，这使得子查询不能首先被执行，如果外表很大，性能上会出问题。

  3. 查询优化器的复杂度：子查询会影响查询优化器的判断，导致不够优化的执行计划。相比之下，联表查询更容易被优化器理解和处理。

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

  5. 维护成本：使用 JOIN 写的 SQL 语句在修改表的 schema 时比较复杂，成本较大，尤其是在系统较大时，不易维护。


针对这些原因，可以采取以下解决方案：


  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');
复制代码


优化方案：


CREATE TEMPORARY TABLE temp_customers AS SELECT customer_id FROM customers WHERE last_order_date > '2023-01-01';SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM temp_customers);
复制代码


对于复杂的子查询，使用临时表存储中间结果，简化查询并提高性能。

案例 7：使用窗口函数替代子查询

原始查询：


SELECT employee_id, salary, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_salary FROM employees e;
复制代码


优化方案：


SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_salary FROM employees;
复制代码


使用窗口函数替代子查询，提高查询效率。

案例 8：优化子查询以避免全表扫描

原始查询：


SELECT * FROM users WHERE username IN (SELECT username FROM orders WHERE order_date = '2024-01-01');
复制代码


优化方案：


CREATE INDEX idx_order_date ON orders(order_date);SELECT * FROM users WHERE username IN (SELECT username FROM orders WHERE order_date = '2024-01-01');
复制代码


为order_date字段创建索引，避免全表扫描，提高子查询效率。

案例 9：使用 LIMIT 子句限制子查询返回数据量

原始查询：


SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');
复制代码


优化方案：


SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA' LIMIT 100);
复制代码


使用LIMIT子句限制子查询返回的数据量，减少主查询需要处理的数据量，提高查询效率。

案例 10：使用 JOIN 代替子查询以利用索引

原始查询：


SELECT * FROM transactions WHERE product_id IN (SELECT product_id FROM products WHERE category = 'Equity');
复制代码


优化方案：


SELECT t.* FROM transactions t JOIN products p ON t.product_id = p.product_id WHERE p.category = 'Equity';
复制代码


使用 JOIN 代替子查询，并且可以更容易地利用products表上的category索引。


这些案例展示了如何通过不同的优化策略来提升 MySQL 查询性能，特别是在处理子查询时。

最后

通过上述分析和案例，我们可以看到，在实际业务场景中，替代子查询和 JOIN 的高效编程方法能够在不同场景下显著提升 MySQL 数据库的查询性能。在实际应用中，应根据具体业务需求和数据特点，灵活选择合适的优化方案。关注威哥爱编程，痴迷技术咱是认真滴。官人，都看到这了，高低点个赞再走呗，V 哥感谢你的支持。

划线
评论
复制
发布于: 刚刚阅读数: 4

版权声明: 本文为 InfoQ 作者【威哥爱编程】的原创文章。

原文链接:【http://xie.infoq.cn/article/a7b34948ed3078042e2fd260e】。

本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。

MySQLsql
用户头像

威哥爱编程

关注

华为 HDE、CSDN 博客专家、Java畅销书作者 2018-05-30 加入

全栈领域优质创作者（Java/HarmonyOS/AI），公众号：威哥爱编程

评论

发布
暂无评论
Copyright © 2024, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
10个案例告诉你mysql不使用子查询的原因_MySQL_威哥爱编程_InfoQ写作社区