MySQL 中的myisam内部临时表

本文只是记录一下验证过程,源码比较复杂,时间有限没仔细读过。如有误导请见谅。

源码版本 percona 5.7.14

一、问题由来

一个朋友问我下面的tmp目录的文件是干什么的，一会就删除了。他的版本是5.6

我发现我的好多文章都是朋友问的问题。^_^

二、初步分析

因为对MySQL中的临时文件的种类和作用还是比较熟悉参考下文：
http://blog.itpub.net/7728585/viewspace-2146356/
但是都是基于5.7写的，但是对这种文件确实没见过，但是回想起在5.7官方文档中描述过，5.7过后默认的内部临时表磁盘文件使用了innodb引擎，但是5.6中默认还是myisam引擎的。5.7中使用什么引擎由参数internal_tmp_disk_storage_engine控制，但是在内存中始终是memory引擎的内部表，详细参考5.7官方文档:
8.4.4 Internal Temporary Table Use in MySQL
所以我告诉朋友这个应该是myisam引擎的内部临时表。

三、源码确认

我们发现这里的临时表名字为#sql_bec0_14.MYD等打开函数我们可以在如下代码中找到为什么这样命名方式：

 sprintf(path, "%s_%lx_%i", tmp_file_prefix,
            current_pid, temp_pool_slot);

所以我们大概明白：

• #sql:来自tmp_file_prefix是宏定义

#define tmp_file_prefix "#sql" /**< Prefix for tmp tables */

• bec0:来自mysqld的当前进程号
• 14:临时表缓冲区的某种槽号,没仔细看

四、什么时候用到内部临时表以及磁盘文件

这个问题在官方文档描述参考：
8.4.4 Internal Temporary Table Use in MySQL 
我就不过多描述了，执行计划一般会出现use temporary字样，当然不出现也可能使用内部临时表，自行参考。
而对于是否磁盘文件则如下描述：

• If an internal temporary table is created as an in-memory table but becomes too large, MySQL

1. converts it to an on-disk table. The maximum size for in-memory temporary tables is determined from whichever of the values of tmp_table_size and max_heap_table_size is
2. This differs from MEMORY tables explicitly created with CREATE TABLE: For such tables, only the max_heap_table_size system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.

• The internal_tmp_disk_storage_engine system variable determines which storage engine the

1. uses to manage on-disk internal temporary tables. Permitted values are INNODB (the default) and MYISAM.

• In-memory temporary tables are managed by the MEMORY storage engine, which uses fixed-length row format. VARCHAR and VARBINARY column values are padded to the maximum column length, in effect storing them as CHAR and BINARY columns.
• On-disk temporary tables are managed by the InnoDB or MyISAM storage engine (depending on the internal_tmp_disk_storage_engine setting). Both engines store temporary tables using
  dynamic-width row format. Columns take only as much storage as needed, which reduces disk I/O and space requirements, and processing time compared to on-disk tables that use fixed-length rows. For statements that initially create an internal temporary table in memory, then convert it to an on-disk table, better performance might be achieved by skipping the conversion step and creating the table on disk to begin with. The big_tables system variable can be used to force disk storage of internal temporary tables.

实际上如果设置参数big_tables为TURE或者包含了大字段必然会使用磁盘临时表如下：