mysql - 内存表使用总结

部分转自：  

1、http://my.oschina.net/lanzp/blog/369179

内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库，但是使用上受到一些限制
定义：
create table (...)  engine = memory/heap ;  (一般使用memory，heap用得少了)
用法： 
①、内存表的数据是使用hash的方式存储在内存上，故其查询的时候只支持 <> 和 = 这两种比较方式；
②、内存表的数据可以对所有用户的连接可见，但是其不能跨服务器重启； 服务器重启后，内存表中的所有数据都将丢失，但是表结构还在，因为表结构是存储在磁盘中；
③、内存表的数据在max_heap_table_size这里设定，若单张表行数超过了这个长度，则报数据满错误 ; 我电脑里的mysql本参数默认值是16777216 ；
④、内存表不支持事务，而且是表锁，当修改频繁时，性能会下降
⑤、内存表使用一个固定的记录长度格式，一般来说不要用varchar类型，如果使用了它会以varchar的最大长度来申请内存。内存表不支持BLOB或TEXT类型
⑥、内存表不支持auto_increment <我的新版的mysql测试时支持 自增列的>；只允许对非空数据列进行索引（not null）
⑦、如果heap是复制的某数据表，则复制之后所有主键、索引、自增等格式将不复存在，需要重新添加主键和索引，如果需要的话。

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

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

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

?

1

2

3

4

5

CREATETABLE`test` (

  `id`char(5)NOTNULL,

  `username`varchar(15)COLLATEutf8_unicode_ciDEFAULTNULL,

  PRIMARYKEY(`id`)

) ENGINE=MEMORY DEFAULTCHARSET=utf8COLLATE=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不会告诉你表已经锁了，这个时候就只能靠自己的直觉去判断是不是表锁了。

好吧，暂时就这么多了，如果有什么不对的地方欢迎指正、探讨。周一要吼一吼，才有气势，至少可以把红薯镇住，嗷~~~~~