第三十五讲：为什么临时表可以重名？

水色盛开

第三十五讲：为什么临时表可以重名？

简概：

还是引用

​        在上一篇文章中，我们在优化 join 查询的时候使用到了临时表。当时，我们是这么用的：

1. create temporary table temp_t like t1;
2. alter table temp_t add index(b);
3. insert into temp_t select * from t2 where b>=1 and b<=2000;
4. select * from t1 join temp_t on (t1.b=temp_t.b);

复制代码

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

• 第一种思路是，在 proxy 层的进程代码中实现排序。这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过，这个方案的缺点也比较明显：需要的开发工作量比较大。我们举例的这条语句还算是比较简单的，如果涉及到复杂的操作，比如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高；对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。
  
• 另一种思路就是，把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。比如上面这条语句，执行流程可以类似这样：在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；在各个分库上执行
  

1. select v from ht where f=N;

复制代码

​        把分库执行的结果插入到 temp_ht 表中；
​        执行

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

复制代码

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

   
          图 3 跨库查询流程示意图          ​        在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表 temp_ht 放到 32 个分库中的某一个上。这时的查询逻辑与图 3 类似，你可以自己再思考一下具体的流程。为什么临时表可以重名？你可能会问，不同线程可以创建同名的临时表，这是怎么做到的呢？
​        接下来，我们就看一下这个问题。我们在执行

1. select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

复制代码

​        这个语句的时候，MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。
​        这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id} 序列号”。你可以使用 select @@tmpdir 命令，来显示实例的临时文件目录。
​        而关于表中数据的存放方式，在不同的 MySQL 版本中有着不同的处理方式：
在 5.6 以及之前的版本里，MySQL 会在临时文件目录下创建一个相同前缀、以.ibd 为后缀的文件，用来存放数据文件；
而从 5.7 版本开始，MySQL 引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，我们就不需要再创建 ibd 文件了。
​        从文件名的前缀规则，我们可以看到，其实创建一个叫作 t1 的 InnoDB 临时表，MySQL 在存储上认为我们创建的表名跟普通表 t1 是不同的，因此同一个库下面已经有普通表 t1 的情况下，还是可以再创建一个临时表 t1 的。为了便于后面讨论，我先来举一个例子。
   

   
          图 4 临时表的表名          ​        这个进程的进程号是 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 和 sessionB 创建的两个临时表 t1，它们的 table_def_key 不同，磁盘文件名也不同，因此可以并存。
​        在实现上，每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；
​        在 session 结束的时候，对链表里的每个临时表，执行 “DROP TEMPORARY TABLE + 表名”操作。这时候你会发现，binlog 中也记录了 DROP TEMPORARY TABLE 这条命令。
​        你一定会觉得奇怪，临时表只在线程内自己可以访问，为什么需要写到 binlog 里面？这，就需要说到主备复制了。临时表和主备复制既然写 binlog，就意味着备库需要。
​        你可以设想一下，在主库上执行下面这个语句序列：

1. select v from temp_ht order by t_modified desc limit 100;

复制代码

​        如果关于临时表的操作都不记录，那么在备库就只有 create table t_normal 表和 insert into t_normal select * from temp_t 这两个语句的 binlog 日志，备库在执行到 insert into t_normal 的时候，就会报错“表 temp_t 不存在”。
​        你可能会说，如果把 binlog 设置为 row 格式就好了吧？因为 binlog 是 row 格式时，在记录 insert into t_normal 的 binlog 时，记录的是这个操作的数据，即：write_row event 里面记录的逻辑是“插入一行数据（1,1)”。
​        确实是这样。如果当前的 binlog_format=row，那么跟临时表有关的语句，就不会记录到 binlog 里。也就是说，只在 binlog_format=statment/mixed 的时候，binlog 中才会记录临时表的操作。

binlog_format=row，那么跟临时表有关的语句，就不会记录到 binlog 里,这点强调

​        这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。
​        之前有人问过我一个有趣的问题：MySQL 在记录 binlog 的时候，不论是 create table 还是 alter table 语句，都是原样记录，甚至于连空格都不变。但是如果执行 drop table t_normal，系统记录 binlog 就会写成：

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

复制代码

​        也就是改成了标准的格式。为什么要这么做呢 ？现在你知道原因了，那就是：drop table 命令是可以一次删除多个表的。比如，在上面的例子中，设置 binlog_format=row，如果主库上执行 "drop table t_normal, temp_t"这个命令，那么 binlog 中就只能记录：

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

复制代码

​        因为备库上并没有表 temp_t，将这个命令重写后再传到备库执行，才不会导致备库同步线程停止。所以，drop table 命令记录 binlog 的时候，就必须对语句做改写。“/* generated by server */”说明了这是一个被服务端改写过的命令。说到主备复制，还有另外一个问题需要解决：主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的呢？
​        现在，我给你举个例子，下面的序列中实例 S 是 M 的备库。
   

   
          图 5 主备关系中的临时表操作          ​        主库 M 上的两个 session 创建了同名的临时表 t1，这两个 create temporary table t1 语句都会被传到备库 S 上。但是，备库的应用日志线程是共用的，也就是说要在应用线程里面先后执行这个 create 语句两次。（即使开了多线程复制，也可能被分配到从库的同一个 worker 中执行）。
​        那么，这会不会导致同步线程报错 ？显然是不会的，否则临时表就是一个 bug 了。
​        也就是说，备库线程在执行的时候，要把这两个 t1 表当做两个不同的临时表来处理。这，又是怎么实现的呢？MySQL 在记录 binlog 的时候，会把主库执行这个语句的线程 id 写到 binlog 中。这样，在备库的应用线程就能够知道执行每个语句的主库线程 id，并利用这个线程 id 来构造临时表的 table_def_key：

• session A 的临时表 t1，在备库的 table_def_key 就是：库名 +t1+“M 的 serverid”+“session A 的 thread_id”;
  
• session B 的临时表 t1，在备库的 table_def_key 就是 ：库名 +t1+“M 的 serverid”+“session B 的 thread_id”。
  

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

​        今天这篇文章，我和你介绍了临时表的用法和特性。在实际应用中，临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。
​        在 binlog_format='row’的时候，临时表的操作不记录到 binlog 中，也省去了不少麻烦，这也可以成为你选择 binlog_format 时的一个考虑因素。需要注意的是，我们上面说到的这种临时表，是用户自己创建的 ，也可以称为用户临时表。与它相对应的，就是内部临时表，在第 17 篇文章中我已经和你介绍过。
问答

​        最后，我给你留下一个思考题吧。下面的语句序列是创建一个临时表，并将其改名：
   

   
          图 6 关于临时表改名的思考题          ​        可以看到，我们可以使用 alter table 语法修改临时表的表名，而不能使用 rename 语法。你知道这是什么原因吗？
答案

​        在实现上，执行 rename table 语句的时候，要求按照“库名 / 表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的，并且文件名的规则是“#sql{进程 id}{线程 id} 序列号.frm”，因此会报“找不到文件名”的错误。

来源:https://www.cnblogs.com/guixiangyyds/p/18530892
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！