MySQL中的临时表使用方法

转载 2012年02月21日 18:06:50
当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后多这些表运行查询。

  当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后多这些表运行查询。

  创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字:

  CREATE TEMPORARY TABLE tmp_table (

  name VARCHAR(10) NOT NULL,

  value INTEGER NOT NULL

  )

  临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

  DROP TABLE tmp_table

  如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在,临时表将有必要屏蔽(隐藏)非临时表tmp_table。

  如果你声明临时表是一个HEAP表,MySQL也允许你指定在内存中创建它:

  CREATE TEMPORARY TABLE tmp_table (

  name VARCHAR(10) NOT NULL,

  value INTEGER NOT NULL

  ) TYPE = HEAP

  因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。然而,HEAP表与一般的表有些不同,且有自身的限制。详见MySQL参考手册。

  正如前面的建议,你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引,临时表可能一点不快


文章主要讲述的是Mysql临时表的具体使用方案,并提醒测试Mysql临时表查看它们是否真的比对大量数据库的运行查询要快。如果相关的数据很好地索引,临时表可能一点不快。标签:Mysql

当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后多这些表运行查询。

创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字:

   1. CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL)  

临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

   1. DROP TABLE tmp_table

如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在,Mysql临时表将有必要屏蔽(隐藏)非临时表tmp_table。

如果你声明临时表是一个HEAP表,MySQL也允许你指定在内存中创建它:

   1. CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL) TYPE = HEAP  

因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。然而,HEAP表与一般的表有些不同,且有自身的限制。详见MySQL参考手册。

正如前面的建议,你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引,Mysql临时表可能一点不快。
Mysql:临时表、表变量

在mysql中没有表变量这一概念!


mysql有临时表:create temporary table if not exists {表定义}  --关键字”temporary“指示mysql创建会话级别的临时表。

    * 临时表只对当前会话可见,连接断开时,自动删除!
    * 你不必担心所创建的临时表的名称会和其他会话建立的临时表、或非临时表冲突!注意如果你的临时表和正常表名称相同,正常表会被隐藏——如同全局变量和局部变量那样
    * 创建临时表不会引发通常的commit事务提交



使用临时表的诸多限制

    * 引擎类型只能是:memory(heap)、myisam、merge、innodb
    * 不支持mysql cluster
    * 同一个查询语句中只能引用一次! 如 SELECT * FROM TP_TABLE , TP_TABLE AS ALIAS_NAME;  是错误的
    *
    * 同一个用户存储函数中只能引用一次!
    * show tables 不会显示临时表
    * 不能使用rename重命名临时表。只能使用ALTER TABLE OLD_TP_TABLE_NAME RENAME NEW_TP_TABLE_NAME;
    * 影响使用replication功能



mysql服务器自动使用的内部临时表

    * 在某些情况下,mysql服务器会自动创建内部临时表:该临时表可以是只存在于内存的memory临时表,或者是存储于硬盘的myisam临时表;而且 初始创建的memory临时表由于表的增大 可能会转变为myisam临时表——其转化临界点由max_heap_table_size 和tmp_table_size系统变量的 较小值决定的!注意:max_heap_table_size系统变量应用于所有的memory引擎的表,不管是用户临时表、正常表、或者内部临时表。
    * 内部临时表的创建条件:

由于直接使用临时表来创建中间表,其速度不如人意,因而就有了把临时表建成内存表的想法。但内存表和临时表的区别且并不熟悉,需要查找资料了。
一开始以为临时表是创建后存在,当连接断开时临时表就会被删除,即临时表是存在于磁盘上的。而实际操作中发现临时表创建后去目录下查看发现并没有发现对应的临时表文件(未断开链接).因而猜测临时表的数据和结构都是存放在内存中,而不是在磁盘中.
    这样一想内存表不是也是存在在内存中吗,那么他和临时表有什么区别?他们的速度是什么样子?

    查找了官方手册有以下的一些解释:
The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.

Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of .frm to indicate that it stores the table definition.

由此可以看出来内存表会把表结构存放在磁盘上 ,把数据放在内存中 。
并做了以下实验:
临时表
mysql> create temporary table tmp1(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp1;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+----------------------------------------------------------------------------------------------+
| tmp1   | CREATE TEMPORARY TABLE `tmp1` ( `id` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8    |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

内存表
mysql> create table tmp2(id int not null) TYPE=HEAP;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp2;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| tmp2   | CREATE TABLE `tmp2` (
   `id` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看出来临时表和内存表的ENGINE 不同,临时表默认的是MyISAM,而内存表是MEMORY .去数据库目录查看,发现tmp2.frm而没有tmp1表的任何文件。看来实际情况是符合官方解释的。


那么速度方面呢(即MyISAM和MEMORY之间的区别)?
实验开始:
实现手段:对基于2张千万级别的表做一些OLAP切分操作,中间表的建立使用2种不同的方式。最后把中间表的数据按照要求取出,插入到结果表中
实验目的;测试临时内存表和临时表的速度
1.中间表的建立使用Create temporary table type = heap 即 把中间表建立成临时内存表
2.中间表直接使用Create temporary table建立

实验结果:
临时内存表: 1小时
1 2008-09-25 11:03:48
1 2008-09-25 12:03:39
临时表:1小时17分钟
2 2008-09-25 12:25:28
2 2008-09-25 13:42:37

由此发现MEMORY比MyISAM快大概20%。


接着查找官方手册:
As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.


可以看出来MEMORY确实是very fast,and very useful for creating temporary tables .把临时表和内存表放在一起使用确实会快不少 :create table tmp2(id int not null) engine memory;

内存表的建立还有一些限制条件:
MEMORY tables cannot contain        BLOB or TEXT columns. HEAP不支持BLOB/TEXT列。   
The server needs sufficient memory to maintain all   MEMORY tables that are in use at the same time. 在同一时间需要足够的内存.
To free memory used by a MEMORY table when   you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP        TABLE.为了释放内存,你应该执行DELETE FROM heap_table或DROP TABLE heap_table。




MySQL中的两种临时表

http://mysql.taobao.org/monthly/2016/06/07/ 外部临时表 通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表...

MySQL 中的两种临时表

外部临时表 通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这种临时表的命名与非临时...

mysql临时表

工作中有时候需要查询一些特殊的数据,尤其一个大表里的个别字段的数值,可以讲这些字段放到临时表,查询会更快些。 在同一个session中,临时表在selece、insert、 1、创建临时表:和正...

mysql 临时表和内存表创建 查询 删除以及注意事项

mysql 临时表和内存表创建 查询 删除以及注意事项临时表和内存表的ENGINE 不同,临时表默认的是MyISAM,而内存表是MEMORY ,临时表只对当前会话可见,连接断开时,自动删除! m...

SQLSERVER与MYSQL临时表的使用

SQLSERVER与MYSQL语法上的差异

MySQL使用临时表的情况

原文地址:http://www.tuicool.com/articles/BBRFN3B MySQL在以下几种情况会创建临时表: 1、UNION查询; 2、用到TEMPTABLE算法或者是...
  • gmgmyxx
  • gmgmyxx
  • 2017年01月16日 11:13
  • 671

mysql临时表产生的执行效率问题改进

公司系统中的任务中一直加载很慢

mysql动态创建临时表

-- 创建临时表  set v_sql='create temporary table if not exists temp_visit_status_list( projectid varchar(...

mysql临时表和内存表的使用

当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后多这些表运行查询。...

mysql存储过程建立临时表,从别的表赋值给这个临时表

DELIMITER // CREATE PROCEDURE baseweb.test16() BEGIN     DROP TABLE d;     CREATE TEMPORARY TABL...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL中的临时表使用方法
举报原因:
原因补充:

(最多只允许输入30个字)