mysql优化： 内存储器表和临时表

mysql优化： 内存表和临时表

由于直接使用临时表来创建中间表，其速度不如人意，因而就有了把临时表建成内存表的想法。但内存表和临时表的区别且并不熟悉，需要查找资料了。Java代码

1. mysql>?create?temporary?table?tmp1(id?int?not?null);??
2. Query?OK,?0?rows?affected?(0.00?sec)??

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

内存表Java代码

1. mysql>?create?table?tmp2(id?int?not?null)?TYPE＝HEAP;??
2. Query?OK,?0?rows?affected?(0.00?sec)??

1. mysql>?show?create?table?tmp2;??
2. +-------+------------------------------------------------------------------------------------+??
3. |?Table?|?Create?Table???????????????????????????????????????????????????????????????????????|??
4. +-------+------------------------------------------------------------------------------------+??
5. |?tmp2???|?CREATE?TABLE?`tmp2`?(??
6. ???`id`?int(11)?NOT?NULL??
7. )?ENGINE=MEMORY?DEFAULT?CHARSET=utf8?|??
8. +-------+------------------------------------------------------------------------------------+??
9. 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。