代码改变世界

MySQL 的内部临时表

  abce  阅读(85)  评论(0编辑  收藏  举报

在有些情况下,MySQL Server 会在处理语句时创建内部临时表。用户无法直接控制这种情况何时发生。

 

通常情况下,内部临时表首先保留在内存中,以获得最佳查询性能。为避免内存分配过多,MySQL 提供了参数,可用于设置内存限制。当达到该限制时,内部临时表就会溢出到磁盘存储。MySQL 可能会为内存中的内部临时表指定与磁盘存储中的内部临时表不同的存储引擎。

在 8.0 版之前,MySQL 使用MEMORY 存储引擎作为内存内部临时表的默认存储引擎。MySQL 8.0 用TempTable 存储引擎取代了它,但也提供了切换回 MEMORY 存储引擎的选项。

 

 

TempTable 存储引擎在可管理性方面的一个关键简化是,它为所有使用它的会话使用一个内存池。由temptable_max_ram 设置的内存限制适用于并发会话的内存消耗总和。这与 MEMORY 存储引擎不同,MEMORY 存储引擎的内存限制由较小的tmp_table_size和max_heap_table_size 设置,是针对会话的每个表。TempTable 存储引擎也有自己的磁盘溢出机制。你可以将其配置为溢出到内存映射临时文件或InnoDB 磁盘内部临时表。内存映射文件提供了文件和内存空间之间的映射,可加快文件读写操作。除了内存限制外,这两种不同的溢出路径还能展现内部临时表存储引擎的独特之处,并直接影响查询性能。

 

以下是MySQL 8.0.28的说明:

 

MySQL 会使用内部临时表的的场景

MySQL Server 会在以下情况下创建临时表:

• 评估 UNION 语句(后面会介绍一些例外情况)

• 评估某些视图,如使用 TEMPTABLE 算法、UNION 或聚合的视图

• 求值派生表(derived tables)

• 评估常用表表达式(common table expressions)

• 为子查询或半连接物化创建的表

• 评估包含ORDER BY子句和不同GROUP BY子句的语句,或者ORDER BY或GROUP BY包含来自连接队列中第一个表以外的表的列的语句

• 结合ORDER BY对DISTINCT进行评估可能需要使用临时表。

• 对于使用SQL_SMALL_RESULT修改器的查询,MySQL 会使用内存中的临时表,除非查询还包含需要磁盘存储的元素

• 要评估从同一个表中选择并插入到同一个表中的INSERT ... SELECT 语句,MySQL 会创建一个内部临时表来保存SELECT 中的记录,然后将这些记录插入到目标表中

• 评估多表UPDATE语句

• 评估GROUP_CONCAT()或COUNT(DISTINCT)表达式

• 评估窗口函数,必要时使用临时表。

 

如何确认语句是否使用了临时表

要想确认语句是否需要临时表,可以使用EXPLAIN命令,并检查Extra列是否显示Using temporary。对于派生或物化的临时表,EXPLAIN不一定显示Using temporary。对于使用窗口函数的语句,FORMAT=JSON的EXPLAIN总是提供有关窗口步骤的信息。如果窗口函数使用临时表,则会在每个步骤中指明。

 

某些查询条件不允许使用基于内存的临时表,在这种情况下,服务器会使用基于磁盘的临时表来代替:

• 表中存在BLOB或TEXT列。不过,从 MySQL 8.0.13 开始,作为 MySQL 8.0 中基于内存的内部临时表默认存储引擎的TempTable存储引擎支持二进制大对象类型。

• 如果使用UNION或UNION ALL,SELECT列表中存在最大长度大于 512(二进制字符串为字节,非二进制字符串为字符)的任何字符串列。

• SHOW COLUMNS和DESCRIBE语句使用BLOB作为某些列的类型,因此结果使用的临时表是基于磁盘的临时表。

 

对于符合特定条件的UNION语句,MySQL不会使用临时表。比如:

• 使用的是union all,而不是 union、union distinc

• 没有全局的 order by 子句

• union 语句不是 insert、replace、select语句的顶级查询块。

以上不会完全实例化临时表,也不会向表中写入或读取记录;记录会直接发送到客户端。这样做的结果是降低了内存和磁盘需求,并减少了向客户端发送第一行之前的延迟,因为服务器无需等到最后一个查询块执行完毕。EXPLAIN和优化器的跟踪输出反映了这种执行策略: UNION RESULT查询块不存在,因为该查询块对应的是从临时表中读取数据。

 

相反,在所需的数据结构需要执行结果列类型转换的时候,还是会创建临时表。

 

内部临时表存储引擎

内部临时表可以通过TempTable或MEMORY引擎存储在内存中;或者通过InnoDB引擎存储到磁盘上。

1.基于内存的内部临时表

位于内存中的内部临时表存储引擎是通过变量internal_tmp_mem_storage_engine 来设置的,默认是TempTable,此外还可以设置为MEMORY。

 

TempTable引擎可以高效存储varchar、varbinary类型的列。TempTable引擎的控制变量有:

• tmp_table_size :

从 MySQL 8.0.28 开始,tmp_table_size定义了由 TempTable 存储引擎创建的任何单个内存内部临时表的最大大小。当达到tmp_table_size限制时,MySQL 会自动将内存内部临时表转换为InnoDB基于磁盘的内部临时表。默认的tmp_table_size设置为 16777216 字节(16MB)。

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基于磁盘内部临时表之前,TempTable存储引擎可以使用的最大内存容量,具体取决于配置。默认temptable_max_ram设置为 1073741824 字节(1GB)。

temptable_max_ram设置不包括分配给使用TempTable存储引擎的每个线程的线程本地内存块。线程本地内存块的大小取决于线程第一次内存分配请求的大小。如果请求小于 1MB(大多数情况下都小于 1MB),则线程本地内存块大小为 1MB。如果请求大于 1MB,则线程本地内存块的大小与初始内存请求大致相同。线程本地内存块保存在线程本地存储中,直到线程退出。

• temptable_use_mmap:

当超过temptable_max_ram限制时,控制TempTable存储引擎是从内存映射文件分配空间,还是 MySQL 使用InnoDB磁盘上的内部临时表。默认设置为temptable_use_mmap=ON。

temptable_use_mmap变量在 MySQL 8.0.16 中引入,在 MySQL 8.0.26 中被弃用;预计未来版本的 MySQL 将删除对它的支持。设置temptable_max_mmap=0等于设置temptable_use_mmap=OFF。

• temptable_max_mmap:

在 MySQL 8.0.23 中引入。定义在 MySQL 开始使用InnoDB磁盘内部临时表之前,允许 TempTable 存储引擎从内存映射文件中分配的最大内存量。默认设置为 1073741824 字节(1GiB)。该限制旨在解决内存映射文件占用临时目录(tmpdir)太多空间的风险。如果temptable_max_mmap=0设置为禁用内存映射文件的分配,则无论temptable_use_mmap设置如何,都将有效禁用内存映射文件。

 

TempTable 引擎使用内存映射文件(memory-mapped files),有以下规则控制:

• 临时文件在tmpdir变量定义的目录中创建。

• 临时文件在创建和打开后会立即被删除,因此不会保留在tmpdir目录中。临时文件占用的空间在临时文件打开时由操作系统保留。当临时文件被TempTable存储引擎关闭或mysqld进程关闭时,这些空间会被回收。

• 数据不会在内存和临时文件之间、内存内部或临时文件之间移动

• 如果在temptable_max_ram 定义的限制范围内有可用空间,新数据就会存储在内存中。否则,新数据将存储在临时文件中。

• 如果在表的部分数据写入临时文件后,内存中出现可用空间,则可以将剩余的表数据存储在内存中。

 

当对内存中的临时表使用 MEMORY 存储引擎(internal_tmp_mem_storage_engine=MEMORY)时,如果内存中的临时表过大,MySQL 会自动将其转换为磁盘上的表。内存中临时表的最大大小由tmp_table_size或max_heap_table_size值定义,以较小者为准。

这与使用CREATE TABLE 明确创建的MEMORY表不同,对于这类表,只有max_heap_table_size变量决定表的大小,而不需要转换为磁盘上的格式。

 

2.基于磁盘的内部临时表

位于磁盘的的内部临时表存储引擎,在MySQL 8.0.16之前是通过变量internal_tmp_disk_storage_engine 来设置的,支持的选项是InnoDB和MyISAM;从MySQL 8.0.16开始只能使用InnoDB引擎。

 

内部临时表存储格式

当内存内部临时表由TempTable存储引擎管理时,包含VARCHAR列、VARBINARY列和其他二进制大型对象类型列(从 MySQL 8.0.13 开始支持)的行在内存中用单元格数组表示,每个单元格包含一个 NULL 标志、数据长度和一个数据指针。列值按连续顺序排列在数组之后,位于内存的单个区域内,没有填充。数组中的每个单元使用 16 字节的存储空间。当TempTable存储引擎从内存映射文件中分配空间时,也采用相同的存储格式。

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

 

在 MySQL 8.0.16 之前,磁盘上的内部临时表由InnoDB或MyISAM存储引擎管理(取决于internal_tmp_disk_storage_engine设置)。这两种引擎都使用动态行宽格式存储内部临时表。列只占用所需的存储空间,这与使用固定长度行的磁盘表相比,减少了磁盘 I/O、空间需求和处理时间。从 MySQL 8.0.16 开始,不支持internal_tmp_disk_storage_engine,磁盘上的内部临时表始终由InnoDB 管理。

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

 

监控内部临时表的创建

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

 

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

相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~
历史上的今天:
2023-01-03 fn_dblog()和fn_full_dblog()的使用
点击右上角即可分享
微信分享提示