首页 > 编程笔记 > MySQL笔记

MySQL临时表(内部和外部)

顾名思义,临时表就是临时用来存储数据的表,是建立在系统临时文件夹中的表,如果使用得当,完全可以像普通表一样进行各种操作。

我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时,可以把中间结果或小的子集放到一个临时表里,再对这些表进行查询,以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。

一般情况下,临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。

外部临时表

外部临时表也可称为会话临时表,这种临时表只对当前用户可见,它的数据和表结构都存储在内存中。当前会话中断或结束后,数据表数据就会丢失,MySQL 会自动删除表并释放其所占空间。

1)创建临时表

创建临时表很容易,在 CREATE TABLE 语句上添加 TEMPORARY 关键字,如下所示:

CREATE TEMPORARY TABLE <表名>...

临时表的命名可以和非临时表同名,但是同名后非临时表将对当前会话不可见,直到临时表被删除。

2)查询临时表

创建了临时表之后,运行 SHOW TABLES 命令不会列出临时表,以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息,这不是 Bug,而是设计就是如此。

我们可以使用以下命令来查看临时表:

SHOW CREATE TABLE <表名>; 

3)删除临时表

当然,我们也可以在当前会话中手动销毁临时表,SQL 语句如下:

DROP TABLE <表名>;

例 1

下面我们创建 tmp_table 表,并对它进行操作。
mysql> CREATE TEMPORARY TABLE tmp_table (
    -> id INT NOT NULL,
    -> name VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test    |
+-------------------+
| product           |
| product_price     |
| score             |
| student           |
| student_comment   |
| tb_student        |
| tb_student_course |
| tb_students_info  |
| tb_students_score |
| tb_usertest       |
+-------------------+
10 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE tmp_table \G
*************************** 1. row ***************************
       Table: tmp_table
Create Table: CREATE TEMPORARY TABLE `tmp_table` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> DROP TABLE tmp_table;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tmp_table \G
ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist
如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话,再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表,会发现数据库中没有该表的存在,因为在退出会话时该临时表已经被销毁了。

外部临时表也有一些限制,使用时需注意以下几点:

内部临时表

内部临时表是一种特殊轻量级的临时表,不同于手工创建的临时表,它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果,该过程由 MySQL 自动完成,用户无法手工干预,且这种内部表对用户来说是不可见的。

我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。

内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色,MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价,所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。

以下是生成内部临时表的几种可能:
内部临时表有两种类型,一种是 HEAP 临时表,这种临时表的所有数据都会存在内存中,对于这种表的操作不需要 IO 操作;另一种是 OnDisk 临时表,顾名思义,这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。

以下情况可能阻碍 MySQL 使用 HEAP 临时表,改用 OnDisk 临时表:

MySQL 5.7 版本中,OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。
Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。

所有教程

优秀文章