mysql的视图和临时表区别(MySQL 内存表和临时表的用法详解)
mysql的视图和临时表区别
MySQL 内存表和临时表的用法详解MySQL 内存表和临时表的用法
内存表:
|
session 1 $ mysql -uroot root@(none) 10:05:06>use test Database changed root@test 10:06:06> CREATE TABLE tmp_memory (i INT ) ENGINE = MEMORY; Query OK, 0 rows affected (0.00 sec) root@test 10:08:46> insert into tmp_memory values (1); Query OK, 1 row affected (0.00 sec) root@test 10:08:46> session2 $ mysql -uroot test root@test 10:05:12> CREATE TABLE tmp_memory (i INT ) ENGINE = MEMORY; ERROR 1050 (42S01): Table 'tmp_memory' already exists root@test 10:16:27> select * from tmp_memory; + ------+ | i | + ------+ | 1 | + ------+ 1 row in set (0.00 sec) |
1. 多个session,创建表的名字不能一样
2. 一个session创建会话后,对其他session也是可见的
3. data目录下只有tmp_memory.frm ,表结构放在磁盘上,数据放在内存中
4. mysql 重启或者关闭后内存表里的数据会丢失,但是表结构仍然存在
5. 可以创建索引,删除索引,支持唯一索引
6. 不影响主备,主库上插入的数据,备库也可以查到
7. show tables 看得到表
临时表:
|
session1 $ mysql -uroot test root@test 10:30:18> CREATE TEMPORARY TABLE tmp_table ( name VARCHAR (10) NOT NULL ,value INTEGER NOT NULL ); Query OK, 0 rows affected (0.05 sec) root@test 10:31:54> select * from tmp_table; + --------+-------+ | name | value | + --------+-------+ | aaaaaa | 10 | + --------+-------+ 1 row in set (0.00 sec) session2 root@test 10:20:13> CREATE TEMPORARY TABLE tmp_table ( name VARCHAR (10) NOT NULL ,value INTEGER NOT NULL ); Query OK, 0 rows affected (0.02 sec) root@test 10:30:39> insert into tmp_table values ( 'bbbbbbb' ,10); Query OK, 1 row affected (0.01 sec) root@test 10:31:33> select * from tmp_table; + ---------+-------+ | name | value | + ---------+-------+ | bbbbbbb | 10 | + ---------+-------+ 1 row in set (0.00 sec) root@test 10:31:43>exit Bye [1 Single:MS-Master db152011.sqa.cm6: mysql ~ ] $ mysql -uroot test root@test 10:32:17> select * from tmp_table; ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist root@test 10:32:22> root@test 10:32:23> |
1. 创建的表的名字可以一样
2. 表结构和数据都放在内存中
3. 会话消失表结构和数据都消失
4. 可以创建索引,删除索引
5. 主库创建的表,备库查不到,
6. show tables 看不到表
使用内存表需要注意的事项
1. 内存表需要自己delete数据或者drop表;需要drop权限,这点比较危险
2. 内存表的表结构是保存在磁盘上的,如果多个session使用同一个表名,会存在冲突;如果不需要使用表名,如果使用一次都需要创建表结构,到时候会有很多小文件存在,不利于db的维护,dba清理表也有风险;
基于以上不适合用内存表
1. 临时表是会话级别的,即使多个session创建的表名一样,都相互不影响
2. 会话消失,所有的都消失,这点很不利于应用排查问题
另外这两个都需要消耗额外的内存空间,虽然db端可以忍受,但是不太可控;DB端还有这个参数:
max_tmp_tables 一个客户能同时保持打开的临时表的最大数量,这个值默认32,可以根据需要调整此值
mysql创建临时表,将查询结果插入已有表中
今天遇到一个很棘手的问题,想临时存起来一部分数据,然后再读取。我记得学数据库理论课老师说可以创建临时表,不知道mysql有没有这样的功能呢?临时表在内存之中,读取速度应该比视图快一些。然后还需要将查询的结果存储到临时表中。
下面是创建临时表以及插入数据的例子,以供大家参考。
A、临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表:
1)定义字段
|
CREATE TEMPORARY TABLE tmp_table ( name VARCHAR (10) NOT NULL , time date NOT NULL ) |
更高级点就是:
|
create temporary TABLE `temtable` ( `jws` varchar (100) character set utf8 collate utf8_bin NOT NULL , `tzlb` varchar (100) character set utf8 collate utf8_bin NOT NULL , `uptime` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1″ |
连编码方式都规定了。。呵呵,以防乱码啊。
2)直接将查询结果导入临时表
|
CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name |
B、另外mysql也允许你在内存中直接创建临时表,因为是在内存中所有速度会很快,语法如下:
|
CREATE TEMPORARY TABLE tmp_table ( name VARCHAR (10) NOT NULL , value INTEGER NOT NULL ) TYPE = HEAP |
那如何将查询的结果存入已有的表呢?
1、可以使用A中第二个方法
2、使用insert into temtable (select a,b,c,d from tablea)”;
MySQL 数据库之内存临时表参数
一、内部内存临时表的最大大小
该变量不适用于用户创建的MEMORY表。临时表会在一些较复杂查询中创建。实际限制由tmp_table_size和max_heap_table_size中较小的值确定。
tmp-table-size参数Default 16777216
1.1、内存临时表和硬盘临时表
如果内存中的临时表超出限制,MySQL会自动将其转换为磁盘上的临时表。
从MySQL 5.7.5开始,internal_tmp_disk_storage_engine选项定义用于磁盘临时表的存储引擎。
在MySQL 5.7.5之前,使用MyISAM存储引擎。
创建的内存临时表的数量:Created_tmp_tables
创建的硬盘临时表的数量:Created_tmp_disk_tables
补充:Created_tmp_files
1.2、设置参考
在设置tmp-table-size时,请参考硬盘临时表和内存临时表的数值。
一般的比例关系是:Created_tmp_disk_tables/Created_tmp_tables<5%
如果您执行了许多高级的GROUP BY查询并且您拥有大量内存,请增加tmp_table_size的值(如有必要,请增大max_heap_table_size)。
二、MEMORY表最大SIZE
该变量设置用户创建的MEMORY表允许增长的最大大小。他的变量值是用来计算MEMORY表的MAX_ROWS值的。
max_heap_table_size参数default 16777216
2.1、使用方式
设置此变量对任何现有的MEMORY表都没有影响,除非使用CREATE TABLE等语句重新创建表或使用ALTER TABLE或TRUNCATE TABLE进行更改。
服务器重启也将现有MEMORY表的最大大小设置为全局max_heap_table_size值。
该变量还与tmp_table_size一起使用,以限制内部内存表的大小。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持开心学习网。如有错误或未考虑完全的地方,望不吝赐教。
原文链接:https://www.cnblogs.com/sunss/p/3191137.html
- mysql给字段加默认值(MySQL 如何处理隐式默认值)
- mysql 时间戳获取(MySQL时间盲注的五种延时方法实现)
- cent os7.0 安装mysql(mysql8.0.23 linuxcentos7安装完整超详细教程)
- mysql索引基本知识(MySql索引使用策略分析)
- mysql自增锁(深入剖析 MySQL 自增锁)
- mysql权限收回(MySQL如何利用DCL管理用户和控制权限)
- mysql8.0.23.0官方安装手册(MySQL8.0.23安装超详细教程)
- navicat15.0.28注册码(Navicat for MySQL 11注册码激活码汇总)
- mysql触发器语法规则(MySQL 触发器的使用及需要注意的地方)
- mysql一次查询的过程(一篇文章弄懂MySQL查询语句的执行过程)
- 所有聚合函数mysql教程(Mysql 聚合函数嵌套使用操作)
- 5.6以上版本mysql数据复制(MySQL5.7并行复制原理及实现)
- mysql支持存储表情(MySQL如何插入Emoji表情)
- mysql长连接释放和不释放的问题(解决MySQL存储时间出现不一致的问题)
- 最新版mysql 安装教程(新手如何安装Mysql亲测有效)
- mysql突然报错206无法连接(详解MySQL连接挂死的原因)
- 蓝天代表什么(蓝天代表什么生肖)
- 今天要吃什么(今天要吃什么菜)
- 营养餐是什么(学校营养餐是什么)
- 谁说女子不如男 范冰冰演的武则天只是其一,另外两位你认识吗(谁说女子不如男)
- 杯酒人生---瓦伦丁酒杯和奥丁格啤酒(杯酒人生---瓦伦丁酒杯和奥丁格啤酒)
- 中秋节买啤酒,预算超过7元试试这8种啤酒,麦香浓郁都是真啤酒(预算超过7元试试这8种啤酒)
热门推荐
- docker如何加volume(Dockerfile 中 VOLUME 与 docker -v 的区别说明)
- Sql Server中的Null值
- try中finally与return
- 阿里云ssl证书安装教程(阿里云申请的 CA Wosin 证书,在Windows iis下导入证书的步骤分享)
- django操作默认数据库(Django数据库类库MySQLdb使用详解)
- nginx对静态文件开启缓存(nginx proxy_cache 缓存配置详解)
- css3设置元素的边框图像(详解CSS3 用border写 空心三角箭头 两种写法)
- sql数据分页如何查询(SQL分页查询方式汇总)
- php日期和时间怎么设置(PHP实现时间日期友好显示实现代码)
- css控制表格换行
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9