MySQL 临时表

临时表和内存表

• 内存表指的是使用Memory引擎的表，建表语法是create table ... engine=memory。表数据保存在内存中，系统重启时会被清除，但表结构存在
• 临时表的引擎可以使用各种类型，临时表的建表语法是create temporary table ... engine=xxx，如果使用的是InnoDB或者MyISAM引擎，写数据的时候是往磁盘写入。

临时表的特点

• 临时表只对创建它的Session访问，对其他线程不可见
• 临时表可以和普通表同名
• 如果一个Session中有同名的临时表或者普通表的时候，show create语句，增删改查语句访问的是临时表
• show tables命令不显示临时表
• 临时表的数据会在Session意外或主动断开时删除

临时表的应用

• 复杂查询的优化借助临时表
• 分库分表查询

分库分表中应用临时表

分库分表就是我们把一个大表拆开分到不同的数据库实例上，比如将一个大表bt按照字段id（分区key）拆分成32个库中。分区key的选择尽量减少跨库和跨表查询。

select * from bt where id = N;

假设我们的分表规则是N%32，那么计算出来的值就是在哪个库上，然后直接去该库上取回结果。

select * from bt where k >= m order by create_time desc limit 100;

由于在查询条件中没有用到分区字段，因此我们必须去所有的库中查找满足条件的行，然后统一做order by处理。

对于分库分表的系统，我们都会有一个中间层Proxy，借助Proxy我们有两种思路实现：

1. 第一种思路是在Proxy中进行排序，内存中计算速度快，但对Proxy端压力较大，而且如果查询语句涉及到更复杂的操作（比如group by或者join），对中间层的开发能力要求较高
2. 第二种思路是把各个分库拿到的数据，汇总到一个MySQL实例的一个临时表中，然后在汇总实例的临时表上进行逻辑操作。

临时表表结构存储在哪？

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

创建临时表时，需要给上面的InnoDB表创建一个frm文件保存表结构，且需要有地方保存数据。

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

-- 查看临时目录
select @@tmpdir;

临时表的数据存放在哪里？

• MySQL5.6以前，会存放在临时目录下，创建一个相同前缀，以.ibd为结尾的文件用来存放数据
• MySQL5.7开始，MySQL引入了一个临时文件表空间，专门用来放存放临时文件的数据

参数innodb_temp_data_file_path：定义临时表空间的路径、文件名、初始化大小和最大上限。

show global variables like 'innodb_temp_data_file_path';

为什么不同线程可以创建同名临时表？

两个Session由于线程ID不一样，因此创建的临时表在磁盘上的文件不会重名。

MySQL除了维护物理文件，在内存中也要区分不同的表，每个表对应一个table_def_key：

• 普通表的table_def_key是由库名+表名得到，因此无法创建相同的表
• 对于临时表，table_def_key在库名和表名的基础上还加入了server_id和thread_id

每个线程都维护自己的临时表链表，线程在操作表时，会先遍历临时表链表，如果在临时表链表中有优先操作临时表，如果没有再操作普通表，当线程退出时，会对链表里面的每个临时表执行drop temporary table + 表名的操作。

临时表和主备复制

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

如果binlog的格式是ROW模式，上述关于临时表的日志不会写入binlog，因为ROW模式下记录insert into t_normal时记录的是这个操作的数据，因此临时表的相关日志可以不写入binlog。

如果binlog的模式是statement或者mixed，binlog必须要写入临时表的相关操作，假设我们没有写入到binlog，insert into t_normal select * from temp_t;这条语句被传到备库回放时将会因为找不到表而报错，从而导致同步中断。

上述创建临时表的语句会被同步到备库执行，因此备库的同步线程也会创建这个临时表。主库线程在退出时会自动删除临时表，但是备库线程一直在运行，因此，我们需要在主库上再写一个DROP TEMPORARY TABLE传给备库执行，如下图：

主库两个线程创建了同名的临时表，从库如何处理？

MySQL在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中，备库的同步线程能够知道每个执行语句的主库线程id，通过这个线程id来构造临时表的table_def_key（库名+表名+severid +线程id)

由于table_def_key不同，所以两个Session的同名临时表在备库中不会发生冲突。