目录

24 临时表

什么时候会使用临时表

1. 临时表

1.1 临时表跟内存表

  1. 内存表,指的是使用 Memory 引擎的表,建表语法是 create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。除了这两个特性看上去比较“奇怪”外,从其他的特征上看,它就是一个正常的表。
  2. 而临时表,可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用 Memory 引擎

1.2 临时表的特征

/images/mysql/MySQL45%E8%AE%B2/temporary_table_feature.png

从行面可以看到,临时表在使用上有以下几个特点:

  1. 一个临时表只能被创建它的 session 访问,对其他线程不可见所以,图中 session A 创建的临时表 t,对于 session B 就是不可见的。
  2. 临时表可以与普通表同名。
  3. session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。
  4. show tables 命令不显示临时表。
  5. 由于临时表只能被创建它的 session 访问,所以在这个 session 结束的时候,会自动删除临时表。
  6. 不同 session 的临时表是可以重名的,如果有多个 session 同时执行 join 优化,不需要担心表名重复导致建表失败的问题。
  7. 不需要担心数据删除问题。临时表由于会自动回收,所以不需要这个额外的回收操作。

1.3 临时表的应用场景

由于不用担心线程之间的重名冲突,临时表经常会被用在复杂查询的优化过程中。其中,分库分表系统的跨库查询就是一个典型的使用场景。

一般分库分表的场景,就是要把一个逻辑上的大表分散到不同的数据库实例上。分区 key 的选择是以“减少跨库和跨表查询”为依据的。

但是如果查询条件里面没有用到分区字段,那么该如何实现查询呢,有以下两种思路:

  1. 第一种思路是,在 proxy 层的进程代码中实现:
    • 优势是处理速度快,拿到分库的数据以后,直接在内存中参与计算
    • 需要的开发工作量比较大,特别是是对于 group by,join 的操作
    • 对 proxy 端的压力比较大,尤其是很容易出现内存不够用和 CPU 瓶颈的问题
  2. 把各个分库拿到的数据,汇总到一个 MySQL 实例的一个表中,然后在这个汇总实例上做逻辑操作。

我们看下面这个示例:

1
2
3
4
5
6
7
8
9
# ht 是一个大的分库分表,分区 key 是 f
select v from ht where k >= M order by t_modified desc limit 100;

# 思路二: 使用临时表实现的分库查询,汇总到汇总库
# 每个分库的查询
select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

# 汇总库的查询
select v from temp_ht order by t_modified desc limit 100;

按照第二种思路,我们可以这样执行查询:

  1. 在汇总库上创建一个临时表 temp_ht,表里包含三个字段 v、k、t_modified;
  2. 在各个分库上执行 select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;
  3. 把分库执行的结果插入到 temp_ht 表中;
  4. 执行select v from temp_ht order by t_modified desc limit 100;

/images/mysql/MySQL45%E8%AE%B2/temporary_table_sort.jpg

我们往往会发现每个分库的计算量都不饱和,所以会直接把临时表 temp_ht 放到 32 个分库中的某一个上。

1.4 为什么临时表可以重命名

不同线程可以创建同名的临时表,这是怎么做到的呢?我们来看看MySQL是如何保存临时表的表结构与数据的

执行 create temporary table temp_t(id int primary key)engine=innodb;

  1. 临时表的 frm 文件放在临时文件目录下,文件名的后缀是.frm,前缀是#sql{进程 id}_{线程 id}_ 序列号。可以使用 select @@tmpdir 命令,来显示实例的临时文件目录。
  2. 关于表中数据的存放方式,在不同的 MySQL 版本中有着不同的处理方式:
    • 在 5.6 以及之前的版本里,MySQL 会在临时文件目录下创建一个相同前缀、以.ibd 为后缀的文件,用来存放数据文件;
    • 从 5.7 版本开始,MySQL 引入了一个临时文件表空间,专门用来存放临时文件的数据。因此,我们就不需要再创建 ibd 文件了。

MySQL 维护数据表,除了物理上要有文件外,内存里面也有一套机制区别不同的表,每个表都对应一个 table_def_key。

  1. 个普通表的 table_def_key 的值是由“库名 + 表名”得到的
  2. 而对于临时表,table_def_key 在“库名 + 表名”基础上,又加入了“server_id+thread_id”。

在实现上,每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候,先遍历链表,检查是否有这个名字的临时表,如果有就优先操作临时表,如果没有再操作普通表;在 session 结束的时候,对链表里的每个临时表,执行 “DROP TEMPORARY TABLE + 表名”操作。binlog 中也记录了 DROP TEMPORARY TABLE 这条命令。

临时表的 rename 操作

执行 rename table 语句的时候,要求按照“库名 / 表名.frm”的规则去磁盘找文件,但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的,并且文件名的规则是“#sql{进程 id}{线程 id} 序列号.frm”,因此会报“找不到文件名”的错误。

1.5 临时表与主从复制

临时表只在线程内自己可以访问,为什么需要写到 binlog 里面?

1
2
3
4
create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录,备库在执行到 insert into t_normal 的时候,就会报错“表 temp_t 不存在”。

如果当前的 binlog_format=row,那么跟临时表有关的语句,就不会记录到 binlog 里。也就是说,只在 binlog_format=statment/mixed 的时候,binlog 中才会记录临时表的操作。这种情况下,创建临时表的语句会传到备库执行,因此备库的同步线程就会创建这个临时表。主库在线程退出的时候,会自动删除临时表,但是备库同步线程是持续在运行的。所以,这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。

在 binlog_format=‘row’的时候,临时表的操作不记录到 binlog 中,也省去了不少麻烦,这也可以成为你选择 binlog_format 时的一个考虑因素。

MySQL 为什么会重写 drop table 命令

MySQL 在记录 binlog 的时候,不论是 create table 还是 alter table 语句,都是原样记录,甚至于连空格都不变。但是如果执行 drop table t_normal,系统记录 binlog 就会写成: DROP TABLE t_normal /* generated by server */

drop table 命令是可以一次删除多个表的。比如,在上面的例子中,设置 binlog_format=row,如果主库上执行 “drop table t_normal, temp_t"这个命令,那么 binlog 中就只能记录:DROP TABLE t_normal /* generated by server */

因为备库上并没有表 temp_t,将这个命令重写后再传到备库执行,才不会导致备库同步线程停止。所以,drop table 命令记录 binlog 的时候,就必须对语句做改写。/* generated by server */说明了这是一个被服务端改写过的命令。

从服务如何保证临时表不冲突

MySQL 在记录 binlog 的时候,会把主库执行这个语句的线程 id 写到 binlog 中。这样,在备库的应用线程就能够知道执行每个语句的主库线程 id,并利用这个线程 id 来构造临时表的 table_def_key:

  1. session A 的临时表 t1,在备库的 table_def_key 就是:库名 +t1+“M 的 serverid”+“session A 的 thread_id”;
  2. session B 的临时表 t1,在备库的 table_def_key 就是 :库名 +t1+“M 的 serverid”+“session B 的 thread_id”

2.内存临时表

MySQL 什么时候会使用内部临时表?

  1. 如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果;
  2. join_buffer 是无序数组,sort_buffer 是有序数组,临时表是二维表结构;
  3. 如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如我们的例子中,union 需要用到唯一索引约束, group by 还需要用到另外一个字段来存累积计数;前面讲的如何显示随机消息中使用的 order by random 则是另一个例子。

2.1 union

union,它的语义是,取这两个子查询结果的并集。并集的意思就是这两个集合加起来,重复的行只保留一行。

1
(select 1000 as f) union (select id from t1 order by id desc limit 2);

上面这个 union 语句的执行过程是这样的:

  1. 创建一个内存临时表,这个临时表只有一个整型字段 f,并且 f 是主键字段。
  2. 执行第一个子查询,得到 1000 这个值,并存入临时表中。
  3. 执行第二个子查询:
    • 拿到第一行 id=1000,试图插入临时表中。但由于 1000 这个值已经存在于临时表了,违反了唯一性约束,所以插入失败,然后继续执行;
    • 取到第二行 id=999,插入临时表成功。
  4. 从临时表中按行取出数据,返回结果,并删除临时表,结果中包含两行数据分别是 1000 和 999

这里的内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键 id 的唯一性约束,实现了 union 的语义。如果把上面这个语句中的 union 改成 union all 的话,就没有了“去重”的语义。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了。

2.2 group by

1
2
3
4
5
# 会对返回结果排序
select id%10 as m, count(*) as c from t1 group by m;

# order by null 结果集不排序
select id%10 as m, count(*) as c from t1 group by m order by null;

它的 explain 结果如下:

/images/mysql/MySQL45%E8%AE%B2/group_explain.png

Extra 字段:

  1. Using index,表示这个语句使用了覆盖索引,选择了索引 a,不需要回表;
  2. Using temporary,表示使用了临时表;
  3. Using filesort,表示需要排序。

个语句的执行流程是这样的:

  1. 创建内存临时表,表里有两个字段 m 和 c,主键是 m;
  2. 扫描表 t1 的索引 a,依次取出叶子节点上的 id 值,计算 id%10 的结果,记为 x;
    • 如果临时表中没有主键为 x 的行,就插入一个记录 (x,1);
    • 如果表中有主键为 x 的行,就将 x 这一行的 c 值加 1;
  3. 遍历完成后,再根据字段 m 做排序,得到结果集返回给客户端。

最后一步,对内存临时表的排序,在如何显示随机消息中已经有过介绍。

临时表大小

参数 tmp_table_size 用于控制内存临时表大小的,默认是 16M。如果执行过程中会发现内存临时表大小到达了上限,这时候就会把内存临时表转成磁盘临时表,磁盘临时表默认使用的引擎是 InnoDB。

3. group by 优化

3.1 索引优化

可以看到,不论是使用内存临时表还是磁盘临时表,group by 逻辑都需要构造一个带唯一索引的表,执行代价很高。

group by 的语义逻辑,是统计不同的值出现的个数。那么,如果扫描过程中可以保证出现的数据是有序的就无须临时表了。在 MySQL 5.7 版本支持了 generated column 机制,用来实现列数据的关联更新。

1
alter table t1 add column z int generated always as(id % 100), add index(z);

3.2 直接排序

碰上不适合创建索引的场景,如果我们明明知道,一个 group by 语句中需要放到临时表上的数据量特别大,却还是要按照“先放到内存临时表,插入一部分数据后,发现内存临时表不够用了再转成磁盘临时表”,看上去就有点儿傻。

在 group by 语句中加入 SQL_BIG_RESULT 这个提示(hint),就可以告诉优化器:这个语句涉及的数据量很大,请直接用磁盘临时表。

MySQL 的优化器一看,磁盘临时表是 B+ 树存储,存储效率不如数组来得高。所以,既然你告诉我数据量很大,那从磁盘空间考虑,还是直接用数组来存吧。

1
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

执行流程就是这样的:

  1. 初始化 sort_buffer,确定放入一个整型字段,记为 m;
  2. 扫描表 t1 的索引 a,依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中;
  3. 扫描完成后,对 sort_buffer 的字段 m 做排序(如果 sort_buffer 内存不够用,就会利用磁盘临时文件辅助排序);
  4. 排序完成后,就得到了一个有序数组
  5. 据有序数组,得到数组里面的不同值,以及每个值的出现次数。

3.3 group by 使用技巧

group by 的几种实现算法,从中可以总结一些使用的指导原则:

  1. 如果对 group by 语句的结果没有排序要求,要在语句后面加 order by null;
  2. 尽量让 group by 过程用上表的索引,确认方法是 explain 结果里没有 Using temporary 和 Using filesort;
  3. 如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表;
  4. 如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果

3.4 distinct 和 group by 的性能

如果只需要去重,不需要执行聚合函数,distinct 和 group by 哪种效率高一些呢?

1
2
select a from t group by a order by null;
select distinct a from t;

不需要执行聚合函数时,distinct 和 group by 这两条语句的语义和执行流程是相同的,因此执行性能也相同。执行流程是下面这样的。

  1. 创建一个临时表,临时表有一个字段 a,并且在这个字段 a 上创建一个唯一索引;
  2. 遍历表 t,依次取数据插入临时表中:
    • 如果发现唯一键冲突,就跳过;
    • 否则插入成功;
  3. 遍历完成后,将临时表作为结果集返回给客户端。