4、查询重写技巧：子查询优化、JOIN优化、OR条件改写、聚合查询优化

各位同学，咱们接着聊。上一章讲了慢查询的定位和基础分析，这一章咱们来点真功夫——查询重写。

说白了，很多时候SQL写得不好，不是数据库不行，是咱们没把意图表达清楚。我见过太多开发同学，上来就是一顿子查询嵌套，看得我头皮发麻。今天我就把压箱底的重写技巧掏出来，一个一个讲透。

4.1 子查询优化：能不嵌套就别嵌套

先说说子查询。很多新手喜欢这么写：

SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers 
    WHERE status = 'vip'
);

这段代码逻辑上没问题。但性能上呢？MySQL 5.6之前，这种写法会对外层表的每一行都执行一次子查询。你想想看，如果orders表有100万行，子查询就得跑100万次。这不是要命吗？

我个人习惯，遇到这种场景，直接改成JOIN：

SELECT o.* 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'vip';

改完之后，数据库只需要做一次关联查询，效率高得多。我在项目中遇到过好几次，改完这个写法，查询时间从3秒降到了0.1秒。

小技巧： 如果子查询里用了 DISTINCT 或者 GROUP BY，改成 JOIN 后记得检查结果是否一致。有时候子查询去重了，JOIN 反而会多出重复行。

还有一种情况，是关联子查询。比如查每个分类下最新的一条商品：

SELECT * FROM products p1
WHERE p1.created_at = (
    SELECT MAX(created_at) 
    FROM products p2 
    WHERE p2.category_id = p1.category_id
);

这种写法，每行都要算一次MAX。我建议改成派生表的方式：

SELECT p.* 
FROM products p
JOIN (
    SELECT category_id, MAX(created_at) as max_time
    FROM products
    GROUP BY category_id
) t ON p.category_id = t.category_id AND p.created_at = t.max_time;

嗯，这里要注意：派生表会生成临时表，如果数据量特别大，记得给临时表加索引。否则内存撑不住。

4.2 JOIN优化：小表驱动大表，索引要到位

JOIN查询是重灾区。我见过最离谱的，是五张表全连在一起，每张表几百万行，跑一次查询半小时。

JOIN优化的核心原则就一条：小表驱动大表。

什么意思呢？比如你查订单和用户：

SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01';

如果orders表有100万行，customers表有10万行。那MySQL会先扫描orders表，再逐行去customers表匹配。这就是大表驱动小表，效率低。

更好的做法是，先过滤出小结果集，再去关联大表：

SELECT * FROM (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
) o
JOIN customers c ON o.customer_id = c.id;

当然，更直接的办法是确保JOIN字段上有索引。我曾经接手过一个项目，查询慢得不行，一查发现JOIN字段上连索引都没有。加上索引后，查询时间从15秒降到了0.5秒。

JOIN类型 适用场景 索引要求
INNER JOIN 两边都有匹配数据 两边JOIN字段都要索引
LEFT JOIN 左表数据必须全保留 右表JOIN字段必须索引
STRAIGHT_JOIN 强制指定驱动表 驱动表要小，被驱动表要索引
避坑指南： 我曾经犯过一个错，在LEFT JOIN的右表上没加索引，结果查询跑了20分钟。后来加了索引，秒出结果。记住：LEFT JOIN的右表，JOIN字段一定要有索引。

4.3 OR条件改写：别让优化器犯难

OR条件，是优化器最头疼的东西。比如：

SELECT * FROM users 
WHERE name = '张三' OR email = 'zhangsan@example.com';

如果name和email上都有索引，MySQL可能会选择全表扫描。为什么？因为OR条件需要合并两个索引的结果集，这个操作成本很高。

我建议改成UNION：

SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'zhangsan@example.com';

这样每个查询都能用到索引，然后UNION去重。效率提升很明显。

还有一种情况，是OR条件里包含范围查询：

SELECT * FROM products 
WHERE price < 100 OR category = '电子';

这种写法，索引基本废了。我建议拆成两个查询，或者用IN代替OR（如果条件固定的话）。

小技巧： 如果OR条件里的字段是同一个，比如 status = 1 OR status = 2，直接改成 IN (1, 2)。IN查询可以用到索引，OR不行。

4.4 聚合查询优化：先过滤，后聚合

聚合查询，最常见的问题是数据量太大。比如统计每个月的订单总额：

SELECT MONTH(created_at), SUM(amount)
FROM orders
GROUP BY MONTH(created_at);

如果orders表有1000万行，这个查询会把所有数据都扫描一遍。你想想看，有必要吗？

我建议先过滤，再聚合。比如只查最近一年的数据：

SELECT MONTH(created_at), SUM(amount)
FROM orders
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY MONTH(created_at);

这样扫描的数据量大大减少。我在项目中遇到过，一个聚合查询从30秒降到了2秒。

还有一个坑，是GROUP BY的字段没有索引。比如：

SELECT category_id, COUNT(*) 
FROM products
GROUP BY category_id;

如果category_id上没有索引，MySQL会先全表扫描，再用临时表做分组。我建议给category_id加上索引，或者用覆盖索引来优化。

另外，HAVING子句也要注意。能用WHERE过滤的，就别用HAVING：

-- 不推荐
SELECT category_id, COUNT(*) as cnt
FROM products
GROUP BY category_id
HAVING cnt > 100;

-- 推荐
SELECT category_id, COUNT(*) as cnt
FROM products
WHERE price > 0
GROUP BY category_id
HAVING cnt > 100;

WHERE在聚合前过滤，HAVING在聚合后过滤。能提前过滤的，一定要提前。

核心总结： 查询重写，本质上就是帮优化器做决策。子查询改JOIN、OR改UNION、先过滤后聚合——这些技巧的核心思想，都是减少数据扫描量，让索引发挥作用。

好了，这一章的内容就到这里。下一章咱们聊聊索引设计，那才是慢查询治理的终极武器。到时候我会分享一些实战案例，保证让你大开眼界。

← 上一章 📖 返回目录 下一章 →