mysql 的 内存表和临时表

1 内存表的表机构还是在数据库中的，只是数据时放在内存中的。

2 内存表中不可以使用TEXT和BLOB类型的字段。

3 内存表可以通过max_heap_table_size = 2048M来加大使用的内存。

 

1 临时表的表机构和数据都是放在内存中的

 

具体见下：

内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库。
官方手册对内存表有以下的一些解释：
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.
1、heap对所有用户的连接是可见的，这使得它非常适合做缓存。
2、仅适合使用的场合。heap不允许使用xxxTEXT和xxxBLOB数据类型；只允许使用=和<=>操作符来搜索记录（不允许& amp; lt;、>、<=或>=）；mysql4.1版本之前不支持auto_increment；只允许对非空数据列进行索引（not null）。
注：操作符 “<=>” 说明：NULL-safe equal.这个操作符和“=”操作符执行相同的比较操作，不过在两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。
3、一旦服务器重启，所有heap表数据丢失，但是heap表结构仍然存在，因为heap表结构是存放在实际数据库路径下的，不会自动删除。重启之后，heap将被清空，这时候对heap的查询结果都是空的。
4、如果heap是复制的某数据表（创建普通用户表这些也会丢失），则复制之后所有主键、索引、自增等格式将不复存在，需要重新添加主键和索引，如果需要的话。
5、对于重启造成的数据丢失，有以下的解决办法：
　a、在任何查询之前，执行一次简单的查询，判断heap表是否存在数据，如果不存在，则把数据重新写入，或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件，在需要用该heap表的页面随时调用，比较方便。
　b、对于需要该heap表的页面，在该页面第一次且仅在第一次查询该表时，对数据集结果进行判断，如果结果为空，则需要重新写入数据。这样可以节省一次查询。
　c、更好的办法是在mysql每次重新启动时自动写入数据到heap，但是需要配置服务器，过程比较复杂，通用性受到限制。

6、一些预期可能用到的sql语句
//如果表存在，则删除
DROP TABLE IF EXISTS `abc`;
//复制整张表xyz为heap表abc（包含所有数据）
CREATE TABLE `abc` type=heap select * from `xyz`;
//添加主键id
ALTER TABLE `abc` ADD PRIMARY KEY (`id`);
//添加索引username
ALTER TABLE `abc` ADD INDEX `abc` (`username`);
7.建表实例
CREATE TABLE `DB` (
`id` int(11) default NULL,
`songname` varchar(255) NOT NULL default ”,
`singer` varchar(255) NOT NULL default ”,
KEY `songname` (`songname`,`singer`)
) TYPE=HEAP
建表时TABLE TYPE 选项也有
这个表结构就是建立了内存表。
注意：type=heap创建内存表的方式在5.2中将删除，支持的创建方式是最后加上engine=memory.
临时表和内存表的区别：
1）临时表的默认存储引擎是myisam，它的表结构和数据 都是存放在内存中的，当mysql断开连接后，就会将其都删除；
2）内存表的存储引擎是memory，它的表结构信息存放在磁盘中，数据 存放在内存中，当mysql断开连接后，数据会被清空，而表结构信息仍然存在。
另内存表存储引擎memory要比临时表存储引擎myisam快，具体还没有测试。
其它参考：
官方文档：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。
另外如何创建内存表？
    创建内存表非常的简单，只需注明 ENGINE= MEMORY 即可:
    CREATE TABLE `tablename` ( `columnName` varchar(256) NOT NUL) ENGINE=MEMORY DEFAULT CHARSET=utf-8 MAX_ROWS=100000000;

或 写成 TYPE=HEAP MAX_ROWS=100000000; 效果是一样的

注意：
    当内存表中的数据大于max_heap_table_size设定的容量大小时，mysql会转换超出的数据存储到磁盘上，因此这是性能就大打折扣了，所以我们还需要根据我们的实际情况调整max_heap_table_size，例如在.cnf文件中[mysqld]的下面加入：
        max_heap_table_size = 2048M
    另外在建表语句中还可以通过MAX_ROWS来控制表的记录数。

内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库，但是使用上受到一些限制。