简述

之前讲述了MySQL体系结构，当时谈到了MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。他根据MySQL AB公司提供的文件访问层抽象接口来定制一种文件访问的机制（该机制叫存储引擎）。MySQL 的核心就是存储引擎。

存储引擎是MySQL中具体与文件打交道的子系统，主要负责 MySQL 中数据的存储和提取，为存储的数据建立索引。

MySQL 支持的存储引擎

MySQL 8.0 支持的存储引擎有 InnoDB、MyISAM、Memory、MRG_MYISAM、Archive、Federated、CSV、BLACKHOLEPERFORMANCE_SCHEMA。可以使用SHOW ENGINES语句查看系统所支持的引擎类型，结果如下图所示。

具体参数说明如下：

• Support：Support列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。
• transactions：是够支持事务。
• xa：是否支持xa事务(分布式事务)。
• savepoints：是否支持保存savepoints之间的内容。

我们可以看到 InnoDB 是系统的默认引擎，支持可靠的事务处理。

MySQL 常见的存储引擎

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5之后, InnoDB 是默认的 MySQL 存储引擎。

例如，建立一个使用 MyISAM 存储引擎的 test_myisam 表，

create table test_innodb(
    id int(10) unsigned not null auto_increment,
    name varchar(10) character set utf8,
    age int(10),
    primary key(id)
) engine = InnoDB;
复制代码

那么就会生成以下一个文件：

• test_innodb.ibd: test_innodb代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件, 存储该表的表结构(frm、 sdi)、数据和索引。

通过参数innodb_file_per_table设置是否每一张表对应一个表空间文件。可通过show variables like "%innodb_file_per_table%";查看是否开启，默认开启。

特点

• DML(增删改)操作遵循ACID模型，支持事务
• 同时支持表锁和行锁，通过行级锁，提高并发访问性能
• 支持外键约束，保证数据的完整性和正确性

应用场景

在以下场合下，使用InnoDB是最理想的选择：

• 更新密集的表。 InnoDB存储引擎特别适合处理多重并发的更新请求。
• 事务。 InnoDB存储引擎是支持事务的标准MySQL存储引擎。
• 自动灾难恢复。 与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
• 外键约束。 MySQL支持外键的存储引擎只有InnoDB。
• 支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

MyISAM

MylSAM 是 MySQL 早期版本的默认存储引擎。MyISAM 表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器。

每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。

例如，建立一个使用 MyISAM 存储引擎的 test_myisam 表，

create table test_myisam(
    id int(10) unsigned not null auto_increment,
    name varchar(10) character set utf8,
    age int(10),
    primary key(id)
) engine = MyISAM;
复制代码

那么就会生成以下三个文件（数据文件夹地址：/usr/local/mysql/data）：

• test_myisam_362.sdi，存储表定义。
• test_myisam.MYD，存储数据。
• test_myisam.MYI，存储索引。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用 MyISAM 存储引擎。

特点

• 不支持事务，不支持外键
• 只支持表锁，不支持行锁
• 访问速度快

应用场景

MyISAM 存储引擎特别适合在以下几种情况下使用：

• 选择密集型的表。 MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
• 插入密集型的表。 MyISAM的并发插入特性允许同时选择和插入数据。

由此看来，MyISAM 存储引擎很适合管理服务器日志数据。

MEMORY

使用MySQL Memory存储引擎的出发点是速度，为得到最快的响应时间，采用的逻辑存储介质是系统内存。

虽然在内存中存储表数据确实会提供很高的性能，由于受到硬件问题、断电问题的影响、mysqld守护进程崩溃时，所有的Memory数据都会丢失。

特点

• 内存存放
• 同时支持Hash索引（默认）和B树索引

应用场景

一般在以下几种情况下使用Memory存储引擎：

• 目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
• 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
• 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

CSV

CSV 存储引擎是基于 CSV 格式存储数据。

• CSV 存储引擎因为自身文件格式的原因，所有列必须强制指定 NOT NULL 。
• CSV 存储引擎不支持索引，也不支持分区。
• 每个数据行占用一个文本行。

因为 csv 文件本身就可以被Office等软件直接编辑，保不齐就有不按规则出牌的情况，如果出现 csv 文件中的内容损坏了的情况，也可以使用 CHECK TABLE 或者 REPAIR TABLE 命令检查和修复。

例如，建立一个使用 CSV 存储引擎的 test_csv 表，

create table test_csv(
    id int(10) not null,
    name varchar(10) not null,
    age int(10) not null
) engine = CSV;
复制代码

那么就会生成以下三个文件：

• test_csv.CSM：元信息文件，用来保存表的状态及表中保存的数据量。
• test_csv.CSV：存储数据的文件
• test_csv_364.sdi：存储表结构

ARCHIVE

Archive 是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。

• archive存储引擎支持insert、replace和select操作，但是不支持update和delete操作。
• archive存储引擎支持blob、text等大字段类型。
• 支持auto_increment自增列同时自增列可以不是唯一索引，但是不支持往auto_increment列插入一个小于当前最大值的值。

在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。

Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

例如，建立一个使用 Archive 存储引擎的 test_archive 表，

create table test_archive(
    id int not null ,
    name varchar(30),
    address varchar(300),
    mark text
) engine=archive;
复制代码

那么就会生成以下两个文件：

• test_archive.ARZ：存储数据
• test_archive_365.sdi：存储表定义

由于高压缩和快速插入的特点，Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

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

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示。

为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。下面提供几个选择标准，然后按照标准，根据实际情况，选择对应的存储引擎即可：

• 是否需要支持事务
• 是否需要使用热备
• 崩溃恢复，能否接受崩溃
• 是否需要外键支持
• 存储的限制
• 对索引和缓存的支持

可以根据以下的原则来选择 MySQL 存储引擎：

• 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
• 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
• 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

具体使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。