读mysql45讲-临时表

最新推荐文章于 2023-05-18 17:32:56 发布

西埃斯迪恩-霖

最新推荐文章于 2023-05-18 17:32:56 发布

阅读量228

点赞数

分类专栏：待删除文章标签： mysql 数据库

本文链接：https://blog.csdn.net/ZRL1996/article/details/124066994

版权

待删除专栏收录该内容

82 篇文章 0 订阅

订阅专栏

内存表和临时表的概念：

内存表，指的是使用Memory引擎的表，建表语法是create table …engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。

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

临时表的一些特征：

建表语法是create temporary table …。
一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的临时表t，对于session B就是不可见的。
临时表可以与普通表同名。
session A内有同名的临时表和普通表的时候，showcreate语句，以及增删改查语句访问的是临时表。
showtables命令不显示临时表。

由于临时表只能在所创建表的session中被访问，所以当session结束的时候会自动删除临时表。

临时表的应用

分库分表系统的跨库查询就是一个典型的使用场景。
把一个大表按照某个字段的值分布区间分成多个小表，分区key的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包含 f 的等值条件，那么就要用f做分区键。

比如下面这个sql：

select v from ht where f=N;

可以直接通过分表规则找到需要查询哪个表，这种情况就只需要查询一张小表了。

如果是下面这个sql：

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

因为查询条件里并没有用到分区字段f，所以只能到所有的分区表中去查询满足条件的数据，然后统一做排序的处理，这里有两种方式：

第一种是直接在内存中做处理。这种处理方式会出现内存不够用或是CPU卡住的情况。
另外一总就是将所有数据汇总到一个临时表中做处理

为什么临时表可以重名

在创建一个临时表的时候，MYSQL会创建一个frm文件来存储表结构的定义，这个frm文件存放在临时文件目录下，文件名的后缀是.frm，文件名称的前缀是#sql{进程id}_{线程id}_序列号，可以使用select @@tmpdir命令查看临时文件目录的存储路径。

而关于表中数据的存放方式，在不同的MySQL版本中有着不同的处理方式：
在5.6以及之前的版本里，MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文件，用来存放数据文件；
而从 5.7版本开始，MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，我们就不需要再创建ibd文件了。

MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。
一个普通表的table_def_key的值是由“库名+表名”得到的，所以如果在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。
而对于临时表，table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”。

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

临时表和主备复制

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*/