Mysql临时表的用法

Posted on | Edited on | In ,

外部临时表

本问主要解决如下的问题：

  1. 临时表为什么能重名
  2. 什么情况下使用临时表

临时表和引擎是Mermory表的区别

  • 首先内存表的数据是保存到内存中的，当数据库关闭后数据会消失，只保留表结构。
  • 而临时表是可以指定任意表引擎的，且临时表和当前Mysql的htread现关，当一个session关闭后临时表会被删掉。
  • 不同session的临时表可以重名

为什么临时表能重名

临时表是和当前的线程相关：

  • Mysql5.6包含5.6之前，Mysql会在临时文件目录下建立一个”前缀.ibd”的文件，用来存放数据文件
  • Mysql5.7之后的版本，引入了一个临时表空间，专门用来存放练市文件，不需要在创建ibd文件了。
  • 前缀是：库名+表名+serverid名+线程id。

Mysql每个名都有一个def_table_name，临时表的表明就是”库名+表名+serverid名+线程id”，所以不同线程之间的临时表是可以重名的。

注：innodb类型的临时表会在临时文件目录下，文件名的是#sql{进程 id}+{线程 id}+序列号，语句如下：

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

如何使用临时表

1
2
3
4
5
6
7
8
9
10
11

#建表语法是：
create temporary table temp_t like t1;

#查看临时表
show create table temp_t;

#对临时表操作
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);

mysql每个库都维护一个表名的链表，临时表排在前面，查找表名时候会轮训链表，发现表名对应的表是临时表时候会优先使用临时表。

临时表的使用场景

分库分表：比如我们按照字段对一个表进行分库分表，当有一个查询条件不是按照分表规则进行查询时候有俩种解决方法

  1. proxy来做聚合
  2. 通过临时表聚合

临时表的主备库处理

  • 当binlog格式是state时候，主从同步时候要把临时表涉及的binglo都同步过去
  • 如果是row格式则不用。
  • 另外删除临时表的binlog会在一条语句中完成，所以只有drop语句binlog会格式化，crud的语句statement会原封不懂的传递给从库