Skip to main content

4.MySQL 中内部临时表的使用

在某些情况下，服务器在处理语句时会创建内部临时表。 用户无法直接控制何时发生这种情况。

服务器在以下条件下创建临时表：

  • UNION 语句的评估，稍后描述一些例外情况。
  • 评估某些视图，例如使用 TEMPTABLE 算法、UNION 或聚合的视图。
  • 派生表的评估。
  • 公共表表达式的求值。
  • 为子查询或半连接具体化创建的表。
  • 评估包含 ORDER BY 子句和不同的 GROUP BY 子句的语句，或者 ORDER BY 或 GROUP BY 包含连接队列中第一个表以外的表中的列的语句。
  • DISTINCT 与 ORDER BY 结合的评估可能需要临时表。
  • 对于使用 SQL_SMALL_RESULT 修饰符的查询，MySQL 使用内存临时表，除非查询还包含需要磁盘存储的元素。
  • 为了评估从同一个表中选择并插入到同一个表中的 INSERT ... SELECT 语句，MySQL 创建一个内部临时表来保存 SELECT 中的行，然后将这些行插入到目标表中。
  • 评估多表 UPDATE 语句。
  • GROUP_CONCAT() 或 COUNT(DISTINCT) 表达式的计算。
  • 窗口函数的评估根据需要使用临时表。

要确定语句是否需要临时表，请使用 EXPLAIN 并检查 Extra 列以查看是否显示“使用临时表”。 EXPLAIN 不一定说对派生临时表或具体化临时表使用临时表。 对于使用窗口函数的语句，EXPLAIN with FORMAT=JSON 始终提供有关窗口步骤的信息。 如果窗口函数使用临时表，则会在每个步骤中进行指示。

某些查询条件会阻止使用内存中的临时表，在这种情况下，服务器会使用磁盘上的表：

  • 表中存在 BLOB 或 TEXT 列。 TempTable存储引擎是MySQL 8.3中内存内部临时表的默认存储引擎，支持二进制大对象类型。 请参阅内部临时表存储引擎。
  • 如果使用 UNION 或 UNION ALL，则 SELECT 列表中存在最大长度大于 512（二进制字符串的字节，非二进制字符串的字符）的任何字符串列。
  • SHOW COLUMNS 和 DESCRIBE 语句使用 BLOB 作为某些列的类型，因此用于结果的临时表是磁盘上的表。

对于满足特定条件的 UNION 语句，服务器不使用临时表。 相反，它在创建临时表时仅保留执行结果列类型转换所需的数据结构。 该表未完全实例化，并且没有写入或读取任何行； 行直接发送到客户端。 结果是减少了内存和磁盘需求，并且在将第一行发送到客户端之前的延迟更小，因为服务器不需要等到最后一个查询块被执行。 EXPLAIN 和优化器跟踪输出反映了此执行策略：UNION RESULT 查询块不存在，因为该块对应于从临时表读取的部分。

这些条件使 UNION 可以在没有临时表的情况下进行评估：

  • 联合是 UNION ALL，而不是 UNION 或 UNION DISTINCT。
  • 没有全局 ORDER BY 子句。
  • 联合不是 {INSERT | REPLACE} ... SELECT ... 语句的顶级查询块。

内部临时表存储引擎​

内部临时表可以保存在内存中并由 TempTable 或 MEMORY 存储引擎处理，也可以由 InnoDB 存储引擎存储在磁盘上。

内存内部临时表的存储引擎​

Internal_tmp_mem_storage_engine 变量定义用于内存内部临时表的存储引擎。 允许的值为 TempTable（默认值）和 MEMORY。

TempTable 存储引擎为 VARCHAR 和 VARBINARY 列以及其他二进制大对象类型提供高效的存储。

以下变量控制 TempTable 存储引擎的限制和行为：

  • tmp_table_size：定义 TempTable 存储引擎创建的任何单个内存内部临时表的最大大小。 当达到 tmp_table_size 限制时，MySQL 会自动将内存内部临时表转换为 InnoDB 磁盘内部临时表。 默认 tmp_table_size 设置为 16777216 字节 (16 MiB)。

    tmp_table_size 限制旨在防止单个查询消耗过多的全局 TempTable 资源，这可能会影响需要 TempTable 资源的并发查询的性能。 全局 TempTable 资源由 temptable_max_ram 和 temptable_max_mmap 设置控制。

    如果 tmp_table_size 限制小于 temptable_max_ram 限制，则内存中临时表不可能包含多于 tmp_table_size 限制允许的数据。 如果 tmp_table_size 限制大于 temptable_max_ram 和 temptable_max_mmap 限制之和，则内存中临时表不可能包含超过 temptable_max_ram 和 temptable_max_mmap 限制之和的内容。

  • temptable_max_ram：定义 TempTable 存储引擎在开始从内存映射文件分配空间之前或 MySQL 开始使用 InnoDB 磁盘内部临时表之前可以使用的最大 RAM 量，具体取决于您的配置。 默认 temptable_max_ram 设置为 1073741824 字节 (1GiB)。

  • temptable_max_mmap：设置在 MySQL 开始使用 InnoDB 磁盘内部临时表之前 TempTable 存储引擎允许从内存映射文件分配的最大内存量。 默认设置为 1073741824 字节 (1GiB)。 该限制旨在解决内存映射文件使用临时目录 (tmpdir) 中过多空间的风险。 temptable_max_mmap = 0 禁用内存映射文件的分配，从而有效地禁用它们的使用，无论 temptable_use_mmap 设置如何。

TempTable 存储引擎对内存映射文件的使用受以下规则约束：

  • 临时文件在 tmpdir 变量定义的目录中创建。
  • 临时文件在创建和打开后立即被删除，因此在 tmpdir 目录中不再可见。 当临时文件打开时，临时文件占用的空间由操作系统保留。 当 TempTable 存储引擎关闭临时文件或关闭 mysqld 进程时，会回收该空间。
  • 数据永远不会在 RAM 和临时文件之间、RAM 内或临时文件之间移动。
  • 如果空间在 temptable_max_ram 定义的限制内可用，则新数据将存储在 RAM 中。 否则，新数据将存储在临时文件中。
  • 如果将表的部分数据写入临时文件后 RAM 中出现可用空间，则剩余的表数据可能会存储在 RAM 中。

当对内存临时表使用 MEMORY 存储引擎（internal_tmp_mem_storage_engine=MEMORY）时，如果内存临时表变得太大，MySQL 会自动将其转换为磁盘表。 内存临时表的最大大小由 tmp_table_size 或 max_heap_table_size 值定义，以较小者为准。 这与使用 CREATE TABLE 显式创建的 MEMORY 表不同。 对于此类表，只有 max_heap_table_size 变量决定表可以增长多大，并且不会转换为磁盘格式。

磁盘内部临时表的存储引擎​

MySQL 8.3 仅使用 InnoDB 存储引擎作为磁盘内部临时表。 （为此目的不再支持 MYISAM 存储引擎。）

InnoDB 磁盘内部临时表是在默认驻留在数据目录中的会话临时表空间中创建的。

内部临时表存储格式​

当内存中的内部临时表由 TempTable 存储引擎管理时，包含 VARCHAR 列、VARBINARY 列和其他二进制大对象类型列的行在内存中由单元格数组表示，每个单元格包含 NULL 标志， 数据长度和数据指针。 列值按连续顺序放置在数组后面的单个内存区域中，没有填充。 数组中的每个单元使用 16 字节的存储空间。 当 TempTable 存储引擎从内存映射文件分配空间时，适用相同的存储格式。

当内存内部临时表由 MEMORY 存储引擎管理时，使用固定长度行格式。 VARCHAR 和 VARBINARY 列值被填充到最大列长度，实际上将它们存储为 CHAR 和 BINARY 列。

磁盘上的内部临时表始终由 InnoDB 管理。​

使用 MEMORY 存储引擎时，语句可以首先创建内存中的内部临时表，然后在该表变得太大时将其转换为磁盘上的表。 在这种情况下，通过跳过转换并首先在磁盘上创建内部临时表可能会获得更好的性能。 big_tables 变量可用于强制内部临时表的磁盘存储。

监控内部临时表的创建​

当在内存或磁盘上创建内部临时表时，服务器会递增 Created_tmp_tables 值。 当在磁盘上创建内部临时表时，服务器会递增 Created_tmp_disk_tables 值。 如果在磁盘上创建了太多内部临时表，请考虑调整内部临时表存储引擎中所述的特定于引擎的限制。

注意：

由于已知限制，Created_tmp_disk_tables 不计算在内存映射文件中创建的磁盘临时表。 默认情况下，TempTable 存储引擎溢出机制在内存映射文件中创建内部临时表。

memory/temptable/physical_ram 和 memory/temptable/physical_disk Performance Schema 工具可用于监视内存和磁盘的 TempTable 空间分配。 memory/temptable/physical_ram 报告分配的 RAM 量。 memory/temptable/physical_disk 报告当内存映射文件用作 TempTable 溢出机制时从磁盘分配的空间量。 如果physical_disk仪器报告非0的值并且内存映射文件用作TempTable溢出机制，则在某个时刻达到了TempTable内存限制。 可以在 Performance Schema 内存汇总表（例如 memory_summary_global_by_event_name）中查询数据。