从 InnoDB 到 Memory：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 存储引擎比较

🍐3.3 具体应用场景

• 高并发事务处理：
  • 选择：InnoDB 或 NDB，支持行级锁和事务，适合高频写入场景。
• 读多写少的应用：
  • 选择：MyISAM，在只读查询中表现优异，适合静态内容。
• 临时数据或会话信息：
  • 选择：MEMORY，快速存取，适合缓存和会话存储。
• 分布式系统：
  • 选择：NDB，支持自动分片和高可用性，适合大规模在线应用。

🍇3.4 性能优化

• 索引使用：
  • 根据数据访问模式，合理设计索引以提高查询性能。
• 表设计：
  • 选择合适的列类型和长度，优化存储空间和访问速度。
• 配置调整：
  • 根据具体需求调整存储引擎的参数配置，以提升性能。

📖总结

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

💞 💞 💞那么本篇到此就结束，希望我的这篇博客可以给你提供有益的参考和启示，感谢大家支持！！！祝大家天天开心