MySQL 如何存储临时文件

MySQL 如何存储临时文件

在 Unix 系统上，MySQL 使用 TMPDIR 环境变量的值作为存储临时文件的目录路径。如果 TMPDIR 未设置，MySQL 将使用系统默认值，通常为 /tmp、/var/tmp或 /usr/tmp。

在 Windows 系统上，MySQL 依次检查 TMPDIR、TEMP 和 TMP 环境变量的值。当找到第一个已设置的变量时，MySQL 即采用该值并停止检查后续变量。若 TMPDIR、TEMP 或TMP 均未设置，则采用 Windows 系统默认值 C:\windows\temp\。

如果临时文件目录所在的文件系统空间不足，可通过 mysqld 的 --tmpdir 选项指定具有充足空间的文件系统目录。

--tmpdir 选项可设置为多个路径的列表，这些路径将以轮询方式使用。Unix 系统中路径应以冒号(:)分隔，Windows系统中则以分号(;)分隔。为了有效分散负载，这些路径应位于不同的物理磁盘上，非同一磁盘的不同分区。

如果 MySQL 作为从库运行，可通过设置系统变量 replica_load_tmpdir 为 LOAD DATA 语句的复制指定独立的临时文件存储目录。该目录应位于磁盘文件系统（而非内存文件系统）中，以确保 LOAD DATA 复制使用的临时文件能在机器重启后保留。该目录也不应被操作系统在系统启动过程中清除。不过，即使临时文件被删除，复制过程在重启后仍可继续。

MySQL 会安排在 mysqld 终止时清除临时文件。在支持此功能的平台（如Unix）上，操作方式是在打开文件后执行 unlink 操作。此方法的缺点在于：该名称不会出现在目录列表中，且无法观察到填满临时文件目录所在文件系统的大型临时文件。(这就是临时文件看不到的原因，此类情况下，可通过 lsof +L1 命令识别与 mysqld 相关的大型文件)

# lsof +L1
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NLINK NODE NAME
mysqld  74007 mysql    5u   REG    8,1        0     0  163 /tmp/#163 (deleted)
mysqld  74007 mysql    6u   REG    8,1        0     0 2817 /tmp/#2817 (deleted)
mysqld  74007 mysql   11u   REG    8,1        0     0 2819 /tmp/#2819 (deleted)

在排序操作（ORDER BY 或 GROUP BY）中，MySQL 通常会使用一到两个临时文件。所需最大磁盘空间由以下表达式决定：

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

其中，行指针通常为 4 字节，但未来处理超大规模表时可能增大。

对于某些语句，MySQL 会创建非隐藏的临时 SQL 表，其名称以#sql开头。

部分 SELECT 查询会创建临时 SQL 表来存储中间结果。对于不是使用 ALGORITHM=INPLACE 进行在线操作的 DDL 操作，会在原始表所在目录创建原始表的临时副本。在线 DDL 操作可能使用临时日志文件记录并发 DML 操作，创建索引时使用临时排序文件，重建表时使用临时中间表文件。

InnoDB 用户创建的临时表和磁盘上的内部临时表，均存储于 MySQL 数据目录下的临时表空间文件 ibtmp1 中。(要明确区分临时文件和临时表是不同的概念)

可选的 EXTENDED 修饰符会使 SHOW TABLES 列出由失败的 ALTER TABLE 语句创建的隐藏表。

show extended tables;

总结一下，临时文件可能存在的位置：

1.--tmpdir 指定的目录
2.--replica_load_tmpdir 指定的目录
3.原表存在的目录
4.临时表空间(ibtmp1文件)

 

对应的一些变量

1.innodb_temp_data_file_path

定义全局临时表空间数据文件的相对路径、名称、大小和属性。全局临时表空间用于存储对用户创建的临时表所做的更改的回滚段。默认是位于 innodb_data_home_dir 指定的目录中。

>show variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+

2.innodb_temp_tablespaces_dir

定义 InnoDB 在启动时创建会话临时表空间池的位置。默认位置是数据目录中的 #innodb_temp 目录。允许使用完全限定路径或相对于数据目录的路径。

会话临时表空间始终用于存储用户创建的临时表以及优化器使用InnoDB创建的内部临时表。（老版本中，内部临时表的磁盘存储引擎由系统变量 internal_tmp_disk_storage_engine 决定，该变量现已弃用）

>show variables like 'innodb_temp_tablespaces_dir';
+-----------------------------+-----------------+
| Variable_name               | Value           |
+-----------------------------+-----------------+
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
+-----------------------------+-----------------+

3.innodb_tmpdir

用于定义 online ALTER TABLE 重建表时创建的临时排序文件的替代目录。

online ALTER TABLE 操作在重建表时，也会在原始表所在目录创建一个中间表文件。innodb_tmpdir 选项不适用于中间表文件。

该变量的有效值为除 MySQL 数据目录路径外的任意目录路径。若值为NULL（默认值），则临时文件将创建于 MySQL 临时目录（Unix系统为$TMPDIR，Windows系统为%TEMP%，或由--tmpdir配置选项指定的目录）。

若指定目录路径，则仅当通过 SE T语句配置 innodb_tmpdir 时才会检查目录存在性及权限。若目录字符串包含符号链接，系统将解析该链接并存储为绝对路径。

路径长度不得超过512字节。当 innodb_tmpdir 指向无效目录时，online ALTER TABLE 将报告错误。该选项会覆盖 MySQL 的 tmpdir 设置，但仅限于 online ALTER TABLE操作。

配置 innodb_tmpdir 需要 FILE 权限。

innodb_tmpdir 选项的引入旨在避免位于 tmpfs 文件系统上的临时文件目录发生溢出。此类溢出可能由 online ALTER TABLE 操作重建表时生成的大型临时排序文件引发。

>show variables like 'innodb_tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| innodb_tmpdir |       |
+---------------+-------+

4.tmpdir

用于创建临时文件的目录路径。当默认的 /tmp 目录所在分区空间不足以容纳临时表时，此设置可能有所帮助。

该变量可设置为多个路径的列表，系统将按循环方式使用这些路径。Unix系统中路径应以冒号(:)分隔，Windows系统中则以分号(;)分隔。

tmpdir 可设置为非永久性位置，例如内存文件系统中的目录，或服务器重启时会被清除的目录。

若 MySQL 服务器作为从库运行且使用非永久性 tmpdir 位置，建议通过 replica_load_tmpdir 变量为从库设置独立的临时目录。

对于从库，用于复制 LOAD DATA 语句的临时文件存储在此目录中。因此，若采用永久性位置，这些文件可在机器重启后保留；但需注意，若临时文件已被删除，重启后复制过程仍可继续。

>show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+

5.replica_load_tmpdir

replica_load_tmpdir 指定从库创建临时文件的目录名称。设置此变量将立即对所有复制通道生效，包括正在运行的通道。该变量默认值等于 tmpdir 系统变量的值，或当该系统变量未指定时适用的默认值。

当从库 SQL线程复制 LOAD DATA 语句时，会将待加载文件从中继日志提取至临时文件，再加载至表中。若主库加载的文件体积庞大，从库的临时文件同样会非常庞大。

因此建议使用此选项，指示从库将临时文件存放于拥有充足可用空间的文件系统目录中。此时中继日志同样会变得庞大，故还需将 relay_log 系统变量设置的足够大。

该选项指定的目录应位于磁盘文件系统（而非内存文件系统）中，以确保用于复制 LOAD DATA 语句的临时文件能在机器重启后保留。该目录也不应被操作系统在启动过程中清除。不过，即使临时文件被删除，复制在重启后仍可继续进行。

>show variables like 'replica_load_tmpdir';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| replica_load_tmpdir | /tmp  |
+---------------------+-------+

在线DDL的空间需求

在线DDL操作所需的磁盘空间要求如下所述。这些要求不适用于即时执行( instantly)的操作。

1.临时日志文件

当在线DDL操作创建索引或修改表时，临时日志文件会记录并发DML操作。该文件会根据 innodb_sort_buffer_size 的值动态扩展，直至达到 innodb_online_alter_log_max_size 设定的上限。若操作耗时较长且并发DML对表的修改导致临时日志文件大小超过 innodb_online_alter_log_max_size 的值，在线DDL操作将因DB_ONLINE_LOG_TOO_BIG错误失败，未提交的并发DML操作将被回滚。较大的innodb_online_alter_log_max_size设置允许在线DDL操作期间处理更多DML，但也会延长DDL操作结束时为应用日志记录的DML而锁定表的时间。

 

innodb_sort_buffer_size 变量同时定义临时日志文件读缓冲区和写缓冲区的大小。

2.临时排序文件

在线DDL操作在重建表时，会在索引创建过程中将临时排序文件写入MySQL临时目录（Unix系统为$TMPDIR，Windows系统为%TEMP%，或通过--tmpdir指定的目录）。

临时排序文件不会创建在原始表所在目录中。每个临时排序文件的容量足以容纳一列数据，当其数据合并到最终表或索引时即被删除。

涉及临时排序文件的操作可能需要临时空间，其大小等于表数据量加上索引量。若在线DDL操作耗尽数据目录所在文件系统的可用磁盘空间，则会报告错误。

 

若MySQL临时目录空间不足以容纳排序文件，请将tmpdir设置为其他目录。或通过 innodb_tmpdir 为在线DDL操作定义独立临时目录。此选项旨在避免因大型临时排序文件导致的临时目录溢出问题。

3.中间表文件

某些在线DDL操作在重建表时，会在原始表所在目录创建临时中间表文件。中间表文件可能需要占用与原始表大小相当的空间。中间表文件名以#sql-ib前缀开头，仅在在线DDL操作期间短暂存在。

(innodb_tmpdir选项不适用于中间表文件)