MySQL中临时表主要有两类，包括外部临时表和内部临时表。外部临时表是通过语句create temporary table…创建的临时表，临时表只在本会话有效，会话断开后，临时表数据会自动清理。内部临时表主要有两类，一类是information_schema中临时表，另一类是会话执行查询时，如果执行计划中包含有”Using temporary”时，会产生临时表。内部临时表与外部临时表的一个区别在于，我们看不到内部临时表的表结构定义文件frm。而外部临时表的表定义文件frm，一般是以#sql{进程id}_{线程id}_序列号组成，因此不同会话可以创建同名的临时表。
临时表
临时表与普通表的主要区别在于是否在实例，会话，或语句结束后，自动清理数据。比如，内部临时表，我们在一个查询中，如果要存储中间结果集，而查询结束后，临时表就会自动回收，不会影响用户表结构和数据。另外就是，不同会话的临时表可以重名，所有多个会话执行查询时，如果要使用临时表，不会有重名的担忧。5.7引入了临时表空间后，所有临时表都存储在临时表空间(非压缩)中，临时表空间的数据可以复用。临时表并非只支持Innodb引擎，还支持myisam引擎，memory引擎等。因此，临时表我们看不到实体(idb文件)，但其实不一定是内存表，也可能存储在临时表空间中。
临时表 VS 内存表
临时表既可以innodb引擎表，也可以是memory引擎表。这里所谓的内存表，是说memory引擎表，通过建表语句create table …engine=memory，数据全部在内存，表结构通过frm管理，同样的内部的memory引擎表，也是看不到frm文件中，甚至看不到information_schema在磁盘上的目录。在MySQL内部，information_schema里面的临时表就包含两类：innodb引擎的临时表和memory引擎的临时表。比如TABLES表属于memory临时表，而columns,processlist,属于innodb引擎临时表。内存表所有数据都在内存中，在内存中数据结构是一个数组(堆表)，所有数据操作都在内存中完成，对于小数据量场景，速度比较快(不涉及物理IO操作)。但内存毕竟是有限的资源，因此，如果数据量比较大，则不适合用内存表，而是选择用磁盘临时表(innodb引擎)，这种临时表采用B+树存储结构(innodb引擎)，innodb的bufferpool资源是共享的，临时表的数据可能会对bufferpool的热数据有一定的影响，另外，操作可能涉及到物理IO。memory引擎表实际上也是可以创建索引的，包括Btree索引和Hash索引，所以查询速度很快，主要缺陷是内存资源有限。
使用临时表的场景
前面提到执行计划中包含有”Using temporary”时，会使用临时表，这里列两个主要的场景。
测试表结构如下：
union操作的含义是，取两个子查询结果的并集，重复的数据只保留一行，通过建立一个带主键的临时表，就可以解决”去重”问题，通过临时表存储最终的结果集，所以能看到执行计划中Extra这一项里面有”Using temporary”。与union相关的一个操作是union all，后者也是将两个子查询结果合并，但不解决重复问题。所以对于union all，没有”去重”的含义，因此也就不需要临时表了。
1).消除filesort
实际上，group by也可以显示消除”排序含义”。
2).消除临时表
3).SQL_BIG_RESULT
同时我们语句中用到了”SQL_BIG_RESULT”这个hint，正是因为这个hint导致了我们没有使用临时表，先说说SQL_BIG_RESULT和SQL_SMALL_RESULT的含义。
SQL_SMALL_RESULT：显示指定用内存表(memory引擎)
SQL_BIG_RESULT：显示指定用磁盘临时表(myisam引擎或innodb引擎)
两者区别在于，使用磁盘临时表可以借助主键做去重排序，适合大数据量；使用内存表写入更快，然后在内存中排序，适合小数据量。下面是从MySQL手册中摘录的说明。
如何避免使用临时表
通常的SQL优化方式是让group by 的列建立索引，那么执行group by时，直接按索引扫描该列，并统计即可，也就不需要temporary和filesort了。
相关参数与状态监控
1).参数说明
max_heap_table_size
This variable sets the maximum size to which user-created MEMORY tables are permitted to grow,The value of the variable is used to calculate MEMORY table MAX_ROWS values.
这个参数主要针对用户创建的MEMORY表，限制内存表最大空间大小，注意不是记录数目，与单条记录的长度有关。如果超出阀值，则报错。ERROR 1114 (HY000): The table ‘xxx’ is full
tmp_table_size
The maximum size of internal in-memory temporary tables.
对于用户手工创建的内存表，只有参数max_heap_table_size起作用；对于内部产生的内存表，则参数max_heap_table_size和tmp_table_size同时起作用。对于内部产生的内存表(比如union，group by等产生的临时表)，先是采用内存表(memory表)，然后超过设置的阀值(max_heap_table_size,tmp_table_size)就会转为磁盘表，使用innodb引擎或者myisam引擎，通过参数internal_tmp_disk_storage_engine指定。
tmpdir
如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下
2.状态监控
Created_tmp_tables，内部临时表数目
Created_tmp_disk_tables，磁盘临时表数目
3.information_schema相关
Original: https://www.cnblogs.com/cchust/p/10891520.html
Author: 天士梦
Title: MySQL临时表
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/591724/
转载文章受原作者版权保护。转载请注明原作者出处！