网站首页 > 文章精选 正文
当子查询成为"甜蜜的陷阱"
"小王,你的报表查询怎么又超时了?"凌晨两点,运维同事的夺命连环call让我瞬间清醒。打开慢日志一看,一个嵌套了5层子查询的统计SQL竟扫描了上亿条数据。原来这个"看似优雅"的写法,在数据量破百万后彻底暴露了性能缺陷。这让我深刻意识到:子查询是SQL中的双刃剑,用得好能简化逻辑,用不好就是性能杀手。
今天带大家深入子查询与联表查询的性能战场,通过真实案例,揭秘哪些子查询必须改写为JOIN,并提供可复用的优化套路。文末还有互动彩蛋,帮你大家检验学习成果!
一、必须改写的三类子查询(附性能对比)
1. 关联子查询:循环嵌套的"时间黑洞"
典型特征:子查询中引用了外部表的列,导致每行数据触发一次子查询执行。
-- 原始低效写法(耗时42秒)
SELECT username
FROM users
WHERE id IN (
SELECT user_id
FROM orders
WHERE users.reg_time = orders.create_date -- 关联条件
);
-- 优化为JOIN(耗时0.12秒)
SELECT DISTINCT u.username
FROM users u
JOIN orders o ON u.id = o.user_id
AND u.reg_time = o.create_date;
执行计划对比:
- 原查询:DEPENDENT SUBQUERY类型,users表373行 × orders表15万次扫描
- 优化后:INDEX MERGE,仅扫描索引
性能数据:
数据量 | 原查询耗时 | 优化后耗时 |
users:1万 | 41秒 | 0.8秒 |
orders:100万 | 无法完成 | 2.3秒 |
2. IN子查询:隐形的全表扫描器
高危场景:当IN子查询返回结果集较大时,非常有可能触发全表扫描。
-- 危险写法(orders表无索引时耗时8秒)
SELECT *
FROM products
WHERE id IN (
SELECT product_id
FROM orders
WHERE status = 'paid'
);
-- 优化方案:临时表+JOIN(耗时0.3秒)
CREATE TEMPORARY TABLE tmp_paid_products
SELECT DISTINCT product_id
FROM orders
WHERE status = 'paid';
SELECT p.*
FROM products p
JOIN tmp_paid_products tmp ON p.id = tmp.product_id;
关键点:
- 临时表自动创建索引(默认使用MEMORY引擎)
- 避免对products表全表扫描
3. SELECT列表中的子查询:重复计算的"性能刺客"
常见误区:在SELECT中通过子查询获取关联信息,导致N次额外查询。
-- 低效写法(每行触发一次子查询)
SELECT
order_id,
(SELECT username FROM users WHERE users.id = orders.user_id) AS name
FROM orders;
-- 优化为LEFT JOIN(性能提升极大)
SELECT
o.order_id,
u.username
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;
执行计划变化:
- 原查询:DEPENDENT SUBQUERY,每行订单触发用户表查询
- 优化后:单次索引扫描完成关联
二、四大黄金改写技巧
技巧1:关联条件上移——给子查询"松绑"
适用场景:WHERE中的子查询包含外部表关联条件。
-- 原始嵌套
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM salaries
WHERE dept = e.dept -- 关联条件在子查询中
);
-- 优化为JOIN+GROUP BY
SELECT e.*
FROM employees e
JOIN (
SELECT dept, AVG(salary) avg_salary
FROM salaries
GROUP BY dept
) s ON e.dept = s.dept
WHERE e.salary > s.avg_salary;
优势:
- 子查询仅执行1次聚合计算
- 利用覆盖索引加速
技巧2:EXISTS魔法——小表驱动大表
最佳实践:当需要判断存在性时,优先使用EXISTS而非IN。
-- 低效IN查询
SELECT *
FROM customers
WHERE id IN (
SELECT customer_id
FROM big_orders
WHERE amount > 1000
);
-- 高效EXISTS改写
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM big_orders
WHERE customer_id = c.id
AND amount > 1000
);
性能对比:
- 当customers表1万行,big_orders表100万行时:
- IN查询:12秒
- EXISTS:0.8秒
技巧3:派生表封装——复杂查询的"分治法"
适用场景:多层嵌套子查询可拆解为分步计算。
-- 原始复杂嵌套
SELECT *
FROM products
WHERE price > (
SELECT AVG(price)
FROM (
SELECT price
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.vip_level > 3
) t
);
-- 分步优化
CREATE TEMPORARY TABLE vip_avg_price
SELECT AVG(o.price) avg_price
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.vip_level > 3;
SELECT *
FROM products
WHERE price > (SELECT avg_price FROM vip_avg_price);
优势:
- 每步可单独优化
- 避免多层嵌套导致的执行计划混乱
技巧4:巧用索引覆盖——给JOIN装上"涡轮增压"
实战案例:
-- 原始无索引JOIN(耗时3.2秒)
SELECT o.order_no, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;
-- 优化步骤:
-- 1. 为users.id添加主键
-- 2. 为orders.user_id添加索引
-- 3. 使用覆盖索引
ALTER TABLE users ADD INDEX idx_cover(id, name);
-- 优化后耗时:0.07秒
原理:
- 索引覆盖避免回表
- BNLJ算法转为BKA算法
暂时无法在飞书文档外展示此内容
说明:BKA可以理解为BNLJ和MRR的结合,它利用缓存按照主键排序后一次性读取数据,减少了排序的开销,同时也减少了随机I/O操作。
三、总结
通过以上案例可以看到,80%的子查询性能问题可通过改写为JOIN解决。但也要注意:
- 小结果集的EXISTS可能优于JOIN
- 临时表虽好,要警惕内存消耗
- 永远用EXPLAIN验证执行计划
互动问答:
- 你的系统中是否还有"SELECT * FROM A WHERE id IN (SELECT ...)"这样的查询?用EXPLAIN查看它的type列是否是DEPENDENT SUBQUERY?
- 尝试将一个关联子查询改写为JOIN,记录优化前后的执行时间差异,欢迎在评论区分享你的战绩!
猜你喜欢
- 2025-07-10 失业程序员复习python笔记——mysql
- 2025-07-10 MySQL高性能注意事项简述(高性能mysql重点章节)
- 2025-07-10 MySQL触发器介绍(mysql触发器使用)
- 2025-07-10 2021年超详细的java学习路线总结—纯干货分享
- 2025-07-10 MySQL常见错误及解决方法(mysql常见错误提示及解决方法)
- 2025-07-10 MySQL索引失效场景分析与优化(mysql索引何时失效)
- 2025-07-10 SQL 神操作:用 LEFT JOIN 干掉 NOT IN,查询速度直接飙车!
- 2025-07-10 The entangled power of BRICS(the power of dreams)
- 2025-07-10 谁再在 SQL 中写 in 和 not in,直接走人!
- 2025-07-10 SQL面试题及答案( Top20)(mysql面试题及答案)
- 最近发表
-
- Vue3+Django4全新技术实战全栈项目|高清完结
- 工厂模式+策略模式消除 if else 实战
- 每天一个 Python 库:httpx异步请求,让接口测试飞起来
- 如何高效实现API接口的自动化测试?
- 前端工程化:从“手忙脚乱”到“从容协作”的进化记
- 使用C#创建服务端Web API(c#开发web服务器)
- SpringBoot之旅第四篇-web开发(springboot做web项目)
- 一文读懂SpringMVC(一文读懂新型政策性金融工具)
- Rust Web编程:第十二章 在 Rocket 中重新创建我们的应用程序
- Apache Druid 数据摄取——本地数据和kafka流式数据 一篇文章看懂
- 标签列表
-
- newcoder (56)
- 字符串的长度是指 (45)
- drawcontours()参数说明 (60)
- unsignedshortint (59)
- postman并发请求 (47)
- python列表删除 (50)
- 左程云什么水平 (56)
- 计算机网络的拓扑结构是指() (45)
- 编程题 (64)
- postgresql默认端口 (66)
- 数据库的概念模型独立于 (48)
- 产生系统死锁的原因可能是由于 (51)
- 数据库中只存放视图的 (62)
- 在vi中退出不保存的命令是 (53)
- 哪个命令可以将普通用户转换成超级用户 (49)
- noscript标签的作用 (48)
- 联合利华网申 (49)
- swagger和postman (46)
- 结构化程序设计主要强调 (53)
- 172.1 (57)
- apipostwebsocket (47)
- 唯品会后台 (61)
- 简历助手 (56)
- offshow (61)
- mysql数据库面试题 (57)