MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
42945 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

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

林晓斌 2019-02-04
今天是大年三十,在开始我们今天的学习之前,我要先和你道一声春节快乐!
在上一篇文章中,我们在优化 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/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(32)

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

    作者回复: 会,“临时表会自动回收”这个功能,主要用于“应用程序异常断开、MySQL异常重启”后,不需要主动去删除表。

    而平时正常使用的时候,用完删除,还是应该有的好习惯。😆

    好问题,新年快乐~

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

    作者回复: 新年好!

    “insert into select语句好像会给select的表加锁,如果没有索引,就锁全表”,是的。

    这类最好不要很大胆😆,如果不是业务急需的,从源表导出来再写到目标表也是好的。

     后面第40篇会说到哈。

    2019-02-04
    12
  • Ryoma
    贴一下官方文档中的说明:To rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE instead.
    全文见:https://dev.mysql.com/doc/refman/8.0/en/rename-table.html

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

    作者回复: 👍

    2019-02-26
    5
  • One day
    错过得还是得补上,新得一年,新的开始,加油

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

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

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

    2019-02-04
    3
  • 鸠翱
    放假结束该补课了😅

    评论区有个回答说到了连接池的问题问到会不会有问题……而老师您回答的是会有问题 可是临时表在session结束后不就删除了嘛 那么即使是用同一个线程又有什么问题呢?

    作者回复: 是这样的,要看连接池怎么实现。

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

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

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

    新年快乐~

    2019-02-04
    2
  • 砖瓦工
    一点一点刷新着我对mysql的认识,真心谢谢老师!期待老师有更加多的课程!祝福老师!

    作者回复: 🤝 加油

    2019-04-11
    1
  • 唯她命
    老师 为什么过滤了条件之后,选择了t1或者t3,剩下的就固定了呢?选择了t2就需要重新评估t1和t3呢?不明白
    2019-04-10
    1
    1
  • 土门一哥
    又要去学习分库分表了,学老师的一节课程从来都不止学一节课……

    作者回复: 😆加油

    2019-04-06
    1
  • 夜空中最亮的星(华仔)
    过年的时候课程落下了,给老师拜个年。

    作者回复: 新年快乐

    2019-02-13
    1
  • 慕塔
    打卡 新年快乐😲😲😲

    作者回复: 新年快乐、共同进步🤝

    好勤奋呀😆

    2019-02-04
    1
  • cheriston
    老师辛苦了,大年三十还给我们分享技术,老师新年好🎉.

    作者回复: 同祝新年好,共同进步😄

    2019-02-04
    1
  • 长杰
    老师,新年快乐,万事如意!

    作者回复: 新春快乐~

    2019-02-04
    1
  • 丁大大新春快乐

    作者回复: 新年快乐 工作顺利~

    2019-02-04
    1
  • 某、人
    老师,新年快乐。由于自身原因,错过几期精彩的内容,年后上班以后在好好补补。

    作者回复: 春节快乐 新年身体健康哈

    2019-02-04
    1
  • 尘封
    新年快乐

    作者回复: 新年快乐🤝

    2019-02-04
    1
收起评论
32
返回
顶部