MySQL的临时表

1. 临时表的特点

• 临时表为当前线程可见，对其他线程不可见；
• 临时表可以与普通表重名（命名时底层会加上线程id），MySQL会优先使用临时表；
• show table命令不显示临时表；
• 线程结束后临时表会自动被清理

2. 临时表的应用—复杂查询操作

比如分库分表时进行跨库跨表查询，每个分库分表会先将各自的查询结果放到临时表中，然后再对临时表中的数据进行筛选。

3. 临时表和主备复制

当bin log的格式为row时，bin log中可以不记录临时表相关操作。因为bin log中记录了每次操作的行数据的最终结果，所以回滚时不需要知道主表上关于临时表的具体操作。

但是当bin log的格式为statement或者mixed时，bin log需要记录对于临时表的操作，因为主表有些数据操作是与临时表上的数据相关的，比如从临时表取数据进行数学计算后更新到主表。

此时需要考虑的是，在备库是怎么区分两个同名的临时表的—主库创建临时表时写入到bin log中的临时表的表名会包含主库此时的线程id，那么备库创建同名临时表就会在表名上加上该线程id用以区分。

另外，虽然主库中的线程死亡后，临时表会被自动清理，但是备库中的线程不会死亡，所以备库中的临时表会一直存在，需要主库手动drop临时表并写入bin log，那么备库才能清理临时表。

4. 外部临时表与内部临时表

• 外部临时表：用户主动创建的
• 内部临时表：MySQL执行sql语句时会自动判断是否要建立一张临时表存放中转数据，如果不能一边执行sql语句，一边得到结果数据，就需要建立临时表来存放中转数据，比如group by操作，需要一张临时表计算每个分类字段的累计计数。

ps：对group by语句的优化：

1. 分组后会默认进行排序，所以如果对分类结果没有排序要求时，要在group by后面加order by null；
2. 尽量让group by用上索引；
3. group by时尽量使用内存临时表，避免使用磁盘临时表（数据量大时则直接使用磁盘临时表）。

参考文章：

https://time.geekbang.org/column/article/80477