共计 8486 个字符，预计需要花费 22 分钟才能阅读完成。
这篇文章主要讲解了“MySQL 中的 myisam 内部临时表分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“MySQL 中的 myisam 内部临时表分析”吧！
一、问题由来
一个朋友问我下面的 tmp 目录的文件是干什么的，一会就删除了。他的版本是 5.6
tmpfile.png
br data-filtered= filtered font-size:18px;white-space:normal;background-color:#FFFFFF; / 我发现我的好多文章都是朋友问的问题。_
二、初步分析
因为对 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
automatically 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
smaller. 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
server 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 或者包含了大字段必然会使用磁盘临时表如下：
Presence of a BLOB or TEXT column in the table
Presence of any string column with a maximum length larger than 512 (bytes for binary strings,
characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used
The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the
temporary table used for the results is an on-disk table.
The big_tables system variable can be used to force disk storage of internal temporary tables.
当然 create_tmp_table 函数代码中有这样一段逻辑如下来证明上面的描述，这段代码同时标记了 internal_tmp_disk_storage_engine 参数的作用，如下：
/* If result table is small; use a heap */
if (select_options TMP_TABLE_FORCE_MYISAM)
{
share- db_plugin= ha_lock_engine(0, myisam_hton);
table- file= get_new_handler(share, table- mem_root,
share- db_type());
}
else if (blob_count || // 大字段计数器
(thd- variables.big_tables // 参数 big_tables 设置
!(select_options SELECT_SMALL_RESULT)))
{
/*
* Except for special conditions, tmp table engine will be choosen by user.
*/
switch (internal_tmp_disk_storage_engine) // 参数 internal_tmp_disk_storage_engine 设置
{
case TMP_TABLE_MYISAM:
share- db_plugin= ha_lock_engine(0, myisam_hton); //myisam 引擎内部临时表
break;
case TMP_TABLE_INNODB:
share- db_plugin= ha_lock_engine(0, innodb_hton);//innodb 引擎内部临时表
break;
default:
DBUG_ASSERT(0);
share- db_plugin= ha_lock_engine(0, innodb_hton);
}
table- file= get_new_handler(share, table- mem_root,
share- db_type());
}
else
{
share- db_plugin= ha_lock_engine(0, heap_hton);////memory 引擎内部临时表?
table- file= get_new_handler(share, table- mem_root,
share- db_type());
}
而对于 tmp_table_size 和 max_heap_table_size 的比较这个逻辑依然在 create_tmp_table 函数中如下：
if (thd- variables.tmp_table_size == ~ (ulonglong) 0) // No limit
share- max_rows= ~(ha_rows) 0;
else
share- max_rows= (ha_rows) (((share- db_type() == heap_hton) ?
min(thd- variables.tmp_table_size,// 参数 tmp_table_size
thd- variables.max_heap_table_size) :// 参数 max_heap_table_size
thd- variables.tmp_table_size) /
share- reclength);
但是在测试的时候我将 tmp_table_size 设置得很小了,share- max_rows 自然很小，但是还是没有磁盘内部临时表，很是纳闷，如下自己加入的打印输出如下：
2018-03-01T09:27:52.189710Z 3 [Note] (create_tmp_table 1404) tmp_table_size:1024,max_heap_table_size:1048576,blob_count:0,big_tables0
2018-03-01T09:27:52.189748Z 3 [Note] (create_tmp_table 1420) rows_limit:18446744073709551615,max_rows:73
当然我对这个函数的认知还非常有限，以后再说吧。
五、内部临时表的最终建立函数
实际上这个函数就是 instantiate_tmp_table。在 instantiate_tmp_table 中也会看到如下逻辑：
if (table- s- db_type() == innodb_hton)
{ if (create_innodb_tmp_table(table, keyinfo)) return TRUE; // Make empty record so random data is not written to disk empty_record(table);
} else if (table- s- db_type() == myisam_hton)
{ if (create_myisam_tmp_table(table, keyinfo, start_recinfo, recinfo,
options, big_tables)) return TRUE; // Make empty record so random data is not written to disk empty_record(table);
}其实最终的建立什么样的内部临时表就是通过 instantiate_tmp_table 函数进行判断的，如果有兴趣可以将断点放上去进行各种测试，我水平有限，只能抛砖引玉。但是从我大概的测试来看建立内部临时表的情况比官方文档列出来的多得多比如：show table status，这是栈帧放在这里供以后参考一下：
#0 instantiate_tmp_table (table=0x7fff2818a930, keyinfo=0x7fff2818b8e8, start_recinfo=0x7fff2818b988, recinfo=0x7fff2818a290, options=4096, big_tables=0 \000 ,
trace=0x7fff2800a688) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_tmp_table.cc:2345
#1 0x0000000001657289 in create_tmp_table (thd=0x7fff280080c0, param=0x7fff2818a250, fields=..., group=0x0, distinct=false, save_sum_fields=false,
select_options=4096, rows_limit=18446744073709551615, table_alias=0x7fff28002900 TABLES ) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_tmp_table.cc:1518
#2 0x00000000016250d8 in create_schema_table (thd=0x7fff280080c0, table_list=0x7fff28188c80) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_show.cc:8212
#3 0x0000000001625de9 in mysql_schema_table (thd=0x7fff280080c0, lex=0x7fff2800a6a0, table_list=0x7fff28188c80)
at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_show.cc:8441
#4 0x000000000151ae29 in open_and_process_table (thd=0x7fff280080c0, lex=0x7fff2800a6a0, tables=0x7fff28188c80, counter=0x7fff2800a760, flags=0,
prelocking_strategy=0x7ffff0318c30, has_prelocking_list=false, ot_ctx=0x7ffff0318b00) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5061
#5 0x000000000151c383 in open_tables (thd=0x7fff280080c0, start=0x7ffff0318bf0, counter=0x7fff2800a760, flags=0, prelocking_strategy=0x7ffff0318c30)
at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5789
#6 0x000000000151d7bd in open_tables_for_query (thd=0x7fff280080c0, tables=0x7fff28188c80, flags=0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:6564
#7 0x00000000015acb30 in execute_sqlcom_select (thd=0x7fff280080c0, all_ta六、5.7 上的验证
为了一定出现这种文件我设置和测试如下：
mysql show variables like %big_tables%
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| big_tables | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql show variables like %internal_tmp_disk_storage_engine%
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| internal_tmp_disk_storage_engine | MyISAM |
+----------------------------------+--------+
1 row in set (0.00 sec)
mysql desc select id,count(*) from kkks group by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
| 1 | SIMPLE | kkks | NULL | ALL | NULL | NULL | NULL | NULL | 1033982 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
终止在 tmp 目录下看到如下文件
total 8
-rw-r-----. 1 root root 1024 Mar 1 18:18 #sql_148_0.MYI
-rw-r-----. 1 root root 14 Mar 1 18:18 #sql_148_0.MYD
得以证明。感谢各位的阅读，以上就是“MySQL 中的 myisam 内部临时表分析”的内容了，经过本文的学习后，相信大家对 MySQL 中的 myisam 内部临时表分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
