MySQL中的myisam內部臨時表分析

137次閱讀

沒有評論

共計 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 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完