MySQL主流存储引擎概述

Bertha 。 2022-09-20 15:11 141阅读 0赞

MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事务安全表，其他存储引擎都是非事务安全表。   
  
MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。   
  
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。   
  
MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。其次它有空间大小的限制。  
  
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。  
![mysql_storage_engine_compare.png][]  
  
它们间的特点关系对比  
  
MyISAM  
Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。  
每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm(存储表定义)、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。     
  
要指定索引和数据文件的路径，需要在创建表时通过'data directory'和'index dircetory'语句来指定，即它们可以放置在不同的路径下，当然要给出其绝对路径且还要有相应的权限。  
  
造成myisam类型表损坏的原因有多种，且损坏后表就不能再被访问了，会提示错误的结束或要求修正或异常重启。解决的方法有：使用'check table'来检查myisam表，'repair table'可用来修复一个受损的myisam表。  
  
MyISAM表还支持3中不同的存储格式：   
1、静态表  
2、动态表  
3、压缩表  
  
静态表是默认的存储格式，静态表中的字段都是非变长的字段，优点是：存储非常迅速，容易缓存，出现故障容易恢复；缺点是：占用的空间通常比动态表多。(注意： 在存储时，列的宽度不足时，用空格补足，在返回给应用时并不会将这些空格也给应用；同理当字段后本来有空格时，数据库也会自动处理掉它，但字段前的空格将会保留)  
   
动态表的字段是变长的，优点是：占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期改善性能(optimize table或myisamchk -r命令)，并且出现故障的时候恢复相对比较困难。   
  
压缩表占用磁盘空间小，每个记录是被单独压缩的，所以只有非常小的访问开支且为只读。  
  
如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择。  
  
在myisam表中，自增列可以是组合索引的其它列，当插入记录后，自增列是按组合索引的前几列进行排序后递增的。  
  
InnoDB  
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。支持外键存储引擎只有InnoDB，在创建外键的时候，要求附表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。   
InnoDB存储方式为两种：  
1、使用共享表空间存储，这种方式创建的表的结构保存在'.frm'文件中，数据和索引保存在innodb\_data\_home\_dir和innodb\_data\_file\_path定义的表空间中，可以是多个文件。  
2、使用多表空间，这种方式创建的表结构仍然保存在'.frm'文件中，但数据和索引单独保存中'.ibd'文件中。如果是分区表，则每个分区对应单独的'.ibd'文件，文件名是“表名+分区名”，可以在创建分区时指定每个分区的数据文件位置，以此来将i/o分布在多个磁盘上。要使用多表空间方式，需要设置参数：innodb\_file\_per\_table，并重启服务生效，新建表将新的方式来创建，已有的表仍会使用共享表空间方式存储。如果将已有的多表空间方式改回为共享表方式，则新建表会在共享表空间中创建，但已经有的多表空间依然保持原有的访问方式。所以表空间参数生效后，只对新建的表有效。  
  
多表空间方式的数据文件没有大小限制，无需初始其大小，也不需要设置文件最大限制、扩展大小等参数。对于多表空间表，可以比较方便地进行单表备份和恢复，但直接复制'.ibd'文件是不行的，因为没有提供其数据字典信息，直接复制'.ibd'与'.frm'文件恢复时是不能被正确识别的，要通过下面指令来操作：  
alter table tbl\_x discard tablespace;  
alter table tbl\_x import tablespace;  
  
即可将备份恢复到数据库中，但这种的单表备份，只能恢复到原表所在的数据库中，而不能恢复到其它库中，当然这需要使用'mysqldump'或'mysqlimport'工具来实现。  
  
注意：即使在多表空间模式下，共享表空间是必须的，因此innodb将内部数据词典和未完成日志存放于此。  
  
当对auto\_increment列插入空、'0'、'null'时，它都将自动转换为合适且正确值。  
  
当使用'last\_insert\_id()'查询当前线程最后插入记录使用值，若一次插入了多条记录，则它返回的是第一条记录所使用的自增值。  
  
对于innodb表，自动增长列必须是索引，如果是组合索引，也必须是其的第一列，这个与myisam是有区别的。  
  
InnoDB被设计成适用于高并发读写的情况.使用MVCC(Multi-Version Concurrency Control)以及行级锁来提供遵从ACID的事务支持。InnoDB支持外键参照完整性，具备故障恢复能力。另外 InnoDB的性能其实还是不错的，特别是在处理大数据量的情况下,用官方的话说就是： InnoDB的CPU效率是其他基于磁盘的关系数据库存储引擎所不能比的。不过InnoDB的备份恢复要麻烦一点，除非你使用了4.1以后版本提供的Mulit-tablespace支持，因为InnoDB和MyISAM不同，他的数据文件并不是独立对应于每张表的。而是使用的共享表空间，简单的拷贝覆盖方法对他不适用，必须在停掉MYSQL后对进行数据恢复。使用Per-Table Tablespacesd，使其每张表对应一个独立的表空间文件，则情况要简单很多。  
  
在指定外鍵约束时，可以指定在删除、更新父表时，对子表进行相应操作，包括：restrict、cascade、set null、no action。其中restrict与no action相同，即限制在子表有关联记录的情况下父表不能更新；cascade表示父表在更新或删除时，更新或删除子表对应的记录；set null则表示父表在更新或删除时，更新或删除子表对应的字段被set null。所以选择后两种方式要谨慎，可能会因此错误的操作导致数据丢失。  
  
当某表被其它表创建了外键参照，那么该表的对应索引或主键禁止被删除。  
  
mysql> create table menu(id smallint not null auto\_increment primary key,alias char(16) not null,descri varchar(256))engine=InnoDB;  
mysql> insert into menu values('','news','about all news');  
mysql> insert into menu values('null','product','in open source product variety');  
  
mysql>create table article(id int not null auto\_increment,pid smallint not null,keywords varchar(256),intro tinytext,last\_update timestamp default 0 on update current\_timestamp,primary key(id),key idx\_art\_menu(pid),constraint foreign key (pid) references menu(id) on delete restrict on update cascade)engine=innodb;  
  
constraint 后最好跟一直观别名，来描述此外键约束。  
  
mysql> insert into article values('',1,'mysql key words','none of none','');  
Query OK, 1 row affected, 2 warnings (0.00 sec)  
  
mysql> show warnings;  
\+---------+------+------------------------------------------------------+  
| Level   | Code | Message                                              |  
\+---------+------+------------------------------------------------------+  
| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 1 |  
| Warning | 1265 | Data truncated for column 'last\_update' at row 1     |  
\+---------+------+------------------------------------------------------+  
2 rows in set (0.00 sec)  
  
mysql> select \* from article;  
\+----+-----+-----------------+--------------+---------------------+  
| id | pid | keywords        | intro        | last\_update         |  
\+----+-----+-----------------+--------------+---------------------+  
|  1 |   1 | mysql key words | none of none | 0000-00-00 00:00:00 |  
\+----+-----+-----------------+--------------+---------------------+  
  
mysql> insert into article values('null',2,'free oa office','intro text here',now());  
  
mysql> insert into article values('null',3,'mysql storeage engine ','may be lots of it',now());  
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (\`test\`.\`article\`, CONSTRAINT \`article\_ibfk\_1\` FOREIGN KEY (\`pid\`) REFERENCES \`menu\` (\`id\`) ON UPDATE CASCADE)  
  
mysql> delete from menu where id=2;  
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (\`test\`.\`article\`, CONSTRAINT \`article\_ibfk\_1\` FOREIGN KEY (\`pid\`) REFERENCES \`menu\` (\`id\`) ON UPDATE CASCADE)  
  
在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同理，在执行load data 和 alter table操作的时候，可以通过暂时关闭外键约束来加快处理速度，指令是："set foreign\_key\_checks=0;"，在处理完成后，通过"set foreign\_key\_checks=1;"来复原。  
  
MEMORY  
  
MEMORY类型的存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。对MEMORY存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。 此类表实际仅对应一个磁盘文件：.frm，默认使用hash索引。  
  
mysql>create table arts engine=memory select id,alias,title,hits from freeoadb.content;  
mysql> show table status like 'corps';  
  
每个memory有所能放置的数据量，受到max\_heap\_table\_size系统变量约束，初始值为16MB，可以按需加大；另外在其定义时，可以通过max\_rows来指定表的最大行数。服务器需要充足的内存来维持这些表，当不需要这些表及内容时，清空(delete from 或 truncate)它们可以释放内存。  
当数据量过大时，就会报出下面的问题，而导致建表失败：  
ERROR 1114 (HY000): The table 'arts' is full  
  
此种类型表有许多自身不足，从而限制了其用途。大小限制、字段类型限制、无持久性。查询速度快，可用其做一些中间结果暂存表之用。  
  
Merge

MERGE用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它。MERGE表的优点在于可以突破对单个MyISAM表大小的限制，通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。 对此类表的插入操作，是通过insert\_method子句定义的表，可以有3个不同值：first：作用在第一个表上；last：作用在最后一个表上；不定义或定义为'no'：不能对这个merge表进入插入操作。当对其进行drop时，只是删除了merge的定义，而对内部表内容没有影响。  
merge表在磁盘上有两个相关文件，一个是'.frm'文件存储表定义，另一个'.mrg'文件包含了组合表的信息，由哪些表组成、插入数据时的依据规则。可以直接通过编辑'.mrg'文件来修改其内容，后通过'flush tables'来刷新生效。  
  
示例  
mysql> create table art10 like freeoa.content;  
mysql> create table art11 like freeoa.content;  
mysql> create table art12 like freeoa.content;  
  
insert into art10(id,title,created,hits) select id,title,created,hits from freeoa.content where year(created)=2010 limit 5;  
  
art11与art12表采取类似的操作，插入相关年份的一些记录。  
  
创建主表，这里采用复制其它表的方法，快速而不容易出错。  
mysql> create table art like art10;  
mysql> alter table art engine=merge union(art10,art11,art12) insert\_method=last;  
  
mysql> show create table art;  
......  
ENGINE=MRG\_MyISAM DEFAULT CHARSET=utf8 INSERT\_METHOD=LAST UNION=(\`art10\`,\`art11\`,\`art12\`)  
  
且从art表全部查询出的记录数目为各个子表的记录数之和。  
  
当向art表中插入记录时，会插入到最后一张子表中：即art12表，即使这条记录是2011年的。这也正是merge表与分区表之间的区别，merge表并不能智能地将记录写到对应的表中，而分区是支持此类逻缉的，它们在使用上是透明的。需要注意的是，分区表不是存储引擎。  
  
如何选择合适的存储引擎  
选择标准: 根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。下面是常用存储引擎的适用环境：  
1、MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一  
2、InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持  
3、Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问  
4、Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

[mysql_storage_engine_compare.png]: /images/20220828/3ede737fb754403baede7a3f0ed09ca6.png