MySQL 中的临时表

点击上方蓝色字体，选择“标星公众号”

优质文章，第一时间送达

  作者 |  萌新J

来源 |  urlify.cn/7VvYBz

76套java从入门到精通实战课程分享

文章正文：

在使用 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 格式下直接记录的是经过临时表得出的具体要操作的数据。

总结

临时表是一种非常方便的结构，因为其会随着会话结束而自动删除，所以在一些查询效率较低但筛选字段使用很少的场景，就可以通过创建临时表，然后在临时表上创建索引来提高查询效率，同时也避免了索引的后续维护，而在其他复杂操作中，临时表也可以充当中间表的作用。所以临时表广泛出现在查询（多表联查）、分组、排序（排序返回的字段总长度过大）等场景中。在某些复杂操作我们可以使用临时表去优化，但是使用临时表还是会消耗一定的性能，所以如果 explain 一条语句出现 using temporary，首要是想办法去优化使其没有用到临时表，如果没有更好的方法优化才放弃。

总结：

1、如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
2、join_buffer 是无序数组，专门用于多表联查未用到索引时辅助查询的，如果不足以装下驱动表就会分多次进行；sort_buffer 是有序数组，在排序时优先使用的区域，如果要排序返回的总字段长度超过设置的区域就会用到内存临时表，如果再超过 tmp_table_size  设置的值就会转成磁盘临时表；内存临时表是二维表结构，无序；磁盘临时表默认是B+结构，可以是数组，有序。
3、如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

粉丝福利：Java从入门到入土学习路线图

👇👇👇

👆长按上方微信二维码 2 秒

感谢点赞支持下哈