SQL高级优化（三）之存储引擎

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

一、MySQL数据库引擎简介

1. ISAM(indexed Sequential Access Method)

ISAM 是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM 的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把 ISAM 用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL 能够支持这样的备份应用程序。

注意：使用ISAM注意点：必须经常备份所有实时数据

版本：MySQL5.7不支持ISAM

2. MyISAM

MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

MyISAM引擎使用注意：必须经常使用OPTIMIZE TABLE命令清理空间；必须经常备份所有实时数据。工具有用来修复数据库文件的MyISAMCHK工具和用来恢复浪费攻坚的MyISAMPACK工具。不支持事务。数据越多，写操作效率越低。因为要维护数据和索引信息。（索引列越多，相对效率越低。）

如果使用该引擎，会生成三个文件： .frm：表结构信息 .MYD：数据文件 .MYI：表的索引信息

3. InnoDB

InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。是现在的MySQL（5.5版本以上）常用版本默认引擎，也是默认的存储引擎

MySQL 官方对InnoDB是这样解释的：InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

在MySQL5.7版本中，InnoDB存储引擎管理的数据文件为两个：分别是frm,idb文件。

innoDB特点：支持事务数据多版本读取（InnoDB+MyISAM+ISAM) 锁定机制的改进（表级锁到行级锁）实现外键

3.1 InnoDB与MyISAM区别

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以自豪把多条SQL语句放在begin transaction和commit之间，组成一个事务；

InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

3.2 如何选择：

是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。

系统奔溃后，MyISAM恢复起来更困难，能否接受；

MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

4. Memory存储引擎

Memory存储引擎，通过名字就很容易让人知道，它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。MEMORY至此索引，并且同时支持HASH和B-tree索引，默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。由于存放在内存中，所以Memory都是按照订场的空间来存储数据的，并且不支持BLOB和TEXT类型的字段。Memory存储引擎使用页级锁定。

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

5. NDBCluster存储引擎

NDBCluster存储引擎，主要用于MySQL Cluster分布式集群环境，Cluster是MySQL从5.0版本才开始提供的新功能。

6. MRG_MYISAM存储引擎

在以前叫做MERGE，MySQL5.7中叫做MRG_MYISAM

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 表直接使用基表上面的全文索引，要使用全文索引，必须通过基表本身的存取才能实现。

7. FEDERATED存储引擎

FEDERATED 存储引擎所实现的功能，和Oracle 的DBLINK 基本相似，主要用来提供对远程MySQL 服务器上面的数据的访问借口。如果我们使用源码编译来安装MySQL，那么必须手工指定启用

FEDERATED 存储引擎才行，因为MySQL 默认是不起用该存储引擎的。当我们创建一个FEDERATED 表的时候，仅仅在本地创建了一个表的结构定义信息的文件而已，所有数据均实时取自远程的MySQL 服务器上面的数据库。

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

SQL 调用被本地发布

MySQL 处理器API（数据以处理器格式）

MySQL 客户端API（数据被转换成SQL 调用）

远程数据库-> MySQL 客户端API

转换结果包（如果有的话）到处理器格式

处理器API -> 结果行或受行影响的对本地的计数

8. ARCHIVE存储引擎

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

9. BLACKHOL存储引擎

BLACKHOLE 存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix 系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。

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

SQL 文件语法的验证。

来自二进制日志记录的开销测量，通过比较允许二进制日志功能的BLACKHOLE 的性能与禁止二进制日志功能的BLACKHOLE 的性能。

因为BLACKHOLE 本质上是一个“no-op” 存储引擎，它可能被用来查找与存储引擎自身不相关的性能瓶颈。

10. CSV存储引擎

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

11. PERFORMANCE_SCHEMA 存储引擎

MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的，需要手动开启。

二、存储引擎管理

1. 查看数据库支持的存储引擎

show engines;

SHOW ENGINES 输出包含以下列：

Engine

存储引擎的名称。

Support

服务器对存储引擎的支持级别，如下表所示。

值	含义
YES	引擎受支持且处于活动状态
DEFAULT	就像一样YES，这是默认引擎
NO	不支持引擎
DISABLED	支持引擎，但已将其禁用

值`NO`表示服务器在编译时不支持引擎，因此无法在运行时启用。

值`DISABLED`，因为服务器启动与关闭发动机，或者是因为不是所有的选项来启用它要求给予一个选项，无论发生。在后一种情况下，错误日志应包含指示为什么禁用该选项的原因。请参见 [第5.4.2节“错误日志”](https://dev.mysql.com/doc/refman/5.7/en/error-log.html)。

您可能还会看到`DISABLED`存储引擎，如果服务器经过编译以支持该服务器，但是该服务器是通过一个选项启动的。对于存储引擎，意味着服务器是在编译时支持NDB Cluster的，但未使用该选项启动。

所有MySQL服务器都支持`MyISAM`表。无法禁用`MyISAM`。