0%

MySQL临时表

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提示告诉优化器直接使用磁盘临时表