MySQL 派生表和临时表区别

派生表（Derived Table）

定义：

在查询的执行过程中，MySQL 将子查询的结果作为一个临时的虚拟表，用于进一步查询处理。

特性：

• 存在于查询执行的生命周期中。
• 只在内存中或磁盘上临时存储，不是显式的表。
• 自动创建和销毁，不需要手动管理。

使用方式：

派生表通常通过子查询产生，用于主查询的进一步处理。例如：

SELECT *
FROM (
SELECT id, MAX(add_time) AS latest_time
FROM visit_log
GROUP BY id
) derived_table
WHERE latest_time > ‘2024-01-01’;

特点：

• 子查询的结果被作为一个“虚拟表”（即派生表）。
• 不需要显式声明，MySQL自动创建。
• 通常出现在 FROM 子句中，也可能出现在 JOIN 子查询中。

临时表（Temporary Table）

定义：

用户显式创建的一种临时表，存储临时数据，通常用于复杂查询或数据处理中。

特性：

• 必须显式创建（CREATE TEMPORARY TABLE）。
• 存在于会话（Session）生命周期中，当前会话结束后自动销毁。
• 数据可以被多次访问和操作。

使用方式：

临时表需要显式创建和使用。例如：

CREATE TEMPORARY TABLE temp_table AS
SELECT id, MAX(add_time) AS latest_time
FROM visit_log
GROUP BY id;

SELECT *
FROM temp_table
WHERE latest_time > ‘2024-01-01’;

特点：

• 用户手动定义。
• 数据可以被多次查询或更新。
• 临时表支持索引、事务等操作。

索引支持

派生表

• 索引限制：派生表不支持索引，MySQL 对其操作通常是全表扫描。
• 性能影响：如果派生表的数据量较大，缺少索引可能导致主查询性能下降。

临时表

• 索引支持：临时表支持显式定义索引，提升查询效率。
• 操作灵活性：可以在临时表中增删改数据，也可以添加主键或唯一约束。

性能对比

派生表

• 优点：
  • 自动管理，不需要额外的存储空间声明。
  • 对于简单的临时数据处理，派生表更加快捷。
• 缺点：
  • 不支持索引，处理大数据量时性能可能较差。
  • 数据只能在当前查询中访问，无法重复使用。

临时表

• 优点：
  • 支持多次访问，适用于复杂逻辑或需要多步处理的数据。
  • 支持索引优化，适合大数据量的操作。
• 缺点：
  • 需要显式创建和管理。
  • 存储空间占用可能较大，尤其是数据量较大时。