8.4.4 MySQL内部临时表的使用

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

8.4.4 MySQL内部临时表的使用

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

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

评估UNION 陈述。
评估某些视图，例如使用 TEMPTABLE算法 UNION、或聚合的视图。
派生表的评估（请参阅第 13.2.10.8 节，“派生表”）。
为子查询或半连接具体化创建的表（请参阅第 8.2.2 节，“优化子查询和派生表”）。
评估包含ORDER BY子句和不同GROUP BY子句的语句，ORDER BY或GROUP BY包含连接队列中第一个表以外的表的列的语句。
DISTINCT结合评估ORDER BY可能需要一个临时表。
对于使用SQL_SMALL_RESULT 修饰符的查询，MySQL 使用内存中的临时表，除非查询还包含需要磁盘存储的元素（稍后描述）。
为了评估 INSERT ... SELECT从同一个表中选择并插入到同一个表中的语句，MySQL 创建一个内部临时表来保存来自的行 SELECT，然后将这些行插入到目标表中。请参阅第 13.2.5.1 节，“INSERT ... SELECT 语句”。
多表 UPDATE语句的评估。
GROUP_CONCAT() 或COUNT(DISTINCT) 表达式的评估。

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

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

表中存在一个BLOB或 TEXT列。这包括具有字符串值的用户定义变量，因为它们被视为 BLOB或 TEXT列，具体取决于它们的值分别是二进制字符串还是非二进制字符串。
GROUP BYor子句中存在DISTINCT大于 512 个字节（对于二进制字符串）或 512 个字符（对于非二进制字符串）的字符串列。
如果使用或 SELECT，则列表中存在最大长度大于 512（二进制字符串为字节，非二进制字符串为字符）的任何字符串列。 UNIONUNION ALL
SHOW COLUMNSand DESCRIBE语句用作某些列的BLOB类型，因此用于结果的临时表是磁盘表。

内部临时表存储引擎

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

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

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

内部临时表存储格式

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

磁盘上的临时表由 MyISAM存储引擎使用动态宽度行格式进行管理。列仅占用所需的存储空间，与使用固定长度行的磁盘表相比，这减少了磁盘 I/O 和空间要求以及处理时间。

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