MySQL内存表使用技巧

首先，我们来说一下什么是内存表，所谓内存表，是指整个数据库表都常驻在内存中的表，相对于普通表而言，内存表存储数据在内存中，而普通表存储在硬盘中。那么内存表到底有什么特点呢？下面我们来详细地分析一下。

1.MySQL内存表要怎样创建呢？

 首先，我们先来学习一下到底要怎样创建一个内存表呢？方法很简单，就跟普通表差不多，唯一的差异是，内存表所使用的数据库引擎是内存。如下：

CREATE TABLE `test` (    `id` char(5) NOT NULL,    `username` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我们普通表的引擎一般是InnoDB，我们要使用内存表，就必须把引擎设置成MEMORY。内存表的结构存放在磁盘上，扩展名为.frm， 所以重启不会丢失。但是数据是存储在内存当中，所以重启之后，表数据会全部丢失。

2.MySQL内存表到底有什么限制？

  如果你平时设计数据库表的时候，有喜欢使用varchar类型的习惯的话，那么，你使用内存表的时候，就需要注意到一个细节了。我们都知道，在使用内存之前，都要先申请一段内存，那么也就是说，内存表在创建之后，每一行要使用的内存就已经固定下来了，但是我们都知道，varchar类型是动态可变长度，只有一个上限值，那么内存表会怎么做呢，先看下面一个例子：

以上面的表为例，假如有这么两条记录：

10000,'hellowrold!'  

10001,'hey!'

普通表占用空间 -> 10000,'hellowrold!'+10001,'hey!'

内存表占用空间 -> 10000,'hellowrold!    '+10001,'hey!          '

从上面的对比我们可以知道，内存表中如果使用了varchar类型，那么表创建的时候，会以varchar最大的长度来申请内存，这样，如果我们如果设计长度不合理的时候，就会造成内存浪费。并且内存表不支持BLOB或TEXT类型，这个不支持的原因也可以理解了。内存表不支持事务，因为内存表是表锁，所以当修改频繁时会影响表的性能。

再者，是表大小的问题，内存表到底能有多大？理论上说，只要你内存足够大，表就可以有多大，但是默认内存表默认的大小是64MB（如果我没有记错的话），如果我们要设置成自己想要的大小，我们需要在my.cnf文件中修改max_heap_table_size参数，修改完成后，要重启MySQL才会生效。如果我们的表满了以后，MySQL并不会把数据存储到硬盘中，而是直接报表已经满了的错误。

3.我们使用内存表的时候还需要注意些什么操作？

  我之前用100GB的内存做了测试，建了35张表，其中有一张表插入了大概30GB左右的数据，其他34张表平均不到1GB的数据，然后我尝试在30GB的表里面做了turncate操作，卡了一段时间之后，操作成功，但其他34张表也受到了影响，数据竟然全部不见了！于是我又继续插入上次的数据，再对34张表中的其中任意一张表做turncate操作，这次竟然没有影响，我猜测可能是内存占用过大的话，会有什么不可预料的事情发生吧，这一切发生在RHEL，具体什么原因也不太清楚了。

 后来我尝试使用delete、update语句可以正常使用，不过由于是表锁机制，所以我们在实际使用过程中，也要注意操作表的先后顺序，保证读或者写的时候，没有其他连接操作把表锁住了，不然你会发现你的操作会失败，并且MySQL不会告诉你表已经锁了，这个时候就只能靠自己的直觉去判断是不是表锁了。

来自：http://my.oschina.net/lanzp/blog/369179