• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

查询优化手段之临时表

开发技术 开发技术 5小时前 2次浏览
create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

临时表有什么特征,直接用普通表是不是也可以呢?临时表和内存表有什么区别?

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

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

临时表的特性

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

由于临时表只能被创建它的session访问,所以在这个session结束的时候,会自动删除临时表。正是这个特性,临时表就特别适合我们文章开头的join优化这种场景。

原因主要包括以下两个方面:

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

临时表的应用

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

  • 为什么不同线程可以创建同名的临时表,这是怎么做到的呢?
create temporary table temp_t(id int primary key)engine=innodb;

MySQL要给这个InnoDB表创建一个frm文件保存表结构定义,还要有地方保存表数据。
这个frm文件放在临时文件目录下,文件名的后缀是.frm

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

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

总结: 临时表建表语法create temporary table ,和普通的表不一样,和内存表也不一样。内存表数据保存到内存里,重启会丢失,临时表会写入到磁盘。临时表只对自己的session中可见,session结束后自动删除表结构和表数据。适用场景是分库分表,查询到的数据在临时表中做聚合。临时表可以重名,实际的存储文件名有线程id,在内存中表的命名有table_ref_key,是由库名加表名加serverid+线程id组成。bin log设置为row模式,临时表不会同步到备库中,设置为statement模式,会同步到备库中。


程序员灯塔
转载请注明原文链接:查询优化手段之临时表
喜欢 (0)