mysql内存表heap使用总结

有一些应用程序需要存放一些临时数据，这时候临时表似乎是一个很好的选择，但是内存表在主从数据库上表现却不那么好。

       原因很简单，无论是基于STATEMENT还是基于ROW复制，都要在二进制日志中包含改变的数据。这就要求在主从机上数据必须一致。当重启从库的时候，你就会丢失内存表的数据，复制中断。

      我们该怎么办呢？

1.使用Innodb表代替

innodb表非常快，能满足我们对性能的需求。

2.在复制中忽略内存表

如果不是非常有必要的话，忽略复制内存表，使用这个选项replicate-ignore-table=db.memory_table。我们需要注意的是：STATEMENT复制，不要使用insert ... select 添加数据到内存表。如果要使用insert ... select，从机上的表将会是空的，甚至某些时候，内存表根本不会复制到从机上。还有一种解决办法是在主从机上都部署上相同的计划任务，来刷新这个表。

3.谨慎重启从机

从长远角度考虑我不会使用内存表，作为解决方案。

翻译自：

http://www.mysqlperformanceblog.com/2010/10/15/replication-of-memory-heap-tables/

此外还有关于如果真在工作中使用了内存表，并且重启了从机上的MySQL，还想和主机上的MySQL保持数据同步的话，这里有一个解决方案。

http://forge.mysql.com/tools/tool.php?id=163

mysql> CREATE TABLE test ENGINE=MEMORY
    ->     SELECT ip,SUM(downloads) AS down
    ->     FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

1、内存表不支持事务
2、内存表不支持BLOB/TEXT列
3、内存表大小可用　max_heap_table_size　参数来设置
4、内存表所使用的内存不回收，除非整个表删除。之前删除数据留下的内存，仅在同一表中被重新利用。
5、mysql启动时，能利用--init-file参数，从其它表取数据。INSERT INTO ... SELECT or LOAD DATA INFILE
6、作mysql采用主从同步机制的时候，当master shutdown或restart时，slave中内存表中数据并不清空，只有当master重新启动后，第一次使用该内存表时，master向binlog中写入一条delete命令，slave才清空该表。

内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库。

官方手册对内存表有以下的一些解释：
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来控制表的记录数。

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