MySQL临时表详细解释

MySQL临时表详细解释

1.1 临时表简介

MySQL临时表在很多场景中都会用到，比如用户自己创建的临时表用于保存临时数据，以及MySQL内部在执行复杂SQL时，需要借助临时表进行分组、排序、去重等操作。临时表的作用仅限于本次会话，等连接关闭后重新打开连接临时表将不存在。

1.2 临时表类型

1.2.1 外部临时表

通过create temporary table语句创建的临时表为外部临时表，在创建时可以手动指定临时表的存储引擎。

create temporary table temp_table(
	id int,
	name varchar(10)
) ENGINE = InnoDB;
insert into temp_table values (1,'1');

select * from temp_table ;

1.2.2 内部临时表

通常在执行复杂SQL语句时，比如group by，distinct，union等语句，执行计划中如果包含Using temporary，那么MySQL内部将使用自动生成的临时表，以辅助SQL的执行。

explain select username from userinfo group by username;

explain select distinct age from userinfo ;

explain select * from userinfo union select * from userinfo;

1.2.3 临时表说明

1）临时表，顾名思义就是"临时"的表，不管是内部临时表还是外部临时表，都是临时的，我们查询不到；在服务器重启之后，所有的临时表将会被销毁。

不管是内部临时表还是外部临时表，我们都查询不到，但外部临时表可以查询到里面的数据。

2）临时表是每个进程独享的，当前进程（客户端）创建的临时表，其他进程（客户端）是查不到临时表里面的数据的。

• 再打开一个窗口，看是否能查询到我们刚刚那个窗口创建的临时表里面的数据：

发现查询不到其他客户端创建的临时表数据。

1.3 临时表相关系统参数变量

• 查看临时表相关系统变量：

show variables like '%tmp%';

• tmpdir：临时表存储路径
• tmp_table_size：内部临时表内存最大值，超过此值将转移到磁盘中存储。
• max_heap_table_size：外部临时表内存最大值，超过此值将转移到磁盘中存储。
• default_tmp_storage_engine：外部临时表默认采用的存储引擎。
• internal_tmp_disk_storage_engine：内部临时表默认采用的存储引擎

1.4 临时表相关状态参数

show status like '%tmp%';

• Created_tmp_disk_tables：MySQL创建内部磁盘临时表次数。
• Created_tmp_tables：MySQL创建内部临时表次数。
• Created_tmp_files：创建的临时表文件数（系统内部维护，不用我们管）。