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

本文涉及的产品
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
云数据库 Tair(兼容Redis),内存型 2GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 大家好,我是V哥。上周与朋友讨论数据库子查询问题,深受启发。为此,我整理了10个案例,详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例,帮助大家在实际开发中选择更高效的查询方式,提升系统性能。关注V哥,一起探讨技术,欢迎点赞支持!

大家好,我是 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 哥感谢你的支持。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
9天前
|
存储 SQL 关系型数据库
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器,linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时,操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行,导致生产库上部分表被truncate,还有部分表中少量数据被delete。
48 25
|
4月前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
71 3
|
9天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
4月前
|
SQL 关系型数据库 MySQL
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
379 0
案例剖析:MySQL唯一索引并发插入导致死锁!
|
4月前
|
SQL 关系型数据库 MySQL
案例剖析,MySQL共享锁引发的死锁问题!
案例剖析,MySQL共享锁引发的死锁问题!
|
4月前
|
消息中间件 关系型数据库 MySQL
大数据-117 - Flink DataStream Sink 案例:写出到MySQL、写出到Kafka
大数据-117 - Flink DataStream Sink 案例:写出到MySQL、写出到Kafka
379 0
|
6月前
|
SQL 关系型数据库 MySQL
在 MySQL 中使用子查询
【8月更文挑战第12天】
324 0
在 MySQL 中使用子查询
|
2月前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
4月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
339 3

相关产品

  • 云数据库 RDS MySQL 版