MySQLNotes

第 13 章：子查询与物化

zjc 于 2026-01-13 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 子查询让 SQL 更接近业务语义，但也可能引入重复执行、临时表和不可下推条件。理解优化器如何改写、物化子查询，才能放心使用现代 MySQL 的表达能力。

13.1 子查询类型回顾

-- 标量子查询
SELECT (SELECT MAX(price) FROM products) AS max_price;

-- 列子查询
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders);

-- 行子查询
SELECT *
FROM products
WHERE (category, price) = ('BOOK', 79.00);

-- 派生表
SELECT t.category
FROM (
  SELECT category, COUNT(*) AS cnt
  FROM products
  GROUP BY category
) t;

-- 相关子查询
SELECT u.username
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

执行方式取决于是否相关、结果大小、索引可用性和 MySQL 版本。

13.2 相关子查询

相关子查询引用外层列：

EXPLAIN
SELECT o.order_no
FROM orders o
WHERE (
  SELECT COUNT(*)
  FROM order_items i
  WHERE i.order_id = o.id
) > 2;

概念上每条订单都要执行一次 COUNT。优化器可能改写或下推，但要重点看执行计划。

更清晰的写法：

WITH item_stats AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
)
SELECT o.order_no
FROM orders o
JOIN item_stats s ON s.order_id = o.id
WHERE s.item_count > 2;

13.3 EXISTS 与 IN

查询有订单用户：

SELECT *
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

SELECT *
FROM users u
WHERE u.id IN (
  SELECT o.user_id
  FROM orders o
);

现代 MySQL 会尝试将符合条件的 IN / EXISTS 改写为 Semijoin。选择建议：

  1. 两边都写执行计划验证；
  2. 关联列必须有索引；
  3. 反向查询优先 NOT EXISTS；
  4. NOT IN 必须排除 NULL；
  5. 不必执着于语法，看实际计划。

13.4 NOT IN 的 NULL 陷阱

以下查询结果为空：

SELECT 1
WHERE 1 NOT IN (2, NULL);

因为 1 <> NULL 是 UNKNOWN。

风险写法：

SELECT *
FROM users u
WHERE u.id NOT IN (
  SELECT o.user_id
  FROM orders o
);

如果 orders.user_id 有 NULL，所有行都可能消失。

修正：

SELECT *
FROM users u
WHERE u.id NOT IN (
  SELECT o.user_id
  FROM orders o
  WHERE o.user_id IS NOT NULL
);

推荐：

SELECT *
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

13.5 派生表合并与物化

派生表有两种处理方式：

方式 说明
Merge 把派生表合并到外层查询，类似视图展开
Materialization 先执行派生表并生成临时表，再参与外层查询

可合并示例：

SELECT t.user_id
FROM (
  SELECT user_id, status
  FROM orders
  WHERE status = 'PAID'
) t
WHERE t.user_id = 1001;

优化器可以直接下推 user_id=1001 和 status='PAID'。

可能物化示例：

SELECT t.user_id, t.order_count
FROM (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) t
WHERE t.order_count >= 3;

聚合必须先完成，外层 order_count 条件无法直接下推到聚合前。

13.6 CTE 是否只执行一次

非递归 CTE：

WITH paid_orders AS (
  SELECT id, user_id, total_amount
  FROM orders
  WHERE status = 'PAID'
)
SELECT COUNT(*)
FROM paid_orders
WHERE user_id = 1001;

优化器可能合并 CTE 并下推条件。但当 CTE 被多处引用或包含聚合、窗口、LIMIT 时，可能物化。

查看：

EXPLAIN FORMAT=TREE
WITH user_stats AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY user_id
)
SELECT *
FROM user_stats
WHERE cnt > 3;

不要假设 CTE 一定只执行一次，也不要假设一定只执行多次。以执行计划为准。

13.7 递归 CTE

组织树：

CREATE TABLE org_nodes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id BIGINT UNSIGNED NULL,
    node_name VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO org_nodes (id, parent_id, node_name) VALUES
(1, NULL, 'CEO'),
(2, 1, 'Tech VP'),
(3, 1, 'Sales VP'),
(4, 2, 'DBA Team'),
(5, 2, 'Backend Team');

向下遍历：

WITH RECURSIVE org_tree AS (
  SELECT id, parent_id, node_name, 1 AS lvl,
         CAST(CONCAT('/', id) AS CHAR(1000)) AS path
  FROM org_nodes
  WHERE parent_id IS NULL

  UNION ALL

  SELECT n.id, n.parent_id, n.node_name, t.lvl + 1,
         CONCAT(t.path, '/', n.id)
  FROM org_nodes n
  JOIN org_tree t ON n.parent_id = t.id
)
SELECT *
FROM org_tree
ORDER BY path;

防环措施：

  1. 递归深度限制；
  2. 路径中检查是否重复；
  3. 写入前禁止环；
  4. 后台任务校验层级；
  5. 超过一定层级改用闭包表或图模型。

设置递归深度：

SET SESSION cte_max_recursion_depth = 100;

13.8 LATERAL 子查询

MySQL 8.0.22+ 支持 LATERAL 派生表：

SELECT
  u.username,
  latest.order_no,
  latest.created_at
FROM users u
JOIN LATERAL (
  SELECT o.order_no, o.created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 1
) latest ON TRUE
WHERE u.status = 1;

适合“每个外行取 Top N”。必须确保内层查询有高效索引，例如：

KEY idx_user_created (user_id, created_at)

如果外层用户很多且每个内层查询昂贵，LATERAL 会放大成本。

13.9 子查询优化策略

  1. 能用 JOIN 表达的简单集合关系，优先写清晰 JOIN；
  2. 相关子查询看是否可用聚合表替代；
  3. IN / EXISTS 用执行计划对比；
  4. 反向关系使用 NOT EXISTS；
  5. CTE 命名复杂逻辑；
  6. 聚合派生表先过滤基础数据；
  7. 小心 LIMIT、窗口、UNION 导致物化；
  8. 大临时表要评估磁盘和内存；
  9. 报表子查询迁移到只读库或 OLAP；
  10. 确认结果集语义没有因改写改变。

13.10 物化临时表观察

查看临时表状态：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW SESSION STATUS LIKE 'Created_tmp%';

相关参数：

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

临时表超过内存限制后会落盘。MySQL 8.0 使用 TempTable 引擎和 InnoDB 临时表空间：

SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';
SHOW VARIABLES LIKE 'temptable_max_ram';

发现大量磁盘临时表时：

  1. 检查 GROUP BY / DISTINCT / UNION；
  2. 减少 SELECT 字段；
  3. 增加过滤条件；
  4. 优化索引排序；
  5. 拆分 SQL；
  6. 引入预聚合；
  7. 迁移分析查询。

13.11 子查询改写案例

案例一：每用户最近订单

相关子查询：

SELECT o.*
FROM orders o
WHERE o.id = (
  SELECT MAX(i.id)
  FROM orders i
  WHERE i.user_id = o.user_id
);

窗口函数：

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY o.user_id
           ORDER BY o.created_at DESC, o.id DESC
         ) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

LATERAL：

SELECT u.id, u.username, latest.order_no
FROM users u
JOIN LATERAL (
  SELECT o.order_no
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 1
) latest ON TRUE;

以数据量和执行计划选择。

案例二：统计在群但未下单用户

SELECT g.user_id
FROM group_members g
WHERE g.group_id = 100
  AND NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = g.user_id
      AND o.created_at >= '2026-08-01'
  );

案例三：巨大 IN 列表

应用拼出十万元素的 IN 会导致：

  1. SQL 过长；
  2. 解析成本高；
  3. 网络传输大；
  4. 优化器估算困难。

改法：

  1. 分批查询；
  2. 使用临时表；
  3. 先落筛选结果；
  4. 使用 JOIN；
  5. 改为按维度范围查询。

本章小结

子查询的执行形态由优化器决定，可能是合并、物化、Semijoin 或相关执行。现代 MySQL 对 IN 和 EXISTS 的优化较好，但必须通过执行计划验证。NOT IN 的 NULL 陷阱、派生表物化、递归 CTE 防环和 LATERAL 索引依赖，是子查询实战中最需要关注的点。

思考题

  1. 什么是相关子查询？它的风险是什么？
  2. 派生表 Merge 和 Materialization 有什么区别？
  3. 为什么 NOT EXISTS 通常比 NOT IN 更安全？
  4. CTE 是否一定只执行一次？如何验证？
  5. 将一个三层嵌套子查询改写为 CTE 或 JOIN，并比较执行计划。