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

2025-01-16 04:50
文章标签 mysql 查询 案例 优化 原因 使用 database

本文主要是介绍MySQL不使用子查询的原因及优化案例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQL不使用子查询的原因及优化案例》对于mysql,不推荐使用子查询,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,本文给大家...

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

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

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

解决方案

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

  • 应用层关联：在业务层单表查询出数据后，作为条件给下一个单表查询，减少数据库层负担。
  • 使用IN代替子查询：若子查询结果集小，可用“IN”操作符查询，数据量小时，查询效率javascript更高。
  • 使用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');

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

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

原始查询：

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

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

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

原始查询：

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

优化方案：

SELECT * FROM orders WHERE customer_id IjavascriptN (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_i编程d 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 * FROMandroid 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查询性能，改善用户体验。

以上就是MySQL不使用子查询的原因及优化案例的详细内容，更多关于MySQL不使用子查询原因的资料请关注China编程(www.chinasem.cn)其它相关文章！

这篇关于MySQL不使用子查询的原因及优化案例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1153079。

相关文章

使用PyQt5编写一个简单的取色器

使用PyQt5编写一个简单的取色器

《使用PyQt5编写一个简单的取色器》：本文主要介绍PyQt5搭建的一个取色器,一共写了两款应用,一款使用快捷键捕获鼠标附近图像的RGB和16进制颜色编码,一款跟随鼠标刷新图像的RGB和16... 目录取色器1取色器2PyQt5搭建的一个取色器，一共写了两款应用，一款使用快捷键捕获鼠标附近图像的RGB和16
阅读更多...
Linux(Centos7)安装Mysql/Redis/MinIO方式

Linux(Centos7)安装Mysql/Redis/MinIO方式

《Linux(Centos7)安装Mysql/Redis/MinIO方式》文章总结：介绍了如何安装MySQL和Redis,以及如何配置它们为开机自启,还详细讲解了如何安装MinIO,包括配置Syste... 目录安装mysql安装Redis安装MinIO总结安装Mysql安装Redis搜索Red
阅读更多...
使用Python实现批量访问URL并解析XML响应功能

使用Python实现批量访问URL并解析XML响应功能

《使用Python实现批量访问URL并解析XML响应功能》在现代Web开发和数据抓取中,批量访问URL并解析响应内容是一个常见的需求,本文将详细介绍如何使用Python实现批量访问URL并解析XML响... 目录引言1. 背景与需求2. 工具方法实现2.1 单URL访问与解析代码实现代码说明2.2 示例调用
阅读更多...
使用SpringBoot创建一个RESTful API的详细步骤

使用SpringBoot创建一个RESTful API的详细步骤

《使用SpringBoot创建一个RESTfulAPI的详细步骤》使用Java的SpringBoot创建RESTfulAPI可以满足多种开发场景,它提供了快速开发、易于配置、可扩展、可维护的优点,尤... 目录一、创建 Spring Boot 项目二、创建控制器类（Controller Class）三、运行
阅读更多...
Python使用Pandas库将Excel数据叠加生成新DataFrame的操作指南

Python使用Pandas库将Excel数据叠加生成新DataFrame的操作指南

《Python使用Pandas库将Excel数据叠加生成新DataFrame的操作指南》在日常数据处理工作中,我们经常需要将不同Excel文档中的数据整合到一个新的DataFrame中,以便进行进一步... 目录一、准备工作二、读取Excel文件三、数据叠加四、处理重复数据（可选）五、保存新DataFram
阅读更多...
Mysql8.0修改配置文件my.ini的坑及解决

Mysql8.0修改配置文件my.ini的坑及解决

《Mysql8.0修改配置文件my.ini的坑及解决》使用记事本直接编辑my.ini文件保存后,可能会导致MySQL无法启动,因为MySQL会以ANSI编码读取该文件,解决方法是使用Notepad++... 目录Myhttp://www.chinasem.cnsql8.0修改配置文件my.ini的坑出现的问题
阅读更多...
MySQL8.0找不到my.ini如何解决

MySQL8.0找不到my.ini如何解决

《MySQL8.0找不到my.ini如何解决》在配置MySQL主从复制时,发现找不到my.ini配置文件,通过检查路径和打开隐藏文件夹,最终在C:ProgramDataMySQLMySQLSer... 目录问题描述解决方法总结问题描述今天在配置mysql主从复制的时候发现，找不到my.ini这个配置文件。
阅读更多...
MySQL中my.ini文件的基础配置和优化配置方式

MySQL中my.ini文件的基础配置和优化配置方式

《MySQL中my.ini文件的基础配置和优化配置方式》文章讨论了数据库异步同步的优化思路,包括三个主要方面：幂等性、时序和延迟,作者还分享了MySQL配置文件的优化经验,并鼓励读者提供支持... 目录mysql my.ini文件的配置和优化配置优化思路MySQL配置文件优化总结MySQL my.ini文件
阅读更多...
使用Java解析JSON数据并提取特定字段的实现步骤(以提取mailNo为例)

使用Java解析JSON数据并提取特定字段的实现步骤(以提取mailNo为例)

《使用Java解析JSON数据并提取特定字段的实现步骤(以提取mailNo为例)》在现代软件开发中,处理JSON数据是一项非常常见的任务,无论是从API接口获取数据,还是将数据存储为JSON格式,解析... 目录1. 背景介绍1.1 jsON简介1.2 实际案例2. 准备工作2.1 环境搭建2.1.1 添加
阅读更多...
MySQL中删除重复数据SQL的三种写法

MySQL中删除重复数据SQL的三种写法

《MySQL中删除重复数据SQL的三种写法》：本文主要介绍MySQL中删除重复数据SQL的三种写法,文中通过代码示例讲解的非常详细,对大家的学习或工作有一定的帮助,需要的朋友可以参考下... 目录方法一：使用 left join + 子查询删除重复数据(推荐)方法二：创建临时表(需分多步执行，逻辑清晰，但会
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2