派生表与性能瓶颈

在关系型数据库开发中，开发者经常利用子查询在 FROM 子句中构建临时结果集，这种结构被称为派生表（Derived Table）。虽然它能有效拆解复杂的 SQL 逻辑，但往往会引发隐式的临时表物化（Materialization），从而带来额外的磁盘 I/O 和内存消耗。为了突破这一性能瓶颈，MySQL 优化器引入了派生表合并（Derived Table Merge）机制，通过将子查询逻辑上推到外层查询来消除临时表的创建。

合并优化的核心机制

派生表合并的本质是查询树的重构。优化器会尝试将内部子查询的表连接、过滤条件以及投影列直接"展平"并融入外部查询中。

触发合并的条件

• 简单视图或子查询：仅包含基础的表连接、列映射和简单的 WHERE 过滤，通常会被无条件合并。
• 包含聚合或分组的复杂查询：如果子查询中包含 GROUP BY、DISTINCT 或聚合函数，优化器在满足特定代数等价性条件时，会将分组和聚合操作上推到外层查询，从而实现逻辑合并。
• 阻断合并的因素：若子查询包含 LIMIT、ORDER BY（且外层不依赖其排序）、UNION 等破坏关系代数等价性的操作，优化器将放弃合并，转而采用物化策略。

基础重构示例

假设我们需要统计各个部门的项目总预算。常规写法可能会在子查询中预先完成聚合：

SELECT d.dept_name, p_summary.total_budget
FROM departments d
INNER JOIN (
    SELECT dept_id, SUM(budget) AS total_budget
    FROM projects
    GROUP BY dept_id
) p_summary ON d.id = p_summary.dept_id;

经过优化器的合并重写后，内部的 GROUP BY 和聚合函数被提升至外层，消除了对 projects 表的全表扫描和临时表构建：

SELECT d.dept_name, SUM(p.budget) AS total_budget
FROM departments d
INNER JOIN projects p ON d.id = p.dept_id
GROUP BY d.id, d.dept_name;

多层嵌套查询的展平实践

在实际业务中，派生表往往存在多层嵌套。手动或通过 ORM 生成的复杂 SQL 需要被彻底展平以释放优化器的能力。

嵌套结构分析

以下查询旨在统计每位作者已发布文章的总字数，其中包含了过滤层和聚合层的双重嵌套：

SELECT a.author_name, stats.total_words
FROM authors a
INNER JOIN (
    SELECT art.author_id, SUM(art.word_count) AS total_words
    FROM (
        SELECT author_id, word_count
        FROM articles
        WHERE publish_status = 'published'
    ) art
    GROUP BY art.author_id
) stats ON a.id = stats.author_id;

逻辑上推与重写

通过解除嵌套，我们将数据过滤（WHERE）和聚合计算（SUM、GROUP BY）统一整合到主查询流中：

SELECT a.author_name, SUM(art.word_count) AS total_words
FROM authors a
INNER JOIN articles art ON a.id = art.author_id
WHERE art.publish_status = 'published'
GROUP BY a.id, a.author_name;

重构与优化的关键考量

警惕过度展平引发的性能退化

合并派生表并非绝对的"银弹"。在某些多表关联且数据量分布极不均匀的场景下，提前聚合（即保留派生表物化）反而能大幅减少外层 JOIN 的数据行数。盲目展平可能导致中间结果集膨胀，甚至引发笛卡尔积效应。因此，重构前必须结合数据基数（Cardinality）进行评估。

利用执行计划验证优化路径

通过 EXPLAIN 语句可以直观地观察优化器是否成功执行了合并操作。如果 select_type 中不再出现 DERIVED，且 table 列直接显示了底层物理表，则说明合并已生效。

EXPLAIN 
SELECT a.author_name, SUM(art.word_count) AS total_words
FROM authors a
INNER JOIN articles art ON a.id = art.author_id
WHERE art.publish_status = 'published'
GROUP BY a.id, a.author_name;

配套索引设计的调整

查询结构改变后，原有的索引可能失效。针对展平后的查询，需要重新设计复合索引以覆盖过滤和连接条件。例如，为上述文章统计查询建立覆盖索引：

CREATE INDEX idx_articles_status_author ON articles(publish_status, author_id, word_count);