mysql如何优化子查询与联接查询的执行流程-mysql教程-PHP中文网

子查询在where中被重复执行的主因是mysql 5.6及更早版本缺乏相关子查询优化，解决方法包括改用join、添加索引、启用8.0+的materialization/semijoin，或使用派生表预聚合。

mysql如何优化子查询与联接查询的执行流程

子查询在 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” 就该警惕

这个提示说明子查询依赖外层查询字段，无法一次性物化，大概率触发嵌套循环。比如：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

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 每一行去匹配。

验证和修正的关键动作：

用 EXPLAIN FORMAT=TREE（MySQL 8.0+）看真实执行计划树，注意哪张表是 table 下的第一级
用 STRAIGHT_JOIN 强制顺序前，先加索引：large_table(created_at, type_id) 覆盖 WHERE + JOIN
避免多表 JOIN 时混用 LEFT 和 INNER——MySQL 可能因语法顺序误判驱动表，显式用括号分组或拆成两个查询更可控

复杂点在于：执行流程优化不是单点调参，而是索引设计、语义表达、优化器能力三者咬合的结果。最容易被忽略的是——你以为在优化 SQL，其实是在教优化器读懂你的业务约束。