浅谈MySQL临时表与派生表

MySQL在处理特定请求场景时，有时会创建内部临时表以提高查询效率。这些临时表可以是使用MEMORY引擎在内存中创建，也可以是使用MyISAM引擎在磁盘上创建。如果表的大小超出了设定的阈值，内存中的临时表会被转移到磁盘上存储。

当我们谈论派生表时，我们指的是在SQL查询中由MySQL自动创建的临时结果集。这些临时表在主查询包含派生表、SELECT语句包含UNION子句或包含对另一字段的GROUP BY子句的字段的ORDER BY子句时出现。为了优化性能，MySQL优先使用内存临时表。但当表变得过大时，系统会将其转移到外存。这一过程由系统变量max_heap_table_size和tmp_table_size的较小值控制。

关于临时表的用途，当你在处理大型表并需要获取其小数据子集时，让MySQL每次找出所需的少量记录可能更为高效。这种情况下，可以选择将记录选择到一个临时表，然后在这个表上运行查询。

创建临时表非常简单，只需在正常的CREATE TABLE语句前加上TEMPORARY关键字。例如：

```sql

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

);

```

这个临时表仅在你连接到MySQL时存在。当你断开连接时，MySQL会自动删除该表并释放所占用的空间。你也可以在仍然连接的时候删除表并释放空间，使用语句：DROP TABLE tmp_table。

值得注意的是，如果你创建的临时表名为tmp_table，而数据库中已经存在一个同名的非临时表，那么你的临时表将会覆盖（或隐藏）非临时表。你还可以指定在内存中创建临时表，这可以通过TYPE=HEAP指定。由于HEAP表存储在内存中，因此对其运行的查询可能比磁盘上的临时表更快。但请注意，HEAP表与一般的表有所不同，且有自身的限制，详细情况请参见MySQL参考手册。

正如前述，是否使用临时表应根据具体情况测试决定。如果数据已经很好地索引，那么使用临时表可能并不会带来明显的性能提升。当与MySQL断开连接后，系统会自动删除临时表中的数据（仅限于使用特定方法建立的表）。如果你直接将查询结果导入临时表，可以使用如下语句：

```sql

CREATE TEMPORARY TABLE tmp_table SELECT FROM table_name;

```

MySQL也允许你在内存中直接创建临时表以提高速度，语法如下：

```sql

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL TYPE = HEAP,

value INTEGER NOT NULL TYPE = HEAP

);

```

临时表的数据在断开与MySQL的连接后会被自动清空。在一个数据库连接中执行多次SQL时，系统不会自动清空临时表的数据。只有在断开数据库连接后，才会清空临时表的数据。你无需担心每次执行SQL都会导致数据被清空。