mysql的各种存储引擎

---

前言

MySQL 支持多种存储引擎，每种引擎都有其独特的功能和适用场景。存储引擎是指数据库管理系统用来存储、检索和索引数据的方法。不同的存储引擎提供了不同的特性，例如事务支持、并发控制、锁定粒度等。以下是 MySQL 中常用的几种存储引擎及其特点：

1. InnoDB

InnoDB 是 MySQL 默认的存储引擎，支持事务（ACID）、外键约束、行级锁定和 MVCC（多版本并发控制）。InnoDB 被广泛应用于需要高可靠性的应用程序中，尤其是在需要事务支持的情况下。

特点

1. 支持事务处理（ACID）。

• 原子性（Atomicity）：事务作为一个整体被执行，包含在事务中的所有操作要么都完成，要么都不完成。
• 一致性（Consistency）：事务开始前和结束后，数据库都必须处于一致性状态。
• 隔离性（Isolation）：并发执行的事务彼此隔离，一个事务的执行不应影响另一个事务。
• 持久性（Durability）：一旦事务提交，它对数据库的影响将是永久的，即使系统发生故障也是如此

2. 支持外键约束。
   InnoDB 支持外键约束，这有助于维护数据库的一致性和完整性。通过定义外键，可以确保引用完整性，即在一个表中引用的记录在另一个表中确实存在。
3. 行级锁定，有利于高并发应用。
   InnoDB 使用行级锁定机制，这允许在并发环境中提高数据的并发访问能力。行级锁只锁定实际涉及的行，而不会锁定整个表，从而减少了锁争用的情况。
4. MVCC（多版本并发控制）
   InnoDB 使用多版本并发控制来支持读取未提交的事务数据而不干扰正在进行的事务。这意味着多个事务可以同时读取同一份数据的不同版本，而不会互相阻塞。
5. 索引
   InnoDB 使用 B+ 树作为索引结构，支持唯一索引和非唯一索引。此外，InnoDB 还支持空间索引（用于地理信息系统）。
6. BufferPool缓冲池
   InnoDB 使用缓冲池来缓存表和索引的数据，以减少磁盘 I/O 操作，提高查询性能。
   Buffer Pool 缓存池的主要作用是在内存中缓存数据页，从而减少对磁盘的 I/O 访问次数。

BufferPool缓冲池

Buffer Pool 的主要功能

• 数据页缓存：当 InnoDB 需要读取某个数据页时，会首先检查该页面是否已经在 Buffer Pool 中。如果在 Buffer Pool 中找到了所需的数据页，则直接从内存中返回；否则，从磁盘读取到内存中，并加入到 Buffer Pool 中。
• 脏页管理：如果需要修改某个数据页上的数据（例如，插入、删除或更新），InnoDB 会先在内存中修改这个数据页，此时这个数据页就变成了脏页（dirty page）。脏页会在适当的时候被刷新回磁盘，这个过程称为 flush。
• LRU 列表：为了管理 Buffer Pool 中的数据页，InnoDB 使用了一种称为最近最少使用（Least Recently Used，LRU）的算法。当 Buffer Pool 的空间不足时，LRU 算法会选择最近最少使用的页面淘汰出 Buffer Pool。

Buffer Pool 的配置参数：

MySQL 中可以通过一些配置参数来控制 Buffer Pool 的行为：

• innodb_buffer_pool_size：设置 Buffer Pool 的大小。这是最重要的参数之一，因为它直接影响了 Buffer Pool 的容量。通常建议设置为服务器物理内存的 80% 左右，但具体数值取决于工作负载和其他内存需求。
• innodb_buffer_pool_instances：设置 Buffer Pool 的实例数量。增加这个参数的数量可以提高并发性能，特别是在多处理器或多核系统上。
• innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup：这两个参数分别控制是否在 MySQL 关闭时将 Buffer Pool 内容写入到磁盘文件中，以及在 MySQL 启动时从磁盘文件加载 Buffer Pool 内容。

合理的 Buffer Pool 大小对于优化 InnoDB 数据库的性能至关重要。可以通过监控系统性能指标（如缓冲池命中率、磁盘 I/O 操作次数等）来调整 Buffer Pool 的大小和其他相关参数。

Buffer Pool 的内部结构

1. 数据页
   Buffer Pool 中的基本单位是数据页（data page），每个数据页通常固定大小，默认情况下为 16KB，但可以通过 innodb_page_size 参数进行配置。用于缓存数据页和索引页，以减少磁盘 I/O 操作。

2. LRU 列表

   • LRU（Least Recently Used）列表是一种缓存替换策略，用于记录哪些Buffer Pool中的数据页最近被访问过。
   • 新加入的数据页会被放置到 LRU 列表的尾部，而最近访问过的数据页则被移动到 LRU 列表的头部。
   • LRU 列表是一个双向链表，其中每个节点代表 Buffer Pool 中的一个数据页。
   • InnoDB 使用 LRU 列表来决定哪些数据页应该被保留，哪些可以被淘汰。当 Buffer Pool 的空间不足时，InnoDB 会从 LRU 列表的尾部选择最近最少使用的页面进行淘汰。

LRU 列表分为两部分：

• Old 部分：当一个页面被访问时，它会从 Old 部分移到 Young 部分。
• Young 部分：这部分的页面最近被访问过。如果页面在一定时间内没有再次被访问，它会从 Young 部分移动到 Old 部分。

3. Free 列表
   Free 列表（Free List）是一个包含未被使用的数据页的链表。当 Buffer Pool 需要分配新的内存页来缓存数据时，它会首先检查 Free 列表中是否有可用的空闲页面。
   如果有，则从 Free 列表中取出一个数据页分配给请求，否则 Buffer Pool 会触发淘汰机制，从 LRU（Least Recently Used）列表中淘汰掉一部分最近最少使用的页面，腾出空间来满足新的缓存请求。

Free 列表与 LRU 列表的关系

• LRU 列表：记录了 Buffer Pool 中所有已分配的数据页，根据数据页的访问频率和时间排序。
• Free 列表：记录了 Buffer Pool 中未被分配的数据页。

当 Buffer Pool 需要分配新的数据页时，它会优先从 Free 列表中获取，如果 Free 列表为空，则会从 LRU 列表中淘汰页面来腾出空间。当数据页被释放时，它们会被放回 Free 列表。
Free 列表的存在使得 Buffer Pool 能够有效地管理空闲内存，避免频繁地进行磁盘 I/O 操作。通过维护一个 Free 列表，InnoDB 可以快速地分配和释放数据页，从而提高整体性能。

4. Flush 列表
   Flush 列表（也称为脏页列表）包含了已经被修改但尚未写回到磁盘的数据页。InnoDB 会定期将脏页写回到磁盘上，以保持数据的一致性。

数据页在 Buffer Pool 中的状态转换

Free 列表 → LRU 列表：当一个数据页从 Free 列表中被分配出来后，它会被插入到 LRU 列表中。
LRU 列表 → Free 列表：当数据页不再需要被缓存时，它会被从 LRU 列表中移除，并放回 Free 列表中。
LRU 列表 → Disk：当 Buffer Pool 需要释放空间时，从 LRU 列表中选择的数据页可能会被写回到磁盘上，然后放回 Free 列表。

2. MyISAM

MyISAM 是 MySQL 较早使用的默认存储引擎，不支持事务处理，但是提供了高速的存储和检索功能。MyISAM 通常用于读取密集型的应用场景。

特点

• 不支持事务处理。
• 支持全文索引。
• 表级锁定（整个表被锁定），并发性能较差。
• 支持压缩表（COMPRESSED TABLES）。
• 支持自动增量。

innodb与myisam引擎之间的区别

1.myisam没有事务
2.myisam没有行锁，只有表锁(使用不当会导致锁表)
mysql8去除了myisam引擎
3.innoDB存在事务acid原则
4.innoDB有行锁
5.innoDB使用聚集索引（主键索引）方式进行数据存储，myisam使用非聚集索引叶子节点存储的是数据的内存地址。
6.innoDB支持外键关系保证数据完整性（不常用）

3. MEMORY

MEMORY 引擎将所有数据存储在内存中，提供极高的读写速度，但是不适合存储大量的数据或持久化数据。适用于临时表和高速缓存。

特点

• 数据存储在内存中，速度快。
• 支持哈希索引。
• 适合小规模数据和临时表。
• 数据在 MySQL 重启后丢失。

4. ARCHIVE

ARCHIVE 引擎主要用于数据归档，提供极高的压缩率，只支持 SELECT 和 INSERT 操作。

特点

• 极高的压缩率。
• 只支持 SELECT 和 INSERT 操作。
• 适合存储大量很少查询的历史数据。

5. NDBCluster

NDBCluster 是 MySQL 用于集群环境的存储引擎，提供了高可用性和可扩展性。

特点

• 支持分布式的集群环境。
• 数据和索引存储在内存中。
• 提供高可用性和故障转移机制。
• 适合读写密集型应用。

6. FEDERATED

FEDERATED 存储引擎允许在本地 MySQL 服务器上访问远程 MySQL 服务器上的数据。

特点

• 透明地访问远程数据。
• 需要远程服务器的支持。
• 安全性问题需要注意。

7. CSV

CSV 引擎将数据存储为纯文本文件，每行一条记录，字段之间用逗号分隔。

特点

• 简单的数据存储格式。
• 便于导入导出数据。
• 不支持索引。

总结

选择哪种存储引擎取决于你的应用需求。如果需要支持事务处理并且对数据的一致性要求较高，那么 InnoDB 是最合适的选择。如果你的应用主要是读取操作，并且不需要事务支持，那么 MyISAM 就是一个不错的选择。对于临时数据存储或者需要极高性能的场景，MEMORY 引擎可能更适合。而对于历史数据归档，可以考虑使用 ARCHIVE 引擎。

---