8.4.4 MySQL 中的内部临时 table 使用
在某些情况下,服务器在处理语句时会创建内部临时 table。用户无法直接控制何时发生这种情况。
服务器在以下条件下创建临时 table:
-
评估UNION语句,但稍后会有一些 exception。
-
评估某些视图,例如使用
TEMPTABLE
算法,UNION或聚合的视图。 -
派生 table 的评估(请参阅第 13.2.10.8 节“派生 table”)。
-
为子查询或半联接实现创建的 table(请参见第 8.2.2 节“优化子查询,派生 table 和视图引用”)。
-
评估包含
ORDER BY
子句和不同的GROUP BY
子句的语句,或者ORDER BY
或GROUP BY
包含来自联接队列中第一个 table 以外的 table 的列的语句。 -
对
DISTINCT
和ORDER BY
相结合的评估可能需要一个临时 table。 -
对于使用
SQL_SMALL_RESULT
修饰符的查询,MySQL 使用内存中临时 table,除非查询还包含需要磁盘存储的元素(稍后描述)。 -
为了评估从同一 table 中选择并插入到该 table 中的插入...选择语句,MySQL 创建一个内部临时 table 来保存SELECT中的行,然后将这些行插入目标 table 中。参见第 13.2.5.1 节“ INSERT ... SELECT 语句”。
-
评估多 tableUPDATE语句。
-
评估GROUP_CONCAT()或COUNT(DISTINCT)table 达式。
要确定语句是否需要临时 table,请使用EXPLAIN并检查Extra
列以查看其是否 table 示Using temporary
(请参见第 8.8.1 节“使用 EXPLAIN 优化查询”)。对于派生的或物化的临时 table,EXPLAIN
不一定会说Using temporary
。
某些查询条件阻止使用内存中的临时 table,在这种情况下,服务器将使用磁盘上的 table 代替:
-
table 格中存在BLOB或TEXT列。这包括具有字符串值的用户定义变量,因为它们分别被视为BLOB列或TEXT列,具体取决于它们的值是二进制字符串还是非二进制字符串。
-
如果使用了UNION或UNION ALL,则SELECT列 table 中存在任何最大长度大于 512(字符串为二进制字符串,非二进制为字符)的字符串列。
-
SHOW COLUMNS和DESCRIBE语句使用
BLOB
作为某些列的类型,因此用于结果的临时 table 是磁盘上的 table。
服务器不对满足某些条件的UNION语句使用临时 table。而是从临时 table 创建中仅保留执行结果列类型转换所需的数据结构。该 table 尚未完全实例化,并且没有向其写入或读取任何行;行直接发送到 Client 端。结果是减少了内存和磁盘需求,并减少了将第一行发送到 Client 端之前的延迟,因为服务器不必等到最后一个查询块执行完毕。 EXPLAIN和优化器跟踪输出反映了这种执行策略:UNION RESULT
查询块不存在,因为该块对应于从临时 table 读取的部分。
这些条件使UNION
可以在没有临时 table 的情况下进行评估:
-
联合是
UNION ALL
,而不是UNION
或UNION DISTINCT
。 -
没有全局
ORDER BY
子句。 -
联合不是
{INSERT | REPLACE} ... SELECT ...
语句的顶级查询块。
内部临时 table 存储引擎
内部临时 table 可以保存在内存中,并由MEMORY
存储引擎处理,或者由InnoDB
或MyISAM
存储引擎存储在磁盘上。
如果内部临时 table 被创建为内存 table,但又太大了,MySQL 会自动将其转换为磁盘 table。内存中临时 table 的最大大小由tmp_table_size或max_heap_table_size值定义,以较小者为准。这与使用CREATE TABLE显式创建的MEMORY
table 不同。对于此类 table,只有max_heap_table_size变量确定 table 可以增长到多大,并且不转换为磁盘格式。
internal_tmp_disk_storage_engine变量定义服务器用来 Management 磁盘内部临时 table 的存储引擎。允许的值为INNODB
(默认值)和MYISAM
。
Note
使用internal_tmp_disk_storage_engine=INNODB时,生成超过InnoDB 行或列限制的磁盘内部临时 table 的查询将返回行大小太大或列过多的错误。解决方法是将internal_tmp_disk_storage_engine设置为MYISAM
。
当在内存或磁盘中创建内部临时 table 时,服务器将使Created_tmp_tables值递增。在磁盘上创建内部临时 table 时,服务器将使Created_tmp_disk_tables值递增。如果在磁盘上创建了太多内部临时 table,请考虑增加tmp_table_size和max_heap_table_size设置。
内部临时 table 存储格式
内存中的临时 table 由MEMORY
存储引擎 Management,该引擎使用固定长度的行格式。 VARCHAR
和VARBINARY
列值被填充为最大列长度,实际上将它们存储为CHAR
和BINARY
列。
磁盘上的临时 table 由InnoDB
或MyISAM
存储引擎(取决于internal_tmp_disk_storage_engine设置)Management。两个引擎都使用动态宽度行格式存储临时 table。列仅占用所需的存储空间,与使用固定长度行的磁盘 table 相比,这减少了磁盘 I/O,空间需求和处理时间。
对于最初在内存中创建内部临时 table 然后将其转换为磁盘上 table 的语句,跳过转换步骤并在磁盘上开始创建 table 可能会获得更好的性能。 big_tables变量可用于强制内部临时 table 进行磁盘存储。