MySQL存储引擎篇

目录

什么是存储引擎

MySQL种有哪些存储引擎?

三大存储引擎的特点

1.InnoDB 

2.MyISAM

3. Memory

4.三大存储引擎的对比

3.存储引擎相关语句

4.三个存储引擎表结构文件

1.InnoDB

2.MyISAM

3.Memory

5.总结

---

什么是存储引擎

存储引擎是数据库中的一个部件，负责处理数据的存储和检索。我们可以把存储引擎看作是数据库中的“搬运工”，它决定了数据如何被存储、读取、更新和删除。就像我们在搬家时需要选择合适的工具，数据库也需要根据不同的应用场景选择不同的存储引擎来完成相应的任务。比如，有些存储引擎适合高并发和大量写操作的场景，有些则适合大量读操作的场景。

在 SQL 中，存储引擎是指数据库管理系统中负责管理数据存储和检索的组件。存储引擎决定了如何存储、读取、更新和删除数据。SQL 支持多种存储引擎，每种存储引擎都有其自身的特点和优缺点。

---

MySQL种有哪些存储引擎?

MySQL 支持多种存储引擎，以下是一些常见的存储引擎：

1. InnoDB：这是 MySQL 的默认存储引擎，支持事务处理和行级锁定，并且具有良好的并发性能和数据完整性，适合用于 OLTP（联机事务处理）场景。

2. MyISAM：这是 MySQL 的另一种常用的存储引擎，不支持事务处理，但具有较高的读取性能，适合用于 OLAP（联机分析处理）场景。

3. Memory：这是 MySQL 的一种内存存储引擎，所有的数据都存储在内存中，因此具有非常高的读写性能，但是在数据库重启时会丢失所有数据。

4. Archive：这是 MySQL 的一种归档存储引擎，适用于需要大量数据归档和存储的场景，具有较高的数据压缩比例。

此外，还有其他一些较少使用的存储引擎，如CSV、Blackhole、Federated等。需要根据具体的应用场景和需求来选择合适的存储引擎。

---

三大存储引擎的特点

1.InnoDB 

InnoDB 是 MySQL 中最常用的存储引擎之一，也是 MySQL 默认的事务存储引擎。以下是 InnoDB 存储引擎的一些特点：

1. 支持事务处理：InnoDB 支持 ACID（原子性、一致性、隔离性和持久性）事务，保证了数据的完整性和一致性。

2. 行级锁定：InnoDB 使用行级锁定机制，避免了传统数据库中的锁表问题，使得并发操作性能更好。

3. 支持外键：InnoDB 支持外键约束，可以保证数据的一致性和完整性。

4. 支持MVCC（多版本并发控制）：InnoDB 使用 MVCC 技术来处理并发操作，支持非阻塞的读操作。

5. 高性能：InnoDB 支持缓存机制、多线程处理和高效的磁盘 I/O，具有较高的性能和扩展性。

6. 支持热备份：InnoDB 支持热备份，可以在线备份数据。

需要注意的是，由于 InnoDB 存储引擎本身的复杂性，相比 MyISAM 存储引擎，InnoDB 在一些特定场景下的读取性能可能稍逊一筹。因此，在选择存储引擎时需要根据具体的应用场景和需求来进行评估。

2.MyISAM

MyISAM 是 MySQL 中最常用的存储引擎之一，其主要特点如下：

1. 不支持事务处理：MyISAM 存储引擎不支持事务处理，因此在写入操作的过程中，如果发生了错误，可能会导致数据的不一致性。

2. 表级锁定：MyISAM 使用表级锁定机制，对于多个并发写操作会出现锁表的问题，影响并发性能。

3. 高效的读取性能：MyISAM 存储引擎采用的是非聚集索引（B+ 树索引），因此对于大量的读取操作，具有非常高效的性能。

4. 不支持外键：MyISAM 存储引擎不支持外键约束，因此在维护数据的完整性和一致性方面需要开发人员自行控制。

5. 支持全文搜索：MyISAM 存储引擎支持全文搜索，可以对文本内容进行高效的检索。

6. 不支持热备份：MyISAM 存储引擎不支持热备份，需要停机备份数据。

需要注意的是，由于 MyISAM 存储引擎不支持事务处理，因此在对数据的完整性和一致性有严格要求的场景下，不宜采用 MyISAM 存储引擎。同时，MyISAM 存储引擎的表级锁定机制在高并发的写操作场景下也会带来性能问题，因此需要谨慎选择。

3. Memory

Memory 存储引擎（也称为 Heap 存储引擎）是 MySQL 中一种特殊的存储引擎，其主要特点如下：

1. 数据存储在内存中：Memory 存储引擎的数据是存储在内存中的，因此读写操作都非常快速。

2. 不支持持久化：Memory 存储引擎不支持数据持久化，一旦 MySQL 服务器关闭，所有的数据都会丢失。

3. 不支持 TEXT 和 BLOB 类型：Memory 存储引擎不支持 TEXT 和 BLOB 类型的数据，因为这些数据类型的大小可能超过了 Memory 存储引擎的限制。

4. 表级锁定：Memory 存储引擎使用表级锁定机制，因此在高并发写操作场景下，会有锁表的问题。

5. 支持 HASH 和 BTREE 索引：Memory 存储引擎支持 HASH 和 BTREE 索引，因此对于索引查询操作，具有非常高效的性能。

6. 支持自动清空：Memory 存储引擎支持自动清空功能，可以在每次 MySQL 服务器启动时自动清空所有数据，防止数据泄露。

需要注意的是，由于 Memory 存储引擎的数据存储在内存中，因此存储空间比较有限，不适合存储大量的数据。同时，由于不支持数据持久化，一旦 MySQL 服务器关闭，所有的数据都会丢失，因此在需要持久化存储数据的场景下，不适合采用 Memory 存储引擎。

4.三大存储引擎的对比

InnoDB，MyISAM和Memory是MySQL中最常用的三种存储引擎。它们各自具有不同的特点和适用场景，下面是它们的主要特点对比：

1. InnoDB：

• 支持事务处理和外键约束，这使得它非常适合处理需要ACID事务支持的应用程序。
• 支持行级锁，可以提高多个用户同时访问同一数据时的并发性能。
• 支持高级索引和查询优化器，可以提高查询性能。
• 支持表空间，可以优化磁盘空间的利用率。
• 适合处理大型、高并发的数据访问场景。

1. MyISAM：

• 不支持事务处理和外键约束，这使得它对于非事务性应用程序来说非常适合。
• 支持全文本索引和压缩表格，可以提高查询性能和降低磁盘空间的使用。
• 支持表级锁，这意味着在高并发访问时可能会出现锁等待。
• 适合处理插入、更新、删除频率较高但查询较少的应用程序。

1. Memory：

• 将表数据存储在内存中，因此读写速度非常快。
• 不支持持久化存储，数据在MySQL服务器关闭后会丢失。
• 不支持大多数的MySQL数据类型，因此仅适用于某些特定的数据存储需求。
• 适合处理需要频繁读写的临时表或缓存数据的应用程序。

综上所述，选择合适的存储引擎应该根据应用程序的需求和特点来进行评估和选择。如果应用程序需要ACID事务支持，或者需要高并发和高性能查询支持，那么InnoDB可能是最好的选择；如果应用程序是非事务性的，且需要支持全文本索引和压缩表格，那么MyISAM可能是更好的选择；如果应用程序需要频繁读写的临时表或缓存数据，那么Memory可能是最好的选择。

 

---

3.存储引擎相关语句

1.查询当前数据库支持的存储引擎

-- 查看数据库支持的存储引擎
show engines ;

 

2.建立表结构时指定存储引擎

-- 创建表 指明某个存储引擎
create table my_test(
    id int primary key auto_increment,
    name varchar(20)
)engine = MyISAM ;

---

4.三个存储引擎表结构文件

1.InnoDB

在InnoDB存储引擎中，每个表都有一个.ibd文件，用于存储该表的数据和索引。.ibd文件是InnoDB表空间的一部分，包含了表的数据和索引的数据。

在默认情况下，MySQL服务器使用系统默认的数据目录来存储所有表的数据和索引文件，可以通过查看my.cnf文件中datadir参数的值来确定数据目录的位置。在数据目录下，每个数据库都有一个独立的目录，其中包含了该数据库中所有表的数据和索引文件。对于使用InnoDB存储引擎的表，每个表都有一个对应的.ibd文件，存储在该数据库的目录下。

除了.ibd文件外，InnoDB表还有一个.frm文件，用于存储表的定义，包括列名、列类型、索引等信息。.frm文件存储在与.ibd文件相同的目录下。

此外，InnoDB存储引擎还有一个共享表空间文件ibdata1，用于存储系统表空间和共享表空间的数据。系统表空间包括InnoDB数据字典，共享表空间包含了未使用单独的.ibd文件存储的表的数据和索引。在某些情况下，如果多个InnoDB表使用了共享表空间，则它们的数据和索引都会存储在ibdata1文件中。

总之，InnoDB存储引擎的表的文件主要包括.ibd文件、.frm文件和共享表空间文件ibdata1。每个表都有一个对应的.ibd文件用于存储表的数据和索引。

2.MyISAM

在MySQL中，MyISAM存储引擎的表文件主要由三个部分组成：.frm文件、.MYD文件和.MYI文件。

1. .frm文件： .frm文件是MyISAM表的定义文件，包含了表的结构信息，如列名、数据类型、约束条件等。每个MyISAM表都有一个独立的.frm文件，存储在对应数据库的数据目录下。

2. .MYD文件： .MYD文件是MyISAM表的数据文件，存储了表中的实际数据。每个MyISAM表都有一个独立的.MYD文件，存储在对应数据库的数据目录下。

3. .MYI文件： .MYI文件是MyISAM表的索引文件，存储了表的所有索引信息。每个MyISAM表都有一个独立的.MYI文件，存储在对应数据库的数据目录下。

总的来说，MyISAM存储引擎的表文件包含了表的结构定义、数据和索引三个部分，分别存储在对应的.frm、.MYD和.MYI文件中。因此，在备份和恢复MyISAM表时，需要同时备份和恢复这三个文件。需要注意的是，MyISAM存储引擎不支持事务处理和行级锁定，因此不适合用于高并发的事务性应用程序。

3.Memory

在MySQL中，Memory存储引擎的表不像InnoDB和MyISAM那样将表数据和索引存储在文件中，而是将表的数据存储在内存中。因此，Memory存储引擎的表不需要磁盘IO操作，具有非常高的查询和插入速度。

虽然Memory存储引擎的表数据存储在内存中，但是为了保证表的可持久性，MySQL仍然会将表定义信息存储在.frm文件中。该文件包含了表的结构定义信息，如列名、数据类型、索引等，存储在对应数据库的数据目录下。

需要注意的是，由于Memory存储引擎的表数据存储在内存中，所以当MySQL服务器重启或崩溃时，表中的数据会丢失。为了避免数据丢失，可以使用MySQL的热备份工具mysqldump来定期备份Memory存储引擎的表数据，或者将Memory存储引擎的表数据复制到磁盘上的MyISAM或InnoDB表中。

---

5.总结

总的来说，在MySQL中，不同的存储引擎对应着不同的表文件类型和存储方式。

• InnoDB存储引擎的表文件包含了表的结构定义、数据和索引三个部分，存储在.ibd文件中。支持事务处理和行级锁定，适用于高并发的事务性应用程序。
• MyISAM存储引擎的表文件包含了表的结构定义、数据和索引三个部分，分别存储在.frm、.MYD和.MYI文件中。不支持事务处理和行级锁定，适用于只有少量写操作、大量读操作的应用程序。
• Memory存储引擎的表数据存储在内存中，不像InnoDB和MyISAM那样将表数据和索引存储在文件中。需要将表定义信息存储在.frm文件中，支持高速查询和插入，但是当MySQL服务器重启或崩溃时，表中的数据会丢失。适用于临时存储和高速缓存。

---