MySQL深入32-临时表深入
# MySQL深入32-临时表深入
临时表和内存表的区别：
- 内存表：指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
- 临时表：可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。
# 临时表的特性
现有如下操作序列：
临时表在使用上有几个特点：
- 建表语法是create temporary table。
- 一个临时表只能被创建它的 session 访问，对其他线程不可见。
- 临时表可以与普通表同名。
- 增删改查用的是临时表
- show tables不显示普通表
可以发现这些特点特别适合join优化这种场景，原因如下：
- 不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。
- 不需要担心数据删除问题。临时表由于会自动回收，不需要额外的操作。
# 临时表的应用
由于不用担心线程之间的重名冲突，临时表经常会被用到复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。
一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如，将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上。
一般情况下，这种分库分表系统都有一个中间层proxy，有一些是没有proxy这一层的。
在这个架构中，分区key的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包含 f 的等值条件，那么就要用 f 做分区键。这样，在 proxy 这一层解析完 SQL 语句以后，就能确定将这条语句路由到哪个分表做查询。
对于使用分区键的查询语句：
select v from ht where f=N;
这时，我们就可以通过分表规则（N%1024）来确认需要的数据被放在了那个分表上。
但是对于没有使用分区键的查询语句：
select v from ht where k >= M order by t_modified desc limit 100;
由于查询语句里面没有分区字段f，就只能到所有分区中找到满足条件的所有数据行，然后统一做排序操作，这种情况有两种思路优化：
思路一：proxy 层实现排序
这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。但是也存在缺点：
- 需要的开发工作量比较大。如果涉及到复杂语句，就需要对中间层的开发能力要求较高。
- 对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。
思路二：把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。
流程如下：
在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；
在各个分库上执行对于的查询语句
把分库执行的结果插入到temp_ht表中
最后对临时表执行查询操作
# 临时表为什么可以重名
在执行创建临时表语句的时候：
create temporary table temp_t(id int primary key)engine=innodb;
MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。
这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id} 序列号”
MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表对应一个table_def_key。
- 一个普通表的 table_def_key 的值是由“库名 + 表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现 table_def_key 已经存在了。
- 而对于临时表，table_def_key 在“库名 + 表名”基础上，又加入了“server_id+thread_id”。
在实现上，每个线程都维护了自己的临时表链表。这次每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束的时候，对链表的每个临时表，执行 “DROP TEMPORARY TABLE + 表名”操作。