MySQL性能调优与架构设计(二)—— MySQL存储引擎简介

什么是存储引擎

1. mysql中的数据用各种不同的技术存储在文件（或者内存）中。
2. 这些技术中每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。
3. 通过选择不同的技术，你能够获得额外的速度和功能，从而改善你的应用的整体功能。
4. 例如，你在研究大量的临时数据，你也许需要使用内存mysql存储引擎。内存存储引擎能够在内存中存储所有的表格数据。
5. 又或者，你也许需要一个支持事务处理的数据库（以确保事务处理不成功时数据的回退能力）。
6. 这些不同的技术及配套的相关功能在mysql中被称为存储引擎。

MySQL存储引擎-前言

1. mysql默认配置了许多不同的存储引擎，可以预先设置或者在mysql服务器中启用。
2. 你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。
3. 对mysql来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用mysql强大的功能。
4. mysql的插件式存储引擎主要包括MyISAM、Innodb、NDB Cluster、Maria、Falcon、Memory、Archive、Merge、Federated 等。

5. 其中最著名而且使用最广泛的是MyISAM和Innodb两种存储引擎。

   （1）MyISAM是mysql最早的ISAM存储引擎的升级版本，也是Mysql默认存储引擎。
   （2）而Innodb实际上并不是mysql公司的，而是第三方软件公司Innobase所开发，其最大的特点是提供了事务控制等特性，所以使用者也非常广泛。

6. 其他的一些存储引擎相对来说使用场景稍微少一些，都是应用于某些特定的场景。

   （1）如NDB Cluster虽然也支持事务，但主要是用于分布式环境，属于一个share nothing的分布式数据库存储引擎。
   （2）Maria是mysql最新开发的对MyISAM的升级版存储引擎
   （3）Falcon是mysql公司自行研发的为了替代当前Innodb存储引擎的一款带有事务等高级特性的数据库存储引擎。
   （4）Memory存储引擎所有数据和所有均存储与内存中，所以主要是用于一些临时表，或者对性能要求极高，但是允许在主机Crash的时候丢失数据的特定场景下。
   （5）Archive是一个数据经过高比例压缩存放的存储引擎，主要用于存放过期而且很少访问的历史信息，不支持索引。
   （6）Merge和Federated在严格意义来说，并不能算作一个存储引擎。
   （7）因为Merge存储引擎主要是将几个基表merge在一起，对外作为一个表来提供服务，基表可以基于其他的几个存储引擎。
   （8）而Federated实际上所做的事情，有点类似于Oracle的dblink，主要用于远程存取其他mysql服务器上面的数据。

MyISAM 存储引擎简介

1. myisam存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件中。
2. 首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。
3. 每个表有且仅有这样三个文件作为MyISAM存储类型的表的存储，也就是说，不管这个表有多少索引，都是存放在一个.MYI文件中。

4. MyISAM支持三种类型的索引：

   （1）B-Tree索引：
       【1】顾名思义，就是所有的索引节点都按照balance tree的数据结构存储
       【2】所有的索引数据节点都在叶节点
   （2）R-Tree索引：
       【1】R-Tree索引的存储方式和B-Tree索引的存储方式有一些区别
       【2】主要设计用于为存储空间和多维数据的字段做索引
   （3）Full-text索引：
       【1】Full-text就是我们常说的全文索引，他的存储结构也是B-Tree
       【2】主要是为了解决我们需要用like查询的低效问题
   （4）MyISAM索引的三种类型中，最经常使用的就是B-Tree索引，偶尔会使用到Full-text索引，但R-Tree索引一般系统中都是很少用到的。

5. MyISAM数据存放格式

   （1）虽然每一个MyISAM的表都是存放在一个相同后缀名的.MYD文件中，但是每一个文件的存放格式实际上可能并不是完全一样的。
   （2）因为MyISAM的数据存放格式是分为静态（FIXED）固定长度、动态（DYNAMIC）可变长度、以及压缩（COMPRESSED）这三种格式
   （3）当然三种格式中是否压缩完全可以任由我们自己决定，可以在创建表的时候通过ROW_FORMAT来指定{COMPRESSED | DEFAULT}，也可以通过myisampack工具来进行压缩，默认是不压缩的。
   （4）而在非压缩的情况下，是静态还是动态，就和我们表中各字段的定义相关了。
   （5）只要表中有可变长度类型的字段存在，那么该表就肯定是DYNAMIC格式的；
   （6）如果没有任何可变长度的字段，则为FIXED格式；
   （7）当然，你也可以通过alter table命令，强行将一个带有VARCHAR类型字段的DYNAMIC的表转换为FIXED，但是所带来的结果是原varchar字段类型会被自动转换成char类型。
   （8）相反，如果将FIXED转换为DYNAMIC，也会将char类型字段转换为varchar类型，所以大家手工强行转换的操作一定要谨慎。

6. MyISAM存储引擎的表是否足够可靠？mysql参考手册列出在遇到如下情况的时候可能会出现表文件损坏：

   （1）当mysqld在做写操作的时候被kill掉或者其他情况造成异常终止
   （2）主机crash
   （3）磁盘硬件故障
   （4）MyISAM存储引擎中的BUG

7. MyISAM表出错后影响范围：

   （1）MyISAM存储引擎的某个表文件出错之后，仅影响到该表，而不会影响到其他表，更不会影响到其他的数据库
   （2）如果我们的数据库正在运行过程中发现某个MyISAM表出现问题了，则可以在线通过check table命令尝试校验它，并可以通过repair table命令尝试修复。
   （3）在数据库关闭状态下，我们也可以通过myisamchk工具来对数据库某个表进行检测和修复。
   （4）不过强烈建议不到万不得已不要轻易对表进行修复操作，修复之前尽量做好可能的备份工作，以免带来不必要的后果。
   （5）另外myisam存储引擎的表，理论上是可以被多个数据库实例同时使用同时操作的，但是不建议这样做，而且mysql官方文档中也有提到，建议尽量不要在多个mysqld之间共享MyISAM存储文件。

8. MyISAM只支持表级锁。
9. 用户在操作myisam表时，select、update、delete、insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。

Innodb存储引擎简介

1. Innodb其功能方面的特点：

   （1）支持事务安装：
       【1】Innodb在功能方面最重要的一点就是对事务安全的支持
       【2】而且实现了SQL92标准所定义的所有四个级别（READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ和SERIALIZABLE）
       【3】对事务安全的支持，无疑让很多之前因为特殊业务要求而不得不放弃使用Mysql的用户转而支持mysql，以及对数据选型持观望态度的用户，也大大增加了对mysql的好感。
   （2）数据多版本读取：
       【1】Innodb在支持事务的同时，为了保证数据的一致性以及并发时候的性能，通过对undo信息，实现了数据的多版本读取。
   （3）锁定机制的改进：
       【1】Innodb改变了MyISAM的锁机制，实现了行锁。
       【2】虽然Innodb的行锁机制的实现是通过索引来完成的，但毕竟在数据库中99%的SQL语句都是要使用索引来检索数据的。
       【3】所以，行锁定机制也无疑为Innodb在承受高并发压力的环境下增强了不小的竞争力。
   （4）实现外键：
       【1】Innodb实现了外键引用这一数据库的重要特性，使在数据库端控制部分数据的完整性成为可能。
       【2】虽然很多数据库系统调优专家都建议不要这样做，但是对于不少用户来说在数据库端加如外键控制可能仍然是成本最低的选择。
   （5）物理存储方面：
       【1】Innodb存储引擎也和MyISAM不太一样，虽然也有.frm文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。
       【2】至于是每个表单独存放还是所有表存放在一起，完全由用户来决定（通过特定配置），同时还支持符号链接。

2. Innodb物理结构：

   （1）数据文件（表数据和索引数据）
       【1】存放数据表中的数据和所有的索引数据，包括主键和其他普通索引。
       【2】在Innodb中，存在了表空间（tablespace）这样一个概念，但是他和Oracle的表空间又有较大的不同。
   （2）Innodb的表空间分为两种
       【1】共享表空间：
            「1」也就是所有表和索引数据都存放在同一个表空间（一个或多个数据文件）中，通过innodb_data_file_path来指定，增加数据文件需要停机重启。
            「2」虽然我们可以自行设定使用共享表空间还是独享表空间来存放我们的表，但是共享表空间必须存在。
            「3」因为Innodb的undo信息和其他一些元数据信息都是存放在共享表空间里面的。
            「4」共享表空间的数据文件是可以设置为固定大小和可自动扩展大小两种形式的，自动扩展形式的文件可以设置文件的最大大小和每次扩展量。
            「5」在创建自动扩展的数据文件的时候，建议大家最好加上最大尺寸的属性，一个原因是文件系统本身是有一定大小限制的（但是Innodb不知道），还有一个原因就是自身维护的方便。
            「6」另外Innodb不仅可以使用文件系统，还可以使用原始块设备，也就是我们常说的裸设备。
            「7」当我们当文件表空间快要用完的时候，我们必须要为其增加数据文件，当然，只有共享表空间有此操作
            「8」共享表空间增加数据文件的操作比较简单，只需要在innodb_data_file_path参数后面按照标准格式设置好文件路经和相关属性即可，不过这里有一点需要注意，就是Innodb在创建新数据文件的时候是不会创建目录的，如果指定目录不存在，则会报错并无法启动。
            「9」Innodb在给共享表空间增加数据文件之后，必须要重启数据库系统才能生效，如果是使用裸设备，还需要有两次重启。
       【2】独享表空间：也就是每个表的数据和索引被存放在一个单独的.ibd文件中。
   （3）日志文件
       【1】Innodb的日志文件和Oracle的redo日志比较类似，同样可以设置多个日志组（最少2个）
       【2】采用轮循策略来顺序的写入
       【3】如果你的数据库中创建来Innodb的表，那么千万别全部删除innodb的日志文件，因为很可能就会让你的数据库crash，无法启动，或者丢失数据。
       【4】由于Innodb是事务安全的存储引擎，所以系统crash对他来说并不能造成非常严重的损失
       【5】由于有redo日志的存在，有checkpoint机制的保护，Innodb完全可以通过redo日志将数据库crash时刻已经完成但还没来得及将数据写入磁盘的事务恢复，也能够将所有部分完成并写入磁盘的未完成事务回滚并将数据还原。

3. Innodb不仅在功能特性方面和MyISAM存储引擎有较大区别，在配置上面也是单独处理的。
4. 在Mysql启动参数文件设置中，Innodb的所有参数基本上都带有前缀“innodb_”，不论是innodb数据和日志相关，还是一些其他性能、事务等等相关的参数都是一样。
5. 和所有Innodb相关的系统变量一样，所有的Innodb相关的系统状态值也同样全部以“innodb_”前缀。
6. 我们也可以仅仅通过一个参数（skip-innodb）来屏蔽Mysql中的Innodb存储引擎，这样即使我们在安装编译的时候将Innodb存储引擎安装进去，使用者也无法创建Innodb表。

NDB Cluster存储引擎简介

1. NDB存储引擎也叫NDB Cluster存储引擎，主要用于MySQL Cluster分布式集群环境
2. Cluster是从Mysql 5.0才开始提供的新功能。
3. 这部分我们不仅仅只是介绍NDB存储引擎，因为离开了Mysql Cluster整个环境，NDB存储引擎也将失去太多意义

4. 所以，这一节，主要介绍一下MySQL Cluster的相关内容

   （1）简单的说，Mysql Cluster实际上就是在无共享存储设备的情况下实现的一种内存数据库Cluster环境，其主要是通过NDB存储引擎来实现的。
   （2）一个Mysql Cluster的环境主要由三部分组成：
       【1】负责管理各个节点的Manage节点主机：
            「1」管理节点负责整个Cluster集群中各个节点的管理工作，包括集群的配置，启动关闭节点，以及实施数据的备份恢复等。
            「2」管理节点会获取整个Cluster环境中各节点的状态和错误信息，并且将各Cluster集群中各个节点的信息反馈给整个集群中其他所有节点。
            「3」由于管理节点上保存了整个Cluster环境的配置，同时担任了集群中的各节点的基本工作，所有他必须是最先被启动的节点。
       【2】SQL层的SQL服务器节点，也就是我们常说的mysql server：
            「1」主要负责实现一个数据库在存储层之上的所有事情，比如连接管理、query优化和响应、cache管理等等。
            「2」只有存储层的工作交给了NDB数据节点去处理了。
            「3」也就是说，在纯粹的Mysql Cluster环境中的SQL节点，可以被认为是一个不需要提供任何存储引擎的mysql服务器，因为他的存储引擎有Cluster环境的NDB节点来担任。
            「4」所以，SQL层各mysql服务器的启动与普通的mysql服务器启动有一定区别，必须要添加ndbcluster项，可以添加在my.cnf配置文件中，也可以通过启动命令行来启动。
       【3】Storage层的NDB数据节点，也就是上面说的NDB Cluster：
            「1」NDB是一个内存式存储引擎，也就是说，他会把所有的数据和索引都load到内存中，但也会将数据持久化到存储设备中。
            「2」最新版本中，已经支持用户自己选择数据可以不全部load到内存中了，这对于有些数据量太大或者基于成本考虑而没有足够内存空间来存放所有数据的用户来说的确是一个大好消息。
            「3」NDB节点主要是实现底层数据存储功能，保存Cluster的数据。
            「4」每一个NDB节点保存完整数据的一部分（或者一份完整的数据，视节点数目和配置而定），在mysql cluster中叫做一个fragment。
            「5」而每一个fragment，正常情况来讲都会在其他的主机上有一份（或者多份）完全相同的镜像存在。
            「6」这些都是通过配置来完成的，所以只要配置得当，Mysql Cluster在存储层不会出现单点的问题。
            「7」一般来说，NDB节点被组织成一个一个的NDB Group，一个NDB Group实际上就是一组存有完全相同的物理数据的NDB节点群。

5. 上面提到了NDB节点对数据的组织，可能每个节点都存有全部的数据也可能只保存一部分数据，主要是是受节点数目和参数来控制的。
6. 首先在Mysql Cluster主配置文件中（在管理节点上，一般为config.ini），有一个非常重要的参数叫NoOfReplicas，这个参数指定来每一份数据被冗余存储在不同节点上的份数，该参数一般至少应该被设置成2，也只需要设置成2就可以了。
7. 因为正常情况下，两个互为冗余的节点同时出现故障的概率还是非常小的，当然如果机器和内存足够多的话，也可以继续增大。
8. 一个节点上面是保存所有数据还是部分数据，还受到存储节点数据的限制。
9. NDB存储引擎首先保证NoOfReplicas参数配置的要求对数据冗余，来使用存储节点，然后再根据节点数目将数据分段来继续使用多余的NDB节点，分段的数目为节点总数除以NoOfReplicas参数所得。

Merge存储引擎简介

1. Merge存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊包装对外提供一个单一的访问入口，以减小应用的复杂度的目的。
2. 要创建Merge表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。
3. Merge表本身不存储数据，仅仅只是为多个基表提供一个统一的存储入口。
4. 所以在创建Merge表时，Mysql只会生成两个较小的文件，一个是.frm的结构定义文件，一个是.MRG文件，用于存放参与Merge的表的名称（包括所属数据库schema）。
5. 之所以需要所属数据库的schema，是因为Merge表不仅可以实现将Merge同一个数据库中的表，还可以Merge不同数据库中的表，只要权限允许，并且是在同一个mysqld下，就可以进行Merge。
6. Merge表被创建后，仍然可以通过相关命令来更改底层的基表。
7. Merge表不仅可以提供读取服务，也可以提供写入服务。
8. 要让Merge表提供可Insert服务，必须在在表被创建的时候就指明Insert数据要被写入哪一个基表，可以通过insert_method参数来控制。
9. 无法通过Merge表直接使用基表上面的全文索引，要使用全文索引，必须通过基表本身的存储才能实现。

Memory存储引擎简介

1. Memory存储引擎，是一个将数据存储在内存中的存储引擎。
2. Memory存储引擎不会将任何数据存储在磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。
3. 所以一旦Mysql Crash或者主机Crash之后，Memory的表就只剩下了一个结构了。
4. Memory支持索引，并且同时支持Hash和B-Tree两种格式的索引。
5. 由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，而且不支持BLOB和TEXT类型的字段。
6. Memory存储引擎实现页级锁定。

7. 既然所有数据都是存在内存中，那么他对内存的消耗量可想而知。在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*))

BDB存储引擎简介

1. BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb一样，也不是Mysql自己开发实现的一个存储引擎，而是由Sleepycat Software所提供，当然，也是开源存储引擎，同样支持事务安全。
2. BDB存储引擎的数据存放也是每个表两个物理文件，一个.frm和一个.db文件，数据和索引信息都是存放在.db文件中。
3. BDB为了实现事务安全，也有自己的redo日志，和Innodb一样，也可以通过参数来指定日志文件的存放位置。
4. 在锁定机制方面，BDB和Memory存储引擎一样，实现页级锁定。
5. 由于BDB存储引擎实现了事务安全，那么他肯定也需要有自己的check point机制。
6. BDB在每次启动的时候，都会做一次check point，并且将之前的所有redo日志清空。
7. 在运行过程中，我们也可以通过执行flush logs来手工对BDB进行check point操作。

FEDERATED存储引擎简介

1. FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要用来提供对远程Mysql服务器上面的数据的访问接口。
2. 如果我们使用源码编译来安装mysql，那么必须手工指定启用FEDRATED存储引擎才行，因为mysql默认是不启用该存储引擎的。
3. 当我们创建一个FEDERATED表的时候，仅仅在本地创建了一个表的结构定义信息的文件而已，所有数据均实时取自远程mysql服务器上面的数据。

4. 当我们通过SQL操作FEDERATED表的时候，实现过程基本如下：

   （1）SQL调用被本地发布
   （2）MYSQL处理器API（数据以处理器格式）
   （3）Mysql客户端API（数据被转换成SQL调用）
   （4）远程数据库->Mysql客户端API
   （5）转换结果包（如果有的话）到处理器格式
   （6）处理器API->结果行或受行影响的对本地的计数

ARCHIVE存储引擎简介

1. ARCHIVE存储引擎主要是通过较小的存储空间来存放过期的很少访问的历史数据。
2. ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。
3. 由于ARCHIVE表的特殊性，ARCHIVE表不支持删除、修改操作，仅支持插入和查询操作。
4. 锁定机制为行级锁定。

BLACKHOLE存储引擎简介

1. BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。
2. 就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。
3. BLACKHOLE存储引擎，写入的任何数据都会消失，用于记录binlog做复制的中继存储。

4. Mysql的用户手册上还介绍了BLACKHOLE存储引擎其他几个用途如下：

   （1）SQL文件语法的验证
   （2）来自二进制日志的开销测量，通过比较允许二进制日志功能的BLACKHOLE的性能与禁止二进制日志功能的BLACKHOLE的性能。
   （3）因为BLACKHOLE存储引擎本质上是一个“no-op”存储引擎，它可能被用来查找与存储引擎自身不相关的性能瓶颈。

CSV存储引擎简介

1. CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。
2. 其主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式。
3. 所以我们可以通过先在数据库中建立一张CSV表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。

参考链接

https://www.cnblogs.com/jesse...