概述

本章，您将了解 MySQL 当中的架构，内容包括：

解读 MySQL 的整个架构
InnoDB 存储引擎的内存结构
InnoDB 存储引擎的磁盘结构

这些内容仅作为拓展内容了解即可，并不需要读者死记硬背。

MySQL 的架构

MySQL 8 的四层架构：

连接层（有时也称 Client 层或应用层）
SQL 层（有时也称 Server 层或服务层或逻辑层）
存储引擎层
文件系统层

连接层

该层负责客户端的连接请求，包括：

建立连接 - 客户端通过 TCP/IP 协议以及特定端口与 MySQL 服务器建立连接。连接成功之后，MySQL 服务器会为每个连接分配连接线程（或者从连接池中复用线程）
身份验证与权限校验 - 当客户端连接到 MySQL 服务器时，需要对用户名和密码进行验证，以便完成登录。同时还需要校验用户所拥有的权限（静态权限或动态权限），以便接下来的进一步操作
连接池机制 - 建立和销毁数据库连接的开销巨大，涉及到 TCP 握手、身份验证和资源分配等过程。为了避免频繁建立/销毁线程所带来的开销，MySQL 8.x 引入了连接池机制，其通过维护一个预先建立的连接池来解决这一问题，使得多个请求可以复用这些连接

SQL 层

该层负责 MySQL 中的所有逻辑功能，这些逻辑功能被划分为多个子组件，包括：

连接池（Connection Pool） - 负责管理客户端与数据库之间的连接
SQL 接口（SQL Interface） - 接受客户端的 SQL 命令（如 DML、DDL、存储例程、触发器、视图等）并返回结果
SQL 解析器（SQL Parser） - 对 SQL 语句进行语法和语义分析，验证其正确性及权限，并生成语法树‌。主要的功能有：
- 词法分析 - 将输入的 SQL 语句拆解成词法单元（Tokens），如关键字、表名和列名、运算符、常量等
- 语法分析 - 验证 SQL 语句的语法是否正确，如 select 后面必须有 from
- 语义分析 - 验证语句的逻辑合法性，如判断表是否存在、列是否存在、数据类型是否兼容等
- 生成解析树 - 将验证后的 SQL 转换成树形结构（抽象语法树，AST），明确层次关系
优化器（Optimizer） - 将 SQL 解析器生成的解析树转化为最优的‌执行计划（Execution Plan），这个计划通常是一个树状结构（执行计划树），描述了如何访问表、使用哪些索引、如何连接表、如何进行过滤和排序等操作的具体步骤和顺序。优化器主要会考虑以下因素来做决策：
- 表统计信息 - 行数、数据分布、索引基数
- 可用索引 - 主键、二级索引、复合索引
- 连接算法 - 嵌套循环连接 (Nested loop)、哈希连接 (Hash join)、排序-合并连接 (Sort-merge join)
- 访问方法 - 全表扫描 (Full table scan)、索引范围扫描 (Index range scan)、索引查找 (Index seek)
- 成本估算 - I/O 成本、CPU 成本、内存使用量
- 查询提示 - 用户提供的优化建议
要查询执行计划，请使用 explain 关键字，该关键字可与 select 语句、delete 语句、insert 语句、update 语句一起工作，如：
```
explain select * from world.city where id=100;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
```
执行器（Executor） - 接收优化器输出的最优执行计划，调用存储引擎的接口（MySQL 8.x 中是 Handler API‌）并按照执行计划描述的步骤去执行。另外，执行器也会在执行过程中申请必要的内存资源（如排序缓冲区、连接缓冲区等）以及处理在运行过程中出现的错误（如死锁）
缓存与缓冲（Caches & Buffers） - 计算机中，缓存指把读取出来的数据保存在内存当中，当再次读取相同数据时，不用读取硬盘而直接从内存当中读取，加速数据的读取过程。计算机中，缓冲指在写入数据时，先把分散的数据写入操作保存到内存当中，达到一定程度后再集中写入到硬盘，减少磁盘碎片和硬盘的反复寻道，加速数据的写入过程。不同存储引擎实现的缓存与缓冲是不一样的，Innodb 存储引擎靠缓冲池实现，缓冲池虽然名称中带有「缓冲」二字，但其实它是利用多种技术与机制（内存的缓存和缓冲、LRU 算法、InnoDB 的定期刷盘机制等）来综合提升数据的读写性能
查询缓存（Query cache） - MySQL 8 中被废弃
系统管理与控制工具（Management Services & Utilities） - 该子组件主要负责提供备份恢复、安全管理、集群管理、数据导入导出、性能监控和分析等功能。

存储引擎层

MySQL 支持的存储引擎有多个，其中 InnoDB 存储引擎适用于绝大多数的业务场景。该层主要作用有：

通过存储引擎负责数据的存储和提取
通过 API 与上层进行交互

文件系统层

该层的主要作用有：

负责将数据持久化到磁盘
与存储引擎进行交互，完成读写操作

部分资料也会将存储引擎层与文件系统层合并，统称存储层。

InnoDB 存储引擎的内存结构

缓冲池

MySQL 官方的说明：

缓冲池是主内存中的一个区域，用于缓存表和索引数据。缓冲池允许频繁使用的数据直接从内存中访问，从而加快处理速度。在专用服务器上，通常将高达 80% 的物理内存分配给缓冲池。
为了提高大量读操作的效率，缓冲池被划分为多个可容纳多行数据的页。为了提升缓存管理的效率，缓冲池采用页的链表结构进行实现；将不常使用的数据通过一种改进的 LRU 算法从缓存中淘汰。

有关 InnoDB 缓冲池的环境变量：

show session variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | OFF            |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1073741824     |
+-------------------------------------+----------------+
11 rows in set (0.02 sec)

innodb_buffer_pool_chunk_size - 缓冲池中操作块（chunk）的大小，单位为Byte（字节），默认为 128MB。要更改该系统变量的值，通常以 1MB 为单位递增或递减。最小值为 1048576，最大值为 innodb_buffer_pool_size 除以 innodb_buffer_pool_instances 得到
innodb_buffer_pool_dump_at_shutdown - 在关闭 MySQL 服务器时，是否记录 InnoDB 缓冲池中缓存的数据页，以缩短下次重启时的预热过程。通常与 innodb_buffer_pool_load_at_startup 结合使用。默认开启
innodb_buffer_pool_dump_now - 是否立即记录缓存在 InnoDB 缓冲池中的数据页，通常与 innodb_buffer_pool_load_now 结合使用。默认关闭
innodb_buffer_pool_dump_pct - 预热的数据页百分比，范围为 1~100
innodb_buffer_pool_filename - 用于定义 InnoDB 缓冲池（Buffer Pool）在磁盘上持久化时使用的文件名
innodb_buffer_pool_in_core_file - 是否排除 InnoDB 缓冲池中的页来减小核心文件的大小
innodb_buffer_pool_instances - InnoDB 缓冲池的实例数，当 innodb_buffer_pool_size >= 1GB 时才有效。值的范围为1~64
innodb_buffer_pool_load_abort - 是否中止正在进行的 InnoDB 缓冲池加载过程‌
innodb_buffer_pool_load_at_startup - 指定在 MySQL 服务器启动时，InnoDB 缓冲池是否会通过加载之前保存的相同数据页来自动预热。默认开启。
innodb_buffer_pool_load_now - 是否立刻手动触发缓冲池的数据加载
innodb_buffer_pool_size - InnoDB 缓冲池大小，以字节为单位。在专用的数据库服务器上，通常设置为当前可用内存的 80% 即可

术语

预热：MySQL 中的一种机制或功能，指的是将磁盘中经常访问的数据提前放入到内存缓冲池中，相当于 MySQL 程序自动填充缓存（而不是等待客户端的访问然后逐个缓存），其本质是 LRU（最近最少使用）的算法变体。有了预热，当 MySQL 重启时不会丢失访问的性能。

Q：若要优化缓冲池的各种配置项（系统变量），有无可以参考的基准？

可通过这个 SQL 语句来查看：

show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 16054 |
| Innodb_buffer_pool_reads              | 908   |
+---------------------------------------+-------+
5 rows in set (0.00 sec)

innodb_buffer_pool_read_requests 表示从内存中读取的逻辑请求数
innodb_buffer_pool_reads 表示 InnoDB 缓冲池中无法满足要求的请求数，需要从磁盘中读取

缓冲池命中百分比 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads) * 100

如果 缓冲池命中百分比 很高（以 99% 作为基准来判断是否需要需要优化缓冲池），则表示需要从磁盘读取的数据少，这时的性能比较高。

若要查看更加详细的信息，请执行以下的 SQL 语句并查看 BUFFER POOL AND MEMORY‌ 这部分的内容：

show engine innodb status;
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 489041
Buffer pool size   65530
Free buffers       64480
Database pages     1050
Old database pages 407
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 907, created 143, written 199
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1050, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
...

LRU 算法

LRU（Least Recently Used，最近最少使用）算法的核心理念：这种算法认为最近使用的数据是热门数据，下一次很大概率将会再次被使用。而最近很少被使用的数据，很大概率下一次不再用到。当缓存容量被占满时，优先淘汰最近很少使用的数据。该算法常采用哈希表（Hash Table）和双向链表（Doubly Linked List）‌的组合来实现。

缓冲池的算法采用 LRU 算法的变体进行管理，官方图如下：

当需要为缓冲池添加新数据页时，最不常用的数据页会被移除，并在列表中间添加新数据页。这种中间插入策略将列表视为了两个子列表：

头部子列表 - 包含最近访问过的 "年轻" 数据页（new/young pages）
尾部子列表 - 包含较久未访问的 "年老" 数据页（old pages）

该算法将频繁使用的数据页保留在‌新子列表‌（new sublist）中，而‌旧子列表‌（old sublist）则包含使用频率较低的数据页，这些数据页是‌淘汰候选者‌（candidates for eviction）

缓冲池算法的默认运行方式：

旧子列表（old sublist）占用缓冲池的 3/8 空间
列表中点作为新子列表尾部与旧子列表头部的边界
InnoDB 将新读入的数据页插入到中点（即旧子列表头部），而触发读取数据页的条件包括：
- 用户发起的 DQL 等操作
- InnoDB 存储引擎的自动预读等操作（预读：预先将相邻的、可能即将被访问的数据页一并加载到缓冲区‌，以减少后续访问时的磁盘 I/O 开销‌）
当旧子列表中的数据页被访问时，这些数据页的状态将变为 "年轻"（young）并移动到新子列表头部
- 若因用户操作触发读取数据页，首次访问时会立即发生并完成状态转换
- 若因预读操作触发读取数据页，首次访问可能延迟甚至不发生（数据页可能已经被淘汰）
随着数据库的运行，未被访问的数据页会向列表尾部移动而 "老化"
新/旧子列表的数据页都会因其他数据页变为新数据页而 "老化"
旧子列表的数据页还会因新数据页插入到中点而 "老化"
长期未使用的数据页最终到达旧子列表尾部时被淘汰

术语

数据页（page，页）：数据页是 InnoDB 存储引擎用于管理存储空间的基本单位。它充当了磁盘与内存之间交互的基本单位，也是数据库 I/O 操作的最小单位，默认大小为 16 KB（innodb_page_size），这是一个在容纳行数据与内存性能开销之间的平衡值。当 InnoDB 需要读取数据时，会将整个数据页从磁盘加载到内存的缓冲池中，因此数据页也是缓冲池进行数据管理的最小单位。每个内存中的数据页，本质上是磁盘上对应数据页的一个副本，用于存储表记录、索引节点等实际数据。

变更缓冲区

变更缓冲区的核心功能和工作流程：

InnoDB 存储引擎中的一种特殊数据结构，用于缓存当二级索引页不在缓冲池中的变更
当执行 INSERT、UPDATE、DELETE 等 DML 操作时，如果目标数据页尚未被加载到缓冲池中，InnoDB 不会立即从磁盘读取该页，而是将此次修改操作暂存到变更缓冲区
当这些被修改的二级索引页因为其他读操作被加载到缓冲池时，InnoDB 会将变更缓冲区中缓存的修改与磁盘页（磁盘数据页）进行合并，一次性完成更新

术语

非聚集索引（二级索引、辅助索引）：除主键索引与唯一索引外的其他索引，都可以被称为二级索引。后面会有专门的章节来介绍索引。

变更缓冲区是一种提升写性能以及减少磁盘随机 I/O 的优化手段，主要使用在以下场景中：

写多读少的二级索引操作
批量数据插入和更新
高并发写入环境

Q：如果中途断电或者数据库突发宕机，DML 的写操作在变更缓冲区中是不是丢失了？

并不会，因为 MySQL 有多种机制来保证不丢失数据：

Redo log - 所有对变更缓冲区的修改都会记录到重做日志
双写缓冲区 - 通过双写缓冲机制来解决数据页损坏问题
变更缓冲区的合并机制

Q：变更缓冲区的合并（merge）是什么意思？

将变更缓冲区中对二级索引页的修改操作应用到磁盘页的过程，即合并。以下几种情况会触发合并：

更新数据（页）后访问这个数据（页）
MySQL 服务器的正常关闭或重启
系统处于空闲低负载时主线程的定期清除操作

变更缓冲区的相关系统变量：

show session variables like '%innodb_change%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | none  |
+-------------------------------+-------+
2 rows in set (0.00 sec)

innodb_change_buffer_max_size - 变更缓冲区占缓冲区的最大百分比，默认 25，最大 50
innodb_change_buffering - 控制对二级索引执行哪些修改操作才会被缓存到变更缓冲区。可允许的值如下（相关值可用数字进行代替）
- none - 0。不缓冲任何操作
- inserts - 1
- deletes - 2
- changes - 3
- purges - 4
- all - 5
在 8.4 之前的版本中，该环境变量的默认值为 all，8.4 以及之后版本的默认值为 none

日志缓冲区

日志缓冲区（Log Buffer）是临时存储重做日志（redo log）的内存区域，这些临时日志数据最终会被写入到磁盘的重做日志文件上（通过一种被称为 WAL（Write-Ahead Logging）的技术）。

日志缓冲区主要作用：

降低磁盘 I/O 压力 - 通过批量缓存事务提交时生成的重做日志（记录数据页的物理修改），避免每次事务提交都直接写入磁盘。
提升性能 - 通过 Innodb 线程的异步刷盘机制，将缓冲区内容刷新至磁盘的重做日志文件（#ib_redo_N 和 #ib_redo_N_tmp）

在前面《MySQL进阶03 --- 数据目录》中提到了：

#innodb_redo - 专门用于存放重做日志（Redo log）文件的目录，这些文件记录了事务执行过程中对数据页的物理修改

show session variables like 'innodb_log_buffer%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 67108864 |
+------------------------+----------+
1 row in set (0.00 sec)

select @@global.innodb_flush_log_at_trx_commit;
+-----------------------------------------+
| @@global.innodb_flush_log_at_trx_commit |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

select @@global.innodb_flush_log_at_timeout;
+--------------------------------------+
| @@global.innodb_flush_log_at_timeout |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

innodb_log_buffer_size - 日志缓冲区的大小，以字节为单位，默认为 67108864 字节（也就是 64 MB）
innodb_flush_log_at_trx_commit - 控制在事务提交时， innodb 对重做日志写入并刷新到磁盘的方式。值有：
- 0 （延迟写，延迟刷）- 每秒将重做日志（事务日志）写入并刷新到磁盘一次，若 MySQL 中途宕掉，内存中可能丢失 1 秒的事务数据
- 1 （实时写，实时刷）- 默认值。每次事务提交时都会将重做日志（事务日志）写入并刷新到磁盘。
- 2 （实时写，延迟刷）- 重做日志（事务日志）将在每次提交事务后写入并每隔 1 秒刷新一次到磁盘。
当值为 0 或 2 时，可通过 innodb_flush_log_at_timeout 控制刷新的频率秒数，默认 1 秒。

额外提示

为了在 innodb 存储引擎的事务中获得最好的一致性与持久性，请设置 innodb-flush-log-at-trx-commit=1 和 sync-binlog=1。

自适应哈希索引

众所周知，要提高数据库中数据的查询速度，索引是必不可少的。

MySQL 的自适应哈希索引（Adaptive Hash Index，AHI）是 InnoDB 存储引擎内部自动管理的一种优化手段，该手段会实时监控表上索引的使用情况，如果观察某些索引值使用地非常频繁，则自动在内存中使用「自适应哈希索引」，即在内存中基于 B+ 树索引之上再创建一个哈希索引。8.4 版本中，默认关闭 AHI

在 DQL 中使用频繁使用等于表达式（where index_col = 'xxx'）是自适应哈希索引的主要使用场景，但在某些其他场景下（若多表并发连接、DQL 中的使用 like 关键字搭配 % 通配符），AHI 反而会增加不必要的性能开销。所以，是否要开启 AHI 需结合具体的业务场景综合判断。

show engine innodb status\G;
...
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
...

show session variables like '%hash_index%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | OFF   |
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+
2 rows in set (0.00 sec)

系统变量说明：

innodb_adaptive_hash_index - 是否开启 AHI
innodb_adaptive_hash_index_parts - AHI 拥有分区机制，通过该系统变量来指定分区数量

InnoDB 存储引擎的磁盘结构

磁盘结构包括：

表空间（Tablespace）
表（Table）
索引（Index）
重做日志（Redo log）
回滚日志（Undo log）
双写缓冲区（Doublewrite Buffer）

表空间

Q：表空间的概念最早来自于 Oracle DBMS ，现在主流的 DBMS 几乎都有这个概念，那什么是表空间？

表空间是逻辑层与物理层的中间桥梁，它是用户逻辑对象（表、索引等）的存储空间，用来统一管理空间中的数据文件。

表空间的属性包括：

表空间在物理层上对应着若干个容器，容器可以是目录名称、文件名或者设备名称
一个库可以包含多个表空间，但一个表空间只能属于一个库
一个表空间可以包含多个数据文件，但一个数据文件只能属于一个表空间

在前面我们提到过：

表空间 指的是 InnoDB 存储引擎用于组织和管理数据的逻辑存储结构，它最终会映射到磁盘上的一个或多个 物理文件，表空间根据用途可划分为：

系统表空间（System Tablespace）

独立表空间（File-Per-Table Tablespaces）

通用表空间（General Tablespaces）

撤销表空间（Undo Tablespaces）

临时表空间（Temporary Tablespaces）

表空间是逻辑存储，其按照表空间（Tablespaces） ---> 段（Segment） ---> 区（Extent） ---> 页（Page） ---> 行（Row）的层级结构构成，如下图所示：

表空间 - 最高层级的存储容器
段 - 管理特定类型数据的连续存储单元
区 - 连续分配的物理存储单元（避免碎片化）。每个区固定为 1M ，其由连续的页组成，页的大小默认为 16KB（由 innodb_page_size 系统变量的值决定，值可以是 4096、8192、16384、32768、65536 中的任意一个，默认 16384）。1024KB 除以 16KB，即一个区等于 64 个连续的页。
页 - InnoDB 读写的最小磁盘单元，默认大小为 16KB
行 - 实际的数据记录

系统表空间

系统表空间是 InnoDB 的默认表空间，主要包含以下内容：

变更缓冲区（Change Buffer）的存储区域
在 MySQL 8.0.20 之前的版本中还包含双写缓冲区（Doublewrite Buffer）的存储区域，之后版本则位于单独的双写文件中
在 MySQL 8.0 之前的版本中，系统表空间还包含 InnoDB 数据字典
如果表是在系统表空间而非独立表空间或通用表空间中创建的，那么这些表的数据和索引也会存储在系统表空间中

系统表空间可以有一个或多个数据文件，默认情况下会在数据目录中创建一个名为 ibdata1 的系统表空间数据文件。可通过系统变量 innodb_data_file_path 进行定义，该系统变量可以定义名称、大小和属性。

Shell > ls -lh /usr/local/mysql8/data/ibdata1
-rw-r----- 1 mysql mysql 12M Nov 18 19:05 /usr/local/mysql8/data/ibdata1

独立表空间

每个表都拥有独立的 .ibd 文件，存储该表的所有数据和索引。

Shell > tree -hugp /usr/local/mysql8/data/world/
/usr/local/mysql8/data/world/
├── [-rw-r----- mysql    mysql     592K]  city.ibd
├── [-rw-r----- mysql    mysql     192K]  country.ibd
└── [-rw-r----- mysql    mysql     240K]  countrylanguage.ibd

0 directories, 3 files

InnoDB 默认使用独立表空间来创建表（innodb_file_per_table=1），若 innodb_file_per_table=0，则 InnoDB 默认使用系统表空间来创建表。

show session variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

通用表空间

通用表空间是使用 create tablespace 语法创建的共享表空间，它具有以下功能：

‌多表共享‌与跨库支持 - 一个通用表空间可存储来自不同数据库的多个表
内存利用率高 - 与独立表空间相比，具有更高的内存利用率

撤销表空间

撤销表空间（回滚表空间，Undo Tablespaces）专门用于存储撤销日志（Undo log），MySQL 8.x 及以上版本进行初始化时，默认会在数据目录中创建两个撤销表空间文件（无 .ibu 后缀标识），这些文件以回滚段的结构形式存储回滚日志。

Shell > ls -lh /usr/local/mysql8/data/undo_00*
-rw-r----- 1 mysql mysql 16M Nov 18 19:07 /usr/local/mysql8/data/undo_001
-rw-r----- 1 mysql mysql 16M Nov 18 19:07 /usr/local/mysql8/data/undo_002

除了初始化时默认创建的两个撤销表空间之外，用户可使用 create undo tablespace 语法来创建专属的撤销表空间，该语法关联到文件时必须有 .ibu 后缀标识，语法如下：

CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';

提示

一个 MySQL 实例最多支持 127 个撤销表空间（包括 MySQL 实例初始化时创建的两个默认撤销表空间）。

临时表空间

InnoDB 会使用两种临时表空间：

会话临时表空间（session temporary tablespaces） - 存储用户创建的临时表以及优化器生成的内部临时表。

前面《MySQL进阶03 --- 数据目录》中提到：

#innodb_temp - 会话临时表空间的存放目录，目录下是以 .ibt 后缀结尾的文件，这些文件会在 MySQL 服务器启动时被创建（通常为 10 个文件）。当客户端连接后执行复杂的 DQL 操作（如分组、排序或多表连接等）时，若中间产生的结果集过大且无法在内存中完成，MySQL 就会使用此目录下的文件，当连接会话断开后，占用的临时表空间会被截断并释放回 "池" 中（而不是直接删除文件），等待后续新的连接会话重新使用

相关文件以 .ibt 后缀标识结尾，其存储路径通过系统变量 innodb_temp_tablespaces_dir 进行定义，默认情况下，这些文件位于数据目录下的 #innodb_temp 目录中。

用户可查询 information_schema.INNODB_SESSION_TEMP_TABLESPACES 表来获取会话临时表的元数据信息。

Shell > ls -lh /usr/local/mysql8/data/#innodb_temp/
total 800K
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_1.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_8.ibt
-rw-r----- 1 mysql mysql 80K Nov 18 19:04 temp_9.ibt

select @@global.innodb_temp_tablespaces_dir;
+--------------------------------------+
| @@global.innodb_temp_tablespaces_dir |
+--------------------------------------+
| ./#innodb_temp/                      |
+--------------------------------------+
1 row in set (0.00 sec)

select * from information_schema.INNODB_SESSION_TEMP_TABLESPACES;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE      | PATH                       | SIZE  | STATE    | PURPOSE   |
+----+------------+----------------------------+-------+----------+-----------+
| 10 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE   | INTRINSIC |
|  0 | 4243767281 | ./#innodb_temp/temp_1.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767282 | ./#innodb_temp/temp_2.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767283 | ./#innodb_temp/temp_3.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767284 | ./#innodb_temp/temp_4.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767285 | ./#innodb_temp/temp_5.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767286 | ./#innodb_temp/temp_6.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767287 | ./#innodb_temp/temp_7.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767288 | ./#innodb_temp/temp_8.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767289 | ./#innodb_temp/temp_9.ibt  | 81920 | INACTIVE | NONE      |
+----+------------+----------------------------+-------+----------+-----------+
10 rows in set (0.00 sec)

全局临时表空间（global temporary tablespace） - 存储对用户创建的临时表所做更改的回滚段。系统变量 innodb_temp_data_file_path 定义了其相对路径、名称、大小与属性。前面《MySQL进阶03 --- 数据目录》中提到：

ib_tmp1 - 全局临时表空间，存储临时表的 Undo 信息

Shell > ls -lh /usr/local/mysql8/data/ibtmp1
-rw-r----- 1 mysql mysql 12M Nov 19 14:28 /usr/local/mysql8/data/ibtmp1

select @@global.innodb_temp_data_file_path;
+-------------------------------------+
| @@global.innodb_temp_data_file_path |
+-------------------------------------+
| ibtmp1:12M:autoextend               |
+-------------------------------------+
1 row in set (0.00 sec)

ibtmp1 表示相对路径下的文件名称；12M表示全局临时表空间的初始大小；autoextend 表示当全局临时表空间文件被写满时，系统会自动扩展该文件的大小，扩展的大小默认为 64M，由 innodb_autoextend_increment 环境变量的值决定。

select @@global.innodb_autoextend_increment;
+--------------------------------------+
| @@global.innodb_autoextend_increment |
+--------------------------------------+
|                                   64 |
+--------------------------------------+
1 row in set (0.00 sec)

为了避免全局临时表空间文件的无限增长，可以在配置文件中进行限制：

Shell > vim /etc/my.cnf
...
[mysqld]
...
innodb-temp-data-file-path=ibtmp1:12M:autoextend:max:10G
...

重启 MySQL 实例即可生效。

表

表是数据库中存储数据的主要对象，使用 create table 语句进行创建，关于库、表的管理，可以翻看前面的《MySQL基础 --- DDL》内容。

Shell > tree -hugp /usr/local/mysql8/data/world/
/usr/local/mysql8/data/world/
├── [-rw-r----- mysql    mysql     592K]  city.ibd
├── [-rw-r----- mysql    mysql     192K]  country.ibd
└── [-rw-r----- mysql    mysql     240K]  countrylanguage.ibd

0 directories, 3 files

innodb 是 MySQL 默认使用的存储引擎，一旦表创建完成（使用默认的独立表空间进行表的创建），则在数据目录下对应的库目录中创建相应的 .ibd 后缀标识文件。

可通过以下的 SQL 语句查看表的状态以及属性：

use world;

show table status like '%city%'\G;
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4035
 Avg_row_length: 101
    Data_length: 409600
Max_data_length: 0
   Index_length: 114688
      Data_free: 0
 Auto_increment: 4081
    Create_time: 2025-08-01 15:15:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

索引

关于索引的内容，由于涉及到大量的基本概念以及原理，这里先不进行说明，只需要知道这一句就可以了 —— 一种用来提高数据查询效率的数据结构。

重做日志与撤销日志

这两个日志都是 InnoDB 存储引擎专有的日志，因为只有 InnoDB 才支持事务！

事务（Transaction）：事务是 MySQL的一种机制（当然其他的关系型数据库也有），它由一条或者多条 SQL 语句组成，这些成批的 SQL 语句是一个执行单元，它们互相依赖且是一个不可分隔的整体，要么全部执行，要么全部不执行，用来完成某一个业务或事情或功能，保证数据库的完整性或一致性。如果某一事务执行成功，则在该事务中进行的所有数据修改均会被提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。

重做日志：重做日志用于故障恢复时修复未完成事务的数据，它位于磁盘中，与内存中的日志缓冲区（log buffer）相对应。在正常操作过程中，重做日志记录了表中的数据修改信息。若运行中的 MySQL 实例出现异常关闭，则重新启动时会自动利用重做日志来恢复未正常更新到数据文件的修改，保证事务ACID中的D（持久性）。默认情况下，所有 32 个重做日志都存放在数据目录下的 #innodb_redo 目录中。

Shell > ls -lh /usr/local/mysql8/data/#innodb_redo/
total 100M
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo10_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo11_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo12_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo13_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo14_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo15_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo16_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo17_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo18_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo19_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo20_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo21_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo22_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo23_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo24_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo25_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo26_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo27_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo28_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo29_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo30_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo31_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo32_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo33_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo34_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo35_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo36_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo37_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo38_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:46 '#ib_redo7'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo8_tmp'
-rw-r----- 1 mysql mysql 3.2M Nov 19 14:28 '#ib_redo9_tmp'

在 MySQL 8.0.30 版本之前，要给重做日志配置容量，需要这么做：

正常停止运行中的 MySQL 实例
编辑 /etc/my.cnf 并配置 innodb-log-file-size 以及 innodb-log-files-in-group
- innodb_log_file_size - 定义单个重做日志的文件大小，默认 50331648 字节，最大值为 512G/innodb_log_files_in_group
- innodb-log-files-in-group - 用来定义重做日志的文件数，默认 2 ，最大为 100
启动 MySQL 实例

从 MySQL 8.0.30 版本以及之后的版本开始，使用系统变量 innodb_redo_log_capacity 定义重做日志占用的磁盘空间量。这个系统变量改变了重做日志的管理方式 —— 将重做日志文件的数量固定为 32 个并默认存放在数据目录下的 #innodb_redo 目录中。

select @@global.innodb_log_file_size;
+-------------------------------+
| @@global.innodb_log_file_size |
+-------------------------------+
|                      50331648 |
+-------------------------------+
1 row in set (0.00 sec)

select @@global.innodb_log_files_in_group;
+------------------------------------+
| @@global.innodb_log_files_in_group |
+------------------------------------+
|                                  2 |
+------------------------------------+
1 row in set (0.00 sec)

select @@global.innodb_redo_log_capacity;
+-----------------------------------+
| @@global.innodb_redo_log_capacity |
+-----------------------------------+
|                         104857600 |
+-----------------------------------+
1 row in set (0.00 sec)

若您的 MySQL 实例处于正在运行的状态，则可以使用 set global 语句直接在线调整（无需重启 MySQL 实例即可生效），比如这样的：

# 设置为 200MB
set global innodb_redo_log_capacity = 200*1024*1024;

撤销日志（回滚日志）：通过记录事务修改前的旧数据（或逆向操作），在事务未提交或提交失败时，能够将数据回滚到事务开始前的状态，从而保证事务 ACID 中的‌ A（原子性）‌。

双写缓冲区

双写缓冲区：MySQL 的一种数据保险机制，也是 InnoDB 存储引擎的一个关键特性，其作用就是解决部分页写失效（Partial Page Write）的问题，提高 InnoDB 的可靠性，安全地将数据页从 buffer pool 刷到磁盘。

众所周知，InnoDB 的数据页大小默认为 16KB（数据页是 InnoDB 存储引擎用于管理存储空间的基本单位，其大小由 innodb_page_size 系统变量的值决定），而操作系统和硬件的最小写入单位是 4KB，换言之，一个数据页（脏页）需要拆分为 4 个 4KB 的块依次写入。在某些极端情况下（如系统崩溃或中途断电），可能导致一个 16KB 的数据页（脏页）只部分写入到了磁盘，从而引发数据损坏‌或不完整。

所谓双写，即写入两次：

第一次：将数据页写入到磁盘上的‌双写缓冲区
第二次：将数据页从双写缓冲区写入到‌实际表空间文件‌的最终位置‌

术语

当内存中修改的数据页与磁盘上存储的数据页内容不一致时，这个内存页就被称为‌脏页‌‌或脏数据。很明显，双写缓冲区就是针对脏页进行设计的，用来解决部分页写失效（Partial Page Write）的问题

Q：为什么不能使用 Redo log 的修改记录进行恢复呢？

Redo log 的恢复依赖 完整的数据页，若数据页本身不完整或损坏，则 Redo log 也无能为力。

在 8.0.20 版本之前，双写缓冲区位于 InnoDB 的系统表空间中；从 8.0.20 开始，双写缓冲区位于独立的双写文件中，以 .dblwr 后缀名结尾，默认有两个文件。

Shell > ls -lh /usr/local/mysql8/data/#ib_16384_*
-rw-r----- 1 mysql mysql 4.0M Nov 19 14:46 /usr/local/mysql8/data/#ib_16384_0.dblwr
-rw-r----- 1 mysql mysql  12M Sep 16 22:51 /usr/local/mysql8/data/#ib_16384_1.dblwr

通常而言，若使用 HDD 存储数据，建议开启双写缓冲区，但若使用 SSD 存储数据，建议关闭双写缓冲区以求性能最大化。

关于双写缓冲区相关的系统变量如下：

show session variables like '%doublewrite%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_doublewrite            | ON    |
| innodb_doublewrite_batch_size | 0     |
| innodb_doublewrite_dir        |       |
| innodb_doublewrite_files      | 2     |
| innodb_doublewrite_pages      | 128   |
+-------------------------------+-------+
5 rows in set (0.01 sec)

innodb_doublewrite - 是否开启双写缓冲区
innodb_doublewrite_batch_size - 控制批量写入的双写页数，默认为值0，适合绝大多数用户。
innodb_doublewrite_dir - 指定存放双写文件的目录，若为空，则将双写文件存放在数据目录下。理想状态下，双写目录应该放在可用的最快存储介质上
innodb_doublewrite_files - 定义双写文件的数量。默认情况下，为每个缓冲池实例（系统变量 innodb_buffer_pool_instances 决定缓冲池的实例数）创建两个双写文件。双写文件的文件命名规则为 #ib_page_size_file_number.dblwr，其中 page_size_file 指系统变量 innodb_page_size 的值
innodb_doublewrite_pages - 控制每个线程双写页的最大数量，如果没有值，则默认为 128。在 MySQL 8.4 版本之前，默认值是系统变量 innodb_write_io_threads 的值，为 4 。

用一杯咖啡支持我们，我们的每一篇[文档]都经过实际操作和精心打磨，而不是简单地从网上复制粘贴。期间投入了大量心血，只为能够真正帮助到您。

MySQL

登录管理

标签云

概述

MySQL 的架构

连接层

SQL 层

存储引擎层

文件系统层

InnoDB 存储引擎的内存结构

缓冲池

LRU 算法

变更缓冲区

日志缓冲区

自适应哈希索引

InnoDB 存储引擎的磁盘结构

表空间

系统表空间

独立表空间

通用表空间

撤销表空间

临时表空间

表

索引

重做日志与撤销日志

双写缓冲区

发送评论编辑评论

登 录 管 理

标 签 云

概述

MySQL 的架构

连接层

SQL 层

存储引擎层

文件系统层

InnoDB 存储引擎的内存结构

缓冲池

LRU 算法

变更缓冲区

日志缓冲区

自适应哈希索引

InnoDB 存储引擎的磁盘结构

表空间

系统表空间

独立表空间

通用表空间

撤销表空间

临时表空间

表

索引

重做日志与撤销日志

双写缓冲区

发送评论 编辑评论

推荐文章

登录管理

标签云

发送评论编辑评论