7 表类型(存储引擎)的选择

Mysql支持许多的存储引擎，但是只要Innode和BDB支持事务类型。

 

查看当前默认的存储引擎：show variables like 'table_type';

可以通过show engines \G 或show variables like 'have%' 查看支持的引擎

修改表的引擎：alter table tablename engine = type;

下面介绍常用的存储引擎：

 

MyISAM：

优势速度快，一般用于没有事务要求，以select 和 insert为主的应用。

表在磁盘上存储分成3个文件：.frm(表定义)，.MYD(MyData,存储数据)，.MYI(MyIndex,存储索引)。数据文件和索引文件可以放置在不同的目录，达到平均分布IO，获得更快的速度，文件路径需要在建表的时候通过data directory和index directory语句指定,文件路径需要是绝对路径。

支持的3种不同的存储格式：

1.静态（固定长度）表：速度快，易缓存，易修复，但占用空间大

2.动态表：占用空间少，但会产生碎片需要定期执行optimize table改善

3.压缩表：由myisampack工具创建，记录被单独压缩了

 

InnoDB：

具有提交，回滚和崩溃恢复能力的事务安全。但效率比MyISAM写的处理效率差一些并且会占用更多的磁盘空间以保存数据和索引。

对于InnoDB表，自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列。

特性-------外键约束

Mysql支持外键的存储引擎只有InnoDB,在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

具体详细资料已做整理，人人网，博客hi.baidu.com/xiaozhe/home都有资料。

特性-------存储方式

InnoDB存储表和索引有以下两种方式：

1. 使用共享表空间存储，这种方式创建的表结构保存在.frm文件中，数据和索引在innodb_data_home_dir和innode_data_file_path定义的表空间中，可以是多个文件。

2. 使用多表空间存储，这种方式创建的表结构仍在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是分区表，则每个分区对应单独的.ibd文件，文件名是"表名+分区名"，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。

要开启多表空间的存储方式，需要设置参数innode_file_per_table,并重启服务器后才生效。多表空间的数据文件没有大小限制，不需要设置初始大小，文件限制和扩展大小。

 

 

MEMORY：

使用存在内存中的内容来创建表。每个memory表实际对应一个磁盘文件，格式是.frm。Memory访问速度非常快，因为数据都保存在内存中，默认使用HASH索引，但一旦服务器关闭，表中数据就会丢失。

创建方式：假如我有一个products表里面有10条数据，则可以将这10条数据存入一个memory表中。

Mysql->create table product_memory engine = memory

->select * from products;

这样就将products表中的数据都插入到了新表product_memory中。

你可以通过show create table product_memory \G;查看结构或show table status like "product_memory";查看一些属性。

加索引：

可以为memory表建立HASH索引或BTREE索引，如create index index_name using HASH on product_memory(name);

释放memory占用的缓存：Delete from 或 truncate table 或者删除整个表drop table。

Memory表中存放数据的大小受到系统变量max_heap_table_size的约束，初始16M。

注：Memory表主要用于那些内容不频繁变化的或者作为统计操作的中间结果表。因为Memory数据存在内存没有写入磁盘文件，所以要对下一次重新启动服务器时如何获取数据考虑。

 

 

MERGE：

是一组MyISAM表的组合，这些MyISAM表必须结构相同。MERGE表本身没有数据，对MERGE类型操作其实是对内部的MyISAM表操作。对MERGE类型表插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同值first，last，no，分别代表插入操作作用在第一个表或最后一个表或不能插入。

如果对MERGE表进行DROP删除，则只删除MERGE定义，对内部表没有任何影响。

它在磁盘上保留2个文件，一个是.frm文件存储表定义，一个是.MRG文件包含组合表的信息，可以通过修改.MRG文件来修改MERGE表，修改后通过flush tables 刷新。

创建语法：假设3个表xiaozhe1,xiaozhe2为MyiSAM并且结构一样，而xiaozhe为MEMRG。

Mysql->create table xiaozhe(

 ->id int,

->name varchar(50),

->index(id)

->)engine=merge union=(xiaozhe1,xiaozhe2) INSERT_METHOD=LAST;

注：xiaozhe表的结构必须与xiaozhe1,xiaozhe2一样(索引不是结构，可以不同)，因为INSERT_METHOD=LAST，所以向xiaozhe插入数据实际会插入到xiaozhe2，如果select * from xiaozhe；则实际是查出xiaozhe1,xiaozhe2合并后的结果。

 

如何选择合适的存储引擎：

MyISAM:默认的存储引擎，如果主要以插入和读操作为主，并且对事务的完整性，并发性要求不高。经常用于WEB，数据仓储和其他应用环境下。

InnoDB:用于事务处理应用程序，支持外键。对事务的完整性要求比较高，在并发条件下要求数据一致，有大量的更新和删除操作，则适合使用。能有效降低删除和更新导致的锁定，还可以确保事务的完整性commit和rollback。对于财务或收费类系统适合。

MEMORY：将数据存在RAM中，在需要快速定位和其他类似数据的环境下，可提供极快的访问，能快速定位记录通常用于不大更新频繁的小表。

MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们，MERGE表的优点在与可以突破单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于诸如数据仓促等VLDB环境十分适合。