mysql 内存表和临时表学习

待我称王封你为后i 2022-05-29 23:11 267阅读 0赞

# 前景 #

*  mysql三种虚拟表  
    
    
     *  临时表
     *  内存表
     *  视图

# 临时表 #

### 简介： ###

临时表是建立在系统临时文件夹中的表。临时表的数据和表结构都存储在内存之中，退出的时候所占的空间会被释放

### 创建临时表 ###

create temporary table tmp_table( name varchar(10) not null, value int not null );

关键字为temporary

### 查看表结构 ###

注意:

show tables;
    show table status

这两个命令无法查看临时表。 但是可以查内存表

--------------------

可以查看表建立sql语句

show create table tmp_table;

### 直接将查询结果导入临时表 ###

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

### 设定临时表大小 ###

tmp\_table\_size临时表的容量

## 临时表的应用场景 ##

当工作在十分大的表上运行时，运行相关查询，来获的一个大量数据的小的子集。较好的办法，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表，然后对这些表运行查询

*  一个sql语句关联两个表以上的时候，查询到的结果存放在临时表中。
 *  程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等
 *  临时表默认的是MyISAM,但是可以修改
 *  内部临时表，就是查询的时候，服务器会优化查询，使用内部临时表。比如 order by 的列不是from中的第一列。

select * from instructor natural join teaches where dept_name='Accounting' order by semester;

get到的新技能- - -查看执行计划

explain extended
    select * from instructor natural join teaches where dept_name='Accounting' order by semester;
    show warnings;

- show warnings 能查看优化查询的sql语句

![show warnings][]  
\- explain extended能查看执行使用的各种东西  
![explain][]

## 临时表的注意事项 ##

*  临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。比如打开mysql 就是一个连接会话。两个不同的连接可以使用相同名字的临时表，两个表之间不存在什么关系，如果临时表的名字和已经存在的磁盘表名字一样，那么临时表会暂时覆盖磁盘表。就是说，你select 查询，只会显示临时表里面的，不会显示磁盘表。
 *  临时表的存储引擎：memor，myisam，merge，innodb
 *  临时表不支持，mysql cluster
 *  同一个查询语句，只能用一次临时表，就是说不能将表和自己做连接等。
 *  重命名表，不能用rename 可以用alter table代替
 *  如果超出了临时表的容量，临时表会转换成磁盘表

alter table old_name rename new_name

# 内存表 #

简介：  
内存表的表结构建立在磁盘里面，数据放在内存里面，当mysql重启之后，内存表的数据会丢失，表结构依旧存在会执行一次truncate操作

## 内存表的建立 ##

CREATE TEMPORARY TABLE tmp_table ( name VARCHAR(10) NOT NULL, value INTEGER NOT NULL ) TYPE = HEAP 注意： TYPE = HEAP必须要有

和临时表不同的地方在于，多了个type=heap

## 使用场景及注意事项 ##

内存表使用hash索引把数据保存在内存中，具有更快的速度，可以用来缓存。

*  内存表对所有的用户连接都是可用的。这就意味着，多个会话连接的内存表名字不能重复，具有唯一性
 *  内存表如果复制数据进去的话，所有的原有格式都不会存在，需要重新设置
 *  重启造成数据丢失，可以drop表之后重新复制数据等。这是最傻瓜的方法了。一定有更好的方法（待补充）
 *  支持简单的操作符>=<这三个，我认为内存表用来缓存的话，应该不会涉及很复杂的操作。
 *  不好的地方的话，应该在于数据了，因为数据都在内存里，处理起来应该蛮麻烦
 *  内存表的默认引擎是memory

# 总结 #

对比一下内存表和临时表的一些主要区别吧

*  存储  
    
    
     *  内存表 表结构存储在磁盘中，数据存储在内存中
     *  临时表 表结构和数据都存储在内存中
 *  会话  
    
    
     *  内存表 是可以多个会话共享的
     *  临时表 是单个会话独享的，是会话级别的
 *  引擎  
    
    
     *  内存表默认，memory
     *  临时表默认，myisam
 *  断开连接  
    
    
     *  临时表 啥都不剩
     *  内存表 只剩下表结构
 *  性能  
    
    
     *  内存表由于所有的内容都是放在内存中，所以相对来说，速度较快但是同时数据的维护较为困难

[show warnings]: /images/20220530/8878a70ce52049d69a15ec1d5ab6a9bc.png
[explain]: /images/20220530/9207a990f0e9448285fdd46a8bf64b8c.png