优化项目实战：mysql如何子查询与联接查询的执行流程 |Duuu笔记
基于真实项目经验的优化实战分享
子查询在WHERE中被重复执行的主因是MySQL 5.6及更早版本缺乏相关子查询优化，解决方法包括改用JOIN、添加索引、启用8.0+的MATERIALIZATION/SEMIJOIN，或使用派生表预聚合。
子查询在 WHERE 中被重复执行怎么办
MySQL 5.6 及更早版本对
WHERE
子句中的相关子查询（correlated subquery）缺乏有效优化，每次外层行扫描都会重新执行子查询，导致性能雪崩。例如：
SELECT * FROM orders o
WHERE o.customer_id IN (
SELECT c.id FROM customers c WHERE c.status = 'active'
);
即使
customers
表很小，若
orders
有百万行，子查询可能被执行百万次。
解决方法不是硬扛，而是让优化器“看懂”你的意图：
用
JOIN
显式替代：把子查询提前物化为临时结果，让优化器有机会使用索引和哈希连接
确保子查询中涉及的列（如
c.status
）有索引；否则全表扫描会放大重复开销
MySQL 8.0+ 支持
MATERIALIZATION
和
SEMIJOIN
优化策略，但需确认
optimizer_switch
中启用：
semijoin=on,materialization=on
LEFT JOIN 后加 WHERE 条件导致逻辑错误
这是最常被忽略的执行顺序陷阱：
ON
先于
WHERE
执行。写成这样：
SELECT o.*, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'active';
表面想查“活跃客户的订单”，实际会把所有
c.status ≠ 'active'
或
c.id IS NULL
的记录过滤掉，
LEFT JOIN
彻底退化为
INNER JOIN
。
正确做法是把过滤条件移到
ON
子句中（仅适用于 LEFT/RIGHT JOIN）：
SELECT o.*, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id AND c.status = 'active';
这样能保留所有
orders
行，同时只关联符合条件的客户。
如果必须在
WHERE
中判断空值（如查“无客户信息的订单”），用
c.id IS NULL
，而非
c.status IS NULL
STRAIGHT_JOIN
可强制连接顺序，但应先通过
EXPLAIN
确认驱动表是否合理
EXPLAIN 输出里出现 “DEPENDENT SUBQUERY” 就该警惕
这个提示说明子查询依赖外层查询字段，无法一次性物化，大概率触发嵌套循环。比如：
SELECT id, (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) item_count
FROM orders o;
EXPLAIN
中该列显示
DEPENDENT SUBQUERY
，且
rows
值会乘以外层行数，估算代价严重失真。
比改写 SQL 更直接的干预方式：
给关联字段加复合索引：
ALTER TABLE order_items ADD INDEX idx_orderid (order_id);
改用派生表（derived table）预聚合：
SELECT o.id, COALESCE(i.item_count, 0) item_count
FROM orders o
LEFT JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
) i ON i.order_id = o.id;
避免在 SELECT 列表中写标量子查询；它无法并行，且 MySQL 不支持标量子查询的物化缓存（直到 8.0.24 才部分支持
SCALAR SUBQUERY
物化）
联接顺序不对会让索引完全失效
MySQL 默认按 FROM 后表顺序作为驱动表（尤其在没有合适索引时），但真正高效的驱动表应该是：过滤后结果集最小、能走索引、且参与 JOIN 的字段区分度高。例如：
SELECT *
FROM large_table l
JOIN small_lookup s ON l.type_id = s.id
WHERE l.created_at > '2024-01-01';
如果
large_table
没有
created_at
索引，优化器可能选
small_lookup
当驱动表，导致全表扫描
large_table
每一行去匹配。
验证和修正的关键动作：
白瓜AI
白瓜AI，一个免费图文AI创作工具，支持 AI 仿写，图文生成，敏感词检测，图片去水印等等。
下载
用
EXPLAIN FORMAT=TREE
（MySQL 8.0+）看真实执行计划树，注意哪张表是
table
下的第一级
用
STRAIGHT_JOIN
强制顺序前，先加索引：
large_table(created_at, type_id)
覆盖 WHERE + JOIN
避免多表 JOIN 时混用
LEFT
和
INNER
——MySQL 可能因语法顺序误判驱动表，显式用括号分组或拆成两个查询更可控
复杂点在于：执行流程优化不是单点调参，而是索引设计、语义表达、优化器能力三者咬合的结果。最容易被忽略的是——你以为在优化 SQL，其实是在教优化器读懂你的业务约束。
