首页 > 数据库 >MySQL不使用子查询的原因

MySQL不使用子查询的原因

时间：2025-01-12 21:28:34浏览次数：3  
标签：customer 优化 查询 MySQL WHERE id SELECT 原因

MySQL不使用子查询的原因及优化案例

在这里插入图片描述

目录

不推荐使用子查询和JOIN的原因

在MySQL中，不推荐使用子查询和JOIN主要有以下原因：

  • 性能问题：子查询执行时，MySQL需创建临时表存储内层查询结果，查询完再删除，增加CPU和IO资源消耗，易产生慢查询。JOIN操作效率也较低，尤其数据量大时，性能难保证。
  • 索引失效：子查询可能使索引失效，MySQL会将查询转为联接执行，子查询不能先执行，若外表大，性能受影响。
  • 查询优化器复杂度：子查询影响查询优化器判断，致执行计划不够优化。相比之下，联表查询更易被优化器理解和处理。
  • 数据传输开销：子查询可能致大量不必要数据传输，每个子查询都需将结果返回给主查询。而联表查询可通过一次查询返回所有所需数据，减少数据传输开销。
  • 维护成本：使用JOIN写的SQL语句，在修改表schema时较复杂，成本大，尤其系统大时，不易维护。

解决方案

针对这些问题，可采取以下解决方案：

  • 应用层关联：在业务层单表查询出数据后，作为条件给下一个单表查询，减少数据库层负担。
  • 使用IN代替子查询：若子查询结果集小，可用“IN”操作符查询，数据量小时，查询效率更高。
  • 使用WHERE EXISTS：WHERE EXISTS比“IN”更好，它检查子查询是否返回结果集，能明显提高查询速度。
  • 改写为JOIN：用JOIN查询替代子查询，无需建立临时表，速度快，若查询中用索引，性能更好。

优化案例

案例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查询性能，特别是在处理子查询时。以下是一些额外的优化建议：

  1. 创建合适的索引：经常用于WHERE和JOIN的字段应建立索引，避免在低选择性的字段上建立索引（如性别字段）。
  2. 避免索引失效的情况：使用函数计算的字段不会使用索引，如SELECT * FROM orders WHERE YEAR(order_date) = 2023;应优化为SELECT * FROM orders WHERE order_date >= '2023-01-01';。
  3. 组合索引的最左前缀法则：确保查询条件从组合索引的最左列开始。
  4. 使用EXPLAIN分析查询执行计划：通过EXPLAIN关键字可以帮助我们了解查询的执行计划，从而发现性能瓶颈。
  5. 优化查询语句：避免使用SELECT *，使用LIMIT限制返回行数，重写子查询为JOIN。
  6. 合理调整Join Buffer：在无索引或索引不可用的情况下，Join Buffer是优化Block Nested-Loop Join的关键，其大小直接影响外层表加载的行数和内层表的扫描效率。

通过这些优化策略，可以显著提升MySQL查询性能，改善用户体验。

标签：customer,优化,查询,MySQL,WHERE,id,SELECT,原因
From： https://blog.csdn.net/2401_85373732/article/details/145099840

相关文章

  • java校园网学生成绩查询系统论文+源码 2025毕设
    本系统（程序+源码）带文档lw万字以上 文末可获取一份本项目的java源码和数据库参考。系统程序文件列表开题报告内容一、研究背景随着校园规模的不断扩大和教育信息化的快速发展，校园网内的数据量急剧增加，其中学生成绩相关信息的管理和查询成为一个重要的需求领域。传统的成绩......
  • （免费送源码）计算机毕业设计原创定制：Java+springboot+MySQL springboot 第三方游戏账号
    摘要本论文主要论述了如何使用JAVA语言开发一个springboot第三方游戏账号交易平台，本系统将严格按照软件开发流程进行各个阶段的工作，采用springboot+vue.js相结合框架，采用B/S架构，面向对象编程思想进行项目开发。在引言中，作者将论述第三方游戏账号交易平台的当前背景以及系统......
  • （免费送源码）计算机毕业设计原创定制：Java+ssm+MySQL 基于SSM的游戏论坛设计与实现
     摘 要本论文主要论述了如何使用SSM框架开发一个游戏论坛，将严格按照软件开发流程进行各个阶段的工作，采用B/S架构JAVA技术，面向对象编程思想进行项目开发。在引言中，作者将论述游戏论坛的当前背景以及系统开发的目的，后续章节将严格按照软件开发流程，对系统进行各个阶段分析设计......
  • （免费送源码）计算机毕业设计原创定制：Java+springboot+MySQL 物流车辆管理系统的设计与实
     摘要由于数据库和数据仓库技术的快速发展，物流车辆管理系统建设越来越向模块化、智能化、自我服务和管理科学化的方向发展。物流车辆管理系统对处理对象和服务对象，自身的系统结构，处理能力，都将适应技术发展的要求发生重大的变化。物流车辆管理系统除了具有共享系统的全部功能......
  • 【Elasticsearch】 复合查询
    ......
  • MySQL 16 章——变量、流程控制和游标
    一、变量在MySQL数据库的存储过程和存储函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据在MySQL数据库中，变量分为系统变量和用户自定义变量（1）系统变量1.1.1系统变量分类变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务，生成MySQL服务实......
  • 基于PHP的MYSQL注入（2）
    符号拼接由于网站开发者对数据类型或者对sql语句的写法（框架）导致1、数字型（无符号干扰）select*fromuserswhereid=$id;2、字符型（不光是单引号，也可能是双引号或者其他）select*fromuserswhereid='$id';3、搜索型（多符号干扰）select*fromuserswhereidlike'%$......
  • ⭐MySQL的底层原理与架构
    前言了解MySQL的架构和原理对于很多的后续很多的操作会有很大的帮助与理解。并且很多知识都与底层架构相关联。了解MySQL架构通过上面的架构图可以得知，Server层中主要由连接器、查询缓存、解析器/分析器、优化器、执行器几部分组成的，下面将主要描述下这几部分。1、连......
  • 前端必知必会-Node.js连接MySQL
    文章目录Node.jsMySQLMySQL数据库安装MySQL驱动程序创建连接查询数据库总结Node.jsMySQLNode.js可用于数据库应用程序。最流行的数据库之一是MySQL。MySQL数据库为了能够试验代码示例，您应该在计算机上安装MySQL。您可以在https://www.mysql.com/down......
  • Prometheus 是一个开源的监控和报警工具，主要用于收集、存储和查询来自不同服务和应用
    Prometheus是什么？Prometheus是一个开源的监控和报警工具，主要用于收集、存储和查询来自不同服务和应用程序的时间序列数据（如CPU使用率、内存消耗、网络流量等）。它特别适合用于微服务架构下的监控，因为它支持多种集成方式，并能够处理大规模的、高频的数据。Prometheus具有以下主......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99