36 | 为什么暂时表可以重名?

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

这里,我需求先帮你厘清一个容易误解的问题:有的人或许会认为,暂时表便是内存表。可是,这两个概念可是彻底不同的。

  • 内存表,指的是运用Memory引擎的表,建表语法是create table …engine=memory。**这种表的数据都保存在内存里,体系重启的时分会被清空,可是表结构还在。**除了这两个特性看上去比较“奇怪”外,从其他的特征上看,它便是一个正常的表。
  • 暂时表,可以运用各种引擎类型。假如是运用InnoDB引擎或许MyISAM引擎的暂时表,写数据的时分是写到磁盘上的。当然,暂时表也可以运用Memory引擎。

弄清楚了内存表和暂时表的差异今后,咱们再来看看暂时表有哪些特征。

暂时表的特性

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

MySQL实战45讲_36 | 为什么临时表可以重名?

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

  1. 建表语法是create temporary table …。
  2. 一个暂时表只能被创立它的session拜访,对其他线程不行见。所以,图中session A创立的暂时表t,关于session B便是不行见的。
  3. 暂时表可以与一般表同名。
  4. session A内有同名的暂时表和一般表的时分,showcreate句子,以及增修改查句子拜访的是暂时表。
  5. showtables指令不显示暂时表。 因为暂时表只能被创立它的session拜访,所以在这个session完毕的时分,会主动删去暂时表

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

  1. 不同session的暂时表是可以重名的,假如有多个session同时履行join优化,不需求忧虑表名重复导致建表失败的问题。
  2. 不需求忧虑数据删去问题。假如运用一般表,在流程履行过程中客户端产生了异常断开,或许数据库产生异常重启,还需求专门来整理中间过程中生成的数据表。而暂时表因为会主动收回,所以不需求这个额定的操作。

暂时表的使用

因为不必忧虑线程之间的重名抵触,暂时表常常会被用在杂乱查询的优化过程中。其间,分库分表体系的跨库查询便是一个典型的运用场景。

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

MySQL实战45讲_36 | 为什么临时表可以重名?

一般情况下,这种分库分表体系都有一个中间层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层的进程代码中完成排序。 这种方式的优势是处理速度快,拿到分库的数据今后,直接在内存中参加计算。不过,这个计划的缺点也比较显着:

  1. 需求的开发工作量比较大。咱们举例的这条句子还算是比较简单的,假如涉及到杂乱的操作,比方group by,甚至join这样的操作,对中间层的开发能力要求比较高;
  2. 对proxy端的压力比较大,尤其是很容易出现内存不够用和CPU瓶颈的问题。

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

比方上面这条句子,履行流程可以相似这样:

  • 在汇总库上创立一个暂时表temp_ht,表里包含三个字段v、k、t_modified;
  • 在各个分库上履行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;
  • 把分库履行的成果刺进到temp_ht表中;
  • 履行select v from temp_ht order by t_modified desc limit 100; 得到成果。 这个过程对应的流程图如下所示:

MySQL实战45讲_36 | 为什么临时表可以重名?

在实践中,咱们往往会发现每个分库的计算量都不饱和,所以会直接把暂时表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的。

先来举一个比如。

MySQL实战45讲_36 | 为什么临时表可以重名?

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

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

  • 一个一般表的table_def_key的值是由“库名+表名”得到的,所以假如你要在同一个库下创立两个同名的一般表,创立第二个表的过程中就会发现table_def_key已经存在了。
  • 而关于暂时表,table_def_key在“库名+表名”基础上,又加入了“server_id+thread_id”。

也便是说,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的备库。

MySQL实战45讲_36 | 为什么临时表可以重名?

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

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

显然是不会的,否则暂时表便是一个bug了。也便是说,备库线程在履行的时分,要把这两个t1表作为两个不同的暂时表来处理。这,又是怎样完成的呢? MySQL在记载binlog的时分,会把主库履行这个句子的线程id写到binlog中。这样,在备库的使用线程就可以知道履行每个句子的主库线程id,并利用这个线程id来构造暂时表的table_def_key:

  1. session A的暂时表t1,在备库的table_def_key便是:库名+t1+“M的serverid”+“session A的thread_id”;
  2. session B的暂时表t1,在备库的table_def_key便是 :库名+t1+“M的serverid”+“session B的thread_id”。

因为table_def_key不同,所以这两个表在备库的使用线程里边是不会抵触的。