在执行SQL过程中,MySQL会自动创建一个内部临时表来处理一些中间过程,例如group by,distinct,insert…select自同一个表等,表现是执行计划的extra显示using temporary。
内部临时表由tmp_table_size或max_heap_table_size来决定在内存中的大小上限,两个参数以较小的为阈值标准。
内部临时表在内存时是采用memory引擎,当大小超过阈值,就会转换为磁盘表,写到磁盘文件上,这时采用的存储引擎由internal_tmp_disk_storage_engine决定,可选Innodb或Myisam。
每创建一个内部临时表,就会使状态变量Created_tmp_tables加1,每产生一个磁盘临时表,就会使状态变量Created_tmp_disk_tables加1。
在内存中,内部临时表对于varchar和varbinary都以定长方式存储,即等效于char和binary。
有时候internal_tmp_disk_storage_engine=INNODB,产生的磁盘临时表超过了Innodb的行或列限制,会报错: Row size too large 或 Too many columns,解决方法可以尝试修改internal_tmp_disk_storage_engine=Myisam。
由内存转换为磁盘临时表,必然会有性能上的消耗和损失,如果确定所有临时表都会转换成磁盘表,可以设置 big_tables=1来强制所有内部临时表直接创建为磁盘表,避免转换的过程。但是一般情况下内存都够用,保持默认,优先使用内存效率更高。