【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

 

You May Also Like