关于mysql:技术分享-浅谈-MySQL-的临时表和临时文件

作者：姚嵩

爱可生南区交付服务部经理，爱好音乐，动漫，电影，游戏，人文，美食，游览，还有其余。尽管都很菜，但毕竟是喜好。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

本文内容来源于对客户的三个问题的思考：

1. 哪些 SQL 会产生长期表 / 临时文件
2. 如何查看已有的长期表
3. 如何管制长期表 / 临时文件的总大小

阐明：

以下测试都是在 MySQL 8.0.21 版本中实现，不同版本可能存在差别，可自行测试；

首先，让咱们理解下什么是长期表 | 临时文件？

长期表和临时文件都是用于长期存放数据集的中央；

个别状况下，须要长期寄存在长期表或临时文件中的数据集应该合乎以下特点：

• 数据集较小 (较大的长期数据集个别意味着 SQL 较烂，当然有例外项)
• 用完就清理 (因为是长期存储数据集的中央，所以生命周期和 SQL 或者会话生命周期雷同)
• 会话隔离 (因为是长期数据集，不波及到与其余会话交互)

• 不产生 GTID

从长期表 | 临时文件产生的主观性来看，分为 2 类：

• 用户创立的长期表
• SQL 产生的长期表 | 临时文件

用户创立长期表：

  用户创立长期表 (只有创立长期表的会话能力查看其创立的长期表的内容)

create database if not exists db_test ;
use db_test ;
CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
select * from db_test.t1 ;

  留神：

  能够创立和一般表同名长期表，其余会话能够看到一般表 (因为看不到其余会话创立的长期表)；

  创立长期表的会话会优先看到长期表；

  同名表的创立的语句如下

CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
insert into  t1 values(1);

  当存在同名的长期表时，会话都是优先解决长期表 (而不是一般表)，包含：select、update、delete、drop、alter 等操作；

查看用户创立的长期表：

  任何 session 都能够执行上面的语句；

  查看用户创立的以后 active 的长期表 (不提供 optimizer 应用的外部 InnoDB 长期表信息)

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

  留神

  用户创立的长期表，表名为 t1，

  然而通过 INNODB_TEMP_TABLE_INFO 查看到的长期表的 NAME 是 #sql 结尾的名字，例如：#sql45aa_7c69_2；

  另外 information_schema.tables 表中是不会记录长期表的信息的。

用户创立的长期表的回收：

1. 会话断开，主动回收用户创立的长期表；
2. 能够通过 drop table 删除用户创立的长期表，例如：drop table t1;

用户创立的长期表的其余信息 & 参数：

  会话长期表空间存储 用户创立的长期表和优化器 optimizer 创立的外部长期表 (当磁盘外部长期表的存储引擎为 InnoDB 时)；

  innodb_temp_tablespaces_dir 变量定义了创立 会话长期表空间的地位，默认是数据目录下的 #innodb_temp 目录；

  文件相似 temp_[1-20].ibt；

  查看会话长期表空间的元数据：

select * from information_schema.innodb_session_temp_tablespaces ;

  用户创立的长期表删除后，其占用的空间会被开释 (temp_[1-20].ibt 文件会变小 )。

  在 MySQL 8.0.16 之前，internal_tmp_disk_storage_engine 变量定义了用户创立的长期表和 optimizer 创立的外部长期表的引擎，可选 INNODB 和 MYISAM；

  从 MySQL 8.0.16 开始，internal_tmp_disk_storage_engine 参数被移除，默认应用 InnoDB 存储引擎；

  innodb_temp_data_file_path 定义了用户创立的长期表应用的回滚段的存储文件的相对路径、名字、大小和属性，该文件是全局长期表空间 (ibtmp1)；

  能够应用语句查问全局长期表空间的数据文件大小：

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE

AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME = 'innodb_temporary'\G 

SQL 什么时候产生长期表 | 临时文件呢？

  须要用到长期表或临时文件的时候，optimizer 天然会创立应用 (感觉是废话，然而又感觉有情理 =.=!)；

  (设想能力强的，能够牢记下面这句话；设想能力弱的，只能死记上面的 SQL 了。我也弱，此处有个疲乏的微笑😊)

上面列举一些 server 在解决 SQL 时，可能会创立外部长期表的 SQL：

  SQL 蕴含 union | union distinct 关键字

  SQL 中存在派生表

  SQL 中蕴含 with 关键字

  SQL 中的 order by 和 group by 的字段不同

  SQL 为多表 update

  SQL 中蕴含 distinct 和 order by 两个关键字

咱们能够通过上面两种形式判断 SQL 语句是否应用了长期表空间：

  # 如果 explain 的 Extra 列蕴含 Using temporary，那么说明会应用长期空间，如果蕴含 Using filesort，那么说明会应用文件排序 (临时文件)；

explain xxx ; 

  如果执行 SQL 后，表的 ID 列变为了 show processlist 中的 id 列的值，那么阐明 SQL 语句应用了长期表空间

select * from information_schema.innodb_session_temp_tablespaces ;   

SQL 创立的外部长期表的存储信息：

  SQL 创立外部长期表时，优先选择在内存中，默认应用 TempTable 存储引擎 (由参数 internal_tmp_mem_storage_engine 确定)，

  当 temptable 应用的内存量超过 temptable_max_ram 定义的大小时，

  由 temptable_use_mmap 确定是应用内存映射文件的形式还是 InnoDB 磁盘外部长期表的形式存储数据

  (temptable_use_mmap 参数在 MySQL 8.0.16 引入，MySQL 8.0.26 版本不举荐，后续应该会移除)；

  temptable_use_mmap 的性能将由 MySQL 8.0.23 版本引入的 temptable_max_mmap 代替，

  当 temptable_max_mmap=0 时，阐明不应用内存映射文件，等价于 temptable_use_mmap=OFF；

  当 temptable_max_mmap=N 时，N 为正整数，蕴含了 temptable_use_mmap=ON 以及申明了容许为内存映射文件调配的最大内存量。

  该参数的定义解决了这些文件应用过多空间的危险。

  内存映射文件产生的临时文件会寄存于 tmpdir 定义的目录中，在 TempTable 存储引擎敞开或 mysqld 过程敞开时，回收空间；

  当 SQL 创立的外部长期表，抉择 MEMORY 存储引擎时，如果内存中的长期表变的太大，MySQL 将主动将其转为磁盘长期表；

  其能应用的内存下限为 min(tmp_table_size,max_heap_table_size)；

监控 TempTable 从内存和磁盘上调配的空间：

select * from performance_schema.memory_summary_global_by_event_name \
where event_name in('memory/temptable/physical_ram','memory/temptable/physical_disk') \G

  具体的字段含意见：Section 27.12.20.10,“Memory Summary Tables”.

监控外部长期表的创立：

  当在内存或磁盘上创立外部长期表，服务器会减少 Created_tmp_tables 的值；

  当在磁盘上创立外部长期表时，服务器会减少 Created_tmp_disk_tables 的值，

  如果在磁盘上创立了太多的外部长期表，请思考减少 tmp_table_size 和 max_heap_table_size 的值；

  created_tmp_disk_tables 不计算在内存映射文件中创立的磁盘长期表；

例外项：

  长期表 / 临时文件个别较小，然而也存在须要大量空间的长期表 / 临时文件的需要：

1. load data local 语句，客户端读取文件并将其内容发送到服务器，服务器将其存储在 tmpdir 参数指定的门路中；

2. 在 replica 中，回放 load data 语句时，须要将从 relay log 中解析进去的数据存储在 slave_load_tmpdir(replica_load_tmpdir) 指定的目录中，该参数默认和 tmpdir 参数指定的门路雷同；
3. 须要 rebuild table 的在线 alter table 须要应用 innodb_tmpdir 寄存排序磁盘排序文件，如果 innodb_tmpdir 未指定，则应用 tmpdir 的值；

因为这些例外项个别须要较大的空间，所以须要思考是否要将其寄存在独立的挂载点上。

其余：

  列出由失败的 alter table 创立的暗藏长期表，这些长期表以 #sql 结尾，能够应用 drop table 删除；

show extended tables ; 

  通过 lsof +L1 能够查看标识为 delete，但还未开释空间的文件。

   如果想开释这些 delete 状态的文件，能够尝试上面的办法 (不举荐，后果自负)：

cd /proc/${pid}/fd   # ${pid} 示意你想开释的 delete 状态的文件持有者的过程号
ls -al | grep '${file_name}'    # 假如 ${file_name} 是 /opt/mysql/tmp/3306/ibBATOn8 (deleted)
echo ""> ${fd_number}    # ${fd_number} 示意你想开释的 delete 状态的文件的 fd，倒数第三个字段，如 echo"" > 6

总结：

一般的磁盘长期表 | 临时文件 (个别须要较小的空间)：

  长期表 | 临时文件的个别所需的空间较小，会优先寄存于内存中，若超过肯定的大小，则会转换为磁盘长期表 | 临时文件；

  磁盘长期表默认为 InnoDB 引擎，其寄存在长期表空间中，由 innodb_temp_tablespaces_dir 定义表空间的寄存目录，表空间文件相似：temp_[1-20].ibt；MySQL 未定义 InnoDB 长期表空间的最大应用下限；

  当长期表 | 临时文件应用结束后，会主动回收长期表空间文件的大小；

  innodb_temp_data_file_path 定义了用户创立的长期表应用的回滚段的存储文件的相对路径、名字、大小和属性，该文件是全局长期表空间 (ibtmp1)，该文件能够设置文件最大应用大小；

例外项 (个别须要较大的空间)：

  load data local 语句，客户端读取文件并将其内容发送到服务器，服务器将其存储在 tmpdir 参数指定的门路中；

  在 replica 中，回放 load data 语句时，须要将从 relay log 中解析进去的数据存储在 slave_load_tmpdir(replica_load_tmpdir) 指定的目录中，该参数默认和 tmpdir 参数指定的门路雷同；

  须要 rebuild table 的在线 alter table 须要应用 innodb_tmpdir 寄存排序磁盘排序文件，如果 innodb_tmpdir 未指定，则应用 tmpdir 的值；

若用户判断产生的长期表 | 临时文件肯定会转换为磁盘长期表 | 临时文件，那么能够设置 set session big_tables=1; 让产生的长期表 | 临时文件间接寄存在磁盘上；

猜想其设计：

  对于须要较小空间的长期表 | 临时文件，MySQL 要么将其存储于内存，要么放在对立的磁盘长期表空间中，用完即开释；

  对于须要较大空间的长期表 | 临时文件，能够通过设置参数，将其存储于独自的目录 | 挂载点；例如：load local data 语句或须要重建表的在线 alter table 语句，都有对应的参数设置其寄存长期表 | 临时文件的门路；

  以后只有 innodb_temp_data_file_path 参数能够限度 用户创立的长期表应用的回滚段的存储文件的大小，无其余参数能够限度长期表 | 临时文件可应用的磁盘空间；