程序员求职经验分享与学习资料整理平台

网站首页 > 文章精选 正文

MySQL实战:聊聊子查询 vs 联表查询应该如何选择?

balukai 2025-07-10 13:11:42 文章精选 2 ℃


当子查询成为"甜蜜的陷阱"

"小王,你的报表查询怎么又超时了?"凌晨两点,运维同事的夺命连环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解决。但也要注意:

  1. 小结果集的EXISTS可能优于JOIN
  2. 临时表虽好,要警惕内存消耗
  3. 永远用EXPLAIN验证执行计划


互动问答

  1. 你的系统中是否还有"SELECT * FROM A WHERE id IN (SELECT ...)"这样的查询?用EXPLAIN查看它的type列是否是DEPENDENT SUBQUERY?
  2. 尝试将一个关联子查询改写为JOIN,记录优化前后的执行时间差异,欢迎在评论区分享你的战绩!
最近发表
标签列表