您的位置:首页 > 数据库 > > 正文

mysql的视图和临时表区别(MySQL 内存表和临时表的用法详解)

更多 时间:2022-01-17 01:23:46 类别:数据库 浏览量:883

mysql的视图和临时表区别

MySQL 内存表和临时表的用法详解

MySQL 内存表和临时表的用法

内存表:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 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 看得到表

    临时表:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 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)定义字段

  • ?
  • 1
  • 2
  • 3
  • 4
  • CREATE TEMPORARY TABLE tmp_table (
  •  name VARCHAR(10) NOT NULL,
  •  time date NOT NULL
  •  )
  • 更高级点就是:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 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)直接将查询结果导入临时表

  • ?
  • 1
  • CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name
  • B、另外mysql也允许你在内存中直接创建临时表,因为是在内存中所有速度会很快,语法如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 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

    您可能感兴趣