从 InnoDB 到 Memory：MySQL 存储引擎的多样性

选择合适的存储引擎取决于具体的应用需求、性能目标和数据特性。在决策时，需要综合考虑数据的持久性、并发性能、事务支持及其它特性，以确保所选引擎能有效满足应用需求。💞 💞 💞！！祝大家天天开心。

island1314

3476人浏览 · 2024-09-19 19:45:43

island1314 · 2024-09-19 19:45:43 发布

📃个人主页：island1314

🔥个人专栏：MySQL学习

⛺️ 欢迎关注：👍点赞 👂🏽留言 😍收藏 💞 💞 💞

🚀前言

🔥事务存储引擎是数据库管理系统中负责数据存储、检索和事务处理的组件。它们支持事务的四个基本特性，通常被称为 ACID 属性：

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，不会留下中间状态。
一致性（Consistency）：事务必须使数据库从一个一致性状态转换到另一个一致性状态。
隔离性（Isolation）：事务的执行不应受到其他事务的干扰。
持久性（Durability）：一旦事务提交，其结果就是永久性的。

🔥MySQL 支持多种事务存储引擎，其中最常见的是 InnoDB 和 NDB（也称为 MySQL Cluster）。此外，还有其他一些如 Archive、Memory、MyISAM等存储引擎，它们在特定场景下也有应用。我们这篇文章主要讲的就是 InnoDB、MyISAM、Memory、NDB

1. 存储引擎的相关指令

☘️1.1 查询建表语句

--语法 
show create table 表名; 

-- 比如查询 student 表的建表语句
show create table student;
-- 结果显示
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table
                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

🍀1.2 查询当前数据库支持的引擎类型

-- 语法
show engines;

-- 结果显示 当前数据库下引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

🌿1.3 创建表，并指定存储引擎

-- 语法 (注：默认存储引擎为 InnoDB)
create table 表名（
	字段1 字段1 类型 [comment 字段1注释]
	......
	字段n 字段n 类型 [comment 字段n注释]
）ENGINE = INNNODB [COMMENT 表注释];

-- 案例：

-- 1、创建表 My_myisam，并且指定MyISAM存储引擎
create table My_myisam(
	id int,
	name varchar(20)
)engine = MyISAM;

--1、创建表 My_memory，并且指定Memory存储引擎
create table My_Memory(
	id int,
	name varchar(20)
)engine = Memory;

2. 存储引擎的种类

🥝2.1 InnnoDB

💢InnoDB 是一种兼顾可靠性和高性能的通用存储引擎，在 MySQL5.5之后，成为了 MySQL 默认的存储引擎，广泛用于需要高可靠性和性能的应用。

特点

（1）事务支持：

InnoDB 完全支持 ACID 事务，确保数据的一致性和完整性。

（2）行级锁：

相比于表级锁定，行级锁定可以减少锁冲突，提高并发性能。InnoDB引擎采用了细粒度的行级锁定策略，使得多个事务可以并发访问不同的数据行（高并发）。

（3）外键约束：

支持外键约束，可以确保数据的引用完整性，防止无效的数据引用

（4）崩溃恢复：

具有自动崩溃恢复功能，使用重做日志和撤销日志保证数据安全。通过重做（redo）日志 和 撤销（undo）日志，InnoDB可以在系统崩溃后恢复数据到一致状态，确保数据的持久性和完整性。

（5）自适应哈希索引：

提升查询性能，自动在内存中维护哈希索引。

文件结构

InnoDB 使用多种文件来管理数据和索引，主要包括：

（1）数据文件（.ibd）：

存储表数据和索引。每个表可以有独立的 .ibd 文件（如果启用了文件存储），来存储该表的表结构（frm、sdi）、数据和索引。

（2）共享表空间文件（ibdata）：

默认情况下，所有 InnoDB 表的数据和索引存储在一个共享的 ibdata 文件中。

（3）日志文件（ib_logfile）：

存储重做日志，确保数据在崩溃时可以恢复。

（4）配置文件（my.cnf）：

包含 InnoDB 的配置参数，如缓冲池大小、日志文件大小等。

适用范围

InnoDB 适用于多种场景，特别是：

（1）在线交易处理（OLTP）：

适合高并发、频繁插入、更新和删除操作的应用。

（2）需要数据完整性和一致性的应用：

如金融、电子商务系统，要求强事务支持和外键约束。

（3）复杂查询和大数据量：

支持高效的查询性能，适合需要处理大量数据的应用。

（4）数据恢复和安全性要求高的场景：

由于其崩溃恢复机制，适合对数据安全性要求严格

总体而言：InnoDB 是一个通用、高效的存储引擎，在性能、可靠性和数据一致性方面表现出色，能够满足各种应用需求，成为了众多企业和开发者的首选。

🥑2.2 NDB

💢NDB（或 NDB Cluster）是 MySQL 的一种分布式事务存储引擎，主要用于高可用性和高可扩展性的应用场景。它支持自动分片、负载均衡和数据冗余，使其特别适合大规模的在线事务处理（OLTP）应用。

特点

（1）分布式架构：

NDB 采用分布式架构，数据可以存储在多个节点上，提高了可扩展性和可用性。

（2）高可用性：

支持数据冗余，通过数据复制确保高可用性，节点故障时可以自动切换。

（3）支持事务：

完全支持 ACID 事务特性，确保数据一致性和完整性。

（4）自动分片：

数据可以在多个数据节点之间自动分片，能够处理大量的并发请求。

（5）实时数据访问：

适合需要实时数据处理的应用，如金融和电信行业。

（6）支持外键和约束：

支持外键和多种数据完整性约束。

文件结构

NDB 存储引擎的文件结构与传统的 MyISAM 或 InnoDB 不同，因为它采用的是分布式存储模型：

（1）数据节点：

数据存储在多个数据节点上，每个节点都可以存储部分数据，并可以并行处理请求。

（2）管理节点：

管理集群的状态和配置，确保数据节点之间的协调。

（3）SQL 节点：

负责接收客户端的 SQL 请求，并将其转发到适当的数据节点。

适用范围

NDB 存储引擎适合以下场景：

（1）高并发 OLTP 应用：

适用于对性能和可扩展性要求高的在线交易处理系统。

（2）分布式系统：

在分布式环境中，需要高可用性和负载均衡的应用。

（3）实时数据处理：

适合需要快速访问和处理实时数据的场景，如电信计费和金融交易。

总体而言：NDB 事务存储引擎以其分布式架构、高可用性和强事务支持，成为大规模、高性能应用的理想选择。尽管其配置和管理相对复杂，但在高并发和大规模数据处理的场景中，NDB 提供了强大的功能和灵活性。

🍉2.3 MyISAM

💢MyISAM 是 MySQL 的一种非事务性存储引擎，主要用于需要快速读取、轻量级存储或者读取频繁、更新较少的应用场景。它在 MySQL 早期版本中是默认的存储引擎，适合用于数据读多写少的应用和一些对事务完整性要求不高的系统。

特点

（1）高性能读取：

MyISAM 优化了读取性能，适合执行大量的 SELECT 查询操作。

（2）表级锁：

与InnoDB引擎的行级锁定不同，MyISAM引擎采用表级锁定机制。这意味着在对表进行写操作时，会锁定整个表，阻止其他线程对该表进行读写操作。虽然这种锁定策略在某些高并发场景下可能导致性能瓶颈，但在读多写少的场景中，表级锁定可以简化锁定机制，提高性能。

（3）不支持事务：

与InnoDB引擎相比，MyISAM引擎不支持ACID事务。这意味着在MyISAM引擎中执行的数据操作不会受到事务的原子性、一致性、隔离性和持久性的保护。因此，在需要保证数据完整性和一致性的场景下，MyISAM引擎可能不是最佳选择。

（4）全文索引：

MyISAM引擎支持全文索引，使得它非常适合于文本搜索和匹配等场景。通过全文索引，用户可以快速地在大量文本数据中查找特定的关键词或短语。

（5）表结构简单：

文件结构清晰，易于管理。

文件结构

MyISAM 的文件结构主要包括：

（1）数据文件（.MYD）：

存储实际的数据。

（2）索引文件（.MYI）：

存储表的索引信息，包括主键索引和辅助索引。

（3）表定义文件（.frm）：

存储表的定义和结构信息。

适用范围

尽管MyISAM引擎在某些方面不如InnoDB引擎强大，但在特定场景下，它仍然具有独特的优势：

（1）只读或大量读操作的应用：

对于只需要进行大量读操作而很少进行写操作的应用场景，MyISAM引擎的表级锁定和快速查询性能使其成为理想选择。例如，一些静态的网站内容、日志数据或只读的数据仓库等。

（2）数据分析：

适合需要快速检索的分析型应用。

（3）全文搜索：

对于需要进行文本搜索的应用，MyISAM 提供良好的支持。例如，博客系统、新闻网站或文档管理系统等。

（4）小型应用：

在不需要复杂事务和外键约束的小型应用中，MyISAM 也非常合适。

（5）简单的数据备份和恢复：

由于MyISAM引擎将数据和索引分开存储，因此在备份和恢复数据时，可以分别处理数据文件和索引文件，提高备份和恢复的效率和灵活性。

总体而言：MyISAM引擎作为MySQL的一个重要存储引擎，虽然在一些方面不如InnoDB引擎强大，但是MyISAM可以适用于对性能要求高，但对数据完整性和事务支持要求不高的场景。

🍋‍🟩2.4 Memory

💢MEMORY 存储引擎是 MySQL 的一种基于内存的数据存储引擎，提供高速的数据访问和操作。它将所有数据存储在内存中，因此在读取和写入方面非常快，适合临时数据存储和快速访问的场景。

特点

（1）高速性能：

所有数据存储在内存中，读写速度极快，适合对性能要求高的应用。

（2）表级锁：

使用表级锁，这可能会在并发写入时导致性能瓶颈。

（3）非持久性：

数据在数据库重启或崩溃时会丢失，因此适合临时数据或缓存使用。

（4）支持索引：

支持主键索引和唯一索引，但不支持外键，默认是hash索引。

（5）内存使用：

可以设置每个表的最大内存使用量，通过 MAX_ROWS 和 AVG_ROW_LENGTH 来优化。

文件结构

MEMORY 存储引擎的文件结构相对简单，主要包括：

表定义文件（.frm）：存储表的定义信息，包括列类型和其他结构信息。

实际数据并不存储在磁盘文件中，而是完全驻留在内存中。

适用范围

MEMORY 存储引擎适合以下场景：

（1）临时数据存储：

适合需要快速读写的临时表，如缓存或会话信息。

（2）高频查询：

适用于频繁读取的场景，如统计数据或快速检索。

（3）测试和开发：

开发和测试过程中，可以快速验证数据操作的逻辑。

总体而言：MEMORY 存储引擎通过在内存中存储数据，提供了极快的访问速度。尽管它不适合长期存储和持久性数据，但在特定应用场景中，其高效性和便利性使其成为一个有价值的选择。

3. 存储引擎的选择

🔥选择适合的存储引擎是数据库设计中至关重要的步骤，影响性能、可扩展性和数据完整性。以下是一些关键因素和不同存储引擎的比较，帮助您做出更明智的选择。

🍎3.1 需求分析

数据持久性：

如果需要数据持久性，选择支持事务的引擎（如 InnoDB）。

性能要求：

高并发读写需求的场景可能适合使用 MEMORY 引擎，快速访问但不持久化数据。

事务支持：

需要支持 ACID 特性的应用应选择 InnoDB 或 NDB 引擎。

🍍3.2 存储引擎比较

特性	InnoDB	MyISAM	MEMORY	NDB
事务支持	支持（ACID）	不支持	不支持	支持（ACID）
锁机制	行级锁	表级锁	表级锁	行级锁
外键支持	支持	不支持	不支持	支持
数据冗余	支持（通过备份和复制）	不支持	不支持	支持（自动分片和复制）
适用场景	高并发 OLTP 应用	只读和小型表	临时数据和缓存	分布式和高可用性场景

🍐3.3 具体应用场景

高并发事务处理：

选择：InnoDB 或 NDB，支持行级锁和事务，适合高频写入场景。

读多写少的应用：

选择：MyISAM，在只读查询中表现优异，适合静态内容。

临时数据或会话信息：

选择：MEMORY，快速存取，适合缓存和会话存储。

分布式系统：

选择：NDB，支持自动分片和高可用性，适合大规模在线应用。