MySQL 中的myisam內部臨時表

一、問題由來

一個朋友問我下面的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
  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
得以證明。

作者微信：