【mysql】關於暫存資料表

標籤：

mysql官方的介紹

In some cases, the server creates internal temporary tables while processing queries. Such a table can be held in memory and processed by the MEMORY storage engine, or stored on disk and processed by the MyISAM storage engine. The server may create a temporary table initially as an in-memory table, then convert it to an on-disk table if it becomes too large. Users have no direct control over when the server creates an internal temporary table or which storage engine the server uses to manage it

以下幾種情況會建立暫存資料表

• UNION查詢
• 用到TEMPTABLE演算法或者是UNION查詢中的視圖
• 在JOIN查詢中，ORDER BY或者GROUP BY使用了不是第一個表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
• 表串連中，ORDER BY的列不是驅動表中的
• DISTINCT查詢並且加上ORDER BY時
• SQL中用到SQL_SMALL_RESULT選項時
• FROM中的子查詢
• 子查詢或者semi-join時建立的表
• 多表更新sql
• GROUP_CONCAT() or COUNT(DISTINCT) evaluation

在以下幾種情況下，會建立磁碟暫存資料表

• 表包含TEXT或者BLOB列
• 在 GROUP BY 或者 DSTINCT 的列中有超過 512字元 的字元類型列（或者超過 512位元組的 二進位類型列，在5.6.15之前只管是否超過512位元組）
• 在SELECT、UNION、UNION ALL查詢中，存在最大長度超過512的列（對於字串類型是512個字元，對於二進位類型則是512位元組）
• 執行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因為它們的執行結果用到了BLOB列類型

暫存資料表相關配置

tmp_table_size：指定系統建立的記憶體暫存資料表最大大小

max_heap_table_size: 指定使用者建立的記憶體表的最大大小

The maximum size for in-memory temporary tables is the minimum of thetmp_table_size and max_heap_table_size values

最佳化建議 

一般要避免使用暫存資料表

1、在ORDER BY或者GROUP BY的列上建立索引

2、TEXT、BLOB等大欄位，拆分表

 

從5.7.5開始，新增一個系統選項 internal_tmp_disk_storage_engine 可定義磁碟暫存資料表的引擎類型為 InnoDB，而在這以前，只能使用 MyISAM。

在5.6.3以後新增的系統選項 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 建立的暫存資料表的引擎類型，在以前預設是MEMORY，不要把這二者混淆了

ysql> show variables like ‘default_tmp%‘; +----------------------------+--------+| Variable_name              | Value  |+----------------------------+--------+| default_tmp_storage_engine | InnoDB |+----------------------------+--------+1 row in set (0.00 sec)mysql> create temporary table tmp1(id int not null);  Query OK, 0 rows affected (0.17 sec)mysql> show create table tmp1 \G*************************** 1. row ***************************       Table: tmp1Create Table: CREATE TEMPORARY TABLE `tmp1` (  `id` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> set default_tmp_storage_engine = myisam;Query OK, 0 rows affected (0.00 sec)mysql> create temporary table tmp2 (id int(11) unsigned not null comment ‘primary key‘ );Query OK, 0 rows affected (0.01 sec)mysql> show create table tmp2 \G*************************** 1. row ***************************       Table: tmp2Create Table: CREATE TEMPORARY TABLE `tmp2` (  `id` int(11) unsigned NOT NULL COMMENT ‘primary key‘) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> set default_tmp_storage_engine = memory;Query OK, 0 rows affected (0.00 sec)mysql> create temporary table tmp3 (id int(11) unsigned not null comment ‘primary key‘ ); Query OK, 0 rows affected (0.00 sec)mysql> show create table tmp3 \G*************************** 1. row ***************************       Table: tmp3Create Table: CREATE TEMPORARY TABLE `tmp3` (  `id` int(11) unsigned NOT NULL COMMENT ‘primary key‘) ENGINE=MEMORY DEFAULT CHARSET=utf8

 

參考： http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

【mysql】關於暫存資料表