Skip to content

什么时候会创建临时表

MySQL在多种情况下会创建临时表，这可以分为两大类：由用户显式创建和由MySQL服务器在执行特定SQL语句时隐式（内部）创建。理解这些场景对于数据库性能优化至关重要，因为临时表的创建，尤其是在磁盘上创建时，会消耗额外的I/O和CPU资源。

1. 用户显式创建 (External Temporary Tables)

用户可以根据业务需求，在会话中手动创建临时表。这种临时表仅在当前的数据库连接中可见，当连接关闭时会自动被删除。

使用场景： * 存储复杂查询的中间结果：当需要对一个大的数据集进行多次复杂的查询或处理时，可以先将筛选出的子集数据存入临时表，后续操作直接针对这个小得多的临时表进行，从而提高效率。 * 大数据量筛选：从一个非常大的表中筛选出少量数据进行后续处理。 * 保存临时数据：在存储过程或应用程序中，需要一个临时的地方存放会话期间需要用到的数据。

创建语法：

CREATE TEMPORARY TABLE temp_table_name (
    -- 列定义
);

-- 或者从查询结果直接创建
CREATE TEMPORARY TABLE temp_table_name AS SELECT ...;

2. MySQL隐式创建 (Internal Temporary Tables)

当MySQL无法直接通过索引或其他高效方式来完成某些操作时，它会在后台自动创建内部临时表来辅助处理。 这对用户是透明的，但可以通过EXPLAIN命令的Extra列中是否出现 Using temporary 来判断。

常见触发场景：

  • 排序（ORDER BY）与分组（GROUP BY）操作：

    • 当ORDER BY和GROUP BY子句中的列不一致时。
    • 当ORDER BY或GROUP BY的列来自于连接查询中的非驱动表（非第一张表）时。
    • 当排序或分组的列没有合适的索引可用，或者排序方向与索引顺序不一致时，MySQL需要将结果集放入临时表再进行排序。

  • 去重（DISTINCT）操作：

    • 当DISTINCT关键字和ORDER BY一起使用时。
    • 如果DISTINCT操作的列上没有索引，MySQL可能需要使用临时表来帮助去重。

  • UNION 操作：

    • UNION查询会合并多个SELECT语句的结果集并去除重复的行，这个去重过程通常需要使用临时表来完成。
    • 相比之下，UNION ALL不进行去重，因此在很多情况下可以避免创建临时表。

  • 子查询与派生表：

    • FROM子句中的子查询（派生表）的结果通常会被物化到一个内部临时表中。
    • 某些情况下，为了优化IN或EXISTS子句中的子查询，MySQL也可能会将其结果存入临时表。

  • 使用特定算法或视图：

    • 当视图的算法是TEMPTABLE时（即无法将视图的查询逻辑与外部查询合并）。
    • 使用UNION或聚合函数的视图。

  • 其他复杂操作：

    • 多表UPDATE语句。
    • 计算COUNT(DISTINCT ...)或GROUP_CONCAT()表达式时。
    • 在评估窗口函数时，也可能需要临时表。

内存临时表与磁盘临时表

MySQL创建的内部临时表首先会尝试在内存中创建，因为这样速度最快。 内存临时表由TempTable存储引擎（MySQL 8.0开始的默认引擎）或MEMORY存储引擎管理。

但是，在以下情况下，内存临时表会被转换为磁盘临时表，或者直接在磁盘上创建，这会显著增加I/O开销，影响性能： * 临时表大小超过限制：当内存临时表的大小超过了tmp_table_size和max_heap_table_size两个参数中的较小值时，它会自动转换为磁盘表。 * 表中包含大数据类型：当表中存在BLOB或TEXT类型的列时。 * 字符串列过长：在GROUP BY或DISTINCT的列中包含长度超过512字节的字符串列。 * UNION查询中的字符串列过长：UNION或UNION ALL的查询列中存在最大长度超过512的字符串列。