36 为什么临时表可以重名

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引擎，建表语法是engine=memory，内存表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在，

--临时表，可以使用各种引擎，如果是innodb引擎或者myisam引擎，写数据的时候是写到磁盘上，当然，也可以使用memory引擎

临时表的特性

SESSION A	SESSION B
create temporary table tmp_t(c int)engine=myisam;
	(system@127.0.0.1:3306) [test]> show create table tmp_t; ERROR 1146 (42S02): Table 'test.tmp_t' doesn't exist
(system@127.0.0.1:3306) [test]> create  table tmp_t(c int primary key)engine=innodb; (system@127.0.0.1:3306) [test]> show create table tmp_t | tmp_t | CREATE TEMPORARY TABLE `tmp_t` (   `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
	insert into tmp_t values(1); select * from tmp_t; //返回1
select * from tmp_t;//返回空

特点：

--1 建表语法create temporary

--2 一个临时表只能被创建它的session访问，对其他线程不可见，所以，session a创建的临时表t，对于session b就是不可见

--3 临时表可以与普通表重名

--4 session a内有同名的临时表和普通表的时候，show create语句，以及dml都是访问的临时表

--5 show tables命令不显示临时表

由于临时表只能被创建的session访问，所以在session结束的时候，会自动删除表，临时表就特别适合上面的join优化这种场景，原因：

--1 不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表名重复到时建表失败的问题

--2 不需要担心数据删除的问题，如果使用普通表，在流程执行中客户端发生的异常端口，或者数据库发生异常重启，还需要专门来清理这种过程中生成的数据表，而临时表由于会自动回收，不需要这个额外的操作。

临时表的应用

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

一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上，比如，将一个大表ht，按照字段f，拆分成1024个表，然后分布到32个数据库实例上，如下图

一般情况下，这种分库分表系统都有一个中间层proxy，不过，也有一些方案会让客户端直接连数据库，也就是没有proxy层

在这个架构中，分区key的选择是以”减少垮库和跨表查询”为依据，如果大部分的语句都会直接包含f的等值条件，那么久用f做分区键，这样，proxy层解析完sql语句以后，就能确定这条语句路由到那个分表做查询。

比如

select v from ht where f=N;

这时，我们就可以通过分表规则（比如N%1024）来确认需要的数据被放在分表上，这种语句只需要访问一个分表，是分库分表方案最欢迎的语句形式。

但是，如果这个表上有另外一个索引k，并且查询语句是这样

select v from ht where k>=M order by t_modifed desc limit 100;

这时候，由于查询条件里面没有分区key，只能到所有的分区中取查询满足条件的记录，然后统一做order by，这种情况下，常见的思路有两种：

--1 在proxy层的代码中实现排序

这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算，缺点也比较明显

---1 需要的开发工作量比较大，如果涉及到复杂的group by等，甚至join这样的操作，都对中间层开发的要求比较高

---2 对proxy端的压力比较大，尤其是很容易出现内存不够和cpu的瓶颈

--2 把各个分库拿到的数据，汇总到一个mysql实例的一个表中，然后在汇总表进行逻辑操作

比如，在汇总库上创建一个临时表tmp_ht，表里包含v,k,t_modified 3个字段，在各个分库上执行，

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

把分库执行的结果插入到临时表tmp_ht，执行

select v from temp_ht order by t_modified desc limit 100;

得到结果，

 

在实践中，会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库上的某一个，

为什么临时表可以重名？

在执行语句

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

Mysql要给这个innodb表创建一个from文件保存表结构定义，还要有地方保存表数据。

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

(system@127.0.0.1:3306) [test]> select @@tmpdir;
+-------------------------------+
| @@tmpdir                      |
+-------------------------------+
| /home/data/mysqldata/3306/tmp |
+-------------------------------+

关于表中数据存放方式，在不同的mysql版本里有不同的方式

--5.6 

[mysql@mysqlhq scripts]$ cd /home/data/mysqldata/3306/tmp
[mysql@mysqlhq tmp]$ ll
total 16
-rw-rw---- 1 mysql mysql 8554 Mar 20 10:42 #sql2bb_4ffb3_0.frm
-rw-rw---- 1 mysql mysql    0 Mar 20 10:42 #sql2bb_4ffb3_0.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 20 10:42 #sql2bb_4ffb3_0.MYI

--5.7 开始，在mysql引入了个临时表空间，专门用来存放临时文件的数据，就不用在创建ibd文件了。

Mysql维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表对应一个table_def_key

--一个普通表的table_def_key由”库名+表名”得到

--对于临时表，table_def_key在以上的基础上，又加入了”server_Id+thread_id”

当session a和session b创建两个临时表t1，他们的table_def_key不同，磁盘文件名也不同，因此可以并存。

实际上，每个线程都维护了自己的临时表链表，这样每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表，在session结束的时候，对链表里的每个临时表，执行”drop temporary table t1”,这时候，会发现，binlog中记录了drop temporary table这个命令

临时表和主备复制

既然写了binlog，就意味着备库需要

可以设想一下，在主库上执行了下面这个语句序列

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录，那么备库只有2,4语句在binlog日志，备库执行到语句4就会报table temp_t不存在，

如果把binlog_format设置为row，在insert的时候就是insert into t_normal values(1,1),那么跟临时表有关的语句，就不会记录到binlog里，也就是说只有binlog_format=mixed/statement，binlog才会记录表的操作

这种情况下，创建临时表的语句会传到备库，因此备库的同步线程就会创建这个临时表，主库线程退出时会自动删除该临时表，但是备库的同步线程是持续运行，所以，在备库就需要在主库上写个drop temporary table传给备库执行。

Mysql在记录binlog的时候，不论create table还是alter table语句，都是原样记录，但是如果在执行drop table table系统会写成

DROP TABLE `t_normal` /* generated by server */

改写成了标准的格式

在drop table命令是可以一次性删除多个表的，比如在上面的例子中，binlog_format=row，如果在主库上执行 drop table t_normal ，tmp_t那么binlog中就只能记录

DROP TABLE `t_normal` /* generated by server */

因为备库上并没有表tmp_t，这样这个命令重写后在传到备库执行，才不会导致备库同步线程停止。

说到主备复制，还有另外一个问题需要解决，主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的呢？

Mysql在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中，这样在备库的应用线程就能够知道执行每个语句的主库线程id，并利用这个线程id来构造临时表的table_def_key

--1 session a的临时表t1，在备库的table_def_key就是：库名+t1+M的server_id+session a的thread_id

--2 session b的临时表t1，在备库的table_def_key就是：库名+t1+M的server_id+session b 的thread_id

小结：

在实际应用中，临时表一般用于处理比较复杂的计算逻辑，由于临时表的每个线程都自己可见，所以不需要考虑多个线程执行同一个处理逻辑，临时表的重名问题，在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作

在binlog_format=row的时候，临时表的操作不记录到binlog中。

注意上面说的临时表是用户自己创建的

(system@127.0.0.1:3306) [test]> create temporary table tmp_t(c int)engine=myisam;
Query OK, 0 rows affected (0.01 sec)
(system@127.0.0.1:3306) [test]> alter table tmp_t rename to tmp_t2; ##alter正常
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
(system@127.0.0.1:3306) [test]> rename table tmp_t2 to tmp_t3; ##rename报错
ERROR 1017 (HY000): Can't find file: './test/tmp_t2.frm' (errno: 2 - No such file or directory)

在使用连接池的情况下，对”临时表的自动回收功能”，主要用于”应用程序异常断开、mysql异常重启”后，不需要主动去删除，而平常在正常使用的时候，还是应该用完删除。

Mysql在重启时候，会扫描临时目录，把表全部删除掉。