精华内容
下载资源
问答
  • mysql 内存表和临时表学习

    千次阅读 2018-02-27 22:15:37
    临时表建立在系统临时文件夹中的表。临时表的数据和表结构都存储在内存之中,退出的时候所占的空间会被释放 创建临时表 create temporary table tmp_table( name varchar(10) not null, value int not null ...

    前景

    • 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
    • 断开连接
      • 临时表 啥都不剩
      • 内存表 只剩下表结构
    • 性能
      • 内存表由于所有的内容都是放在内存中,所以相对来说,速度较快但是同时数据的维护较为困难
    展开全文
  • mysql建立临时表

    千次阅读 2013-07-30 15:34:28
    在生成报表的时候, 由于使用的查询条件中, 用的 in 函数比较多, 而且关联了好几表, 所以查询的... 终于, 想到用临时表了, 这一样来, 我不用改变原来条件中读出的数据, 我只需要把原来读出来的数据放在临时表里, 再在
    在生成报表的时候, 由于使用的查询条件中, 用的 in 函数比较多, 而且关联了好几个表, 所以查询的速度超慢, 甚至会死掉整个数据库. 我以为用 or 可以会快点, 不过, 还是一样, 因为我使用了 in 用在两个字符串上了, 没办法, 想了很久, 常试了很多, 还是不行... 终于, 想到用临时表了, 这一样来, 我不用改变原来条件中读出的数据, 我只需要把原来读出来的数据放在临时表里, 再在显临时表里加上那两个使用in的条件就可以了, 速度快了很多...
     哈~很开心, 现在把这个知识记下...
    

    1、临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表:
    A. 定义字段(注意:不同于mssql,在表名前加上#或@, 这里是加上 temporary.)

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


    B. 可以直接将查询结果导入临时表 (我觉得这种方法很有用, 特别对多表关联, 可以直接把查询出来的放在临时表,再读取)

    create temporary table tmp_table select * from table_name 
    

    2、另外mysql也允许你在内存中直接创建临时表,因为是在内存中所有速度会很快,语法如下:
    create temporary table tmp_table ( 
    
         idno int not null,
         name varchar(10) not null
    
      ) TYPE = HEAP 
    
    
    

    注意: 只有断开数据库连接才会被清空数据,在一个数据库连接里面发行多次sql的话系统是不会自动清空临时表数据的。


    展开全文
  • MySQL临时表与内存表

    千次阅读 2019-01-13 12:03:18
    MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在MySQL 3.23版本中添加。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。如果你使用了其他MySQL客户端程序连接MySQL数据库...

    在MySQL中有三种虚拟表:临时表、内存表、视图。下面简单介绍一下临时表和内存表的使用。

    1、临时表

    MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在MySQL 3.23版本中添加。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然你也可以手动销毁。

    1.1 创建临时表

    在MySQL中创建临时表使用CREATE TEMPORARY TABLE语句,其语法格式如下:

    CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名
    (
    	...
    );

    示例:创建临时表。

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_user
    (
    	id INT NOT NULL DEFAULT 0,
    	name VARCHAR(10) NOT NULL
    );

    还可以通过复制表的方式来创建临时表。

    示例:通过复制表的方式来创建临时表。

    CREATE TEMPORARY TABLE temp_user
    AS SELECT * FROM tb_user;

    1.2 删除临时表

    默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。当然你也可以在当前MySQL会话中手动删除临时表。删除临时表与删除普通表的语句是一样的,使用 DROP TABLE语句。

    示例:删除临时表。

    DROP TABLE IF EXISTS temp_user;

    1.3 使用临时表的注意事项

    (1)临时表只在当前连接可见,当这个连接关闭的时候,会自动drop。比如打开mysql 就是一个连接会话。两个不同的连接可以使用相同名字的临时表,两个表之间不存在什么关系,如果临时表的名字和已经存在的磁盘表名字一样,那么临时表会暂时覆盖磁盘表。就是说,你select 查询,只会显示临时表里面的,不会显示磁盘表。

    (2)临时表的存储引擎:memor,myisam,merge,innodb,临时表不支持mysql cluster簇。

    (3)同一个查询语句,只能用一次临时表,就是说不能将表和自己做连接等。

    (4)重命名表,不能用rename 可以用alter table代替。

    (5)如果超出了临时表的容量,临时表会转换成磁盘表。

    (6)show tables语句不会列出临时表,在information_schema中也不存在临时表信息,show create table可以查看临时表。

     

    2、内存表

    内存表的表结构建立在磁盘里面,数据放在内存里面;

    当MySQL断开当前连接后,临时表的表结构和表数据都没了,但内存表的表结构和表数据都存在;

    当MySQL服务重启之后,内存表的数据会丢失,但表结构依旧存。

    2.1 创建内存表

    创建内存表与创建普通表一样,使用CREATE TABLE语句,但需要将存储引擎设置为:ENGINE = MEMORY。其语法格式如下:

    CREATE TABLE [IF NOT EXISTS]  内存表名
    (
    	...
    ) ENGINE = MEMORY;

    示例:创建内存表。

    CREATE TABLE IF NOT EXISTS tmp_table (
    	id INT NOT NULL DEFAULT 0,
    	name VARCHAR(10) NOT NULL
    ) ENGINE = MEMORY; 

    2.2 删除内存表

    删除内存表与删除普通表的语句是一样的,使用 DROP TABLE语句。

    示例:删除内存表。

    DROP TABLE IF EXISTS tmp_table;

    2.3 使用内存表的注意事项

    (1)当MySQL服务重启之后,内存表的数据会丢失,表结构依旧存。

    (2)多个session,创建表的名字不能一样。

    (3)一个session创建会话后,对其他session也是可见的。

    (4)data目录下只有tmp_memory.frm,表结构放在磁盘上,数据放在内存中。

    (5)可以创建索引,删除索引,支持唯一索引。

    (6)不影响主备,主库上插入的数据,备库也可以查到。

    (7)show tables 语句可以查看得到表。

    (8)内存表不能包含BLOB或者TEXT列。

    (9)内存表支持AUTO_INCREMENT列。

     

    3、临时表与内存表区别

      临时表 内存表
    存储 表结构和数据都存储在内存中 表结构存储在磁盘中,表数据存储在内存中
    会话 单个会话独享的,是会话级别的 可以多个会话共享
    引擎 临时表默认,myisam 内存表默认,memory
    断开连接 表结构和表数据都没了 表结构和表数据都存在
    服务重启 表结构和表数据都没了 表结构存在,表数据不存在
    性能 由于表数据都是存放在内存中,所以相对来说,查询速度较快,但是数据的维护较为困难

     

    展开全文
  • MySQL临时表

    千次阅读 2016-11-23 15:53:41
    1.临时表存储MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表默认使用MySQL的MEMORY存储引擎, default_tmp_storage_engine 可以控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型,默认是MEMORY...

    MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。

    1.临时表

    MySQL临时表分为“内存临时表”和“磁盘临时表”,并且任何临时表都是没有索引的。

    其中内存临时表默认使用MySQL的MEMORY存储引擎,default_tmp_storage_engine 可以控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型,默认是MEMORY。

    磁盘临时表使用MySQL的MyISAM存储引擎,internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB。

    一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表从内存导出到磁盘临时表。

    从磁盘临时表与内存临时表的差异中大家可以看到,磁盘临时表只是内存临时的一个替代品。这就好像操作系的虚拟内存一样。当内存不够用时,可以在硬盘上的一个空间作为其替代品,将内存中的部分数据转移到虚拟内存中。这个磁盘临时表也是相同的道理。

    可以通过 SHOW VARIABLES LIKE ‘tmpdir’; 来查看磁盘临时表的储存位置。

    但是值得注意的是,硬盘的效率与内存的效率是不同的。在执行相同的一个作业时,内存的性能要高于硬盘的性能,一般会高上百倍,甚至上千倍。从这里就可以看出,为了提高数据库系统的性能,最好选择内存临时表,而放弃使用磁盘临时表。

    2.什么时候会用到临时表

    1、GROUP BY xx,并且xx不能利用索引排序时。
    2、在关联查询中,ORDER BY或者GROUP BY使用了不是第一个表(驱动表)的列,例如:SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name。此时,根据嵌套循环查询的特点可以知道,无论是否有索引,最后得到的结果中第二张表中的字段都是乱序的,因此MySQL只能先将关联的结果存放在临时表中,然后在所有的关联都结束后,再进行排序或分组。此时会在 EXPLAIN的结果中看到“Using temporary; Using filesort”。而如果所有的 ORDER BY 和 GROUP BY都在第一张表时,MySQL在关联处理第一张表时就能进行文件排序了,因此此时能够利用索引(如果有的话)省去文件排序和临时表了。
    3、ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
    4、SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL,结果会很小,请直接使用内存临时表,不需要使用索引排序。SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用。一般情况下,没有必要使用这个选项,让MySQL服务器选择即可。
    5、某些子查询会将子查询的结果放入临时表中,然后继续之后的查询。比如 WHERE 子句里的子查询。
    6、UNION。

    3.直接使用磁盘临时表的场景

    1、表包含TEXT或者BLOB列;
    2、GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列;
    3、使用UNION或者UNION ALL时,SELECT子句中包含大于512字节的列;

    4.临时表相关配置

    tmp_table_size:指定系统创建的内存临时表最大大小;
    max_heap_table_size: 指定用户创建的内存表的最大大小;

    注意:最终的系统创建的内存临时表大小是取上述两个配置值的最小值。当内存临时表大小超过配置项后,就会导出到磁盘。

    5.表的设计原则

    使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有:
    1、创建索引:在ORDER BY或者GROUP BY的列上创建索引,并保证能利用到索引;
    2、分拆很长的列:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。

    6.如何判断使用了临时表?

    使用explain查看执行计划,Extra列看到Using temporary就意味着使用了临时表。

    展开全文
  • DELIMITER // CREATE PROCEDURE baseweb.test16() BEGIN  DROP TABLE d;  CREATE TEMPORARY TABLE d(id INT,numbers VARCHAR(50));... INSERT INTO d(Id , numbers ) (SELECT userId , COUNT(*) AS tim
  • MySQL 临时表与内存表

    千次阅读 2017-05-19 15:06:59
    MySQL临时表与内存表总结 1.临时表:表建在内存里,数据在内存里。...创建一个临时表,但是到相应的数据目录下则找不到.frm文件的。 其中包括2个重要的参数  [mysqld]  # 内存表容量  max_heap_tab
  • MySQL 临时表、复制表

    千次阅读 2018-11-12 11:14:27
    MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间. 实例: 创建一个临时表 CREATE TEMPORARY TABLE SalesSummary ( product_name ...
  • mysql 创建大量磁盘临时表

    千次阅读 2018-04-28 14:22:30
    发现 mysql 中创建的临时表基本都是磁盘临时表,对此进行排查。最后为临时表机制说明。通过查看 mysql 状态变量,看到创建的临时表基本都是磁盘临时表。查看临时表配置,默认配置 16M,配置并没有问题。创建两表,...
  • mysql临时表简介

    千次阅读 2019-06-21 13:41:48
    MySQL临时表 首先,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。 创建临时表的语法与创建表语法类似,不同之处...
  • mysql临时表

    千次阅读 2019-04-07 20:52:44
    首先,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。 创建临时表的语法与创建表语法类似,不同之处是增加...
  • mysql创建临时表

    万次阅读 2017-08-21 18:12:05
    今天@小北童鞋遇到一个oracle的...我想到的做法就是把查询结果放到一个临时表,对临时表进行update后再次查询。 解决办法: 当临时表不存在时,将查询结果保存在临时表中:CREATE TEMPORARY TABLE tmp_table SELECT * F
  • MYSQL SELECT INTO临时表

    万次阅读 2016-09-22 21:00:09
    MySql不支持SQL SELECT INTO 这语法 SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename 可以用下面的方式替代: CREATE TEMPORARY TABLE tmp_table( SELECT id, name FROM
  • 1)MySQL临时表 2)dstat mysql 临时表监控插件开发
  • MySql中的临时表

    千次阅读 2015-11-06 10:24:12
    mysql创建临时表:  CREATE TEMPORARY TABLE temp1(myname VARCHAR(10) NOT NULL); mysql创建内存表:  CREATE TABLE `tablename` ( `columnName` varchar(256) NOT NUL) ENGINE=MEMORY DEFAULT CHARSET=utf8 ...
  • mysql 性能优化-临时表

    千次阅读 2018-10-24 10:27:20
    查询当前创建临时表的大小 ###临时表 1.show global status like ‘created_tmp%’; =&gt;Variable_name Value Created_tmp_disk_tables 67842 Created_tmp_files 1430 Created_tmp_tables 327575257 服务器对...
  • mysql 临时表和内存表创建 查询 删除以及注意事项临时表和内存表的ENGINE 不同,临时表默认的是MyISAM,而内存表是MEMORY ,临时表只对当前会话可见,连接断开时,自动删除! mysql教程 临时表和内存表创建 ...
  • MySQL临时表的功能

    千次阅读 2018-06-06 19:55:56
    摘要:MySQL临时表MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。临时表在MySQL3.23… MySQL 临时表在我们需要保存一些临时数据...
  • mysql 创建临时表

    千次阅读 2019-04-03 13:39:18
    创建: create table tmp_(SELECT * FROM shzy.social_qd_db_all_dxy a WHERE 1 = 1 and pz_date >= DATE_FORMAT('2019-03-25 10:01:01','%Y-%m-%d %H:%i:%s') and pz_date <...
  • Mysql 创建临时表

    千次阅读 2018-09-05 21:56:04
    Mysql 创建临时表 create temporary table temp select * from intimacy order by totalTime desc;
  • mysql 临时表和内存表

    千次阅读 2015-06-02 23:35:36
    内存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:...
  • 在SQL包含union、group by等操作时,执行计划可能会显示using temporary,表示用到临时表,此时这是内部临时表; 而memory引擎的表,由于数据存储在内存,实例关闭时不保存数据,也被称为临时表; 通过create ...
  • mysql 临时表使用

    千次阅读 2018-05-24 18:43:14
    语法 : create temporary table ...3.生命周期只在一个连接中,断开连接临时表会被释放 4.临时表分内存临时表和磁盘临时表,当临时表存储空间大于 ‘tmp_table_size’ 值时,会在磁盘上创建临时表,从而效率下降...
  • Mysql 内存表和临时表的区别

    千次阅读 2014-04-02 09:59:23
    内存表,就是放在内存中的表,所使用内存的大小可通过My.cnf中的max_heap_table_size指定,如max_heap_table_size=1024M,内存表与临时表并不相同,临时表也是存放在内存中,临时表最大所需内存需要通过tmp_table_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 126,189
精华内容 50,475
关键字:

mysql如何建立一个临时表

mysql 订阅