首先我们要厘清一个容易误解的问题：临时表是否就是内存表。但其实这两个概念是完全不同的。

  • 内存表指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但表结构还在。


  • 临时表可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。

以下图为例讲解下临时表的特性：
image.png
可以看到，临时表在使用上有以下几个特点：

  • 建表语法是 create temporary table …
  • 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t 对于 session B 是不可见的。不同 session 的临时表是可以重名的，且 session 结束后会自动删除临时表。
  • 临时表可以与普通表同名。
  • session A 内有同名的临时表和普通表的时候，show create 语句以及增删改查语句访问的是临时表。
  • show tables 命令不显示临时表。

临时表应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。分库分表系统的跨库查询就是一个典型的使用场景。一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上。如下图所示：
image.png
在这个架构中，分区 key 的选择是以减少跨库和跨表查询为依据的。如果大部分的语句都会包含 f 的等值条件，那就用 f 做分区键。这样在 proxy 解析完 SQL 语句后，就能确定将这条语句路由到哪个分表做查询。比如下面这条语句：

  1. select v from ht where f=N;

这时，我们就可以通过分表规则来确认需要的数据被放在了哪个分表上。这种语句只需要访问一个分表，是分库分表方案最欢迎的语句形式了。但如果这个表上还有另外一个索引 k，并且查询语句是这样的：

  1. select v from ht where k >= M order by t_modified desc limit 100;

这时由于查询条件里面没有用到分区字段 f，只能到所有的分区中去查找满足条件的所有行，然后统一做 order by 的操作。这种情况下有两种比较常用的思路。

第一种思路是在 proxy 层的进程代码中实现排序。

这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过缺点是需要的开发工作量比较大，如果涉及到复杂的操作，对中间层的开发能力要求比较高。对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。

另一种思路就是，把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。比如上面这条语句，执行流程可以类似这样：

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

  • 在各个分库上执行下面的这个语句

    1. select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

  • 把分库执行的结果插入到 temp_ht 表中

  • 再执行下面的语句得到结果
    1. select v from temp_ht order by t_modified desc limit 100;
    这个过程对应的流程图如下所示：
    image.png
    在实际应用中，临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。

在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。在 binlog_format=’row’的时候，临时表的操作不记录到 binlog 中，也省去了不少麻烦。需要注意的是，我们上面说到的这种临时表，是用户自己创建的 ，也可以称为用户临时表。与它相对应的就是内部临时表。

内部临时表

MySQL 什么时候会使用内部临时表呢？我们先举两个需要用到内部临时表的例子，来看看内部临时表是怎么工作的。然后再分析，什么情况下会使用内部临时表。

1. group by 执行流程

首先，执行如下建表语句：

  1. create table t1(id int primary key, a int, b int, index(a));
  2. delimiter ;;
  3. create procedure idata()
  4. begin
  5.   declare i int;
  7.   set i=1;
  8.   while(i<=1000)do
  9.     insert into t1 values(i, i, i);
  10.     set i=i+1;
  11.   end while;
  12. end;;
  13. delimiter ;
  14. call idata();

举一个 group by 的例子：

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

这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序后输出。它的 explain 结果如下：
image.png
在 Extra 字段里面，我们可以看到三个信息：

  • Using index，表示这个语句使用了覆盖索引，选择了索引 a，不需要回表。
  • Using temporary，表示使用了临时表
  • Using filesort，表示需要排序。

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

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

这个流程的执行图如下：
image.png
如果你的需求并不需要对结果进行排序，那你可以在 SQL 语句末尾增加 order by null，也就是改成：

  1. select id%10 as m, count(*) as c from t1 group by m order by null;

这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

注意：内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。如果内存临时表的大小不够存下全部数据时，即执行过程中发现内存临时表大小到达了上限。那此时就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。 如果数据量很大，很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

2. group by 优化

2.1 索引

可以看到，不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个 group by 语句执行起来就会很慢，那有什么优化方法呢？

首先先想一下，执行 group by 语句为什么需要临时表？group by 的语义逻辑是统计不同的值出现的个数。但由于每一行的 id%10 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。如果扫描过程中可以保证出现的数据是有序的，那是不是就简单了呢？假设，现在有一个类似下图的一个数据结构，我们来看看 group by 可以怎么做。
image.png
如果输入数据是按上图所示顺序排列的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个过程：

  • 当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集里的第一行就是 (0,X)
  • 当碰到第一个 2 的时候，已经知道累积了 Y 个 1，结果集里的第二行就是 (1,Y)

按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序了。而 InnoDB 的索引就可以满足这个输入有序的条件。

在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引（如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引来解决这个问题）。

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

这样，索引 z 上的数据就是类似上图那样有序的了。上面的 group by 语句就可以改成：

  1. select z, count(*) as c from t1 group by z;

优化后的 group by 语句的 explain 结果，如下图所示：
image.png
从 Extra 字段可以看到使用了索引，因此这个语句的执行就不再需要临时表，也不需要排序了。

2.2 直接排序

如果可以通过加索引来完成 group by 逻辑就再好不过了。但是，如果碰上不适合创建索引的场景，我们还是要老老实实做排序的。那此时 group by 要怎么优化呢？

如果我们明明知道，一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照 “先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表” 的逻辑来执行的话，看上去就有点儿傻。那 MySQL 有没有让我们直接走磁盘临时表的方法呢？

答案是，有的。在 group by 语句中加入 SQL_BIG_RESULT 这个提示，就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以从磁盘空间考虑，还是直接用数组来存吧。因此，下面这个语句：

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

的执行流程就是这样的：

  • 初始化 sort_buffer，确定放入一个整型字段，记为 m
  • 扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中
  • 扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 不够用，就利用磁盘临时文件辅助排序）
  • 排序完成后，就得到了一个有序数组。

根据有序数组，得到数组里面的不同值，以及每个值的出现次数。下图为执行流程图和 explain 结果：
image.png
image.png
从 Extra 字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法。

3. 总结

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

  • 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存来保存中间结果。
  • 临时表是二维表结构，如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中 group by 还需要用到另外一个字段来存累积计数。

group by 的几种实现算法？

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