MySQL临时表是一种特殊的数据对象，它具有以下几个特点：

  • 临时表只能被创建它的会话访问，对其他线程不可见
  • 临时表可以用普通表重名，操作默认都是针对临时表
  • show tables是无法看见临时表的
  • 当会话结束后，自动删除临时表

临时表有两种生成方式，我们可以手动执行create temporary table的方式创建临时表，称之外部临时表，通常可以用来辅助实现一些业务逻辑；同时在做复杂运算时，比如在做join、group by、union操作时，如果执行计划中包含了using temporary，但是空间不能容纳时，称之内部临时表。

相关参数与变量

相关参数

  • max_heap_table_size：用户创建的内存临时表的最大值
  • tmp_table_size：内部临时表在内存中的最大值，与max_heap_table_size参数共同决定，取二者的最小值。如果超过则转化为磁盘临时表
  • innodb_tmpdir：ONLINE ALTER TABLE操作时，重建表max_tmp_tables表
  • default_tmp_storage_engine：外部临时表的默认存储引擎
  • innodb_temp_data_file_path：innodb引擎下temp文件属性，可限制临时表空间最大值
  • Internal_tmp_disk_storage_engine：磁盘临时表默认存储引擎
  • tmpdir：磁盘临时表文件存放位置

变量

  • created_tmp_files：创建的临时表数量
  • created_tmp_disk_tables：MySQL在磁盘上创建的内部临时表数量
  • created_tmp_tables：MySQL创建的内部临时表数量

为什么临时表可以重名

在生成临时表时，会创建对应的文件，文件名的规则为#sql{进程id}_{线程id}_序列号。在5.7之前frm表定义文件和ibd数据文件都会保存在tmpdir参数指定的路径下，从5.7开始只有frm文件会保存在tmpdir下，并引入了临时表空间专门用来存放临时表数据。因此在物理文件上其实是不会出现重名的情况。

另外MySQL维护数据表，除了物理上的文件，内存中也需要一套机制来维护不同的表，每个表对应一个table_def_key，普通表和临时表的table_def_key定义是不一样的

  • 普通表的table_def_key的值是由库名+表名组成的，因此在同一个库下创建两个同名的表，就能很快识别到表已存在了
  • 临时表的table_def_key在库名+表名的基础上，又加入了server_id+thread_id

每个会话线程都会维护自身的临时表链表，会话在操作表时，优先遍历链表，检查是否存在对应的临时表，存在则直接操作对应的临时表，没有就操作对应的普通表

临时表与复制

在会话线程关闭后，MySQL会对会话链表内的每个临时表都执行DROP TEMPORARY TABLE的操作。如果binlog_format非ROW，观察binlog日志的话会发现drop操作同样记录到了binlog中。这种情况下创建临时表的语句会同步到从库也创建临时表，但是主库会话关闭后，主库临时表被删除，从库由于SQL应用线程是在不断运行的，因此需要在主库手动执行DROP TEMPORARY TABLE同步到从库。

此时，还有一个问题需要了解，在主库多个线程创建的同名临时表不存在问题的，但是从库SQL应用线程是共用的，这会导致同步故障么？实际上，MySQL在写入binlog时会把主库执行的线程ID记录在binlog中，这样从库就可以利用不同的thread_id来构建不同的table_def_key来避免从库冲突的情况

group by优化

无论使用内存临时表还是磁盘临时表，group by都需要构建一张带有唯一索引的临时表，如果表上的数据量比较大，会影响执行效率。

要想优化这一点，就需要先思考为什么group by语句要用临时表。group by是为了统计不同值出现的个数，但是分组结果是无序的，需要临时表来统计结果，意味着只要在扫描过程中保证有序，那计算group by只要从左往右依次累加即可，就不需要临时表了，因此我们可以通过索引来进行优化。

有时，分组字段并不适合创建索引，那只能老实做排序了。这时我们可以通过SQL_BIG_RESULT这个hint告诉优化器，语句结果集过大，直接使用磁盘临时表，避免从内存临时表切换到磁盘临时表，MySQL优化器对磁盘临时表B+树分析，觉得不如数组效率高，所以最后还是采用有序数组的方式来保存临时数据。

因此，针对group by优化可以考虑以下几点：

  • 如果对group by语句不要求排序，要在语句后面加order by null
  • group by字段尽量拥有索引
  • 如果group by统计的数据量不大，可以考虑临时增大tmp_table_size使它使用内存临时表
  • 如果数据量太大，使用SQL_BIG_RESULT提示告诉优化器直接使用磁盘临时表