【MySQL | 第七篇】一文读懂MySQL存储引擎（InnoDB、MyISAM、Memory）、MVCC（易懂、明白）

文章目录

7.一文读懂MySQL存储引擎

• MySQL存储引擎一共有InnoDB、MyISAM、Memory等常见的存储引擎；
• 下面将从存储结果、事务支持、行/表级锁、外键支持、以及关键特性 共五个角度阐述上面的存储引起；

7.1InnoDB存储引擎

7.1.1概述

InnoDB 是 MySQL数据库系统中最常用的存储引擎之一，尤其在事务处理、并发控制及数据可靠性方面表现突出。以下是对 InnoDB 存储引擎的主要特点和功能的概述：

1. 存储结构：InnoDB 存储引擎将数据按照页（Page）的方式组织在磁盘上，并且采用B+树作为索引结构，使得数据检索效率相对较高。
2. 事务支持：InnoDB 提供完全的 ACID（原子性、一致性、隔离性、持久性）事务支持，适用于需要高可靠性和数据一致性的应用场景，如银行交易、电子商务等。
3. 行级锁定：InnoDB 实现了行级锁定机制，这意味着在执行事务时只锁定受影响的行，而非整个表，这大大提高了并发环境下多用户读写数据的性能。
4. 外键约束：支持外键约束，确保数据的一致性和参照完整性，防止非法的数据操作。
5. 缓冲池：内存管理中，InnoDB 使用了一个称为缓冲池的内存区域，用于缓存表数据和索引，减少磁盘 I/O 操作，提高数据读写的效率。
6. 崩溃恢复：提供完善的崩溃恢复机制，包括 redo 日志（重做日志）和 undo 日志（撤销日志），保证即使在系统崩溃后也能恢复到崩溃前的一致状态。
7. 内存结构：InnoDB 内部有多个内存区域，如redo log buffer、undo logs、buffer pool manager等，用于管理事务、缓存数据以及辅助事务处理过程。
8. 关键特性：- 多版本并发控制（MVCC）：InnoDB 采用 MVCC 来实现高并发下的读写操作，允许在不阻塞读操作的情况下进行写操作，提升了并发性能，降低了死锁概率。- 插入缓冲（Insert Buffer）：优化对非唯一二级索引插入操作的性能。- 两次写（Double Write）：用于保护数据页免受意外宕机造成的数据损坏。- 自适应哈希索引（Adaptive Hash Index）：自动为经常使用的查询创建哈希索引，提高查询速度。- 异步IO（Async I/O）：提高磁盘I/O性能，减少等待时间。

7.1.2体系架构

1. InnoDB存储引擎的体系架构主要由内存结构、磁盘存储、线程与并发控制、数据阻止结构、以及外部接口共五个方面组成
2. 内存结构： - **Buffer Pool (缓冲池)**：它是 InnoDB 的核心组件之一，用于缓存表的数据页和索引页，减少对磁盘 I/O 的访问次数。缓冲池中的数据页在事务提交时会根据需要刷新到磁盘。- Change Buffer（变更缓冲区）：对于非唯一二级索引的插入操作，如果所在的数据页尚未加载到缓冲池中，InnoDB 会将这部分修改暂存在变更缓冲区中，待后续数据页加载到缓冲池时再合并写入。- Adaptive Hash Index（自适应哈希索引）：InnoDB 会根据查询模式自动生成哈希索引，加快某些查询的性能。- Log Buffer（日志缓冲区）：存储待写入重做日志文件（redo log）的事务日志记录，以确保事务的持久性和崩溃恢复能力。
3. 磁盘存储： - 表空间（Tablespaces）：InnoDB 使用表空间作为逻辑上的数据容器，每个表空间可以包含多个数据文件。系统表空间存放全局数据，用户表空间存放用户数据和索引。- 重做日志文件（Redo Log）：记录了对数据的更改操作，以支持事务的持久性和崩溃恢复。- 回滚段（Undo Logs）：用于实现事务的原子性和一致性，存储了事务回滚所需的历史版本数据。
4. 线程与并发控制： - 后台线程：包括 master thread、IO thread（用于处理redo log刷盘）、purge thread（用于清理不再需要的undo日志）等，共同维护数据库的正常运行和数据一致性。- 行级锁定（Row-level Locking）：InnoDB 支持行级锁定，有效减少并发事务之间的冲突，提高并发性能。- 多版本并发控制（MVCC）：InnoDB 通过 MVCC 实现了非锁定读（Read Committed 或 Repeatable Read 隔离级别下），减少了事务之间的阻塞。
5. 数据组织结构： - 页（Page）：InnoDB 使用页作为基本的存储单位，每个页通常为16KB，存储表数据和索引。- B+树索引：InnoDB 表的数据和二级索引都采用 B+树结构组织，保证高效的数据检索和范围查询。
6. 外部接口： - InnoDB 通过与 MySQL Server 的交互接口，响应来自客户端的 SQL 请求，执行事务处理、查询优化和数据检索等功能。

7.1.3存储结构

• InnoDB 逻辑存储单元主要分为：表空间、段、区、页。层级关系如下图：

（1）表空间

MySQL5.7以后，表空间分为：系统表空间、独立表空间、通用表空间、undo表空间、临时表空间。

①系统表空间

1. 系统表空间以 ibdata1 命名，新建一个数据库时，InnoDB 存储引擎会初始化一个名为 ibdata1 的表空间文件
2. 默认情况下，这个文件存储所有表的数据，比如看不到的系统表，此外，还会存储数据字典、双写缓冲区、更新缓冲区和undo log。MySQL 5.6 以后，已经可以通过参数来设置 undo log 的存储位置了，可以独立于 ibdata1 文件进行存储。
3. 如果系统表空间大小不够，可以配置为自动扩展，数据库默认的自动扩展大小是 64MB，当然还可以通过添加另一个数据文件来增加系统表空间的大小。> MySQL 默认 ibdata1 大小是 12MB（Mysql 8），一般不建议使用默认大小，在遇到高并发事务时，会受到很大影响，建议把 ibdata1 的初始数值大小调整为 1GBmysql> show variables like '%innodb_data_file_path%';+-----------------------+------------------------+| Variable_name | Value |+-----------------------+------------------------+| innodb_data_file_path | ibdata1:12M:autoextend |+-----------------------+------------------------+1 row in set (0.02 sec)

②独立表空间

1. 除了系统表空间还有独立表空间(File-per-table)，每个表的表空间包含单个 InnoDB 表的数据和索引，并存储在文件系统上的单个数据文件中。
2. MySQL 每创建一个表，就会生成一个独立表空间，是以 table_name.db 命名的。

• 系统表空间和独立表空间有什么区别？- 截断或删除独立表空间中创建的表后，磁盘空间将被释放- 截断或删除系统表空间中的表会在系统表空间数据文件中创建空闲空间，此空间只能 用于存储 InnoDB 数据，也就是系统表空间在表截断或删除后占用系统空间不会缩小。- • 设置innodb_file_per_table决定表空间模式（on即为采用独立表空间、off为系统表空间）
• 为什么推荐采用独立表空间？- 系统表空间 all in one 不利于管理- 系统表空间会产生IO瓶颈- 系统表空间很难回收存储空间- 独立表空间使用optimize table 命令回收存储空间

③通用表空间

与系统表空间类似，通用表空间是能够为多个表存储数据的共享表空间。可以根据活跃度来划分表，存放在不同的磁盘上，可以减少 metadata 的存储开销。

④undo表空间

undo表空间包含undo log，这是包含有关如何撤消事务对聚集索引记录的最新更改的信息的记录集合。

undo日志默认存储在系统表空间中，但也可以存储在一个或多个undo表空间中。使用undo表空间可以减少任何一个表空间中undo log所需的空间量。undo log 的 I/O 模式也使 undo 空间成为SSD存储的理想选择 。

⑤临时表空间

把临时表的数据从系统表空间中抽离出来，形成独立的表空间参数 innodb_temp_data_file_path ，独立表空间文件名为 ibtmp1，默认大小 12MB

（2）段

1. 表空间是由段组成的，也可以把一个表理解为一个段，通常由数据段、回滚段、索引段等，每个段由 N 个区和 32 个零散的页组成，段空间扩展是以区为单位进行扩展的
2. 通常情况下，创建一个索引的同时会创建两个段，分别为非叶子节点和叶子节点段。

（3）区

1. 区是由连续的页组成的，是物理上连续分配的一段空间，每个区的大小固定是 1MB

（4）页

InnoDB 的最小物理存储单位是页，有数据页回滚页等。一般情况下，一个区由 64 个连续的页组成，页默认大小是 16KB，可以自行调整页的大小。区也就是 64*16KB = 1MB

（5）行

页里又记录着行记录的信息，InnoDB存储引擎是面向列的，也就是数据是按行存储的。行记录格式可以分为四种：Compact，dynamic，redundant，compressed，MySQL5.7 默认使用 dynamic 行记录格式。Compact 是目前使用最多的一种，但默认是 dynamic ，二者有什么不同？

后面再补充

7.1.4 MVCC（多版本并发控制）

1. MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种用于数据库管理系统中实现并发控制的技术。它允许多个事务同时对数据库进行读写操作而不会导致数据不一致或丢失。
2. MVCC 的核心思想是在数据库中维护多个数据版本，并根据事务的隔离级别来决定哪个版本对特定事务是可见的。
3. MVCC的具体实现还需要依赖数据库中的三个隐式字段，undo log 日志，undo log 版本链、readView

（1）相关概念

1. 当前读 & 快照读： - 当前读： - 读取的是当前记录的最新版本，读取的时候需要保证其他并发事务不能修改当前记录，对当前记录加锁- 例子：Insert、Update、Delete、Select… for update（写锁）、Select… lock in share mode（读锁）- 快照读：- 最普通的Select查询SQL语句- 读取的是数据的可见版本，有可能是历史数据、当前版本，不加锁，是非阻塞读- 底层依赖：当执行“快照读”SQL语句时，依据 ReadView（快照） 来提取数据
2. ReadView（快照）：下面论述不同隔离级别下快照生成的时机- RC（读已提交）：每一次select，都生成一个 ReadView- RR（可重复读）：开启一个事务之后，只有第一个select语句才会生成一张快照，此后读的都是快照中的数据，直到事务提交- Serializable（可序列化）：快照读退化成当前读（加锁，阻塞，读取到的是最新的数据）

（2）实现原理

①三个隐藏字段

当我们尝试创建一张表之后，InnoDB会自动为我们加上三个字段：

• DB_ROW_ID：隐藏主键（如果表结构没有主键，将会生成该隐藏字段）
• DB_TRX_ID：最近一次修改或删除记录的事务ID
• DB_ROLL_PTR：回滚指针，指向记录的上一个版本

②undo log 日志

• Undo 日志中记录了修改前的数据值，以及撤销操作所需的信息，以便在事务回滚或 MVCC 中使用。
• 当事务提交的时候，相关的Undo log 日志就被标记为可回收状态，可以在之后的操作中被回收

如果是insert语句，那么Undo log日志只需要在回滚的时候需要，当事务提交之后，会被立即删除。

如果是update,delete的时候，产生的undo log日志不仅在回滚的时候需要，在产生快照读的时候也需要，因此其不会被立即删除。（undo log 中残留的旧版本数据可供其他并发事务进行快照读）

③undo log 版本链

④ReadView 快照

• ReadView快照是一个数据结构，包含四个字段 - m_ids：当前活跃的事务编号集合- min_trx_id：最小活跃事务编号- max_trx_id：预分配事务编号，即当前最大事务编号+1- creator_trx_id：ReadView创建者的事务编号

（3）RC（读已提交）隔离级别：举例

①事务1-4过程如下：

• 其中事务4的两次快照读均会产生ReadView，如下：

②分析ReadViw，提取数据

• 首先声明 undo log版本链的数据访问规则：1. 若 trx_id==creator_trx_id？可以访问该版本，因为数据是当前这个事务更改的；2. 若 trx_id < min_trx_id？可以访问该版本，因为数据已经提交了；3. 若 trx_id > max_trx_id？不可以访问该版本，因为该事务修改的数据是在 ReadView生成后才开启的；4. 若 min_trx_id<=trx_id<=max_trx_id 并且 trx_id不在 m_ids（活跃事务编号集合）中，可以访问该版本，因为该数据已经提交；
• 过程如下：第一个ReadView

• 过程如下：第二个ReadView

③总结

• 在RC（读已提交）的事务隔离级别下，同一事务的两次快照读均会产生两个快照（ReadView）；
• 第一个快照读读取的数据是 事务一修改并提交的数据：张三
• 第二个快照读读取的数据是 事务二修改并提交的数据：张小三
• 小结：同一事务的两个不同select（快照读）读取的数据不一样，产生不可重复读现象

思考：应该怎么解决？

解决：设置隔离级别为 RR（可重复读），同一事务从始至终只会生成一个快照

（4）RR（可重复读）隔离级别：举例

1. 隔离级别为 RR（可重复读），同一事务从始至终只会生成一个快照，即不会产生 不可重复读问题

（5）扩展：RR能解决幻读问题吗？

1. 结论：RR（可重复读）可以解决一部分幻读问题
2. 原因：1. 同一事务的连续多次快照读，ReadView会产生复用，没有幻读问题2. 特例：当两次快照读之间存在当前读，ReadView会重新生成，导致幻读问题

7.2MyISAM存储引擎

7.2.1概述

1. 存储结构：B+树
2. 事务支持：不支持事务
3. 表级锁定：表级锁
4. 外键约束：不支持外键约束
5. 关键特性：- 支持全文检索、支持前缀索引- 紧密存储，顺序读性能好

7.2.2应用场景

1. 非事务应用，如：保存日志
2. 只读类应用，如：报表数据、字典数据

7.3Memory存储引擎

7.3.1概述

1. 存储结构：
2. 事务支持：
3. 表级锁定：表级锁
4. 外键约束：
5. 关键特性：- 内存读写，临时存储- 超高的读写效率，比MyISAM高一个量级

7.3.2应用场景