36 | MySQL中神奇的用户临时表怎么用？

一、前言

你还在为“临时表”和“内存表”两个概念傻傻分不清？“临时表”到底有何妙用？今日说法记录精彩学习历程。

二、内容

2.1.内存表和临时表的区别是什么？

• 内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。

• 临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。

2.2.临时表有哪5个特点？

• 1）语法：create temporary table....；2）只对当前session可见 ；3）可以与普通表重名 ；4）增删改查与普通表同时存在，操作的是临时表 ；5）show tables不显示临时表；6）临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。

2.3.临时表的使用场景有哪些【可以针对临时表的特点说明】？为什么？

• 1）大表的join优化。因为：不同session间临时表名可以重复创建，不用担心创建表失败问题；临时表在客户端断开后，会自动回收。

• 2）分库分表的order by查询优化。

2.4.临时表的物理文件存放、命名与普通表有哪些不同？临时表在内存存放中与普通表有哪些不同？

• 1）表结构，都是以frm结尾，但是临时表是放在“select @@tmpdir 命令”的文件夹内  并且  名字前缀是“#sql{进程 id}_{线程 id}_ 序列号”。2）表数据，在5.6以前是在临时文件夹里创建相同前缀的.ibd文件，5.6之后则是放在临时表空间内

• 每个表都对应一个table_def_key，普通表“库名+表名”，临时表“库名+表名+serverid+threadid”

2.5.临时表的删除操作要记录到binlong中？如果记录到binlog的话目的是什么？备库执行临时表的binlog时由于备库应用日志线程是公用的，那怎么解决临时表冲突问题？

• 不一定，如果binlog_format格式为row就不需要【row格式只记录数值】，如果为mixed和statement就需要记录临时表的操作。举例：

        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*/

• 主从同步

• 主库执行这个语句的线程 id 写到 binlog 中，这样备库执行时候就可以根据serverid+threadid来构建table_def_key

三、思考题

四、评论吊炸天

4.1.用连接池中的连接来操作临时表时，而这些连接不会释放，和数据库保持长连接。这样使用临时表会有问题吗?

会，“临时表会自动回收”这个功能，主要用于“应用程序异常断开、MySQL异常重启”后，不需要主动去删除表。

4.2.insert into select语句好像会给select的表加锁，如果没有索引，就锁全表？

是，最好导出原表

4.3.一个事务创建临时表以后，读写分离就会默认接下来的请求都路由到主库去了，否则就会出现从库没有

4.4.在 session 结束的时候会执行 DROP TEMPORARY TABLE，如果数据库掉电，这个临时表什么时候会被清除？

重启以后MySQL会扫描临时目录，把表都删掉