【mysql】關於臨時表，【mysql】

【mysql】關於臨時表，【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: tmp1
Create Table: CREATE TEMPORARY TABLE `tmp1` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 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: tmp2
Create Table: CREATE TEMPORARY TABLE `tmp2` (
  `id` int(11) unsigned NOT NULL COMMENT 'primary key'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 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: tmp3
Create 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