在某些情况下,服务器在处理语句时创建内部临时表。用户无法直接控制何时发生这种情况。
服务器在如下条件下创建临时表:
评价
联盟
语句,后面将介绍一些例外情况。对某些视图的评估,例如使用
易被诱惑的
算法,联盟
,或聚合。派生表的计算(参见第13.2.10.8节,“派生表”).
为子查询或半连接实体化创建的表(参见第8.2.2节,“优化子查询、派生表和视图引用”).
对象的语句求值
命令
条款和一个不同的集团
子句,或用于命令
或集团
包含来自联接队列中第一个表以外的其他表的列。评价
截然不同的
结合命令
可能需要一个临时表。属性的查询
SQL_SMALL_RESULT
修饰符,MySQL使用内存中的临时表,除非查询还包含需要在磁盘上存储的元素(后面将介绍)。评估
插入……选择
语句从同一个表中选择和插入,MySQL创建一个内部临时表来保存从选择
,然后将这些行插入到目标表中。看到13.2.5.1节,“插入…”SELECT语句”.多表评估
更新
语句。评价
GROUP_CONCAT ()
或数(不同的)
表达式。
若要确定语句是否需要临时表,请使用解释
检查额外的
列查看是否显示使用临时
(见第8.8.1节,“使用EXPLAIN优化查询”).解释
并不是说使用临时
用于派生的或物化的临时表。
有些查询条件会阻止使用内存中的临时表,在这种情况下,服务器会使用磁盘中的临时表:
服务器不使用临时表联盟
满足一定条件的陈述。相反,它只从临时表创建中保留执行结果列类型转换所需的数据结构。表没有完全实例化,也没有向表写入或从表中读取任何行;行被直接发送到客户端。结果是减少了内存和磁盘需求,并且减少了第一行发送到客户端之前的延迟,因为服务器不需要等到最后一个查询块执行。解释
和优化器跟踪输出反映了此执行策略联盟的结果
不存在查询块,因为该块对应于从临时表中读取数据的部分。
这些条件符合联盟
对于没有临时表的计算:
工会是
UNION ALL
,而不是联盟
或结合不同的
.没有全球性
命令
条款。对象的顶级查询块
{insert | replace}…选择……
声明。
内部临时表可以保存在内存中,并由内存
存储引擎,或存储在磁盘上的InnoDB
或MyISAM
存储引擎。
如果内部临时表创建为内存中的表,但变得太大,MySQL会自动将其转换为磁盘上的表。内存中临时表的最大大小由tmp_table_size
或max_heap_table_size
值,取较小的值。这与内存
显式地使用创建表
.对于这样的表,只有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
设置。
内存中临时表由内存
存储引擎,使用定长行格式。VARCHAR
而且VARBINARY
列值被填充到最大列长度,实际上将它们存储为字符
而且二进制
列。
磁盘上的临时表由InnoDB
或MyISAM
存储引擎(取决于internal_tmp_disk_storage_engine
设置)。两个引擎都使用动态宽度行格式存储临时表。列只占用所需的存储空间,与使用固定长度行的磁盘表相比,这减少了磁盘I/O、空间需求和处理时间。
对于最初在内存中创建内部临时表,然后将其转换为磁盘上的表的语句,跳过转换步骤并开始在磁盘上创建表可能会获得更好的性能。的big_tables
变量可用于强制磁盘存储内部临时表。