【数据库】数据库的存储引擎与适用场景

更多面试题总结请看：【面试题】技术面试题汇总

存储引擎定义

现代数据库大体可以分为三层。最上层用于连接、线程处理等；中间层提供数据库的核心功能，包括 SQL 解析、分析、优化、视图等；底层就是数据库的存储引擎，负责数据的存储与提取。

简单来说，存储引擎是为数据库提供创建、查询、更新、存储数据的软件模块。不同的存储引擎的主要区别是数据的存储方式，此外功能、特性、速度等也有所差异。

存储引擎为数据库屏蔽了底层存储的细节。现在许多数据库管理系统都支持多种存储引擎，通过插件化的方式配置。可以根据具体场景，选择不同的存储引擎。

MySQL 支持的存储引擎

在 MySQL 中执行 SHOW ENGINES;，可以看到支持的存储引擎列表，不同环境下可能有一定差异：

MySQL 的存储引擎主要分为两大类：事务型、非事务型，从上图中的“Transactions”一列可以看出来，只有 InnoDB 引擎支持事务。

在 MySQL 5.5 及之后的版本中，默认的存储引擎是 InnoDB，而在这之前是 MyISAM。

不同的存储引擎简介

• InnoDB
• MyISAM
• Memory
• CSV
• Merge
• Archive
• Blackhole
• Federated
• Example

InnoDB 是最广泛使用的存储引擎。支持事务，满足 ACID 约束。支持行级锁，崩溃恢复和多版本并发控制（MVCC）。是唯一支持外键引用完整性约束的存储引擎。

MyISAM 是一个很快的存储引擎。不支持事务。只支持表级锁。常用在 Web 和数据仓库中。

Memory 将数据表创建在内存中，是最快的存储引擎。不支持事务，支持表级锁。Memory 存储引擎很适合于创建临时表或者快速查询的场景。当数据库重启时，内存中的数据会丢失。

CSV 将数据存储为 CSV 格式的文件。CSV 是一种通用的、相对简单的文件格式，可以很方便的被其他应用（如 Excel）读取。

Merge 在新版 MySQL 中称为 MRG_MyISAM，它将一组相似的 MyISAM 表组合起来，视作一个表。Merge 表本身没有数据，实际是在操作底层的 MyISAM 表。Merge 存储引擎能够更简单的管理大量数据，适用于数据仓库（如服务器日志）等场景。

Archive 存储引擎针对高速插入进行了优化。它会将插入的数据实时压缩。不支持事务。由于数据被压缩，因此仅支持插入和查询两种功能。适用于用作仓库，存储大量归档数据。

Blackhole（黑洞）存储引擎接受但不存储任何数据（类似于 Linux 中的 /dev/null），查询时永远返回空集。该存储引擎可用于执行性能测试。

Federated（联合）存储引擎可以将多台物理服务器创建为一个逻辑数据库，数据存储在远程服务器中，本地不存储数据。本地服务器上的查询将在远程（联合）表上自动执行。适用于分布式场景。

Example 可以创建表，但不能存储或获取数据，可以用来学习如何编写新的存储引擎。

InnoDB、MyISAM、Memory 详解

InnoDB

特性

存储结构

InnoDB 将表结构存储在 .frm 文件中，数据和索引存储在 .idb 文件中。.idb 文件是由 InnoDB 管理的特殊格式的数据文件，表示每一个表独有的表空间（tablespace）。

在表空间中，所有的数据记录都被逻辑地存放在表空间中。表空间被进一步划分为段（segment）、区（extent）、页（page）。页是 InnoDB 管理数据的最小磁盘单位，每个 16KB 大小的页中可以存放 2-200 行的记录。

InnoDB 的行记录在物理存储上并不是顺序的。为了保证插入和删除的效率，整个页面并不会按照主键顺序对行记录进行排序，而是自动从左向右寻找空白节点进行插入，通过行记录中的 next_record 指针表示它们之间的逻辑顺序。

InnoDB 在查找某条记录时，并不能直接找到对应的行记录，而是只能获取到记录所在的页，然后将整个页面加载到内存中，在内存中遍历找到具体行。这部分耗时一般可以忽略。

InnoDB 的行记录并不是物理上连续的，主要有两方面原因：

1. B+ 树上相邻的叶节点并不一定是磁盘上相邻的页面
2. 同一个页面中的行记录也不一定是按照主键顺序排列的

聚集索引与非聚集索引

InnoDB 存储引擎的数据存储方式是聚集索引。InnoDB 的每个表有且仅有一个聚集索引。InnoDB 默认会使用每个表的主键顺序，为表格中所有行记录构建一棵 B+ 树，其叶节点以页面为单位，存放完整的行记录，所有叶节点存放了整张表的数据。

这些 B+ 树的叶节点，就是上文的“数据页”。相邻编号的页，在物理上是相邻的，但不一定是 B+ 树的相邻的叶节点。B+ 树的每个非叶节点，也分别保存在一个数据页中。数据页的大小默认与操作系统的页面大小是相同的，都是 16KB。

关于 InnoDB 中记录的格式，可以阅读：

• InnoDB 数据页结构
• MySQL 的 InnoDB 索引原理详解
• InnoDB Record Structure

非聚集索引也称为辅助索引。非聚集索引同样是一棵 B+ 树，但是叶节点仅存储索引列的所有键，和对应行记录的主键。

当通过非聚集索引查找一条行记录时，需要先通过非聚集索引查找到对应的主键，然后在聚集索引中根据主键二次查找，获取到对应的行记录。

聚集索引与非聚集索引的关系（图源）：

优缺点、适用场景

1. 支持事务、行锁、外键、MVCC 等特性
2. 并发能力较好，适用于更新密集的场景。这是因为在更新数据时，InnoDB 使用的是行锁，粒度小，竞争情况少，从而增加增加了并发处理（插入数据时使用的是表锁）
3. 读写效率较差。主要原因在于索引查询后还需要根据主键进行二次查找
4. 占用空间大

大多数场景下都可以选择 InnoDB 引擎，InnoDB 也是 MySQL 的默认存储引擎。

MyISAM

MyISAM 基于旧的 ISAM 存储引擎，增加了许多有用的扩展。

特性

存储结构

每个 MyISAM 表格会保存在磁盘的三个文件中，文件名就是表名：

• .frm：存储表结构
• .MYD（MYData）：存储数据
• .MYI（MYIndex）：存储索引

不同于 InnoDB，MyISAM 的数据是顺序存储的。索引的 B+ 树叶节点存放数据记录的地址，可以直接定位到数据，因此查找速度很快（图源）：

优缺点、适用场景

1. 占用空间小
2. 处理速度快，适用于选择（select）密集的场景。这是因为可以通过索引节点直接定位到数据，不需要二次查找
3. 支持全文索引（InnoDB 也支持）
4. 不支持事务
5. 只支持表级锁（为了并发插入）

适用于包含大量读取操作（read-heavy）、不需要事务支持的场景，效率很高，例如数据仓库和 Web 应用程序。

Memory

简介

Memory 存储引擎，顾名思义，将数据存在系统内存里，因此读写速度快，性能高。但是安全性不高，数据可能因为进程崩溃或硬件重启而丢失。

Memory 中的每个表对应磁盘上的一个 .frm 文件，该文件只存储表结构，数据都存储在内存中。

适用场景

• 需要很快的读写速度
• 数据库表相对较小（内存空间有限）
• 对数据的安全性要求较低

以上是“且”的关系。不过一般很少使用 Memory 存储引擎。

综合对比

如何选择存储引擎

每个存储引擎在功能和限制上都有差异。在某些情况下，只能选择特定的存储引擎，比如需要支持事务、热备份、崩溃恢复、外键支持、缓存等，或者不能有存储限制。在其他情况下，可以灵活选择不同的存储引擎：

• 同一个数据库的不同表，可以使用不同的存储引擎。比如查询密集的表可以使用 MyISAM，用于查询的临时表可以使用 Memory
• 不同服务器上的数据库，也可以使用不同的存储引擎。比如读写分离的场景下，从数据库可以使用 MyISAM，提高查询速度

技术面试题汇总

参考资料

• MySQL storage engines - ZetCode
• MySQL storage engines - w3resource
• 数据库存储引擎
• InnoDB 逻辑存储结构
• 浅入浅出 MySQL 与 InnoDB