MySQL性能调优与架构设计——第3章 MySQL存储引擎简介
第3章 MySQL存储引擎简介
3．1 MySQL 存储引擎概述
MyISAM存储引擎是MySQL默认的存储引擎，也是目前MySQL使用最为广泛的存储引擎之一。他的前身就是我们在MySQL发展历程中所提到的 ISAM，是 ISAM的升级版本。在MySQL 最开始发行的时候是ISAM存储引擎，而且实际上在最初的时候，MySQL甚至是没有存储引擎这个概念的。MySQL在架构上面也没有像现在这样的sql layer和storage engine layer 这两个结构清晰的层次结构，当时不管是代码本身还是系统架构，对于开发者来说都很痛苦的一件事情。到后来，MySQL意识到需要更改架构，将前 端的业务逻辑和后端数据存储以清晰的层次结构拆分开的同时，对ISAM做了功能上面的扩展和代码的重构，这就是MyISAM 存储引擎的由来。
MySQL在5.1（不包括）之前的版本中，存储引擎是需要在MySQL安装的时候就必须和 MySQL一起被编译并同时被安装的。也就是说，5.1之前的 版本中，虽然存储引擎层和sql 层的耦合已经非常少了，基本上完全是通过接口来实现交互，但是这两层之间仍然是没办法分离的，即使在安装的时候也是一样。
但是从MySQL5.1开始，MySQL AB对其结构体系做了较大的改造，并引入了一个新的概念：插件式存储引擎体系结构。MySQL AB 在架构改造的时候，让存储引擎层和sql层各自更为独立，耦合更小，甚至可以做到在线加载信的存储引擎，也就是完全可以将一个新的存储引擎加载到一个正在 运行的MySQL中，而不影响MySQL的正常运行。插件式存储引擎的架构，为存储引擎的加载和移出更为灵活方便，也使自行开发存储引擎更为方便简单。在 这一点上面，目前还没有哪个数据库管理系统能够做到。
MySQL的插件式存储引擎主要包括MyISAM，Innodb，NDB Cluster，Maria，Falcon， Memory，Archive，Merge，Federated等，其中最著名而且使用最为广泛的 MyISAM和Innodb两种存储引擎。MyISAM是MySQL最早的ISAM存储引擎的升级版本，也是MySQL默认的存储引擎。而Innodb实 际上并不是MySQ公司的，而是第三方软件公司Innobase（在2005年被Oracle公司所收购）所开发，其最大的特点是提供了事务控制等特性， 所以使用者也非常广泛。
其他的一些存储引擎相对来说使用场景要稍微少一些，都是应用于某些特定的场景，如NDB Cluster虽然也支持事务，但是主要是用于分布式环境，属于一个share nothing的分布式数据库存储引擎。Maria是MySQL最新开发（还没有发布最终的GA版本）的对MyISAM 的升级版存储引擎，Falcon是 MySQL公司自行研发的为了替代当前的Innodb存储引擎的一款带有事务等高级特性的数据库存储引擎，目前正在研发阶段。Memory存储引擎所有数 据和索引均存储于内存中，所以主要是用于一些临时表，或者对性能要求极高，但是允许在主机Crash的时候丢失数据的特定场景下。Archive是一 个数据经过高比例压缩存放的存储引擎，主要用于存放过期而且很少访问的历史信息，不支持索引。Merge和Federated在严格意义上来说，并不能算 作一个存储引擎。因为Merge存储引擎主要用于将几个基表 merge到一起，对外作为一个表来提供服务，基表可以基于其他的几个存储引擎。而Federated实际上所做的事情，有点类似于Oracle的dblink，主要用于远程存取其他MySQL服务器上面的数据。
3．2 MyISAM 存储引擎简介
MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个文件做为MyISAM存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI文件中。
MyISAM支持以下三种类型的索引：
1、B-Tree索引
B-Tree索引，顾名思义，就是所有的索引节点都按照balance tree的数据结构来存储，所有的索引数据节点都在叶节点。
2、R-Tree索引
R-Tree索引的存储方式和b-tree索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的MySQL版本来说，也仅支持geometry类型的字段作索引。
3、Full-text索引
Full-text索引就是我们长说的全文索引，他的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。
MyISAM上面三种索引类型中，最经常使用的就是B-Tree索引了，偶尔会使用到Full-text，但是R-Tree索引一般系统中都是很少用到的。另外MyISAM的B-Tree索引有一个较大的限制，那就是参与一个索引的所有字段的长度之和不能超过1000字节。
虽然每一个MyISAM的表都是存放在一个相同后缀名的.MYD文件中，但是每个文件的存放格式实际上可能并不是完全一样的，因为MyISAM的数据存放格式是分为静态（FIXED）固定长度、动态（DYNAMIC）可变长度以及压缩（COMPRESSED）这三种格式。当然三种格式中是否压缩是完全可以任由我们自己选择的，可以在创建表的时候通过ROW_FORMAT来指定{COMPRESSED | DEFAULT}，也可以通过myisampack工具来进行压缩，默认是不压缩的。而在非压缩的情况下，是静态还是动态，就和我们表中个字段的定义相关了。只要表中有可变长度类型的字段存在，那么该表就肯定是YNAMIC格式的，如果没有任何可变长度的字段，则为FIXED格式，当然，你也可以通过alter table命令，强行将一个带有VARCHAR类型字段的DYNAMIC的表转换为FIXED，但是所带来的结果是原VARCHAR字段类型会被自动转换成CHAR类型。相反如果将FIXED转换为DYNAMIC，也会将CHAR类型字段转换为VARCHAR类型， 所以大家手工强行转换的操作一定要谨慎。
MyISAM存储引擎的表是否足够可靠呢？在MySQL用户参考手册中列出在遇到如下情况的时候可能会出现表文件损坏：
1、当mysqld正在做写操作的时候被kill掉或者其他情况造成异常终止；
2、主机Crash；
3、磁盘硬件故障；
4、MyISAM存储引擎中的bug？
MyISAM存储引擎的某个表文件出错之后，仅影响到该表，而不会影响到其他表，更不会影响到其他的数据库。如果我们的出据苦正在运行过程中发现某个MyISAM表出现问题了，则可以在线通过check table 命令来尝试校验他，并可以通过repair table命令来尝试修复。在数据库关闭状态下，我们也可以通过myisamchk工具来对数据库中某个（或某些）表进行检测或者修复。不过强烈建议不到万不得已不要轻易对表进行修复操作，修复之前尽量做好可能的备份工作，以免带来不必要的后果。
另外MyISAM存储引擎的表理论上是可以被多个数据库实例同时使用同时操作的，但是不论是我们都不建议这样做，而且MySQL官方的用户手册中也有提到，建议尽量不要在多个mysqld之间共享MyISAM存储文件。
3．3 Innodb 存储引擎简介
在MySQL中使用最为广泛的除了MyISAM之外，就非Innodb莫属了。Innodb做为第三方公司所开发的存储引擎，和MySQL遵守相同的开源License协议。
Innodb之所以能如此受宠，主要是在于其功能方面的较多特点：
1、支持事务安装
Innodb在功能方面最重要的一点就是对事务安全的支持，这无疑是让Innodb成为MySQL最为流行的存储引擎之一的一个非常重要原因。而且实现了SQL92标准所定义的所有四个级别（READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ和SERIALIZABLE）。对事务安全的支持，无疑让很多之前因为特殊业务要求而不得不放弃使用MySQL的用户转向支持MySQL，以及之前对数据库选型持观望态度的用户，也大大增加了对MySQL好感。
2、数据多版本读取
Innodb在事务支持的同时，为了保证数据的一致性已经并发时候的性能，通过对undo信息，实现了数据的多版本读取。
3、锁定机制的改进
Innodb改变了MyISAM的锁机制，实现了行锁。虽然Innodb的行锁机制的实现是通过索引来完成的，但毕竟在数据库中99%的SQL语句都是要使用索引来做检索数据的。所以，行锁定机制也无疑为Innodb在承受高并发压力的环境下增强了不小的竞争力。
4、实现外键
Innodb实现了外键引用这一数据库的重要特性，使在数据库端控制部分数据的完整性成为可能。虽然很多数据库系统调优专家都建议不要这样做，但是对于不少用户来说在数据库端加如外键控制可能仍然是成本最低的选择。
除了以上几个功能上面的亮点之外，Innodb还有很多其他一些功能特色常常带给使用者不小的惊喜，同时也为MySQL带来了更多的客户。
在物理存储方卖弄，Innodb存储引擎也和MyISAM不太一样，虽然也有.frm文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。至于是每个表单独存放还是所有表存放在一起，完全由用户来决定（通过特定配置），同时还支持符号链接。
Innodb的物理结构分为两大部分：
1、数据文件（表数据和索引数据）
存放数据表中的数据和所有的索引数据，包括主键和其他普通索引。在Innodb中，存在了表空间（tablespace）这样一个概念，但是他和Oracle的表空间又有较大的不同。首先，Innodb的表空间分为两种形式。一种是共享表空间，也就是所有表和索引数据被存放在同一个表空间（一个或多个数据文件）中，通过innodb_data_file_path来指定，增加数据文件需要停机重启。 另外一种是独享表空间，也就是每个表的数据和索引被存放在一个单独的.ibd文件中。
虽然我们可以自行设定使用共享表空间还是独享表空间来存放我们的表，但是共享表空间都是必须存在的，因为Innodb的undo信息和其他一些元数据信息都是存放在共享表空间里面的。共享表空间的数据文件是可以设置为固定大小和可自动扩展大小两种形式的，自动扩展形式的文件可以设置文件的最大大小和每次扩展量。在创建自动扩展的数据文件的时候，建议大家最好加上最大尺寸的属性，一个原因是文件系统本身是有一定大小限制的（但是Innodb并不知道），还有一个原因就是自身维护的方便。另外，Innodb不仅可以使用文件系统，还可以使用原始块设备，也就是我们常说的裸设备。
当我们的文件表空间快要用完的时候，我们必须要为其增加数据文件，当然，只有共享表空间有此操作。共享表空间增加数据文件的操作比较简单，只需要在innodb_data_file_path参数后面按照标准格式设置好文件路径和相关属性即可，不过这里有一点需要注意的，就是Innodb在创建新数据文件的时候是不会创建目录的，如果指定目录不存在，则会报错并无法启动。另外一个较为令人头疼的就是Innodb在给共享表空间增加数据文件之后，必须要重启数据库系统才能生效，如果是使用裸设备，还需要有两次重启。这也是我一直不太喜欢使用共享表空间而选用独享表空间的原因之一。
2、日志文件
Innodb的日志文件和Oracle的redo日志比较类似，同样可以设置多个日志组（最少2个），同样采用轮循策略来顺序的写入，甚至在老版本中还有和Oracle一样的日志归档特性。如果你的数据库中有创建了Innodb的表，那么千万别全部删除innodb的日志文件，因为很可能就会让你的数据库crash，无法启动，或者是丢失数据。
由于Innodb是事务安全的存储引擎，所以系统Crash对他来说并不能造成非常严重的损失，由于有redo日志的存在，有checkpoint机制的保护，Innodb完全可以通过redo日志将数据库Crash时刻已经完成但还没有来得及将数据写入磁盘的事务恢复，也能够将所有部分完成并已经写入磁盘的未完成事务回滚并将数据还原。
Innodb不仅在功能特性方面和MyISAM存储引擎有较大区别，在配置上面也是单独处理的。在MySQL启动参数文件设置中，Innodb的所有参数基本上都带有前缀“innodb_”，不论是innodb数据和日志相关，还是其他一些性能，事务等等相关的参数都是一样。和所有Innodb相关的系统变量一样，所有的Innodb相关的系统状态值也同样全部以“Innodb_”前缀。当然，我们也完全可以仅仅通过一个参数（skip-innodb）来屏蔽MySQL中的Innodb存储引擎，这样即使我们在安装编译的时候将Innodb存储引擎安装进去了，使用者也无法创建Innodb的表。
3．4 NDB Cluster 存储引擎简介
NDB存储引擎也叫NDB Cluster存储引擎，主要用于MySQL Cluster分布式集群环境，Cluster是MySQL从5.0版本才开始提供的新功能。这部分我们可能并不仅仅只是介绍NDB存储引擎，因为离开了MySQL CLuster整个环境，NDB存储引擎也将失去太多意义。 所以这一节主要是介绍一下MySQL Cluster的相关内容。
简单的说，Mysql Cluster实际上就是在无共享存储设备的情况下实现的一种内存数据库Cluster环境，其主要是通过NDB Cluster（简称NDB）存储引擎来实现的。
一般来说，一个Mysql Cluster的环境主要由以下三部分组成：
a) 负责管理各个节点的Manage节点主机：
管理节点负责整个Cluster集群中各个节点的管理工作，包括集群的配置，启动关闭各节点，以及实施数据的备份恢复等。管理节点会获取整个Cluster环境中各节点的状态和错误信息，并且将各Cluster集群中各个节点的信息反馈给整个集群中其他的所有节点。由于管理节点上保存在整个Cluster环境的配置，同时担任了集群中各节点的基本沟通工作，所以他必须是最先被启动的节点。
b) SQL层的SQL服务器节点（后面简称为SQL节点），也就是我们常说的Mysql Server：
主要负责实现一个数据库在存储层之上的所有事情，比如连接管理，query优化和响应，cache管理等等，只有存储层的工作交给了NDB数据节点去处理了。也就是说，在纯粹的Mysql Cluster环境中的SQL节点，可以被认为是一个不需要提供任何存储引擎的Mysql服务器，因为他的存储引擎有Cluster环境中的NDB节点来担任。所以，SQL层各Mysql服务器的启动与普通的Mysql启动有一定的区别，必须要添加ndbcluster项，可以添加在my.cnf配置文件中，也可以通过启动命令行来指定。
c) Storage层的NDB数据节点，也就是上面说的NDB Cluster：
NDB是一个内存式存储引擎也就是说，他会将所有的数据和索引数据都load到内存中，但也会将数据持久化到存储设备上。不过，最新版本，已经支持用户自己选择数据可以不全部Load到内存中了，这对于有些数据量太大或者基于成本考虑而没有足够内存空间来存放所有数据的用户来说的确是一个大好消息。
NDB节点主要是实现底层数据存储的功能，保存Cluster的数据。每一个NDB节点保存完整数据的一部分（或者一份完整的数据，视节点数目和配置而定），在MySQL CLuster里面叫做一个fragment。而每一个fragment，正常情况来讲都会在其他的主机上面有一份（或者多分）完全相同的镜像存在。这些都是通过配置来完成的，所以只要配置得当，MysqlCluster在存储层不会出现单点的问题。一般来说，NDB节点被组织成一个一个的NDB Group，一个NDB Group实际上就是一组存有完全相同的物理数据的NDB节点群。
上面提到了NDB各个节点对数据的组织，可能每个节点都存有全部的数据也可能只保存一部分数据，主要是受节点数目和参数来控制的。首先在Mysql Cluster主配置文件（在管理节点上面，一般为config.ini）中，有一个非常重要的参数叫NoOfReplicas，这个参数指定了每一份数据被冗余存储在不同节点上面的份数，该参数一般至少应该被设置成2，也只需要设置成2就可以了。因为正常来说，两个互为冗余的节点同时出现故障的概率还是非常小的，当然如果机器和内存足够多的话，也可以继续增大。一个节点上面是保存所有的数据还是一部分数据，还受到存储节点数目的限制。NDB存储引擎首先保证NoOfReplicas参数配置的要求对数据冗余，来使用存储节点，然后再根据节点数目将数据分段来继续使用多余的NDB节点，分段的数目为节点总数除以NoOfReplicas所得。
MySQL Cluster本身所包含的内容非常之多，出于篇幅考虑，这里暂时不做很深入的介绍，在本书的架构设计部分的高可用性设计一章中将会有更为详细的介绍与实施细节，大家也可以通过MySQL官方文档来进一步了解部分细节。
3．5 其他存储引擎介绍
3.5.1 Merge存储引擎：
MERGE存储引擎，在MySQL用户手册中也提到了，也被大家认识为MRG_MyISAM引擎。Why？因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。要创建MERGE 表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。
MERGE表本身并不存储数据，仅仅只是为多个基表提供一个同意的存储入口。所以在创建MERGE表的时候，MySQL只会生成两个较小的文件，一个是.frm的结构定义文件，还有一个.MRG文件，用于存放参与MERGE的表的名称（包括所属数据库schema）。之所以需要有所属数据库的schema，是因为MERGE表不仅可以实现将Merge同一个数据库中的表，还可以Merge不同数据库中的表，只要是权限允许，并且在同一个mysqld下面，就可以进行Merge。MERGE表在被创建之后，仍然可以通过相关命令来更改底层的基表。
MERGE表不仅可以提供读取服务，也可以提供写入服务。要让MERGE表提供可INSERT服务，必须在在表被创建的时候就指明 INSERT数据要被写入哪一个基表，可以通过insert_method参数来控制。如果没有指定该参数，任何尝试往MERGE表中INSERT数据的操作，都会出错。此外，无法通过MERGE表直接使用基表上面的全文索引，要使用全文索引，必须通过基表本身的存取才能实现。
3.5.2 Memory存储引擎：
Memory存储引擎，通过名字就很容易让人知道，他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQL Crash或者主机Crash之后，Memory的表就只剩下一个结构了。Memory表支持索引，并且同时支持Hash和B－Tree两种格式的索引。由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，而且不支持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。
既然所有数据都存放在内存中，那么他对内存的消耗量是可想而知的。在MySQL的用户手册上面有这样一个公式来计算Memory表实际需要消耗的内存大小：
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))
3.5.3 BDB存储引擎：
BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb一样，也不是MySQL自己开发实现的一个存储引擎，而是由Sleepycat Software所提供，当然，也是开源存储引擎，同样支持事务安全。
BDB存储引擎的数据存放也是每个表两个物理文件，一个.frm和一个.db的文件，数据和索引信息都是存放在.db文件中。此外，BDB为了实现事务安全，也有自己的redo日志，和Innodb一样，也可以通过参数指定日志文件存放的位置。在锁定机制方面，BDB和Memory存储引擎一样，实现页级锁定。
由于BDB存储引擎实现了事务安全，那么他肯定也需要有自己的check point机制。BDB在每次启动的时候，都会做一次check point，并且将之前的所有redo日志清空。在运行过程中，我们也可以通过执行flush logs来手工对BDB进行check point操作。
3.5.4 FEDERATED存储引擎：
FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要用来提供对远程MySQL服务器上面的数据的访问借口。如果我们使用源码编译来安装MySQL，那么必须手工指定启用FEDERATED存储引擎才行，因为MySQL默认是不起用该存储引擎的。
当我们创建一个FEDERATED表的时候，仅仅在本地创建了一个表的结构定义信息的文件而已，所有数据均实时取自远程的MySQL服务器上面的数据库。
当我们通过SQL操作FEDERATED表的时候，实现过程基本如下：
a、SQL调用被本地发布
b、MySQL处理器API（数据以处理器格式）
c、MySQL客户端API（数据被转换成SQL调用）
d、远程数据库-> MySQL客户端API
e、转换结果包（如果有的话）到处理器格式
f、处理器 API -> 结果行或受行影响的对本地的计数
3.5.5 ARCHIVE存储引擎：
ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。锁定机制为行级锁定。
3.5.6 BLACKHOLE存储引擎：
BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。那么BLACKHOLE存储引擎对我们有什么用呢？在我最初接触MySQL的时候我也有过同样的疑问，不知道MySQL提供这样一个存储引擎给我们的用意为何？但是后来在又一次数据的迁移过程中，正是BLACKHOLE给我带来了非常大的功效。在那次数据迁移过程中，由于数据需要经过一个中转的MySQL服务器做一些相关的转换操作，然后再通过复制移植到新的服务器上面。可当时我没有足够的空间来支持这个中转服务器的运作。这时候就显示出 BLACKHOLE的功效了，他不会记录下任何数据，但是会在binlog中记录下所有的sql。而这些sql最终都是会被复制所利用，并实施到最终的slave端。
MySQL的用户手册上面还介绍了BLACKHOLE存储引擎其他几个用途如下：
a、SQL文件语法的验证。
b、来自二进制日志记录的开销测量，通过比较允许二进制日志功能的BLACKHOLE的性能与禁止二进制日志功能的BLACKHOLE的性能。
c、因为BLACKHOLE本质上是一个“no-op” 存储引擎，它可能被用来查找与存储引擎自身不相关的性能瓶颈。
3.5.7 CSV存储引擎：
CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CVS表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。
3．6 小结
多存储引擎是 MySQL 有别于其他数据库管理软件的最大特色，不同的存储引擎有不同的特点，可以应对不同的应用场景，这让我们在实际的应用中可以根据不同的应用特点来选择最有利的存储引擎，给了我们足够的灵活性。通过这一章对 MySQL 各个存储引擎的初步了解，我想各位读者朋友应该已经对 MySQL 的主要存储引擎有了一定的认识，在后续的章节中对于一些常用的存储引擎还会有更为深入的介绍。
摘自：《MySQL性能调优与架构设计》简朝阳
转载请注明出处：
