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.
Temporary tables can be created under conditions such as these:
mysql> explain select avg(age),name3 from three group by name3 order by null;
+—-+————-+——-+——+—————+——+———+——+——+—————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+—————–+
| 1 | SIMPLE | three | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary |
+—-+————-+——-+——+—————+——+———+——+——+—————–+
1 row in set (0.00 sec)
mysql> explain select avg(age),name3 from three group by name3 order by age;
+—-+————-+——-+——+—————+——+———+——+——+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
| 1 | SIMPLE | three | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
1 row in set (0.00 sec)
mysql> explain select avg(age) avgage,name3 from three group by name3 order by avgage;
+—-+————-+——-+——+—————+——+———+——+——+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
| 1 | SIMPLE | three | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
1 row in set (0.02 sec)
只要group by用不上索引，就需要临时表来解决去重和计算聚合函数；
加上索引，执行计划就有所变化了：
mysql> alter table three add key i_name3(name3);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table three\G
*************************** 1. row ***************************
Table: three
Create Table: CREATE TABLE `three` (
`id3` int(11) DEFAULT NULL,
`name3` varchar(10) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
KEY `i_id3` (`id3`),
KEY `i_name3` (`name3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain select avg(age),name3 from three group by name3;
+—-+————-+——-+——-+—————+———+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——+——+——-+
| 1 | SIMPLE | three | index | i_name3 | i_name3 | 13 | NULL | 7 | NULL |
+—-+————-+——-+——-+—————+———+———+——+——+——-+
1 row in set (0.00 sec)
上面这个SQL用到了索引，利用innodb b+tree的特性，按顺序读取name3的值，并计算其聚合函数；当读取的name3的值发生变化时，就表示变化发生前的那个name3的值对应的聚合函数值计算完了，发送给网络接口；由此就不需要用临时表来去掉name3重复值，以及计算聚合函数值了；
下面这个SQL也用到了索引，但是按照聚合函数值排序，就比上面多了临时表和filesort；
mysql> explain select avg(age) avgage,name3 from three group by name3 order by avgage;
+—-+————-+——-+——-+—————+———+———+——+——+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——+——+———————————+
| 1 | SIMPLE | three | index | i_name3 | i_name3 | 13 | NULL | 7 | Using temporary; Using filesort |
+—-+————-+——-+——-+—————+———+———+——+——+———————————+
1 row in set (0.04 sec)
上面这个SQL，group by使用了索引。利用索引的顺序读取name3，并计算avg(age)，算好一个avgage值之后，把该avgage和name3写入临时表；
都写完之后，从临时表读取数据，按avgage排序，然后发送结果；
而group by与order by的列不属于同一个表时，只是多了个join过程，其他过程应该是同理的；
mysql> explain select distinct(name3) from three;
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | three | index | i_name3 | i_name3 | 13 | NULL | 7 | Using index |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
1 row in set (0.04 sec)
To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary (see Section 8.8.1, “Optimizing Queries with EXPLAIN”). EXPLAIN will not necessarily say Using temporary for derived or materialized temporary tables.
If an internal temporary table is created initially 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 the minimum of the tmp_table_size and max_heap_table_size values. This differs from MEMORY tables explicitly created withCREATE 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.
When the server creates an internal temporary table (either in memory or on disk), it increments theCreated_tmp_tables status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables status variable.
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
简单地说，临时表是用来以表的形式保存中间结果的。数据少时是heap表，在内存中，数据多时是MyISAM表，在硬盘上。
注意，不是所有的中间结果都用临时表。filesort保存中间结果用的临时文件，不是临时表。
Internal Temporary Tables
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有