什么是衍生表（derived table）？MySQL 8.0 之前 derived_merge 优化的作用？

考察要点

这道题面试官想看你是否深入理解 MySQL 的查询优化器。它不仅考察你对“衍生表”这个基础概念的定义，更核心的是想了解你是否清楚其背后的性能影响（物化），以及 MySQL 是如何通过 derived_merge 这种关键优化来提升查询性能的。这能体现你从“会写 SQL”到“懂 SQL 优化”的进阶能力。

核心概念

1. 衍生表（Derived Table）

衍生表本质上是一个在 FROM 子句中定义的子查询。它返回一个虚拟的、临时的结果集，外层查询可以像操作普通表一样对其进行查询。

• 语法结构：

  sql

  ⧉⛶

   1SELECT ...
   2FROM (subquery) AS alias_name
   3WHERE ...;

• 关键点：

  • 必须为衍生表指定一个别名（alias_name）。
  • 它的生命周期仅限于外层查询的执行期间。

• 数据示例： 假设有 orders 表：

  order_id	user_id	amount
  1	101	200
  2	102	500
  3	101	300
  4	103	800

  查询每个用户的总订单金额，并筛选出总金额大于 400 的用户：

  sql

  ⧉⛶

   1SELECT user_id, total_amount
   2FROM (
   3    SELECT user_id, SUM(amount) AS total_amount
   4    FROM orders
   5    GROUP BY user_id
   6) AS user_totals -- user_totals 就是一个衍生表
   7WHERE total_amount > 400;

  输出：

  user_id	total_amount
  101	500
  103	800

2. 衍生表的物化（Materialization）

在 MySQL 5.6 之前，以及某些特定场景下，优化器处理衍生表的方式是“物化”。

• 定义：物化是指先独立执行 FROM 子句中的子查询，然后将其结果集存储在一个内部的临时表（in-memory 或 on-disk）中。外层查询再对这个临时表进行操作。

• 性能痛点：

  1. I/O 和内存开销：创建和填充临时表本身就有开销，如果结果集很大，可能需要写入磁盘，产生 I/O 成本。
  2. 索引缺失：物化后的临时表上没有任何索引。如果外层查询需要对它进行 JOIN 或 WHERE 过滤，通常只能进行全表扫描，效率极低。
  3. 优化屏障：物化过程将内外查询隔离开，使得优化器无法将外层查询的 WHERE 条件下推到内层查询，也无法进行跨越内外查询的 JOIN 顺序重排。

3. derived_merge 优化

为了解决物化带来的性能问题，MySQL 引入了 derived_merge 优化（MySQL 5.7 默认开启）。

• 定义：derived_merge 是一种将衍生表的定义“合并”或“展开”到外部查询块中的优化策略，从而避免创建临时表。

• 工作原理：优化器会尝试重写查询，将衍生表的 SELECT 列表、WHERE 子句等逻辑直接融入到外层查询中。

• 示例： 考虑以下查询：

  sql

  ⧉⛶

   1-- 原始查询
   2SELECT p.product_name
   3FROM (
   4    SELECT product_id, product_name FROM products WHERE category = 'electronics'
   5) AS p
   6WHERE p.product_id = 123;

  • 物化执行：先执行 SELECT ... FROM products WHERE category = 'electronics'，将所有电子产品存入临时表，然后扫描该临时表查找 product_id = 123。
  • derived_merge 执行：优化器将查询改写为：
    sql

    ⧉⛶

     1-- 逻辑上等价于
     2SELECT product_name
     3FROM products
     4WHERE category = 'electronics' AND product_id = 123;

    这样，优化器可以直接利用 products 表上的 (product_id) 或 (category, product_id) 索引，性能天差地别。

• 无法使用 derived_merge 的场景： 当衍生表包含以下任何一种结构时，优化器会放弃合并，回退到物化策略：

  • 聚合函数 (SUM(), COUNT(), MIN(), MAX() 等)
  • DISTINCT
  • GROUP BY
  • HAVING
  • LIMIT / OFFSET
  • UNION / UNION ALL
  • 窗口函数 (ROW_NUMBER(), etc.)
  • 赋值给用户变量的子查询

  因为这些操作都需要先计算出完整的子查询结果集，其数据粒度和结构已经发生改变，无法简单地与外层查询合并。

• 版本差异：

  • MySQL 5.6：引入了 derived_merge，但默认关闭。
  • MySQL 5.7 & 8.0：derived_merge 默认开启 (optimizer_switch 中的 derived_merge=on)。MySQL 8.0 进一步增强了优化器，但在处理衍生表的基本逻辑上与 5.7 类似。

常见低分回答

1. 混淆概念：将衍生表与所有类型的子查询混为一谈，无法准确说出它特指 FROM 子句中的子查询。
2. 只知其然，不知其所以然：只知道 derived_merge 是个优化，但说不清它优化的目标是“物化”，也解释不了物化为什么慢（如索引缺失、优化屏障）。
3. 回答过于绝对：声称“MySQL 8.0 会自动合并所有衍生表”，忽略了 GROUP BY, LIMIT 等众多无法合并的场景。这表明对优化的边界条件理解不清。
4. 无法解释如何验证：当被问到“你怎么知道 derived_merge 是否生效了？”，无法回答出使用 EXPLAIN 查看执行计划，并观察是否存在 <derivedN> 表。
5. 对 CTE (Common Table Expressions) 的关系模糊：在 MySQL 8.0 中，简单的非递归 CTE 通常被视为衍生表的语法糖，同样适用 derived_merge 优化。如果候选人认为 CTE 是一种完全不同的、必然更优的技术，说明理解有偏差。

工程实践与优化

• 使用 EXPLAIN 分析：这是判断 derived_merge 是否生效的黄金法则。

  • 未合并（物化）：EXPLAIN 的结果中会有一行 select_type 为 DERIVED，其 table 列会显示为 <derivedN>（N 是一个数字）。外层查询的 table 列会引用这个 <derivedN>。
    sql

    ⧉⛶

     1EXPLAIN SELECT user_id FROM (SELECT user_id FROM orders GROUP BY user_id) AS t;
     2-- 输出中会包含 id=2, select_type=DERIVED, table=orders...
     3-- 和 id=1, select_type=SIMPLE, table=<derived2>...

  • 已合并：EXPLAIN 的结果中不会出现 DERIVED 类型和 <derivedN> 表。整个计划看起来就像一个简单的单层查询。
    sql

    ⧉⛶

     1EXPLAIN SELECT product_name FROM (SELECT product_name FROM products) AS p;
     2-- 输出中只有一行，直接访问 products 表

• 使用优化器提示（Optimizer Hints）：在极少数情况下，当优化器做出错误判断时，可以手动控制 derived_merge 行为。

  • 强制不合并：NO_MERGE(alias_name)。当你认为先物化出一个小结果集再进行后续操作更高效时使用。
    sql

    ⧉⛶

     1SELECT /*+ NO_MERGE(p) */ *
     2FROM (SELECT * FROM products WHERE category = 'rare_category') AS p
     3JOIN large_table ON p.id = large_table.product_id;

  • 强制合并：MERGE(alias_name)。
    sql

    ⧉⛶

     1SELECT /*+ MERGE(p) */ * FROM (SELECT * FROM products) AS p;

• 用 CTE 替代：在 MySQL 8.0+ 中，使用 CTE (Common Table Expressions) 通常能让复杂的 SQL 更具可读性。对于简单的 CTE，其优化方式与衍生表相同。

  sql

  ⧉⛶

   1-- 使用 CTE，可读性更好，优化效果通常与衍生表一致
   2WITH user_totals AS (
   3    SELECT user_id, SUM(amount) AS total_amount
   4    FROM orders
   5    GROUP BY user_id
   6)
   7SELECT user_id, total_amount
   8FROM user_totals
   9WHERE total_amount > 400;

常见追问与延伸题目

1. 追问：在什么情况下，你可能会主动禁用 derived_merge 优化？

   • 回答要点：当优化器错误地估计了成本时。一个典型场景是：衍生表通过一个高选择性的 WHERE 条件过滤后，结果集非常小（比如几十行）。而外层查询需要将这个小结果集与一个非常大的表进行 JOIN。此时，如果强制 derived_merge，优化器可能会选择一个糟糕的 JOIN 顺序（例如，先扫描大表）。而如果使用 NO_MERGE 强制物化，可以确保先生成这个极小的临时表，再用它去驱动与大表的 JOIN（类似 Nested Loop Join），此时的性能可能反而更好。

2. 追问：MySQL 8.0 中的 CTE 和衍生表有什么区别？优化器对它们的处理有何不同？

   • 回答要点：
     • 语法和可读性：CTE 使用 WITH 子句定义在查询开头，可以被多次引用，代码结构更清晰，可读性更好。衍生表定义在 FROM 子句中，只能在当前 FROM 子句内使用。
     • 优化处理：对于只被引用一次的非递归 CTE，MySQL 8.0 的优化器会将其视为衍生表，并尝试应用 derived_merge 优化。所以从性能角度看，两者通常是等价的。
     • 多次引用：如果一个 CTE 被多次引用，优化器会选择将其物化，这样只需计算一次，后续引用直接读取物化后的临时表，避免了重复计算。这是 CTE 相较于衍生表的一个优势。

3. 追问：除了 derived_merge，你还知道哪些针对子查询的优化策略？

   • 回答要点：这展示了你知识的广度。
     • IN-to-EXISTS 转换：将 WHERE col IN (SELECT ...) 转换为 WHERE EXISTS (SELECT ...)，以便更好地利用子查询中的索引。
     • 半连接（Semi-Join）：对于 IN/EXISTS 子查询，优化器可以使用多种半连接执行策略，如 Duplicate Weed-out, FirstMatch, LooseScan 等，它们比简单的子查询执行更高效。
     • 子查询展开/扁平化（Subquery Unnesting）：类似于 derived_merge，但作用于 WHERE 子句中的子查询，尝试将子查询的逻辑合并到父查询中。
     • 物化（Materialization）：当子查询无法转换为半连接时，优化器可能会选择物化子查询的结果，并创建索引来加速 JOIN。这在 IN 子查询中很常见。