Notes

Exploring note

0%

用户临时表简析

发表于 更新于 分类于
本文字数： 4.8k 阅读时长 ≈ 8 分钟
用户临时表简析

内存表与临时表：

  • 内存表，指的是使用Memory引擎的表，建表语法是create table…engine=memory。这种表的数据都保存在内存里，系统启动的时候会被清空，但是表结构还在。
  • 临时表，可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写在磁盘上的。使用Memory引擎数据保存在内存上。

临时表的特性

sessionA sessionB
create temporary table t(c int) engine=myisam;
show create table t;
(Table ‘t’ doesn’t exist)
create table t(id int primary key) engine=innodb;
show create table;
//create temporary table t(c int) engine=myisam;
show tables;
//只显示普通表t
insert into t values(1);
select * from t;
//返回1
select * from t;
Empty set
  1. 建表语法是create temporary table…。
  2. 一个临时表只能被创建它的session访问，对其他线程不可见。所以sessionA创建的临时表t，对于sessionB就是不可见的。
  3. 临时表可以与普通表同名。
  4. sessionA内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
  5. show tables命令不显示临时表。

临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表。这个特性适合join优化的场景。

  1. 不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表名重复导致建表失败的问题。
  2. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

临时表的应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。分库分表系统的跨库查询

一般分库分表的场景，要把一个逻辑上的大表分散到不同的数据库实例上。将一个大表ht，按照字段f，拆分成1024个分表，然后分布到32个数据库实例上。一般情况下，这种分库分表系统都有一个中间层proxy。(也有一些方案会让客户端直接连接数据库，也就是没有proxy这一层)

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

select v from ht where f=N;
这时可以通过分表规则(N%1024)来确认需要的数据被放在了哪个分表上。这种语句只需要访问一个分表。

select v from ht where k>=M order by t_modified desc limit 100;– k为索引字段
由于查询条件里面没有用到分区字段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;
  • 得到结果。

实践中，每个分库的计算量都不饱和，会直接把临时表temp_ht放到32个分库中的某一个上。

临时表可以重名

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

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

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是”#sql{进程id}_{线程id}_序列号”。可以使用select@@tmpdir命令，来显示实例的临时文件目录。关于表中数据的存放方式，不同的MySQL版本有不同的处理方式：

  • 在5.6以及以前的版本，MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文件，用来存放数据文件；
  • 从5.7版本开始，MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。不需要再创建idb文件了。

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

临时表重命名

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

只能用alter table语法修改临时表的表名，而不能使用rename语法。 

1
2
3
create temporary table temp_t(id int primary key)engine=innodb;  
alter table temp_t rename to temp_t2;  
rename table temp_t2 to temp_t3;

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

  • 一个普通的table_def_key的值是有“库名+表名”得到的，如果要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。
  • 对于临时表，table_def_key在“库名+表名”的基础上，又加入了“server_id+thread_id”。

不同session创建的两个同名的临时表t1，它们的table_def_key不同，磁盘文件名也不同，因此可以并存。

在实现上，每个线程都维护了自己的临时表链表。这样每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束的时候，对链表里的每个临时表，执行”DROP TEMPORARY TABLE + 表名”操作。(如果数据库掉电，重启以后MySQL会扫描临时目录，把表都删掉。)

binlog也记录了DROP TEMPORARY TABLE这条命令。

临时表和主备复制

既然写binlog，就意味着备库需要。 

1
2
3
4
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 into t_normal select * from temp_t这两个语句的binlog日志，备库在执行到insert into t_normal的时候，就会报错“表temp_t不存在”。

如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog里(insert into t_normal的binlog记录的是这个操作的数据，write_row event里记录的逻辑是“插入一行数据(1,1)”)。

只在binlog_format=statment/mixed的时候，binlog中才记录临时表的操作。创建临时表的语句会传到备库执行，备库的同步线程会创建这个临时表。在主库线程退出的时候，就会删除临时表，但是备库同步线程是持续在运行的。需要在主库上再写一个DROP TEMPORARY TABLE传给备库执行。

问题一

MySQL在记录binlog的时候，不论是create table还是alter table语句，都是原样记录，甚至于连空格都不会变。drop table语句记录问题。

设置binlog_format=row，如果主库上执行”drop table t_normal,temp_t”命令，binlog改成了标准的格式只能记录：DROP TABLE ‘t_normal’/generated by server/

备库上并没有表temp_t，将这个命令重写后再传到备库执行，才不会导致备库同步线程停止。drop table命令记录到binlog的时候，/generated by server/说明了这是一个被服务端改写过的命令。

问题二

主库上不同的线程创建同名的临时表是没关系的，传到备库执行问题。

M上sessionA M上sessionB S上的应用日志线程
T1 create temporary table t1…;
T2 create temporary table t1…;
T3 create temporary table t1…;
T4 create temporary table t1…;

主库M上的两个session创建了同名的临时表t1，这两个create temporary table t1语句都会被传到备库S上。但是备库的应用日志线程是共用的，在应用线程里面执行这个create语句两次。(即使开了多线程复制，也可能被分配到从库的同一个worker中执行)。备库线程在执行的时候，要把这两个t1表当做两个不同的临时表来处理，才能避免出错。

MySQL在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中。这样，在备库的应用线程就能够知道每个语句的主库线程id，并利用这个线程id来构造临时表的table_def_key:

  1. sessionA的临时表t1，在备库的table_def_key就是：库名+t1+”M的serverid”+”sessionA的thread_id”；
  2. sessionB的临时表t1，在备库的table_def_key就是：库名+t1+”M的serverid”+”sessionB的thread_id”；

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

问题三

如果创建临时表在主库，查询的时候打到从库上，查询就有问题了，查询主库的线程id跟从库不一致。

一般一个事务创建来临时表以后，读写分离就会默认接下来的请求都路由到主库。

总结

临时表一般用于比较复杂的计算逻辑。临时表是每个线程自己乐见的，不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。

以上临时表，是用户自己创建的，称为用户临时表，与其对应的是内部临时表(内存临时表&磁盘临时表)。MySQL-随机排序