鲁达 1 为什么临时表可以重名
1.1 临时表特性
内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。
1. 建表语法是 create temporary table … engine=存储引擎xxx(memory,myisam,innodb),可以指定存储引擎。默认引擎跟普通表一样是innodb
2. 一个临时表只能被创建它的 session 访问，对其他线程不可见。在这个 session 结束的时候，会自动删除临时表
3. 临时表可以与普通表同名。不同 session 的临时表也是可以重名的.
4. session 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。show tables 命令显示普通表。
5，重启以后MySQL会扫描临时目录，把表都删掉；
1.2 应用场景在：跨库查询
由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。
假设用户表user根据id被分成了10个表。
- select id,name from user where id=5; (只需要根据5%10,确定表名user_5,只发出一条sql语句，就可以得到结果。)
如果要查询
- select id,name from user where name='张三';
无法根据利用分表规则，就必须发出10条查询语句去每个user_x表中查询。
这时常见有两种解决办法：
- 拿到每个查询结果，在内存中处理
- 拿到每个查询结果，放入某个库的临时表。再去查询临时表。
1.3 为什么不同线程创建的临时表可以重名？
物理上区分
1，因为普通表和临时表放在不同目录下，普通表放在安装目录/mysql_xx版本/data/数据库名/目录下。临时表放在安装目录/mysql_xx版本/tmp目录下
2，临时表名生产规则根据线程来命名的，#sql{进程 id}_{线程 id}_ 序列号，使用 select @@tmpdir 命令，可以查看临时表目录
比如my版本中，各个引擎存放文件
innodb-(# #)
memory(#)
myisam(# # #)
内存上区分
1，每个表都对应一个 table_def_key。一个普通表的 table_def_key 的值是由“库名 + 表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现 table_def_key 已经存在了。
2，对于临时表，table_def_key 在“库名 + 表名”基础上，又加入了“server_id+thread_id”。
3，session A 和 sessionB 创建的两个临时表 t1，它们的 table_def_key 不同，磁盘文件名也不同，因此可以并存。
4，在实现上，每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session 结束的时候，对链表里的每个临时表，执行 “DROP TEMPORARY TABLE + 表名”操作。这时候你会发现，binlog 中也记录了 DROP TEMPORARY TABLE 这条命令。
1.4 临时表和主备复制
临时表只在线程内自己可以访问，为什么需要写到 binlog 里面？既然写 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 设置为 row 格式，因为 binlog 是 row 格式时，在记录 insert into t_normal 的 binlog 时，记录的是这个操作的数据，即：write_row event 里面记录的逻辑是“插入一行数据（1,1)”，那么跟临时表有关的语句，就不会记录到 binlog 里。也就是说，只在 binlog_format=statment/mixed 的时候，binlog 中才会记录临时表的操作。
- 如果当前的 binlog_format=statment/mixed，这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。
也就是说binlog_format=statment/mixed情况下，备库中的临时表不会主动删除，需要在主库执行DROP TEMPORARY TABLE 传给备库执行。（提问：如何在 备库上删除临时表？）
1.5 思考
思考1
MySQL 在记录 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，将这个命令重写后再传到备库执行，才不会导致备库同步线程停止。所以，drop table 命令记录 binlog 的时候，就必须对语句做改写。“/* generated by server */”说明了这是一个被服务端改写过的命令。
思考2
主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的呢？
现在，我给你举个例子，下面的序列中实例 S 是 M 的备库。
图 5 主备关系中的临时表操作
主库 M 上的两个 session 创建了同名的临时表 t1，这两个 create temporary table t1 语句都会被传到备库 S 上。但是，备库的应用日志线程是共用的，也就是说要在应用线程里面先后执行这个 create 语句两次。（即使开了多线程复制，也可能被分配到从库的同一个 worker 中执行）。那么，这会不会导致同步线程报错 ？
备库线程在执行的时候，要把这两个 t1 表当做两个不同的临时表来处理。这是怎么实现的呢？
MySQL 在记录 binlog 的时候，会把主库执行这个语句的线程 id 写到 binlog 中。这样，在备库的应用线程就能够知道执行每个语句的主库线程 id，并利用这个线程 id 来构造临时表的 table_def_key：
1. session A 的临时表 t1，在备库的 table_def_key 就是：库名 +t1+“M 的 serverid”+“session A 的 thread_id”;
2. session B 的临时表 t1，在备库的 table_def_key 就是 ：库名 +t1+“M 的 serverid”+“session B 的 thread_id”。
由于 table_def_key 不同，所以这两个表在备库的应用线程里面是不会冲突的。
思考3
临时表可以使用 alter table 语法修改表名，而不能使用 rename 语法。你知道这是什么原因吗？
在实现上，执行 rename table 语句的时候，要求按照“库名 / 表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的，并且文件名的规则是“#sql{进程 id}_{线程 id}_ 序列号.frm”，因此会报“找不到文件名”的错误。
思考4
在程序中，用的是连接池中的连接来操作的，而这些连接不会释放，和数据库保持长连接。这样使用临时表会有问题吗?。
肯定会，所以在程序开发中，用完记得手动删除。
2 内存临时表的应用
内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。内存临时表使用的是memory引擎，Using temporary，表示使用了临时表；
MySQL 什么时候会使用内部临时表？
1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，效果最好。否则就需要额外的内存，来保存中间结果；
2. mysql的额外的内存：join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；内存够用，就会用磁盘临时文件。
3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。
2.1 使用场景
1，union 关键字(select 1000 as f) union (select id from t1 order by id desc limit 2);
如果查询出来的记录有重复，会创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。内存临时表可以通过主键去重，使用完后，会删除临时表。
2，group by 关键字 select id%10 as m, count(*) as c from t1 group by m; 分组之后默认会排序。
这个语句的执行流程是这样的：
1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m；
2. 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；
如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);
如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；
3. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。
如果你的需求并不需要对结果进行排序，那你可以在 SQL 语句末尾增加 order by null，也就是改成：
select id%10 as m, count(*) as c from t1 group by m order by null;
内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。
set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;
如果内存临时表大小不够，内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。
2.2 group by 优化方法 -- 索引
不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表。
执行 group by 语句为什么需要临时表？group by 的语义逻辑，是统计不同的值出现的个数。由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表存储，来记录并统计结果。
假设输入的数据是有序的，像下面这样
（图 1）
可以看到，如果可以确保输入的数据是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个过程：
当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集里的第一行就是 (0,X);
当碰到第一个 2 的时候，已经知道累积了 Y 个 1，结果集里的第二行就是 (1,Y);
按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到 group by 的结果，不需要临时表暂存计算，也不需要再额外排序。
InnoDB 的索引，就可以满足这个输入有序的条件。
在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引（如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引，来解决这个问题）。
alter table t1 add column z int generated always as(id % 100), add index(z);
这样，索引 z 上的数据就是类似图 1 这样有序的了。上面的 group by 语句就可以改成：
select z, count(*) as c from t1 group by z;
2.3 group by 优化方法 -- 直接排序
如果我们明明知道，一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。
那么，我们就会想了，MySQL 有没有让我们直接走磁盘临时表的方法呢？答案是，当然有。
在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。
MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
执行流程就是这样的：
1. 初始化 sort_buffer，确定放入一个整型字段，记为 m；
2. 扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；
3. 扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；
4. 排序完成后，就得到了一个有序数组。
根据有序数组，得到数组里面的不同值，以及每个值的出现次数。这一步的逻辑，你已经从前面的图 1 中了解过了。
下面两张图分别是执行流程图和执行 explain 命令得到的结果。
图 12 使用 SQL_BIG_RESULT 的执行流程图
从 Extra 字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法。
总结一些使用的指导原则：
1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
2. 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。
2.4 思考
1 如果只需要去重，不需要执行聚合函数，distinct 和group by那种效率高一些呢？
只需要去重的话，如果没有limit，是一样的；有limit的话，distinct 快些