36 | 临时表和临时表

• 内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
• 临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，默认是MyISAM 引擎，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。

临时表特点：

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

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

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

临时表的应用

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

一般等值查询：

select v from ht where f=N;

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

但是遇到复杂一点的语句：

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

只能到所有的分区中去查找满足条件的所有行，然后统一做 order by 的操作。这种情况下，有两种比较常用的思路：

1. 在 proxy 层的进程代码中实现排序

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

1. 需要的开发工作量比较大。我们举例的这条语句还算是比较简单的，如果涉及到复杂的操作，比如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高；
2. 对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。

2. 把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。

流程：

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

在创建临时表的时候，MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。

这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id} 序列号”

而关于表中数据的存放方式，在不同的 MySQL 版本中有着不同的处理方式：

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

至于为什么不会重名：

• 一个普通表的 table_def_key 的值是由“库名 + 表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现 table_def_key 已经存在了。
• 而对于临时表，table_def_key 在“库名 + 表名”基础上，又加入了“server_id+thread_id”。

在实现上，每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session 结束的时候，对链表里的每个临时表，执行 “DROP TEMPORARY TABLE + 表名”操作。

临时表和主备复制

临时表的操作也会记录到binlog，既然写binlog，意味着备库也会执行。

为什么要记录binlog的原因，demo：

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

以上如果不记录临时表的操作，那么就会报错“表 temp_t 不存在”。

如果binlog设置为binlog_format=row格式，那么就不会记录临时表有关的语句，此时记录的是这个操作的数据。即：write_row event 里面记录的逻辑是“插入一行数据（1,1)”。

也就是说，只在 binlog_format=statment/mixed 的时候，binlog 中才会记录临时表的操作。这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。

通常备库执行drop语句，一般是服务端修改过的，比如：

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

“/* generated by server */”说明了这是一个被服务端改写过的命令。

因为假如真的是row格式，drop table t_normal, temp_t;是会报找不到临时表的。