派生表是 MySQL 查询优化的一种机制，当查询中包含子查询且子查询结果需要进一步处理时，MySQL 会将子查询的结果视为一个临时表，这个临时表就是派生表。
派生表的常见来源
1、JOIN 子查询 这是派生表最常见的场景：
SELECT log.*
FROM main_table log
JOIN (
SELECT id, MAX(date) AS max_date
FROM sub_table
GROUP BY id
) derived_table
ON log.id = derived_table.id;
原因：子查询的结果需要和主表 main_table 进行关联，MySQL 会将子查询的结果存储为一个临时派生表。
2、FROM 子查询 即使没有 JOIN，直接在 FROM 子句中使用子查询，也会生成派生表：
SELECT derived.id, derived.max_date
FROM (
SELECT id, MAX(date) AS max_date
FROM sub_table
GROUP BY id
) derived;
原因：子查询的结果需要作为主查询的输入，所以 MySQL 生成了派生表 derived，并用它作为一个“虚拟表”供主查询使用。
3、UNION 查询 当使用 UNION 将两个结果集合并时，每个分支的结果可能被视为派生表:
SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;
原因：MySQL 会对 table1 和 table2 的查询分别执行，结果存储在临时表中，然后合并两个表的内容。
4、视图（VIEW） 查询视图时，如果视图的定义中包含复杂的子查询或计算，MySQL 可能会将视图内容作为派生表处理：
CREATE VIEW my_view AS
SELECT id, MAX(date) AS max_date
FROM sub_table
GROUP BY id;
SELECT * FROM my_view WHERE max_date > ‘2024-01-01’;
5、CTE（公共表表达式，WITH 子句，MySQL 8.0+） 使用 WITH 定义的临时结果集，也可能被当作派生表处理：
WITH temp AS (
SELECT id, MAX(date) AS max_date
FROM sub_table
GROUP BY id
)
SELECT *
FROM temp
WHERE max_date > ‘2024-01-01’;
原因：WITH 的定义等同于一个子查询，MySQL 会将其作为临时派生表处理。
派生表的作用与影响
作用：
- 将复杂查询分解为更简单的部分。
- 为主查询提供一个固定的“中间结果”。
- 避免重复计算（尤其是子查询中含有聚合操作时）。
影响：
- 如果派生表的结果集较大，可能占用大量内存或磁盘空间。
- 没有索引的派生表在主查询中被访问时，查询性能可能降低。
如何减少派生表的使用
如果发现派生表影响性能，可以通过以下方法优化：
1、避免不必要的子查询 将子查询逻辑合并到主查询中，例如通过窗口函数替代子查询：
SELECT id, date
FROM (
SELECT id, date, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
FROM sub_table
) temp
WHERE rn = 1;
2、使用索引优化派生表的访问 确保派生表中关键字段有合适的索引，减少主查询匹配的代价。
3、使用临时表替代派生表 对于复杂查询，可以将子查询结果存储到显式的临时表中：
CREATE TEMPORARY TABLE temp AS
SELECT id, MAX(date) AS max_date
FROM sub_table
GROUP BY id;
SELECT *
FROM temp
WHERE max_date > ‘2024-01-01’;