1 MySQL存储引擎概述

插件式存储引擎是MySQL数据库最重要的特性之⼀，⽤户可以根据应⽤的需要选择如何存储和索引数据、是否使⽤事务等。MySQL默认⽀持多种存储引擎，以适⽤于不同领域的数据库应⽤需要，⽤户可以通过选择使⽤不同的存储引擎提⾼应⽤的效 率，提供灵活的存储，⽤户甚⾄可以按照⾃⼰的需要定制和使⽤⾃⼰的存储引擎，以 实现最⼤程度的可   定制性。

MySQL 5.0⽀持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、 MERGE、EXAMPLE、NDB      Cluster、ARCHIVE、CSV、BLACKHOLE。其中 InnoDB和BDB提供事务安全表，其他存储引擎都是⾮事务安全 表。

创建新表时如果不指定存储引擎，那么系统就会使⽤默认存储引擎，MySQL 5.5 之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB。如果要修改默认的存储引 擎，可以在参数⽂件中设置default-table-type

查看当前的默认存储引擎，可以使⽤以 下命令：第⼀种⽅法为： mysql> SHOW ENGINES \G

第二种方式：SHOW VARIABLES LIKE 'have%';

下⾯重点介绍⼏种常⽤的存储引擎，并对⽐各个存储引擎之间的区别

下⾯将重点介绍最常使⽤的4种存储引擎：MyISAM、InnoDB、MEMORY和MERGE。

1：MyISAM

MyISAM是MySQL默认的存储引擎。MyISAM不⽀持事务、也不⽀持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应⽤基 本上都可以使⽤这个引擎来创建表。

每个MyISAM在磁盘上存储成3个⽂件，其⽂件名都和表名相同，但扩展 名分别 是：

.frm（存储表定义）；

.MYD（MYData，存储数据）；

.MYI（MYIndex，存储索引）。

数据⽂件和索引⽂件可以放置在不同的⽬录，平均分布IO，数据⽂件和索引⽂件可以放置在不同的⽬录，平均分布IO，获得更快的速度。 要指定索引⽂件和数据⽂件的路径，需要在创建表的时候通过     DATA DIRECTORY 和INDEX DIRECTORY语句指定，也就是说不同MyISAM表的索引⽂件 和数据⽂件可以放置到不同的路径下。⽂件路径需要是绝对路径，并且具有访问权 限。

MyISAM类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能被 访问，会提⽰需要修复或者访问后返回错误的结果 MyISAM类型的表提供修复的⼯ 具，可以⽤CHECK TABLE语句来检查MyISAM表的健康，并⽤ REPAIR TABLE语句 修复⼀个损坏的MyISAM表。表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。

后续文章中会介绍如何修改。。。。

MyISAM的表还⽀持3种不同的存储格式，分别是：

静态（固定长度）表； 动态表；

压缩表。

其中，1.静态表是默认的存储格式。静态表中的字段都是⾮变长字段，这样每个记 录都是固定长度的，这种存储⽅式的优点是存储⾮常迅速，容易缓存，出现故障容易 恢复；缺点是占⽤的空间通常⽐动态表多。静态表的数据在存储时会按照列的宽度定 义补⾜空格，但是在应⽤访问的时候并不会得到这些空格，这些空格在返回给应⽤之 前已经去掉。

2.动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占⽤的空间相对较少，但是频繁地更新和删除记录会产⽣碎⽚，需要定期执⾏OPTIMIZE(optomize) TABLE语 句或myisamchk-r命令来改善性能，并且在出现故障时恢复相对⽐较困难。

3.压缩表由myisampack⼯具创建，占据⾮常⼩的磁盘空间。因为每个记录是被单独 压缩的，所以只有⾮常⼩的访问开⽀

2：InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能⼒的事务安全。但是对⽐MyISAM的存储引擎，InnoDB写的处理效率差⼀些，并且会占⽤更多的磁盘空间以保 留数据和索引

存储引擎为InnoDB的表在使⽤过程中不同于使⽤其他存储引擎的 表的特点：

1．⾃动增长列：

InnoDB 表的⾃动增长列可以⼿⼯插⼊，但是插⼊的值如果是空或者 0，则实际插⼊的将是⾃动增长后的值。下⾯定义新表autoincre_demo，其中列i使⽤⾃动增长列， 对该表插⼊记录，然后查看⾃动增长列的处理情况，可以发现插⼊0或者空时，实际插⼊的都将是⾃动增长后的值：

CREATE TABLE autoincre_demo (
    i SMALLINT NOT NULL auto_increment,
    NAME VARCHAR (10),
    PRIMARY KEY (i)
) ENGINE = INNODB

insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');

插入的id是 0 或者 null  则mysql自动为第二条和第三条记录自动计算id为2 和 3 ，所以出现下面查询结果。

下面 insert into autoincre_demo values(4,'4');  在插入这条记录。有id值，但是执行 select LAST_INSERT_ID();会出现下面图中结果。使⽤LAST_INSERT_ID()查询当前线程最后插⼊记录使⽤的值。

如果⼀次插⼊了多条记录，那么返回的是第⼀条记录使⽤的⾃动增长值。下⾯的例⼦演⽰了使⽤

 insert into autoincre_demo(name) values('5'),('6'),('7');

对于InnoDB表，⾃动增长列必须是索引。如果是组合索引，也必须是组合索引的 第⼀列，但是对于   MyISAM  表，⾃动增长列可以是组合索引的其他列，这样插⼊记录后，⾃动增长列是按照组合索引的前⾯⼏列进⾏排序后递增的。

例如，创建⼀个新的MyISAM     类型的表autoincre_demo，⾃动增长列d1作为组合索引的第⼆列，对该表插⼊⼀些记录后，可以发现⾃动增长列是按照组合索引的第⼀列d2进⾏排序后递增的：

2．外键约束

MySQL⽀持外键的存储引擎只有InnoDB，在创建外键的时候，要求⽗表必须有对 应的索引，⼦表在创建外键的时候也会⾃动创建对应的索引，也就是主表的主键id被字表参照，字表中会有主表主键id，

在创建索引时，可以指定在删除、更新⽗表时，对⼦表进⾏的相应操作，包括RESTRICT、CASCADE、SET NULL和NO ACTION。其中RESTRICT和NO ACTION

相同，是指限制在⼦表有关联记录的情况下⽗表不能更新；CASCADE表⽰⽗表在更 新或者删除时，更新或者删除⼦表对应记录；SET NULL 则表⽰⽗表在更新或者删除 的时候，⼦表的对应字段被 SET NULL。选择后两种⽅式的时候要谨慎，可能会因为 错误的操作导致数据的丢失。

3．存储⽅式

InnoDB存储表和索引有以下两种⽅式。

使⽤共享表空间存储，这种⽅式创建的表的表结构保存在.frm⽂件中，数据和索引保存在innodb_data_home_dir

和innodb_data_file_path定义的表空间中，可以是多个⽂件。 使⽤多表空间存储，这种⽅式创建的表的表结构仍然保存在.frm⽂件中，但是每个表的数据和索引单独保存

在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd ⽂件，⽂件名是“表名+分区名”，可以在创建分区的时候 指定每个分区的数据⽂件的位置，以此来将表的IO均匀分布在多个磁盘上。

要使⽤多表空间的存储⽅式，需要设置参数innodb_file_per_table，并且重新启动服务后才可以⽣效，对于新建的表按照多表空间的⽅式创建，已有的表仍然使⽤共享 表空间存储。如果将已有的多表空间⽅式修改回共享表空间的⽅式，则新建表会在共 享表空间中创建，但已有的多表空间的表仍然保存原来的访问⽅式。所以多表空间的 参数⽣效后，只对新建的表⽣效。

多表空间的数据⽂件没有⼤⼩限制，不需要设置初始⼤⼩，也不需要设置⽂件的 最⼤限制、扩展⼤⼩等参数。

对于使⽤多表空间特性的表，可以⽐较⽅便地进⾏单表备份和恢复操作，但是直 接复制.ibd⽂件是不⾏的，因为没有共享表空间的数据字典信息，直接复制的.ibd⽂件 和.frm⽂件恢复时是不能被正确识别的，但可以通过以下命令：ALTER TABLE tbl_name DISCARD TABLESPACE; ALTER TABLE tbl_name IMPORT TABLESPACE

将备份恢复到数据库中，但是这样的单表备份，只能恢复到表原来所在的数据库

中，⽽不能恢复到其他的数据库中。如果要将单表恢复到⽬标数据库，则需要通过

mysqldump和mysqlimport来实现。

注意：即便在多表空间的存储⽅式下，共享表空间仍然是必须的，InnoDB把内部数据词典和在线重做⽇志放 在这个⽂件中。

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

在选择存储引擎时，应根据应⽤特点选择合适的存储引擎。对于复杂的应⽤系统，还可以根据实际情况选择多种存储引擎进⾏组合。 下⾯是⼏种常⽤存储引擎的适⽤环境

MyISAM：默认的MySQL插件式存储引擎。如果应⽤是以读操作和插⼊操作为主，只有很少的更新和删除操 作，并且对事务的完整性、并发性要求不是很⾼，那么选择这个存储引擎是⾮常适合的。MyISAM是在Web、数据 仓储和其他应⽤环境下最常使⽤的存储引擎之⼀。

InnoDB：⽤于事务处理应⽤程序，⽀持外键。如果应⽤对事务的完整性有⽐较⾼的要求，在并发条件下要求 数据的⼀致性，数据操作除了插⼊和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是⽐较 合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求⽐较⾼的系统，InnoDB都 是合适的选择。

MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访 问。MEMORY 的缺陷是对表的⼤⼩有限制，太⼤的表⽆法缓存在内存中，其次是要确保表的数据可以恢复，数据 库异常终⽌后表中的数据是可以恢复的。MEMORY表通常⽤于更新不太频繁的⼩表，⽤以快速得到访问结果。

MERGE：⽤于将⼀系列等同的MyISAM表以逻辑⽅式组合在⼀起，并作为⼀个对象引⽤它们。MERGE表的优 点在于可以突破对单个MyISAM表⼤⼩的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE 表的访问效率。这对于诸如数据仓储等VLDB环境⼗分适合。