mysql 临时表、内存表和视图

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
虚拟表,顾名思义,就是实际上并不存在(物理上不存在),但是逻辑上存在的表。这样说很抽象,还是看一些实际的例子吧。
MySQL 中,存在三种虚拟表:临时表、内存表和视图。

一、mysql临时表
1、什么是临时表
临时表是建立在系统临时文件夹中的表,如果使用得当,完全可以像普通表一样进行各种操作。 临时表的数据和表结构都储存在内存之中,退出时,其所占的空间会自动被释放。
2、创建临时表
(1)定义字段
CREATE TEMPORARY TABLE tmp_table (        
name VARCHAR(10) NOT NULL,        
value INTEGER NOT NULL)     
(2)直接将查询结果导入临时表
CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name
3、查询临时表
select * from tmp_table
4、删除临时表
drop table tmp_table

二、临时表的应用
当工作在十分大的表上运行时,在实际操作中你可能会需要运行很多的相关查询,来获的一个大量数据的小的子集。较好的办法,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表,然后对这些表运行查询。
1)、当某一个SQL语句关联的表在2张及以上,并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
2)、程序执行过程中可能需要存放一些临时的数据,这些数据在整个程序的会话过程中都需要用的等等。
3)、临时表默认的是MyISAM,但是可以修改。
4)、可以把一些经常访问的数据放到临时表中,这样访问时会快一些,因为数据是在服务器内存中,另外每次查询的时候, 数据库 都需要生成一些临时数据在临时表里

三、临时表使用注意事项:
(1)临时表只在当前连接可见,当这个连接关闭的时候,会自动drop。这就意味着你可以在两个不同的连接里使用相同的临时表名,并且相互不会 冲突,或者使用 已经存在的表,但不是临时表的表名。(当这个临时表存在的时候,存在的表被隐藏了,如果临时表被drop,存在的表就可见了)。
(2) 临时表只能用在 memory,myisam,merge,或者innodb引擎。
3)临时表不支持mysql cluster(簇)。
4)在同一个query语句中,你只能查找一次临时表。例如:下面的就不可用
  mysql> SELECT * FROM temp_table, temp_table AS t2;
  ERROR 1137: Can't reopen table: 'temp_table'
  如果在一个存储函数里,你用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,这个错误都会发生。
5)show tables 语句不会列举临时表,但是会列出内存表。
6)你不能用rename来重命名一个临时表。但是,你可以alter table代替:
  mysql>ALTER TABLE orig_name RENAME new_name;

四、内存表
1、内存表:表结构建在磁盘里,数据在内存里 ,当停止服务后,表中的数据丢失,而表的结构不会丢失。内存表也可以被看作是临时表的一种。
2、内存表的建立:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,       
value INTEGER NOT NULL 
)  TYPE = HEAP  注意: TYPE = HEAP必须要有。
五、内存表的应用
内存表使用哈希散列索引把数据保存在内存中,因此具有极快的速度,适合缓存中小型数据库。
1、heap对所有用户的连接是可见的,这使得它非常适合做缓存。
2、一旦服务器重启,所有heap表数据丢失,但是heap表结构仍然存在,因为heap表结构是存放在实际数据库路径下的,不会自动删除。重启之后,heap将被清空,这时候对heap的查询结果都是空的。
3、如果heap是复制的某数据表(创建普通用户表这些也会丢失),则复制之后所有主键、索引、自增等格式将不复存在,需要重新添加主键和索引,如果需要的话。
4、对于重启造成的数据丢失,有以下的解决办法:
 a、在任何查询之前,执行一次简单的查询,判断heap表是否存在数据,如果不存在,则把数据重新写入,或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件,在需要用该heap表的页面随时调用,比较方便。
 b、对于需要该heap表的页面,在该页面第一次且仅在第一次查询该表时,对数据集结果进行判断,如果结果为空,则需要重新写入数据。这样可以节省一次查询。
 c、更好的办法是在mysql每次重新启动时自动写入数据到heap,但是需要配置服务器,过程比较复杂,通用性受到限制。
六、内存表的注意事项
1、heap不允许使用xxxTEXT和xxxBLOB数据类型;只允许使用=和<=>操作符来搜索记录(不允 许& amp; lt;、>、<=或>=);mysql4.1版本之前不支持auto_increment;只允许对非空数据列进行索引(not null)。
注:操作符 “<=>” 说明:NULL-safe equal.这个操作符和“=”操作符执行相同的比较操作,不过在两个操作码均为NULL时,其所得值为1而不为NULL,而当一个操作码为NULL时,其所得值为0而不为NULL。
2、内存表可以通过max_heap_table_size = 2048M来加大使用的内存。
3、内存表必须使用memory存储引擎

视图和临时表的区别
视图只是一条预编译的SQL语句,并不保存实际数据
临时表是保存在tempdb中的实际的表
物理空间的分配不一样,试图不分配空间,   临时表会分配空间
视图是一个快照,是一个虚表
临时表是客观存在的表类型对象Create TEMPORARY table 
它们的结构一个是表、一个快照。可以把视图像象成联合表的快捷方式
视图存在性能缺陷  视图不会用到索引?有性能问题?
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
关系型数据库 MySQL 数据库
数据迁移脚本优化过程:从 MySQL 到 Django 模型表
在大规模的数据迁移过程中,性能问题往往是开发者面临的主要挑战之一。本文将分析一个数据迁移脚本的优化过程,展示如何从 MySQL 数据库迁移数据到 Django 模型表,并探讨优化前后的性能差异。
|
21天前
|
消息中间件 存储 Kafka
实时计算 Flink版产品使用问题之 从Kafka读取数据,并与两个仅在任务启动时读取一次的维度表进行内连接(inner join)时,如果没有匹配到的数据会被直接丢弃还是会被存储在内存中
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7天前
|
缓存 DataWorks 关系型数据库
DataWorks产品使用合集之如何抽取MySQL视图数据
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
21 4
|
6天前
|
存储 数据可视化 关系型数据库
深入理解 MySQL 视图
深入理解 MySQL 视图
14 2
|
19天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
21天前
|
关系型数据库 MySQL
蓝易云 - 如何修复MySQL中损坏的表
最后,为了防止数据丢失,定期备份数据是非常重要的。
52 3
|
23天前
|
SQL 关系型数据库 MySQL
MySQL数据库练习——视图
MySQL数据库练习——视图
17 1
|
8天前
|
SQL 关系型数据库 MySQL
ClickHouse(23)ClickHouse集成Mysql表引擎详细解析
ClickHouse的MySQL引擎允许执行`SELECT`查询从远程MySQL服务器。使用`MySQL(&#39;host:port&#39;, &#39;database&#39;, &#39;table&#39;, &#39;user&#39;, &#39;password&#39;[,...])`格式连接,支持简单`WHERE`子句在MySQL端处理,复杂条件和`LIMIT`在ClickHouse端执行。不支持`NULL`值,用默认值替换。系列文章涵盖ClickHouse安装、集群搭建、表引擎解析等主题。[链接](https://zhangfeidezhu.com/?p=468)有更多
14 0
|
9天前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之在将RDS迁移到PolarDB后,原先由root用户创建的视图、存储过程等是否可以继续使用的
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
11天前
|
SQL 算法 安全
心得经验总结:深入解析MySQL视图VIEW
心得经验总结:深入解析MySQL视图VIEW
19 0