临时表简介

MySQL临时表在很多场景都会用到，比如用户自己创建的临时表用于【保存临时数据】，以及MySQL内部在执行【复杂SQL】时，需要借助临时表进行【分组、排序、去重】等操作，临时表具有以下几个特点：

  1. 临时表不能通过show tables查看，服务器重启之后，所有的临时表将全部被销毁
  2. 临时表是每个客户端独立的，其他客户端不能访问别的客户端的临时表。

临时表分类

1、外部临时表

通过create temporary table语句创建的临时表为外部临时表：

create temporary table temp_user (
    id int primary key auto_increment comment '自然主键',
    nickname varchar(64) comment '昵称'
) engine=innodb default charset=utf8mb4 collate utf8mb4_general_ci comment '用户临时表';

insert into temp_user values (null,'kaiven');
select * from temp_user;

drop temporary table temp_user; -- 删除临时表

我们一般很少去创建。。。

2、内部临时表

内部临时表用来存储某些操作中间结果，这些操作可能包括在【优化阶段】或者【执行阶段】，这种内部表对用户来说是透明的。通常在执行复杂SQL语句时，比如group by、distinct、union等语句时，MySQL内部将使用【自动生成的临时表】，以辅助SQL的执行。我们可以使用执行计划查看，如果一条SQL语句的执行计划中【列extra】结果为Using temporary，那么也就说明这个查询要使用到临时表：

explain select age from tb_student group by age order by age;

image-20241114203211499

哪些场景会使用临时表呢？

  1. group by，且分组字段没有添加索引

    alter table tb_student add index age_index(age);
explain select age from tb_student group by age;

    image-20241114204332780

​ 给age字段添加索引之后，就没有使用临时表了。

  1. distinct去重
explain select distinct age from tb_student;

image-20241114203930834

  1. 使用union进行结果合并，辅助去重
explain select * from tb_student where age > 15 union select * from tb_student where age < 30;

image-20241114204950767

image-20241114204959284

union all不会进行去重工作，所以不会使用临时表：

explain select * from tb_student where age > 15 union all select * from tb_student where age < 30;

image-20241114205108332

（复杂SQL很容易产生临时表，需要不断的积累和学习）

小知识

其实临时表还可以分为【内存临时表】和【磁盘临时表】。内存临时表使用memory引擎（该引擎不支持BLOB和TEXT类型），磁盘临时表默认使用innodb引擎。下列几种情况，会创建磁盘临时表：

  1. 数据表中包含BLOB/TEXT列
  2. 在grou by 或者 distinct 的列中有超过512字符的字符类型列
  3. 在 select、union、union all 查询中，存在最大长度超过512的列（字符串是512个字符，二进制类型是512字节）

2024.11.14

writeBy kaiven