mysql临时表

临时表

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

1、如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
2、join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；
3、如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如 union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

• 内存表

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

• 临时表

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

临时表的特性

1、建表语法是 create temporary table …。
2、一个临时表只能被创建它的 session 访问，对其他线程不可见。session A 创建的临时表 t，对于 session B 就是不可见的。
3、临时表可以与普通表同名。
4、session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
5、show tables 命令不显示临时表。
6、临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。使用数据库连接池，用完以后要手动删除，临时表

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

1、union 执行流程

create table t1
(
    id int primary key,
    a  int,
    b  int,
    index(a)
);


delimiter;;
create procedure idata()
begin
  declare
i int;
 
  set
i=1;
  while
(i<=1000)do
    insert into t1 values(i, i, i);
    set
i=i+1;
end while;
end;;
delimiter ;
call idata();

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

执行流程是这样的：

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

union : 取这两个子查询结果的并集，重复的行只保留一行
union all : 取这两个子查询结果的并集，不去重

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

2、group by 执行流程

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

执行流程:

1、创建内存临时表，表里有两个字段 m 和 c，主键是 m；
2、扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；

如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);
如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；
3、遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

如果不需要对结果进行排序，那可以在 SQL 语句末尾增加 order by null，这样就跳过了最后排序的阶段，直接从临时表中取数据返回

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

• 这个例子里由于临时表只有 10 行，内存可以放得下，因此全程只使用了内存临时表。但是，内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。
• 如果内存临时表大小不够存下，这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB

• group by 优化方法 – 索引

不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的
执行 group by 语句为什么需要临时表？
group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

如果可以确保输入的数据是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个过程：如果可以确保输入的数据是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个过程：
当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集里的第一行就是 (0,X);
当碰到第一个 2 的时候，已经知道累积了 Y 个 1，结果集里的第二行就是 (1,Y);
按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。

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

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

 -- 索引 z 上的数据就如上所示，group by 语句就可以改成：
 -- 这个语句的执行不再需要临时表，也不需要排序了。
select z, count(*) as c from t1 group by z;

• group by 优化方法 – 直接排序
  如果碰上不适合创建索引的场景，还是要老老实实做排序的
  
  
  在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。
  
  
  MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

  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、排序完成后，就得到了一个有序数组。
  
  后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；
  4、排序完成后，就得到了一个有序数组。