MySQL中的myisam內部臨時表分析

這篇文章主要講解了“MySQL中的myisam內部臨時表分析”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL中的myisam內部臨時表分析”吧！

一、問題由來

一個朋友問我下面的tmp目錄的文件是干什么的，一會就刪除了。他的版本是5.6

tmpfile.png

<br data-filtered="filtered" font-size:18px;white-space:normal;background-color:#FFFFFF;" />我發(fā)現(xiàn)我的好多文章都是朋友問的問題。_

二、初步分析

因為對MySQL中的臨時文件的種類和作用還是比較熟悉參考下文：
http://blog.itpub.net/7728585/viewspace-2146356/
但是都是基于5.7寫的，但是對這種文件確實沒見過，但是回想起在5.7官方文檔中描述過，5.7過后默認的內部臨時表磁盤文件使用了innodb引擎，但是5.6中默認還是myisam引擎的。5.7中使用什么引擎由參數(shù)internal_tmp_disk_storage_engine控制，但是在內存中始終是memory引擎的內部表，詳細參考5.7官方文檔:
8.4.4 Internal Temporary Table Use in MySQL
所以我告訴朋友這個應該是myisam引擎的內部臨時表。

三、源碼確認

我們發(fā)現(xiàn)這里的臨時表名字為#sql_bec0_14.MYD等打開函數(shù)我們可以在如下代碼中找到為什么這樣命名方式：

 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:臨時表緩沖區(qū)的某種槽號,沒仔細看

四、什么時候用到內部臨時表以及磁盤文件

這個問題在官方文檔描述參考：
8.4.4 Internal Temporary Table Use in MySQL
我就不過多描述了，執(zhí)行計劃一般會出現(xiàn)use temporary字樣，當然不出現(xiàn)也可能使用內部臨時表，自行參考。
而對于是否磁盤文件則如下描述：

• 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.

實際上如果設置參數(shù)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函數(shù)代碼中有這樣一段邏輯如下來證明上面的描述，這段代碼同時標記了internal_tmp_disk_storage_engine參數(shù)的作用，如下：
/* 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 || //大字段計數(shù)器
           (thd->variables.big_tables && //參數(shù)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) //參數(shù)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函數(shù)中如下：


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,//參數(shù)tmp_table_size
                                     thd->variables.max_heap_table_size) ://參數(shù)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
當然我對這個函數(shù)的認知還非常有限，以后再說吧。

五、內部臨時表的最終建立函數(shù)

實際上這個函數(shù)就是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函數(shù)進行判斷的，如果有興趣可以將斷點放上去進行各種測試，我水平有限，只能拋磚引玉。但是從我大概的測試來看建立內部臨時表的情況比官方文檔列出來的多得多比如：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上的驗證

為了一定出現(xiàn)這種文件我設置和測試如下：

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
得以證明。

感謝各位的閱讀，以上就是“MySQL中的myisam內部臨時表分析”的內容了，經(jīng)過本文的學習后，相信大家對MySQL中的myisam內部臨時表分析這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！