📃个人主页: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 存储引擎比较

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

🍐3.3 具体应用场景

  • 高并发事务处理

    • 选择:InnoDB 或 NDB,支持行级锁和事务,适合高频写入场景。
  • 读多写少的应用

    • 选择:MyISAM,在只读查询中表现优异,适合静态内容。
  • 临时数据或会话信息

    • 选择:MEMORY,快速存取,适合缓存和会话存储。
  • 分布式系统

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

🍇3.4 性能优化

  • 索引使用

    • 根据数据访问模式,合理设计索引以提高查询性能。
  • 表设计:

    • 选择合适的列类型和长度,优化存储空间和访问速度。
  • 配置调整

    • 根据具体需求调整存储引擎的参数配置,以提升性能。

📖总结

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

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

Logo

欢迎加入西安开发者社区!我们致力于为西安地区的开发者提供学习、合作和成长的机会。参与我们的活动,与专家分享最新技术趋势,解决挑战,探索创新。加入我们,共同打造技术社区!

更多推荐