前言

有时候在写 SQL 语法时为了方便，可能会去建立临时表存一些资料，以利后面需要用到的时候可以拿来使用。也就是利用 CREATE TEMPORARY TABLE table_table ........ ，我们会称它叫临时表。
另外有时候在看一些优化效能的网站时，也会看到有些语法会去建立临时表??
这两种临时表有什么不一样，在这里做一个整理。
这里主要介绍一些临时表的概念，至于怎么建立，相信这块网路上已经非常多了，就不另外说明。

外部临时表

首先临时表之一的就是外部临时表，也就是当我们使用 CREATE TEMPORARY TABLE table_table ........去建立的，此种表只能在当前的 session 使用，也就是说

当 session 断掉此临时表就会自动 drop 掉，当然我们也可以自己 drop 这个临时表其他 session 可以用相同名称的临时表

★ 外部临时表存放位置

这里只要先知道外部临时表是放在 disk 上，后面会再探讨。

内部临时表

内部临时表是由 MySQL 自己创建的，用来进行性能优化用的，对用户来说是不可见的，只有透过 explain 才会看到，藉以了解是否有用到内部临时表来协助完成某些事情。
有很多动作都会利用到内部临时表来进行优化，例如

UNIONDISTINCTviewderived tablesCTEORDER BY , GROUP BY

如果有用到内部临时表，会在 explain 的 extra 栏位看到，如下图

内部临时表又分成两种

放在 memory 中，HEAP 临时表，不需 I/O 操作放在 disk，当中间结果集较大的时候，例如超过参数 MAX_HEAP_TABLE_SIZE 设定的大小，就会放到 disk

★ 内部临时表存放位置

如同上面提到的，有分 memory 和 disk

★ 内部临时表相关参数

只要 MySQL Server 有建立内部临时表(不管是建在 memory 还是 disk 上) Created_tmp_tables 这个 status variable 就会增加。
如果是建立到 disk 上，那么 Created_tmp_disk_tables 这个参数就会增加。
藉由这两个可以去观察内部临时表到底建立到 memory 还是 disk 上。

show status like '%Created_tmp_disk_tables%';show status like '%Created_tmp_tables%';

---

关于临时表空间 temporary tablespace

这里之所以会特别提到 temporary tablespace 是因为前面有谈到关于临时表是建立在 disk 上还是 memory 上，对于资料库来说， I/O 绝对是效能重要的瓶颈，所以我们有必要了解这一块。
在 InnoDB 中 temporary tablespace 有两种

session temporary tablespacesglobal temporary tablespace.

这里我们主要讨论的是 session temporary tablespaces。

session temporary tablespaces

这里主要存放了以下两种
1.外部临时表：使用者自己建的 create temporary table
2.内部临时表: 由 optimizer 建立的，但是前提是这个内部临时表是 on disk

这里以 mysql8.0.16 为例，session temporary tablespaces 在 mysql 8.0.16 之后会在 #innodb_temp/ 这个目录下。从下图可以看到有 10 个档案，这10个档案组成一个 pool。

这个 pool 是在 server start 就建立的，也就是说 server 一开始就有这10个档案，也就是 pool这个 pool 不会 shrinks，且有必要的会会自动 add这些 ibt 副档名的就是 session tamporary tablespace每一次重启 server ，这些 .ibt 档案就会重建只要 session 结束，这些 session tamporary tables 就会 truncate 且还给 pool

---

小结

这篇文章大略介绍了内部外部临时表的差异，主要有鉴于自己在查资料时，大多网站只有写临时表，但其实还是有所区分，所以在这里帮助自己也希望帮助到大家有稍微了解到这两者的差异。
此外跟此主题相关的还有

◎ Materialization:　

这是 MySQL 的特性，主要是跟优化 subquery, cte 相关，当然也跟内部临时表密不可分，大家有兴趣可以上网查询一下，下次也会另外写一篇介绍此特性。

资料库知识相当广泛，文中若有不正确的地方，也烦请各位大神不吝指教，谢谢

---

参考网站
CREATE TEMPORARY TABLE Statement
https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html
Internal Temporary Table Use in MySQL
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
Temporary Tablespaces
https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html
Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization
https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html
Optimizing Subqueries with Materialization
https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html