36 | 为什么临时表可以重名

前面，在join优化查询的时候用到了临时表：

create temporary table temp_t like t1;

alter table temp_t add index(b);

insert into temp_t select * from t2 where b>=1 and b<=2000;

select * from t1 join temp_t on t1.b=temp_t.b;

那为什么要用临时表呢？直接用普通表是不是也可以呢？

临时表有哪些特征，为什么它适合这个场景？

首先，临时表和内存表是完全不同的两个概念。

• 内存表，指的是使用Memory引擎的表，建表语法是create table ... engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两点外，从其他特征上看，就是一个正常的表。
• 临时表，可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。

临时表特性

临时表在使用上有以下几个特点：

1. 建表语法是create temporary table...。
2. 一个临时表只能被创建它的session访问，对其他线程不可见。所以，上图中session A创建的临时表t对于session B是不可见的。
3. 临时表可以与普通表同名。
4. session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
5. show tables命令不显示临时表。

由于临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表。也正是由于这个特性，临时表就特别适合join优化这种场景。

原因有两个方面：

1. 不同session的临时表可以同名，如果有多个session同时执行join优化，不需要担心表名重复导致建表失败的问题。
2. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

临时表的应用

由于不用担心线程之间的重命名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的垮裤查询就是一个经典的使用场景。

为什么临时表可以重名

create temporary table temp_t(id int primary key)engine=innodb;

这个语句，MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是"#sql{进程id}_{线程id}_序列号"。可以使用select @@tmpdir命令，来显示实例的临时文件目录。

数据存储方式，不同的MySQL版本中有不同的处理方式：

• 5.6以及之前的版本里，MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文件，用来存放数据文件；
• 5.7版本开始，MySQL引入了一个临时文件表空间，专门用来放临时文件数据。因此，我们就不需要再创建ibd文件了。

从文件名的前缀规则，可以看出，其实创建一个叫做t1的InnoDB临时表，MySQL在存储上认为我们创建的表名跟普通表t1是不同的，因此同一个库下面已经有普通表t1的情况下，还是可以再创建一个临时表t1的。

临时表和主备复制

 

上一篇：35 | join语句怎么优化

下一篇：37 | 什么时候会使用内部临时表