Documentation Home
MySQL 5.7 参考手册
MySQL 8.0 参考手册  / 第8章优化  / 8.4 优化数据库结构  /  8.4.4 MySQL内部临时表的使用

8.4.4 MySQL内部临时表的使用

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

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

要确定语句是否需要临时表，请使用 EXPLAIN并检查该 Extra列以查看它是否表示 Using temporary（请参阅 第 8.8.1 节，“使用 EXPLAIN 优化查询”）。EXPLAIN 不一定说Using temporary派生或具体化的临时表。

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

  • 表中存在一个BLOB或 TEXT列。这包括具有字符串值的用户定义变量，因为它们被视为 BLOB或 TEXT列，具体取决于它们的值分别是二进制字符串还是非二进制字符串。

  • 如果使用或 SELECT，则列表中 存在最大长度大于 512（二进制字符串为字节，非二进制字符串为字符）的任何字符串列 。 UNIONUNION ALL

  • SHOW COLUMNSand DESCRIBE语句 用作某些列 的BLOB类型，因此用于结果的临时表是磁盘表。

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

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

  • 工会是UNION ALL，不是 UNION或UNION DISTINCT。

  • 没有全局ORDER BY条款。

  • 联合不是语句的顶级查询块 {INSERT | REPLACE} ... SELECT ... 。

内部临时表存储引擎

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

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

该 internal_tmp_disk_storage_engine 变量定义了服务器用来管理磁盘内部临时表的存储引擎。允许的值为 INNODB（默认值）和 MYISAM.

笔记

使用 internal_tmp_disk_storage_engine=INNODB时，生成超过 InnoDB行或列限制的磁盘内部临时表的查询会返回行大小太大或列太多 错误。解决方法是设置 internal_tmp_disk_storage_engine 为MYISAM.

当在内存或磁盘上创建内部临时表时，服务器会递增该 Created_tmp_tables值。在磁盘上创建内部临时表时，服务器会递增该 Created_tmp_disk_tables 值。如果在磁盘上创建了太多的内部临时表，请考虑增加 tmp_table_size和 max_heap_table_size设置。

内部临时表存储格式

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

磁盘上的临时表由 存储引擎InnoDB或MyISAM存储引擎管理（取决于 internal_tmp_disk_storage_engine 设置）。两个引擎都使用动态宽度行格式存储临时表。列只占用所需的存储空间，与使用固定长度行的磁盘表相比，这减少了磁盘 I/O、空间需求和处理时间。

对于最初在内存中创建内部临时表，然后将其转换为磁盘表的语句，跳过转换步骤并首先在磁盘上创建表可能会获得更好的性能。该 big_tables变量可用于强制内部临时表的磁盘存储。