MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224874 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

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

binlog记录临时表的操作,备库同步时会处理临时表的操作
临时表在线程结束时会自动删除
使用临时表可以避免线程间的重名冲突
临时表适合处理复杂计算逻辑
备库的应用线程会根据主库线程id来处理临时表的操作
临时表的操作会记录到binlog中
用户临时表的table_def_key由“库名+表名+server_id+thread_id”得到
内部临时表的table_def_key由“库名+表名”得到
分库分表系统的跨库查询
用于复杂查询的优化过程
show tables命令不显示临时表
session A内有同名的临时表和普通表的时候,show create语句,以及增删改查语句访问的是临时表
临时表可以与普通表同名
一个临时表只能被创建它的session访问,对其他线程不可见
建表语法是create temporary table ...
总结
临时表和主备复制
为什么临时表可以重名?
临时表的应用
临时表的特性

该思维导图由 AI 生成,仅供参考

今天是大年三十,在开始我们今天的学习之前,我要先和你道一声春节快乐!
在上一篇文章中,我们在优化 join 查询的时候使用到了临时表。当时,我们是这么用的:
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 …。
一个临时表只能被创建它的 session 访问,对其他线程不可见。所以,图中 session A 创建的临时表 t,对于 session B 就是不可见的。
临时表可以与普通表同名。
session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。
show tables 命令不显示临时表。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

临时表在MySQL中具有独特的特性,适合于复杂查询优化和分库分表系统中的应用。与内存表不同,临时表可以使用各种引擎类型,包括InnoDB和MyISAM引擎,且可以与普通表同名。由于临时表只能被创建它的session访问,对其他线程不可见,并且在session结束时会自动删除,因此特别适合于避免表名重复导致建表失败的问题和不需要额外的数据清理操作。临时表经常被用于复杂查询的优化过程中,尤其在分库分表系统的跨库查询中发挥重要作用。文章还介绍了临时表的存储机制和命名规则,解释了为什么不同线程可以创建同名的临时表。总体而言,临时表的特性和应用使其成为MySQL中一个重要的技术工具。 在实际应用中,临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的,所以不需要考虑多个线程执行同一个处理逻辑时,临时表的重名问题。在线程退出的时候,临时表也能自动删除,省去了收尾和异常处理的工作。在binlog_format='row'的时候,临时表的操作不记录到binlog中,也省去了不少麻烦,这也可以成为你选择binlog_format时的一个考虑因素。 需要注意的是,我们上面说到的这种临时表,是用户自己创建的,也可以称为用户临时表。与它相对应的,就是内部临时表,在中我已经和你介绍过。 总的来说,临时表在MySQL中的应用非常灵活,能够解决复杂查询和分库分表系统中的问题,同时也减少了一些数据处理的繁琐工作。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(48)

  • 最新
  • 精选
  • 辣椒
    老师,不同线程可以使用同名的临时表,这个没有问题。但是如果在程序中,用的是连接池中的连接来操作的,而这些连接不会释放,和数据库保持长连接。这样使用临时表会有问题吗?。

    作者回复: 会,“临时表会自动回收”这个功能,主要用于“应用程序异常断开、MySQL异常重启”后,不需要主动去删除表。 而平时正常使用的时候,用完删除,还是应该有的好习惯。😆 好问题,新年快乐~

    2019-02-07
    3
    204
  • 老杨同志
    新年快乐,老师好勤奋! 有个问题,insert into select语句好像会给select的表加锁,如果没有索引,就锁全表,是不是这样?什么时候可以大胆的用这类语句?

    作者回复: 新年好! “insert into select语句好像会给select的表加锁,如果没有索引,就锁全表”,是的。 这类最好不要很大胆😆,如果不是业务急需的,从源表导出来再写到目标表也是好的。 后面第40篇会说到哈。

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

    作者回复: 👍

    2019-02-26
    3
    57
  • Carisy
    老师有个问题,如果说创建临时表在主库,查询的时候打到从库上,查询是不是就有问题了,查询主库的线程id跟从库不一致

    作者回复: 是的,所以一般一个事务创建临时表以后,读写分离就会默认接下来的请求都路由到主库去了

    2020-05-18
    2
    44
  • poppy
    老师,新年快乐。 关于思考题,alter table temp_t rename to temp_t2,我的理解是mysql直接修改的是table_def_key,而对于rename table temp_t2 to temp_t3,mysql直接去mysql的data目录下该数据库的目录(例如老师实验用的应该是test数据库,所以对应的是test目录)下寻找名为temp_t2.frm的文件去修改名称,所以就出现了"Can't find file './test/temp_t2.frm'(errno: 2 - No such file or directory)

    作者回复: 春节快乐 👍

    2019-02-04
    39
  • 鸠翱
    放假结束该补课了😅 评论区有个回答说到了连接池的问题问到会不会有问题……而老师您回答的是会有问题 可是临时表在session结束后不就删除了嘛 那么即使是用同一个线程又有什么问题呢?

    作者回复: 是这样的,要看连接池怎么实现。 如果A客户端在执行过程中创建了临时表,用完了连接就放回池子里面,没有做别的清理工作,然后新的客户端B复用这个连接,就可能会看到A的临时表

    2019-02-12
    2
    25
  • 老师您好,在25课里面的置顶留言“6.表上无主键的情况(主库利用索引更改数据,备库回放只能用全表扫描,这种情况可以调整slave_rows_search_algorithms参数适当优化下)” 为啥会存在无主键的表呢,就算dba没创建主键,Innodb可以用rowid给自动建一个虚拟主键呀,这样不就是所有的表都有主键了吗?

    作者回复: 用户没有显示指定主键的话,InnoDB引擎会自己创建一个隐藏的主键,但是这个主键对Server层是透明的,优化器用不上。 新年快乐~

    2019-02-04
    21
  • undifined
    老师 有几个问题 1. 在 session 结束的时候会执行 DROP TEMPORARY TABLE,如果数据库掉电,这个临时表什么时候会被清除 2. 如果binlog 中记录了临时表的操作,因为 session 不同,在从库中访问不到,这样做的意义是什么 辛苦老师解答一下,谢谢老师

    作者回复: 1. 好问题,重启以后MySQL会扫描临时目录,把表都删掉; 2. 就是我们文中说的,如果binlog是statement的时候,也需要同步到备库去,否则备库上执行一个 insert into t_normal (select * from t_temp) 就会报错了

    2019-02-11
    3
    12
  • 布衣骇客
    错过得还是得补上,新得一年,新的开始,加油

    作者回复: 新年快乐,加油💪

    2019-02-11
    6
  • 老师过年好呀,祝您猪年大吉,财源广进;老师咱们这个课结束后,再开一期好不好啊,没学够啊,这是我的新年愿望哦

    作者回复: 新年快乐,共同进步😄

    2019-02-04
    6
收起评论
显示
设置
留言
48
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部