MySQL8的临时表

2023-01-03 19:44:00 浏览数 (2)

临时表

  1. 粗浅的讲，临时表就是临时使用、在会话或sql结束后就会被销毁的表。
  2. 临时表有两种，一种是用户手动创建的，另一种是在执行sql过程中由mysql自动创建的，这种临时表就叫做内部临时表。

手动创建的临时表

创建方式

代码语言：javascript复制
<!-- 必须指明引擎为memory，否则会使用innodb引擎创建为磁盘临时表 -->
CREATE TEMPORARY TABLE 表名(字段列表) ENGINE=MEMORY;

手动创建的临时表可以和普通表同名，会话会优先使用临时表。

查看临时表

代码语言：javascript复制
1. show create table 表名;
2. desc 表名;
3.查看未销毁的且引擎是innodb的临时表：
select * from information_schema.innodb_temp_table_info;

查看临时表的使用情况

代码语言：javascript复制
1. 查看内部临时表：
show status like '%created_tmp%';
2. 查看引擎是temptable的内临时表的内存/磁盘使用情况：
select * from performance_schema.memory_summary_global_by_event_name where event_name like "%temptable%" G;
3. explain的extra字段包含了using temporary，说明sql使用了内部临时表。

内部临时表和用户手动创建的临时表的区别

  1. 手动创建的临时表引擎由参数default_tmp_storage_engine控制，默认是innodb；内部临时表的引擎由参数internal_tmp_mem_storage_engine控制，有temptable和memory两种，创建后都是内存类型的临时表，且temptable引擎只能被内部临时表使用。
  2. 大小限制受不同的参数控制。
  3. 使用场景不同。

相关配置

  1. internal_tmp_mem_storage_engine：设置内部临时表使用的引擎，有temptable和memory两种，默认temptable。
  2. default_tmp_storage_engine：设置用户创建的临时表的引擎，默认是innodb。
  3. tmp_table_size：单个临时表可以占用的最大空间，会话级参数。
  4. temptable_max_ram：所有临时表的总大小超过这个值后，原有的临时表如果继续增大就会被转换为磁盘临时表，如果此时新建临时表也会被存储在磁盘上。这个参数限制的是所有会话的临时表的总大小，而不是单个会话或单个临时表。
  5. temptable_max_mmap：在temptable_use_mmap=on时，所有临时表的总大小超过temptable_max_ram后，继续使用最大为temptable_max_mmap的内存映射文件存储数据。所以在temptable_use_mmap=on时，单个临时表的内存最大是min(tmp_table_size,temptable_max_ram temptable_max_mmap)；如果temptable_use_mmap=off，那么单个临时表的内存最大是min(tmp_table_size,temptable_max_ram)。
  6. max_heap_table_size：引擎是memory的表的最大存储空间，修改这个参数对已经存在的内存表没有影响。和参数tmp_table_size一起控制用户创建的内存临时表的大小：min(tmp_table_size,max_heap_table_size)。于计算内存表的MAX_ROWS值？？？
  7. innodb_temp_tablespaces_dir：会话临时表空间的磁盘存储位置，默认数据存储目录中的#innodb_temp
  8. innodb_temp_data_file_path：定义全局临时表空间的位置、大小等，格式是：file_name:file_size[:autoextend[:max:max_file_size]]

在磁盘上的内部临时表过多时(created_tmp_disk_tables)，需要增大参数tmp_talble_size，temptable_max_ram，temptable_max_mmap。

内存中的临时表被转存到磁盘的情况

  1. 临时表被存储到磁盘上时，引擎会转为default_tmp_storage_engine参数设置的引擎，默认是innodb。
  2. 引擎是memory的临时表使用了text或blog等字段。但temptable引擎可以使用varchar或varbinary字段
  3. 使用了innodb引擎，默认引擎是temptable，可以通过配置参数internal_tmp_mem_storage_engine查看。
  4. 达到大小限制：
  • engine=temptable：内存最大值是16mb，使用tmp_table_size参数设置。表大小达到该限制或所有会话的内部临时表总大小大于temptable_max_ram temptable_max_mmap后会被转换为innodb类型的磁盘临时表。
  • engine=memory：超过min(max_heap_table_size,tmp_table_size)，会产生磁盘临时表，即Created_tmp_disk_tables会增加

磁盘存储

临时表空间：简单来讲就是磁盘上存储临时表的文件。分为会话临时表空间和全局临时表空间。

会话临时表空间

按临时表的类型分为用户创建的临时表空间和mysql的优化器自动创建的内部临时表空间。

分配方式：

  1. 每个会话中最多分配两个会话临时表空间，一个用户存储用户创建的临时表，一个用于存储内部临时表。
  2. 每个会话中的每种临时表空间只分配一次，就是在会话第一次创建该类型的磁盘临时表时分配，此后只会按需要扩大该类型的临时表空间。
  3. 会话结束后释放会话中的临时表空间。

查看会话临时表空间：

代码语言：javascript复制
只能查看引擎是innodb的临时表的空间，根据PURPOSE字段区分用户创建的临时表和内部临时表，分别是user和intrinsic。
select * from information_schema.innodb_session_temp_tablespaces;

初始化临时表空间池

mysql启动时会创建一个包含10个临时表空间的临时表空间池，分配临时表空间时会先使用临时表空间池中的文件。

参考推荐

[官方文档](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html)

[MySQL8.0中消失又回来的磁盘临时表](https://ost.51cto.com/posts/16160)

[mysql 临时表空间](https://blog.csdn.net/weixin_36263738/article/details/113159939)

[MySQL 的临时表和临时文件](https://zhuanlan.zhihu.com/p/390200218)

云数据库MySQL

0 人点赞