mysql优化： 内存表和临时表

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

    查找了官方手册有以下的一些解释：
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如何使用临时表，内存表来加快速度？

黄新颖

1.我结合武侠这个游戏来说一说我的使用心得:
武侠中所有玩家当前所在场景信息存在内存表中，比如A场景有a,b,c玩家， B场景中有e,f,g玩家，都存在一个叫做tbl_player_scene的内存表中。当时并不知道redis, 后来想想其实可以用redis的set来替代。
2.内存表的特点:
a. 支持索引。（这个是对比起redis等nosql的杀手锏）
b. 支持自增长字段。
c. 查询缓存
etc
从内存表的特性来看，他的适合场景有:
a. 在开发初期，内存表在和nosql数据库对比的时候，看查询的复杂程度，数据的复杂程度来进行权衡。
b. 在后期维护中，可以在非常小的改变原先的代码结构，比如dao层的代码，移植成内存表类型。

linux_ubuntu

临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。
重起数据库以后，内存中的数据全部丢失。
1.临时表:表建在内存里，数据在内存里
2.内存表:表建在磁盘里，数据在内存里
其中包括2个重要的参数
[mysqld]
内存表容量
max_heap_table_size=1024M
临时表容量
tmp_table_size=1024M

临时表主要是为了放一些中间大结果集的一些子集，内存表可以放一些经常频繁使用的数据。

自由者：

以下是对内存表和临时表之间区别的总结：

内存表：
1. 通过参数控制：max_heap_table_size大小来设定内存表大小
2. 到达max_heap_table_size所设置的内存上限后报错。
3. 表定义保存在磁盘上，数据和索引保存在内存里面。
4. 不能包含TEXT、BLOB等字段。
临时表：
1. 通过参数控制：tmp_table_size大小来设定临时表大小。
2. 到达tmp_table_size上限后会在磁盘上创建临时文件。
3. 表定义和数据都在内存里。
4. 可以包含TEXT, BLOB等字段。

临时表一般都很少用，而且要用也一般是在程序中动态创建或者由MySQL内部根据SQL执行计划需要自己创建。

内存表则大多数是当Cache用，在早期一台机器没有装Memcache等第三方cache时，h内存表无疑是cache的最好的选择了。而如今随着memcache、NoSQL的流行，内存表就越来越少人使用了