为什么临时表可以重名

内存表: 指的是使用Memory引擎的表、创建语法是: create table … engine=memory. 这种表的数据都保存在内存里、系统重启的时候会被清空, 但表结构还在. 而、临时表是可以使用各种引擎类型. 若使用的是innodb引擎或者mysiam引擎、写数据的时候是写到磁盘的、当前, 临时表也可以使用memory引擎.

临时表的特性

创建语法是: create temporary table…
一个临时表只能被创建它的session访问、对其它线程不可见. (A线程创建的临时表t、B线程不可见). 在session结束时自动删除.
临时表可以与普通表同名
session A内具有同名的临时表和普通表时、show create 语句、及增删改查语句、访问的是临时表
show tables. 不显示临时表.

临时表的应用

分库分表的跨库查询.

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

一般有两种查询场景. 1) 可直接定位分表. 2) 无法定位分表、需要多表数据重组.

此时一般有两种思路解决:

1) 在proxy层进行代码排序

优势: 处理速度快、拿到分库的数据后、直接在内存中参与计算. 但:

开发工作量大, eg. group by, join 等的操作、对中间层开发要求高.

对proxy的压力较大、容易出现内存不够用和CPU瓶颈问题.

2) 将各表拿到的数据、汇总到一个MySQL实例的一个表、然后在汇总实例上进行逻辑操作.

join优化的场景

1) 不同session临时表命名是可以重复的、若有多个session同时执行join优化、无需担心表名重复建表失败.

2) 无需担心数据删除问题. 若使用临时表、在执行流程过程中Client异常断开、或者DB异常重启、还需要专门清理中间过程生成的数据表、而临时表会自动回收、不需要这个额外的操作.

为什么临时表可以重名 ?

在执行 create temporary table tmp(id int primary key)engine=innodb; 这个语句时、Mysql会给innodb表创建一个 frm 文件保存表结构定义、还要有地方保存表数据. frm放在临时文件目录下 #sql{进程id}_{线程id}_序列号、 select @@temdir 可以查看临时文件目录.

5.6之前、MySQL会在临时文件目录下创建一个相同前缀、.ibd 为后缀的文件、用来存放数据文件;

5.7开始、MySQL引入临文件表空间、专门存放临时文件数据、不需要创建ibd文件.

其实创建一个叫t1的innodb临时表、MySQL在存储上认为跟普通表t1不同、可以与普通表重名.

MySQL维护数据表、除了物理上要有文件外、内存里也有一套机制区别不同的表、每个表对应一个table_def_key. 普通表的table_def_key是库名 + 表名得到、而临时表的table_def_key是库名 + 表名 + server_id + thread_id, 所以, 临时表不能重名而临时表是可以的.

在实现上、每个线程都维护了自己的临时表链表. 这样每次session内操作表的时候、先遍历链表、检查是否有对应临时表、有: 优先操作、无: 再操作临时表; session结束时、对链表里的每个临时表都执行 drop temporary table + tb_name的操作. 此时会发现: binlog也记录了 drop temporary操作

```

SET TIMESTAMP=1584267384/!/;

SET @@session.pseudo_thread_id=19/!/;

DROP /*!40005 TEMPORARY */ TABLE IF EXISTS x,tmp

```

临时表只有在县城内可以访问、为什么要写binlog呢 ? –这就需要考虑主备复制了.

临时表和主备复制.

既然写binlog, 就意味着备库需要. eg. 下边的语句序列

create table t_normal(id int primary key, c int)engine=innodb; // Q1

create temporary table tmp_t like t_normal; // Q2

insert into tmp_t values(1,1); // Q3

insert into t_normal select * from tmp_t; // Q4

若关于临时表的操作不记录、在备库重放binlog时、就会报错: tmp_t不存在

如果把binlog设为row格式能解决吗 ? binlog是row时、记录的是操作的数据 插入一行数据(1,1). 确实如此、若binlog_format=row、那么跟临时表有关的操作不会记录到binlog、

即: 只有binlog_format=statement / mixed时、binlog才会记录临时表的操作.

此时, 创建临时表的语句会传到备库执行、备库同步时也会创建临时表、所以、也需要在主库上记录 drop temporary table、传给备库.

MySQL在记录binlog时、不论是create 还是 alter都会原样不变、但drop确会变成

1	drop table `t_normal` /generated by server/

也就是改成了标准格式. 为什么呢 ?

因为 drop是可以一次删除多个表. eg. 若设置 binlog_format=row, 若主库执行 drop table t_normal,tmp_t, 则binlog只记录 drop table t_normal; 因为备库无tmp_t、备库执行会出错、所以, 必须将语句改写.

思考:

备库如何处理不同线程同名临时表

MySQL在记录binlog时、会把线程id写入binlog、这样备库的应用线程就能知道每个语句的主库线程id、并利用这个线程id来构造临时表的table_def_key.

临时表可使用 alter修改表名、不能使用rename ?

因为在rename table时、是按照 db/table.frm 的规则去磁盘找文件、但临时表在磁盘上的frm文件是放在tmpdir目录下的、并且命名规则是: #sql{进程id}_{线程id}_序列号.frm, 所以会找不到.