join优化用临时表：有哪些特征，为什么它适合这个场景？
临时表不是内存表
内存表，Memory 引擎，engine=memory。重启清空，表结构还在
临时表，各种引擎类型 。InnoDB或MyISAM 临时表，写到磁盘上。
一、临时表的特性
1. 建表 create temporary table
2. 只能被创建session 访问，其他线程不可见。 A 创建临时表 t， B 不可见
3. 可与普通表同名
4. show create 及增删改查访问的是临时表。
5. show tables 不显示临时表。
适合join 优化：
1. 不同 session 临时表可重名，多个 session 同时join，不需担心表名重复，导致建表失败
2. 不需担心删除问题。普通表异常断开/重启，专门清理中间生成数据表。session 结束自动删除临时表
二、临时表的应用
分库分表跨库查询典型场景：大表 ht，按字段 f，拆分1024 个分表，分布到 32 个数据库
一般都中间层 proxy，也有直连。
（1）分区 key 以“减少跨库和跨表查询”为依据。大部分语句包含 f 等值条件，f 做分区键。 proxy 解析完 SQL ，确定路由到哪个分表。分表规则（N%1024)
select v from ht where f=N;
（1）另外索引 k，没用分区字段 f
select v from ht where k >= M order by t_modified desc limit 100;
思路1：proxy 层代码实现排序
速度快，拿到分库数据，内存中计算。缺点：
1. 开发工作量大。如 group by， join，中间层开发能力高；
2. proxy 端压力大，内存不够和 CPU 瓶颈问题。
思路2：汇总表
汇总库创建临时表 temp_ht，包含 v、k、t_modified；各个分库执行
select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;结果插入temp_ht 表中；
select v from temp_ht order by t_modified desc limit 100; 得结果。
实践每个分库计算量都不饱和，临时表 temp_ht 放到 32 个分库中某个上。查询逻辑与图 3 类似。
三、为什么临时表可重名？
3.1存储区别不同
create temporary table temp_t(id int primary key)engine=innodb; //这时创建frm 文件保存表结构
放在临时文件目录下，后缀是.frm，前缀是“#sql{进程 id}_{线程 id}_ 序列号”。 select @@tmpdir 显示临时文件目录。
数据存放方式， MySQL 不同版本中处理方式：
5.6 以及之前的版本里，.ibd 为后缀存放，之后临时文件表空间
t1 的 InnoDB 临时表，MySQL 存储上认为跟普通表 t1 不同，session A 的 id 是 4，session B 的线程 id 是 5。磁盘上文件不重名。
3.2 内存区别不同
每个表都对应一个 table_def_key。普通表 table_def_key 值是由“库名 + 表名”得到的，同库下创建同名普通表，第二table_def_key 已存在。
临时表table_def_key 在“库名 + 表名”基础上，加了“server_id+thread_id”。
每个线程维护了自己临时表链表。session 内操作，先遍历链表，临时表优先；结束时，对链表里每个临时表，执行 “DROP TEMPORARY TABLE + 表名”，binlog 中也记录 DROP TEMPORARY TABLE 命令。
四、临时表和主备复制
写 binlog，意味备库需要。
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*/
如临时表操作不记录，备库只有 create table t_normal 表和 insert into t_normal select * from temp_t 的 binlog 日志，备库执行 insert into t_normal 报“表 temp_t 不存在”。如的binlog_format=row，临时表有关语句，不记 binlog 里。
创建临时表语句会传到备库执行，同步线程（备库）创建临时表。主库线程退出自动删除临时表，备库同步线程持续运行。主库再写DROP TEMPORARY TABLE 传给备库执行。
4.1有趣的问题：
记录 binlog时，不论create table还是 alter table都原样记录，空格都不变。
drop table t_normal， binlog 就会写成：
DROP TABLE `t_normal` /* generated by server */ 统一标准格式。为什么？
drop table 命令可一次删除多个表。上面例子binlog_format=row主库 "drop table t_normal, temp_t" binlog 记录：
DROP TABLE `t_normal` /* generated by server */被服务端改写过的命令。
备库上没有表 temp_t，重写后再传到备库执行，不会导致备库同步线程停止。
4.2另外一个问题
主库不同线程创建同名临时表没关系，传到备库执行是怎么处理的呢？
S 是 M 备库。
table_def_key 不同，当做不同临时表处理。（主库线程 id 写到 binlog）
1. A 临时表 t1，备库table_def_key 就是：库名 +t1+“M 的 serverid”+“session A thread_id”;
2. B 临时表 t1，备库table_def_key 就是 ：库名 +t1+“M 的 serverid”+“session B 的 thread_id”。
小结
临时表用法和特性。
处理比较复杂的计算逻辑。自己可见的，所以不需考虑重名问题。在自动删除
binlog_format='row’临时表不记录到 binlog
思考题
临时表改名：alter table 可以，不能用 rename 语法。什么原因？
rename table按照“库名 / 表名.frm”去磁盘找，临时表frm 文件在 tmpdir 目录下，修改table_def_key，且文件名规则是“#sql{进程 id}_{线程 id}_ 序列号.frm”，报错。
评论1
用连接池中连接来操作，而连接不会释放，保持长连接。用临时表会有问题吗?。
会，“自动回收”用于“应用程序异常断开、MySQL异常重启”后，不需主动删除表。
评论2
1. session 结束DROP TEMPORARY TABLE，掉电，临时表什么时候被清除
2. binlog 中记录了临时表的操作，session 不同，从库中访问不到，这样做的意义是什么
作者回复: 1. 好问题，重启后MySQL会扫描临时目录，把表都删掉；
2. binlog是statement的时候，需同步备库，否则备库上执行insert into t_normal (select * from t_temp) 报错
