在使用 explain 解析一个 sql 时，有时我们会发现在 extra 列上显示 using temporary ，这表示这条语句用到了临时表，那么临时表究竟是什么？它又会对 sql 的性能产生什么影响？又会在哪些场景中出现？本文根据 <> 学习整理。

出现场景

其实临时表在之前的博客就已经出现过了，在 MySQL 中的排序 一文中就说到如果 order by 的列上没有索引，或者说没有用到索引，那么就需要进行额外排序(using filesort)，而额外排序优先在一块 sort_buffer 空间中进行，如果这块空间大小小于要加载的字段总长度，那么就会用到临时文件辅助排序，这个临时文件就是临时表。临时表的作用就是作为中间表优化操作，比如 group by 作为分组的中间表， order by rand() (MySQL 中的排序 中的例子)作为中间表帮助运算等。

特点

1、建表语法是 create temporary table …。

2、一个临时表只能被创建它的 session 访问，对其他线程不可见，在会话结束后自动删除。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。(所以特别适合用于join 优化)

3、临时表可以与普通表同名。

4、session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。

5、show tables 命令不显示临时表。

种类

临时表分为磁盘临时表和内存临时表。磁盘临时表指的是存储在磁盘上的临时表，因为在磁盘上，所以执行效率比较低，优点结构可以是有序的，实现可以是 InnoDB(默认)，MyISAM 引擎；内存临时表就是存储在内存中，执行效率高，常用的实现引擎是 Memory。

磁盘临时表和内存临时表的区别

1、相比于 InnoDB 表，使用内存表不需要写磁盘，往表 temp_t 的写数据的速度更快；

2、索引 b 使用 hash 索引，查找的速度比 B-Tree 索引快；

3、临时表数据只有 2000 行，占用的内存有限。

Memory 引擎

与 InnoDB 的区别

1、InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；关于这点可以通过创建 b+ 索引来进行排序，优化查询。alter table t1 add index a_btree_index using btree (id);

2、当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；

3、数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；

4、InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。

5、InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

6、内存表支持 hash 索引，并且数据存储在内存中，所以执行比数据存储在磁盘上的 Innodb 快。

缺点

1、锁粒度大，只支持表级锁，并发度低。

2、数据持久性差。因为是内存结构，所以在重启后数据会丢失 。由此会导致备库在硬件升级后数据就会丢失，并且如果主从库互为 "主备关系" ，备库在关闭后还会将删除数据记录进 binlog，重启后主机会执行备库发送过来的 binlog ，导致主库数据也会丢失。

虽然 Memory 引擎看起来缺点很多，但是因为其存储在内存中，并且关机后会自动清除数据，所以其是作为临时表的一个绝佳选择。

常见的应用场景

分库分表查询

将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上(水平分表)。一般情况下，这种分库分表系统都有一个中间层 proxy。不过，也有一些方案会让客户端直接连接数据库，也就是没有 proxy 这一层。假设分区键是 列 f 。

1、如果只使用分区键作为查询条件如 select v from ht where f=N，那么直接通过分表规则找到 N 所在的表，然后去该表上查询就可以了。

2、如果使用其他字段作为条件且需要排序如 select v from ht where k >= M order by t_modified desc limit 100，那么非但不能确定要查询的记录在哪张表上，而且因为默认使用的是分区键排序，所以得到的结果还是无序的，需要额外排序。

1)在 proxy 层完成排序。优势是速度快，缺点是开发工作量比较大，如果涉及复杂的操作如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高。并且还容易出现内存不够、CPU 瓶颈的问题。

2)将各个分区的查询结果(未排序)总结到一张临时表上进行排序。

Ⅰ、在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；

Ⅱ、在各个分库上执行 select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

Ⅲ、把分库执行的结果插入到 temp_ht 表中；

Ⅳ、执行 select v from temp_ht order by t_modified desc limit 100;

union 作为中间表

有表t1： create table t1(id int primary key, a int, b int, index(a)); 有记录(1,1,1) 到 (1000,1000,1000)　　　　　　执行 (select 1000 as f) union (select id from t1 order by id desc limit 2);

解析这条 sql：

可以知道：

1、左边语句没有进行查表操作　　2、右边语句使用了 id 索引　　3、联合时使用了临时表

具体过程：

1、创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。

2、执行第一个子查询，得到 1000 这个值，并存入临时表中。

3、执行第二个子查询：

1)拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；

2)取到第二行 id=999，插入临时表成功。

4、从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。

排序返回的字段过大

举一个在  MySQL中的排序 中提到过的例子。

select word from words order by rand() limit 3;  　　表数据有10000行  　　　　SQL是从10000行记录中随机获取3条记录返回。

这个执行过程因为涉及到 rand() 且数据量比较大，所以单靠 sort_buffer 排序空间不够，所以还用到临时表。

过程：

1、从缓冲池依次读取记录，每次读取后都调用 rand() 函数生成一个 0-1 的数存入内存临时表，W 是 word 值，R 是 rand() 生成的随机数。到这扫描了 10000 行。

2、初始化 sort_buffer，从内存临时表中将 rowid(这张表自动生成的) 以及 排序数据 R 存入 sort_buffer。到这因为要遍历内存临时表所以又扫描了 10000 行。

3、在 sort_buffer 中根据 R 排好序，然后选择前三个记录的 rowid 逐条去内存临时表中查到 word 值返回。到这因为取了三个数据去内存临时表去查找所以又扫描了 3 行。总共 20003 行。

group by 作为中间表

执行：select id%10 as m, count(*) as c from t1 group by m;

首先解析 SQL：

可以看到使用了临时表和额外排序，接下来来解析

执行过程：

1、创建内存临时表，表里有两个字段 m 和 c，主键是 m；

2、扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；

1)如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);

2)如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；

遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

排序的过程就按照排序规则进行，用到 sort_buffer ，可能用到临时表。

优化 BNL 排序

表结构：

CREATE TABLE`t2` (

`id`int(11) NOT NULL,

`a`int(11) DEFAULT NULL,

`b`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`a` (`a`)

) ENGINE=InnoDB;

t1、t2 结构相等，t2 100万条数据，t1 1000行数据，t1 的数据在 t2 上都有对应，相等。执行语句：select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

分析：因为字段b 没有创建索引，所以排序是属于 BNL 排序，再加上数据量比较大，所以在比较时扫描的总行数就等于  100万*1000，也就是10亿次。

具体过程：

1、把表 t1 的所有字段取出来，存入 join_buffer 中。这个表只有 1000 行，join_buffer_size 默认值是 256k，可以完全存入。

2、扫描表 t2，取出每一行数据跟 join_buffer 中的数据进行对比，

1)如果不满足 t1.b=t2.b，则跳过；

2)如果满足 t1.b=t2.b, 再判断其他条件，也就是是否满足 t2.b 处于[1,2000]的条件，如果是，就作为结果集的一部分返回，否则跳过。

优化：

如果筛选字段用的比较多，那么可以为其创建索引，使 BNL 优化成 NLJ，但是如果这个字段使用的不多，那么为其创建索引反倒会因为多了不必要的维护成本而降低总体的性能。所以。针对于使用率不高的 BNL 筛选字段的优化，可以创建一个临时表，让这个临时表作为一个索引表，来优化成 NLJ，同时因为临时表在会话结束后会自动删除，省去了维护成本。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);

这样执行过程就变成：

1、执行 insert 语句构造 temp_t 表并插入数据的过程中，对表 t2 做了全表扫描，这里扫描行数是 100 万。

2、之后的 join 语句，扫描表 t1，这里的扫描行数是 1000；join 比较过程中，做了 1000 次带索引的查询(因为t1 1000行，作为驱动表，t2作为被驱动表)。相比于优化前的 join 语句需要做 10 亿次条件判断来说，这个优化效果还是很明显的。

为什么临时表可以重名

可以看到在 sessionA 在已经创建了一个名为 t1 的临时表，并且 sessionA 未结束前，sessionB 也创建了一个名为 t1 的临时表，没有发生异常。这是为什么？

首先要知道在 MySQL 启动后每张表都会加载到内存中，所以每张表都分为内存表和磁盘表。

1、对于磁盘表：

1)普通表的表结构和数据文件都是存储在库名文件夹下的，文件名就是表名。

2)结构文件存储在临时文件夹下，文件的后缀是 frm，前缀是 "#sql{进程 id}_{线程id}_序列号"；

数据文件在 5.6 及之前是存储在临时文件夹下的，5.7 开始存放在专门存放临时文件数据的临时表空间。

2、对于内存表：

1)普通表的命名是 "库名 + 表名"。

2)临时表的命名则在 " 库名 + 表名 " 的基础上，加入了 " server_id + thread_id "。比如：

session A 的临时表 t1，在备库的 table_def_key 就是：库名 +t1+“M 的 serverid”+“session A 的 thread_id”;

session B 的临时表 t1，在备库的 table_def_key 就是 ：库名 +t1+“M 的 serverid”+“session B 的 thread_id”。

综上所述，因为临时表在磁盘和内存中表的命名都取自具体的进程id、线程id、所以可以实现不同的会话创建相同的表名。

如果 binlog 的格式是 row，那么是不会记录临时表的各个操作的，因为临时表就是用于辅助各自操作的，所以在 row 格式下直接记录的是经过临时表得出的具体要操作的数据。

总结

临时表是一种非常方便的结构么，因为其会随着会话结束而自动删除，所以在一些查询效率较低但筛选字段使用很少的场景，就可以通过创建临时表，然后在临时表上创建索引来提高查询效率，同时也避免了索引的后续维护，而在其他复杂操作中，临时表也可以充当中间表的作用。所以临时表广泛出现在查询(多表联查)、分组、排序(排序返回的字段总长度过大)等场景中。

总结：

1、如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；

2、join_buffer 是无序数组，sort_buffer 是有序数组，内存临时表是二维表结构，无序；磁盘临时表默认是B+结构，可以是数组，有序。

3、如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

点击关注上方“SQL数据库开发”，

设为“置顶或星标”，第一时间送达干货

SQL专栏

SQL基础知识第二版
SQL高级知识第二版

临时表定义

临时表与实体表类似，只是在使用过程中，临时表是存储在系统数据库tempdb中。当我们不再使用临时表的时候，临时表会自动删除。

临时表分类

临时表分为本地临时表和全局临时表，它们在名称、可见性以及可用性上有区别。

临时表的特性

对于临时表有如下几个特点：

• 本地临时表就是用户在创建表的时候添加了"#"前缀的表，其特点是根据数据库连接独立。只有创建本地临时表的数据库连接有表的访问权限，其它连接不能访问该表；

• 不同的数据库连接中，创建的本地临时表虽然"名字"相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证本地临时表在数据库连接上的独立性，意思是你可以在不同的连接里使用相同的本地临时表名称。

• 全局临时表是用户在创建表的时候添加"##"前缀的表，其特点是所以数据库连接均可使用该全局临时表，当所有引用该临时表的数据库连接断开后自动删除。

• 全局临时表相比本地临时表，命名上就需要注意了，与本地临时表不同的是，全局临时表名不能重复。
  

• 临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了物理表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的I/O次数，因此也提高了系统效率。

• 临时表在事务完毕或会话完毕数据库会自动清空，不必记得用完后删除数据。

本地临时表

本地临时表的名称以单个数字符号"#" 打头；它们仅对当前的用户连接（也就是创建本地临时表的connection）是可见的；当用户从 SQL Server 实例断开连接时被删除。

本地临时表实例

我们以Customers表为实例，表数据如下：

我们新建一个连接，每当“新建查询”就代表打开了一个连接，连接的ID就是sa后面的数字，我们的这个连接ID是57.

下面我们在这个查询页面建立一个临时表。

SELECT * INTO #Customers FROM Customers

这样我们就建好了一个临时表，可以查询一下临时表#Customers的数据。与Customers内容一致。

SELECT *  FROM #Customers

如果我们再打开一个页面，同样查询#Customers表会怎么样呢？

我们在新开的查询页面执行上述查询语句，得到的结果如下：

说明本地临时表不支持跨连接查询。只能在当前连接(或者当前查询页面)访问。

那本地临时表具体在什么地方呢？它又是怎么存放的呢？

这就是我们刚才建立的临时表，在系统中并不是用#Cusomters表示的。

全局临时表

全局临时表的名称以两个数字符号 "##"打头，创建后对任何数据库连接都是可见的，当所有引用该表的数据库连接从 SQL Server 断开时被删除。

全局临时表实例

我们还是按照上面的步骤走一遍

先打开一个查询页面，输入如下查询语句：

SELECT * INTO ##Customers FROM Customers

执行完上面的查询语句后，我们关掉查询页面，再重新开一个页面查询##Customers中的内容

SELECT * FROM ##Customers

结果如下：

此时并不会像本地临时表那样报错了。

全局临时表的位置如下：

它的名称与我们自定义的名称一致，系统不会额外添加其他信息。

临时表的用途

介绍完临时表，我们来说说如何用它来进行优化

临时表的优化一般使用在子查询较多的情况下，也称为嵌套查询。我们写如下子查询：

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN 
(SELECT SalesOrderDetailID FROM sales.SalesOrderDetail
WHERE UnitPrice IN
(SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)
)

(提示：代码可以左右滑动)

这是一个比较简单的两层嵌套子查询，我们看一下执行情况：

可以看到这里的逻辑读取是比较高的。

我们用临时表重新来看下执行情况如何，我们将第一二层的查询结果插入到#temp中，然后从临时表中查询结果。

注：上图的统计信息需开启SSMS相关功能，具体如下：

工具—选项—查询执行—高级

SELECT SalesOrderDetailID INTO #temp FROM sales.SalesOrderDetail
WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN 
(SELECT SalesOrderDetailID FROM #temp)

执行情况如下：

相比上一次的逻辑读，成倍的减少了逻辑读取次数。在对查询的性能进行调节时，如果逻辑读值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果逻辑读值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。

因此我们可以看出临时表在比较复杂的嵌套查询中是可以提高查询效率的。

总结

临时表不管是在SQL Server还是其他平台都有使用，其在查询优化方面可以极大的提高查询效率，而SQL Server平台的临时表相比其他平台更容易创建和使用，其优越性不言而喻。所以如果平时工作或学习过程中，临时表可以作为一个必备技能经常使用。

最后给大家分享我写的SQL两件套：《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。

有需要的读者可以下载学习，在下面的公众号「数据前线」(非本号)后台回复关键字：SQL，就行

数据前线

后台回复关键字：1024，获取一份精心整理的技术干货

后台回复关键字：进群，带你进入高手如云的交流群。

推荐阅读

• 你偷看的小电影，全被监视了
  

• Linux 能替代 Windows 吗？
  

• 知乎：国家何时整治程序员的高薪现象？
  

• SQL触发器实战
  

• 一款SQL自动检查神器，再也不用担心SQL出错了！

1.介绍：

MySQL临时表，属于session级别，当session退出时，临时表被删除。临时表允许与其他表同名，并单独维护在thd的结构体中；因此，不同的session可以创建同名的临时表，并且只操作自己拥有的临时表；

创建临时表的语法很简单：

root@test 03:26:44>show create table tmp1\G

*************************** 1. row ***************************

Table: tmp1

Create Table: CREATE TEMPORARY TABLE `tmp1` (

`a` int(11) NOT NULL AUTO_INCREMENT,

`b` int(11) DEFAULT NULL,

`c` int(11) DEFAULT NULL,

PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

当创建临时表后，会在tmp文件夹下生成两个文件：

#sql3e95_1a_0.frm

#sql3e95_1a_0.ibd

那么MySQL本身究竟是如何创建和删除临时表的呢？

2.创建

执行SQL：

CREATE TEMPORARY TABLE `tmp1` (   `a` int(11) NOT NULL AUTO_INCREMENT,   `b` int(11) DEFAULT NULL,   `c` int(11) DEFAULT NULL,   PRIMARY KEY (`a`) );

1)断点：ysql_execute_command

mysql_execute_command:

2205      switch (lex->sql_command) {

(gdb)

2532        if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))

(gdb) p lex->create_info.options          --------if语句里为false

$2 = 1

create_table_precheck------检查是否具有创建表的权限，以及表名在全局链表上是否已存在(临时表无需检查)

append_file_to_dir     ------Fix names if symlinked tables

if (select_lex->item_list.elements)               -------------------当为create ....select这样的语句时select_lex->item_list.elements为非0值，这里我们只考虑简单的情况

if ((result= new select_create))

res= handle_select(thd, lex, result, 0);

else

(1)mysql_create_like_table           ---------------create table like...类似的语句

(2)mysql_create_table                 ---------------主要分析这个函数

2)断点：mysql_create_table

mysql_create_table

mysql_create_table_no_lock

check_engine

file = get_new_handler

3842      set_table_default_charset(thd, create_info, (char*) db);

3844      if (mysql_prepare_create_table(thd, create_info, alter_info,

3854      path_length= build_tmptable_filename(thd, path, sizeof(path));   -----创建临时表文件名：#sql{进程id}_{thread_id}_{当前线程的临时表整数标识thd->tmp_table}

3978      rea_create_table --------------------------------------------------------------------创建frm文件和ibd文件

3986      open_temporary_table-------------------------------------------------------------打开临时表

(1)构建table和table_share结构体

(2)将table结构体加入到thd->temporary_tables链表中

4009      error= write_create_table_bin_log----------------------------------------------写入binlog

3.删除临时表

手动执行 drop table tmp1

mysql_execute_command

case SQLCOM_DROP_TABLE:

mysql_rm_table

mysql_rm_table_part2

for (table= tables; table; table= table->next_local)

drop_temporary_table-----------------------------从thd->temporary_tables上查找临时表

调用close_temporary_table来关闭、删除临时表文件，并从thd->temporary_tables上删除相应节点

if (!drop_temporary)-------------------------------当删除的是非临时表时，执行下面的逻辑

----------------------------------------

4. 当session退出时。

看看堆栈：

Breakpoint 16, rm_temporary_table (base=0xc8c560, path=0x1427c10 "/u01/mysql-5148.stock/tmp/#sql3e95_1d_0") at sql_base.cc:5634

5634    bool rm_temporary_table(handlerton *base, char *path)

(gdb)

5641      strmov(ext= strend(path), reg_ext);

(gdb) bt

#0  rm_temporary_table (base=0xc8c560, path=0x1427c10 "/u01/mysql-5148.stock/tmp/#sql3e95_1d_0") at sql_base.cc:5641

#1  0x00000000005f6eaa in close_temporary (table=0x1427030, free_share=true, delete_table=true) at sql_base.cc:1928

#2  0x00000000005f725f in close_temporary_tables (thd=0x14065f0) at sql_base.cc:1549

#3  0x0000000000592d9b in THD::cleanup (this=0x14065f0) at sql_class.cc:967

#4  0x00000000005a3579 in unlink_thd (thd=0xc8c560) at mysqld.cc:1858

#5  0x00000000005a35dc in one_thread_per_connection_end (thd=0xc8c560, put_in_cache=16) at mysqld.cc:1945

#6  0x00000000005ac208 in handle_one_connection (arg=0x14065f0) at sql_connect.cc:1141

#7  0x0000003e638064a7 in start_thread () from /lib64/libpthread.so.0

#8  0x0000003e630d3c2d in clone () from /lib64/libc.so.6

#9  0x0000000000000000 in ?? ()

在session结束的时候，会调用THD::cleanup来做临时表的清理工作