Mysql实例mysql优化之内存表与临时表

《Mysql实例mysql优化之内存表与临时表》要点：
本文介绍了Mysql实例mysql优化之内存表与临时表，希望对您有用。如果有疑问，可以联系我们。

MYSQL入门由于直接使用临时表来创建中间表,因而就有了把临时表建成内存表的想法.

MYSQL入门但内存表和临时表的区别且并不熟悉,需要查找资料了.
一开始以为临时表是创建后存在,当连接断开时临时表就会被删除,即临时表是存在于磁盘上的.而实际操作中发现临时表创建后去目录下查看发现并没有发现对应的临时表文件(未断开链接).因而猜测临时表的数据和结构都是存放在内存中,而不是在磁盘中.

MYSQL入门这样一想内存表不是也是存在在内存中吗,那么它与临时表有什么区别?速度如何?

MYSQL入门查看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.

MYSQL入门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入门由此可以看出来内存表会把表结构存放在磁盘上,把数据放在内存中.
并做了以下实验：
临时表
?

MYSQL入门mysql> create temporary table tmp1(id int not null);
query ok,0 rows affected (0.00 sec)

MYSQL入门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入门内存表
?

MYSQL入门mysql> create table tmp2(id int not null) type＝heap;
query ok,0 rows affected (0.00 sec)

MYSQL入门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)

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

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

MYSQL入门实验结果：
临时内存表： 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

MYSQL入门由此发现memory比myisam快大概20％.

MYSQL入门接着查找官方手册：
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.

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

MYSQL入门内存表的建立还有一些限制条件：
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.

MYSQL入门为了释放内存,应该执行：
delete from heap_table或drop table heap_table.

mysql 内存表基础知识
mysql 内存表与临时表有哪些区别
mysql创建内存表办法
有关MySQL内存表的特性及使用介绍
mysql 内存表在主从同步时的注意事项

《Mysql实例mysql优化之内存表与临时表》是否对您有启发，欢迎查看更多与《Mysql实例mysql优化之内存表与临时表》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!