• 欢迎访问开心洋葱网站,在线教程,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站,欢迎加入开心洋葱 QQ群
  • 为方便开心洋葱网用户,开心洋葱官网已经开启复制功能!
  • 欢迎访问开心洋葱网站,手机也能访问哦~欢迎加入开心洋葱多维思维学习平台 QQ群
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏开心洋葱吧~~~~~~~~~~~~~!
  • 由于近期流量激增,小站的ECS没能经的起亲们的访问,本站依然没有盈利,如果各位看如果觉着文字不错,还请看官给小站打个赏~~~~~~~~~~~~~!

MySQL 中的临时表

其他 开心洋葱 2055次浏览 0个评论

在使用 explain 解析一个 sql 时,有时我们会发现在 extra 列上显示 using temporary ,这表示这条语句用到了临时表,那么临时表究竟是什么?它又会对 sql 的性能产生什么影响?又会在哪些场景中出现?本文根据 <<MySQL 实战 45 讲>> 学习整理。

出现场景

其实临时表在之前的博客就已经出现过了,在 MySQL 中的排序 一文中就说到如果 order by 的列上没有索引,或者说没有用到索引,那么就需要进行额外排序(using filesort),而额外排序优先在一块 sort_buffer 空间中进行,如果这块空间大小小于要加载的字段总长度,那么就会用到临时文件辅助排序,这个临时文件就是临时表。临时表的作用就是作为中间表优化操作,比如 group by 作为分组的中间表, order by rand() (MySQL 中的排序 中的例子)作为中间表帮助运算等。

特点

MySQL 中的临时表

 

1、建表语法是 create temporary table …。

2、一个临时表只能被创建它的 session 访问,对其他线程不可见,在会话结束后自动删除。所以,图中 session A 创建的临时表 t,对于 session B 就是不可见的。(所以特别适合用于join 优化)

3、临时表可以与普通表同名。

4、session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。

5、show tables 命令不显示临时表。

种类

临时表分为磁盘临时表和内存临时表。磁盘临时表指的是存储在磁盘上的临时表,因为在磁盘上,所以执行效率比较低,优点结构可以是有序的,实现可以是 InnoDB(默认),MyISAM 引擎;内存临时表就是存储在内存中,执行效率高,常用的实现引擎是 Memory。

磁盘临时表和内存临时表的区别

1、相比于 InnoDB 表,使用内存表不需要写磁盘,往表 temp_t 的写数据的速度更快;

2、索引 b 使用 hash 索引,查找的速度比 B-Tree 索引快;

3、临时表数据只有 2000 行,占用的内存有限。

 

Memory 引擎

与 InnoDB 的区别

1、InnoDB 表的数据总是有序存放的,而内存表的数据就是按照写入顺序存放的;关于这点可以通过创建 b+ 索引来进行排序,优化查询。alter table t1 add index a_btree_index using btree (id);

2、当数据文件有空洞的时候,InnoDB 表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而内存表找到空位就可以插入新值

3、数据位置发生变化的时候,InnoDB 表只需要修改主键索引,而内存表需要修改所有索引

4、InnoDB 表用主键索引查询时需要走一次索引查找,用普通索引查询的时候,需要走两次索引查找。而内存表没有这个区别,所有索引的“地位”都是相同的。

5、InnoDB 支持变长数据类型,不同记录的长度可能不同;内存表不支持 Blob 和 Text 字段,并且即使定义了 varchar(N),实际也当作 char(N),也就是固定长度字符串来存储,因此内存表的每行数据长度相同

6、内存表支持 hash 索引,并且数据存储在内存中,所以执行比数据存储在磁盘上的 Innodb 快

缺点

1、锁粒度大,只支持表级锁,并发度低。

MySQL 中的临时表

MySQL 中的临时表

2、数据持久性差。因为是内存结构,所以在重启后数据会丢失 。由此会导致备库在硬件升级后数据就会丢失,并且如果主从库互为 &#