亲宝软件园·资讯

展开

MySQL临时表重名

财大小陈 人气:0

今天我们就从这个问题说起:临时表有哪些特征,适合哪些场景?

这里,我需要先帮你厘清一个容易误解的问题:有的人可能会认为,临时表就是内存表。但是,这两个概念可是完全不同的。

弄清楚了内存表和临时表的区别以后,我们再来看看临时表有哪些特征。

临时表的特性

为了便于理解,我们来看下下面这个操作序列:

可以看到,临时表在使用上有以下几个特点:

由于临时表只能被创建它的session访问,所以在这个session结束的时候,会自动删除临时表

也正是由于这个特性,临时表就特别适合上篇文章中join优化这种场景。为什么呢? 原因主要包括以下两个方面:

临时表的应用

由于不用担心线程之间的重名冲突,临时表经常会被用在复杂查询的优化过程中。其中,分库分表系统的跨库查询就是一个典型的使用场景。

一般分库分表的场景,就是要把一个逻辑上的大表分散到不同的数据库实例上。比如。将一个大表ht,按照字段f,拆分成1024个分表,然后分布到32个数据库实例上。如下图所示:

一般情况下,这种分库分表系统都有一个中间层proxy。不过,也有一些方案会让客户端直接连接数据库,也就是没有proxy这一层。

在这个架构中,分区key的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包含f的等值条件,那么就要用f做分区键。这样,在proxy这一层解析完SQL语句以后,就能确定将这条语句路由到哪个分表做查询。

比如下面这条语句:

select v from ht where f=N;

这时,我们就可以通过分表规则(比如,N%1024)来确认需要的数据被放在了哪个分表上。这种语句只需要访问一个分表,是分库分表方案最欢迎的语句形式了。

但是,如果这个表上还有另外一个索引k,并且查询语句是这样的:

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

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

第一种思路是,在proxy层的进程代码中实现排序。 这种方式的优势是处理速度快,拿到分库的数据以后,直接在内存中参与计算。不过,这个方案的缺点也比较明显:

另一种思路就是,把各个分库拿到的数据,汇总到一个MySQL实例的一个表中,然后在这个汇总实例上做逻辑操作。

比如上面这条语句,执行流程可以类似这样:

得到结果。 这个过程对应的流程图如下所示:

在实践中,我们往往会发现每个分库的计算量都不饱和,所以会直接把临时表temp_ht放到32个分库中的某一个上

为什么临时表可以重名?

你可能会问,不同线程可以创建同名的临时表,这是怎么做到的呢?

我们在执行

create temporary table temp_t(id int primary key)engine=innodb;

这个语句的时候,MySQL要给这个InnoDB表创建一个frm文件保存表结构定义,还要有地方保存表数据。

这个frm文件放在临时文件目录下,文件名的后缀是.frm,前缀是“#sql{进程id}_ {线程id}_ 序列号”。

从文件名的前缀规则,我们可以看到,其实创建一个叫作t1的InnoDB临时表,MySQL在存储上认为我们创建的表名跟普通表t1是不同的,因此同一个库下面已经有普通表t1的情况下,还是可以再创建一个临时表t1的。

先来举一个例子。

这个进程的进程号是1234,session A的线程id是4,session B的线程id是5。所以你看到了,session A和session B创建的临时表,在磁盘上的文件不会重名。

MySQL维护数据表,除了物理上要有文件外,内存里面也有一套机制区别不同的表,每个表都对应一个table_def_key。

也就是说,session A和session B创建的两个临时表t1,它们的table_def_key不同,磁盘文件名也不同,因此可以并存

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

这时候你会发现,binlog中也记录了DROPTEMPORARY TABLE这条命令。你一定会觉得奇怪,临时表只在线程内自己可以访问,为什么需要写到binlog里面?这,就需要说到主备复制了。

临时表和主备复制

既然写binlog,就意味着备库需要。 你可以设想一下,在主库上执行下面这个语句序列:

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

如果关于临时表的操作都不记录,那么在备库就只有create table t_normal表和insert intot_normal select * fromtemp_t这两个语句的binlog日志,备库在执行到insert into t_normal的时候,就会报错“表temp_t不存在”。

你可能会说,如果把binlog设置为row格式就好了吧?因为binlog是row格式时,在记录insert intot_normal的binlog时,记录的是这个操作的数据,即:write_rowevent里面记录的逻辑是“插入一行数据(1,1)”。

确实是这样。如果当前的binlog_format=row,那么跟临时表有关的语句,就不会记录到binlog里。也就是说,只在binlog_format=statment/mixed的时候,binlog中才会记录临时表的操作

这种情况下,创建临时表的语句会传到备库执行,因此备库的同步线程就会创建这个临时表。主库在线程退出的时候,会自动删除临时表,但是备库同步线程是持续在运行的。所以,这时候我们就需要在主库上再写一个DROPTEMPORARY TABLE传给备库执行。

主库上不同的线程创建同名的临时表是没关系的,但是传到备库执行是怎么处理的呢?

现在,我给你举个例子,下面的序列中实例S是M的备库。

主库M上的两个session创建了同名的临时表t1,这两个create temporary table t1 语句都会被传到备库S上。

但是,备库的应用日志线程是共用的,也就是说要在应用线程里面先后执行这个create 语句两次。(即使开了多线程复制,也可能被分配到从库的同一个worker中执行)。那么,这会不会导致同步线程报错?

显然是不会的,否则临时表就是一个bug了。也就是说,备库线程在执行的时候,要把这两个t1表当做两个不同的临时表来处理。这,又是怎么实现的呢? MySQL在记录binlog的时候,会把主库执行这个语句的线程id写到binlog中。这样,在备库的应用线程就能够知道执行每个语句的主库线程id,并利用这个线程id来构造临时表的table_def_key:

由于table_def_key不同,所以这两个表在备库的应用线程里面是不会冲突的。

加载全部内容

相关教程
猜你喜欢
用户评论