MySQL 中的myisam内部临时表

  • gaopengtttt

    2018-03-01 19:41:23
  • MySQL
  • 原创

一、问题由来

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

tmpfile.png

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


二、初步分析

因为对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> select count(*) from kkks;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (31.65 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目录下看到如下文件


[root@test mysqld.1]# ls -lrt
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
得以证明。

作者微信：

请使用浏览器的分享功能分享到微信等