Tenormis's Blog

Appearance

MySQL 数据引擎 ​

1. InnoDB（默认引擎） ​

InnoDB 是 MySQL 8.x 的默认存储引擎，支持事务、行级锁、外键，是 OLTP（联机事务处理）系统的最佳选择。

1.1. 存储架构 ​

图 1.1 - MySQL 存储架构

1.2. 内存架构 ​

1.2.1. Buffer Pool ​

  • 定义：缓冲池是 InnoDB 在内存中用于缓存表和索引数据的区域；
  • 作用：频繁访问的数据可以直接从内存中读取，加快处理速度；
  • 内存占用：在专用服务器上，通常分配物理内存的 80% 给缓冲池；
1.2.1.1. 缓冲池 LRU 算法 ​
  • 工作原理：缓冲池使用 LRU（Least Recently Used）变体算法进行管理；
  • 双子链表结构：
    • 新页面子链表（Young List）：存放最近访问的页面，位于链表头部；
    • 旧页面子链表（Old List）：存放较少访问的页面，位于链表尾部；
  • 中点插入策略（Midpoint Insertion）：
    • 新页面被加载后，先插入到旧子链表的头部；
    • 若被访问，则移动到新子链表的头部，变为 “年轻” 页面；
    • 未被访问的页面逐渐老化并被淘汰；
  • 默认配置：
    • 3/8 的缓冲池用于旧子链表；
    • 链表中点为新旧子链表的分界线；
1.2.1.2. 缓冲池配置 ​
  1. 缓冲池大小：尽量设大，保证有足够内存给其他进程，避免过多分页。内存越大，InnoDB 越像内存数据库；
  2. 多实例缓冲池：在 64 位系统上，可以将缓冲池分成多个实例，减少并发操作时的内存争用；
  3. 扫描抗性：保持经常访问的数据在内存中，防止大批量的冷数据占用缓冲池；
  4. 预读配置：可以控制何时进行预读操作，以便提前加载页面；
  5. 后台刷盘配置：控制后台刷盘的时间和速率，以优化性能；
  6. 缓冲池状态保存：可配置 InnoDB 保存当前缓冲池状态，减少服务器重启后的缓慢热身；
1.2.1.3. 缓冲池监控 ​

通过命令：

SQL
SHOW ENGINE INNODB STATUS

在 BUFFER POOL AND MEMORY 小节可以看到有关于缓冲池有关的指标：

Log
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872             -- 缓冲池总内存分配量（字节）；
Dictionary memory allocated 776332                  -- 缓冲池总内存分配量（字节）；
Buffer pool size   131072                           -- 缓冲池大小（以页为单位）；
Free buffers       124908                           -- 空闲缓冲区页数；
Database pages     5720                             -- LRU 列表中的数据库页面总数；
Old database pages 2071                             -- LRU 列表中旧页面的数量；
Modified db pages  910                              -- 已被修改但尚未写入磁盘的页面数量；
Pending reads 0                                     -- 等待读取的页面数量；
Pending writes: LRU 0, flush list 0, single page 0  -- 等待写入的页面数量；
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not     -- 缓冲池页面命中率；
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read  -- 预读操作的平均速率；
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0                                      -- LRU 列表的总长度；
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
1.2.1.4. 性能优化建议 ​
  • 提高命中率：增大缓冲池大小，减少磁盘 I/O。调整 LRU 参数，使频繁访问的页面保留更长时间；
  • 减少内存争用：在多核系统上，启用多实例缓冲池，分散并发访问压力；
  • 优化读写性能：根据业务场景调整预读和刷盘策略；
1.2.1.5. 常见问题与调优 ​
  • 缓存污染（Cache Pollution）：
    • 全表扫描（如 mysqldump 或无 WHERE 条件的 SELECT）可能会将大量冷数据加载进缓冲池，导致热数据被淘汰；
    • 解决方法：启用 innodb_old_blocks_time 参数，延迟新加载页面被标记为 “年轻” 的时间；
  • 年轻页面数量低：
    • 如果 youngs/s 值低，考虑增加旧子链表占比，使页面在旧子链表中停留更长时间，提高被再次访问的概率；

1.2.2. Change Buffer ​

Change Buffer 是 InnoDB 中用于缓存对二级索引页面的更改（如 INSERT、UPDATE 和 DELETE 操作）的特殊数据结构。当受影响的二级索引页面未在 Buffer Pool 中时，这些更改会被缓存在 Change Buffer 中，稍后当页面被加载到 Buffer Pool 时再进行合并。这样可以减少随机 I/O 操作，提升性能。

由于二级索引通常是非唯一的，插入、删除和更新操作会影响分布不连续的页面。通过延迟合并，避免了频繁的磁盘读取操作。

1.2.3. Adaptive Hash Index ​

InnoDB 会监控索引查询模式，并在适合时自动对访问频繁的 B-tree 页构建哈希索引，以索引键前缀作为哈希键，前缀长度可变，以提升查询性能。

可以通过 innodb_adaptive_hash_index 参数启用，或在服务器启动时通过 --skip-innodb-adaptive-hash-index 禁用。

性能监控与调优：可通过 SHOW ENGINE INNODB STATUS 中的 SEMAPHORES 部分监控自适应哈希索引的使用情况和争用情况。若出现大量线程等待 btr0sea.c 中的 rw-latches，可考虑增加分区数量或禁用自适应哈希索引。

1.2.4. Log Buffer ​

日志缓冲区是用于存放待写入磁盘日志文件数据的内存区域。大小由 innodb_log_buffer_size 参数定义，默认值为 16MB。较大的日志缓冲区可以让大型事务在提交前无需频繁写入重做日志 (Redo Log)，从而减少磁盘 I/O。

  • innodb_flush_log_at_trx_commit：日志缓冲区内容写入磁盘的方式和时机，以平衡数据安全性和性能；

    • 0：每秒写入并刷新到磁盘；
    • 1：每次提交都写入并刷新到磁盘（默认）；
    • 2：每次提交写入，但每秒刷新到磁盘；

  • innodb_flush_log_at_timeout：日志刷新到磁盘的频率；

1.3. 磁盘架构 ​

1.3.1. 表空间结构 ​

图 1.2 - 表空间结构

InnoDB 表空间（Tablespace）的存储结构如下：

  • 段（Segment）：

    • 数据段（B+Tree 的叶子节点）
    • 索引段（B+Tree 的非叶子节点）
    • 回滚段（存储事务 undo 日志）

  • 区（Extent，固定 1M）：

    • 连续 64 个页（1MB = 64 * 16KB）
    • 物理分配的基本单位，减少碎片化

  • 页（Page，默认 16KB，可通过 innodb_page_size 调整）：

    核心的页类型包括：

    • 数据页（INDEX）：存储行记录
    • Undo 页：事务回滚数据
    • 系统页：管理区/段信息
    • Insert Buffer 页：辅助索引变更缓存
    • 事务数据页：存储事务系统信息

  • 行（Row）：

    行格式（通过 ROW_FORMAT 指定）：

    • Compact：默认格式，优化存储空间
    • Dynamic（MySQL 5.7+ 默认）：改进大对象处理
    • Compressed：支持压缩
    • Redundant：兼容旧版本

    行溢出机制：当行数据超过页容量时，使用 BLOB 页存储溢出数据。

1.3.2. 系统表空间 ​

系统表空间是变更缓冲区（Change Buffer）的存储区域。若未使用独立表空间或通用表空间，则表和索引数据也会存储于此。

默认情况下，系统表空间为单一文件 ibdata1，位于数据目录中。可以通过 innodb_data_file_path 参数配置数据文件的数量与大小：

INI
innodb_data_file_path=ibdata1:10M:autoextend

Note：autoextend 表示自动扩展，默认每次增加 8MB，可通过 innodb_autoextend_increment 参数修改扩展大小。

1.3.3. File-Per-Table 表空间 ​

InnoDB 默认以 File-Per-Table 表空间创建表，该行为由系统变量 innodb_file_per_table 控制。每张表的数据文件以 .ibd 为后缀，存放在对应 schema 目录下。

优势：

  1. 单表更大空间：每个 File-Per-Table 表空间的大小限制为 64TB，单表容量更大；
  2. I/O 优化与备份：可将数据文件存储在不同的存储设备上，以优化 I/O 性能或便于备份管理；
  3. 跨实例导入表：支持从其他 MySQL 实例导入表；
  4. 高效 TRUNCATE TABLE 性能：File-Per-Table 表空间上的 TRUNCATE TABLE 操作性能更优；
  5. 便于监控表大小：可通过文件系统监控各个 .ibd 文件大小；

1.3.4. 通用表空间 ​

通用表空间是一种共享的 InnoDB 表空间，通过 CREATE TABLESPACE 语法创建，能够存储多个表的数据，并具有以下特点：

  • 类似于系统表空间，多个表可共享一个通用表空间；
  • 相较于每表一文件（file-per-table）的表空间，通用表空间在内存中消耗更少的元数据；
  • 数据文件可以放置在 MySQL 数据目录内或目录外，从而方便进行性能优化；
  • 支持所有行格式（REDUNDANT、COMPACT、DYNAMIC、COMPRESSED）；
  • 可以通过 CREATE TABLE 或 ALTER TABLE 指定表空间；

通用表空间的限制：

  • 已生成或现有的表空间不能更改为通用表空间；
  • 删除或截断表后产生的空闲空间不会释放给操作系统；
  • 不支持创建临时通用表空间；
  • 不支持存储临时表；

1.3.5. Undo 表空间 ​

Undo 表空间存储 Undo 日志，用于记录事务对聚簇索引记录的最新更改，以支持回滚操作。初始化 MySQL 实例时，会创建两个默认的 Undo 表空间 (innodb_undo_001 和 innodb_undo_002)，用于存放回滚段。至少需要两个 Undo 表空间以支持自动截断。默认存储在 innodb_undo_directory 变量指定的目录中，若未定义，则存放在数据目录中。

1.3.6. 临时表空间 ​

  • 会话临时表空间：存储用户创建的临时表和优化器创建的内部临时表。会话结束时，表空间被截断并释放回池中。位置由 innodb_temp_tablespaces_dir 变量定义，默认在数据目录的 #innodb_temp 目录下。表空间文件扩展名为 .ibt。

  • 全局临时表空间：存储用户创建的临时表的回滚段。文件默认为 ibtmp1，位于 innodb_data_home_dir 目录下，初始大小约为 12MB，自动扩展。需重启服务器才能回收全局临时表空间。可通过 innodb_temp_data_file_path 变量设置路径、名称、大小和属性，并限制最大文件大小。

1.3.7. 双写缓存 ​

双写缓冲区是 InnoDB 在将缓冲池中的页写入数据文件前，临时存储这些页的区域。用于防止因操作系统、存储子系统或 MySQL 进程意外退出导致的部分页写入（Partial Page Write）问题。崩溃恢复时，InnoDB 可从此缓冲区恢复完整页。

1.4. InnoDB 特性 ​

  • 支持事务：基于 WAL（Write-Ahead Logging）机制，通过 Redo Log 和 Undo Log 实现；
  • 行级锁（Row-Level Locking）：提高并发性，避免表级锁竞争；
  • MVCC（多版本并发控制）：减少读写冲突，提高事务性能；
  • 支持外键：可实现数据完整性约束；

1.5. 适用场景 ​

  • 高并发事务系统，如银行、支付系统、电商订单管理等；
  • 需要支持外键的数据模型；
  • 读写混合的 OLTP 业务场景；

2. MyISAM 存储引擎 ​

MyISAM 是 MySQL 早期的默认存储引擎，它的特点是不支持事务，但读性能较好，适用于只读查询或低写入量场景。

2.1. 数据库文件 ​

MyISAM 表的存储方式如下：

  • .frm 文件：存储表结构信息；
  • .MYD 文件（MyISAM Data）：存储表数据；
  • .MYI 文件（MyISAM Index）：存储索引数据；

2.2. MyISAM 特性 ​

  • 不支持事务和外键；
  • 表级锁（Table-Level Locking），并发性能较低；
  • 支持全文索引（Full-Text Index），适用于搜索引擎需求；
  • 数据崩溃恢复能力差，写入数据时可能发生数据丢失；

2.3. 适用场景 ​

  • 日志系统、数据仓库：数据以写入为主，少量更新操作；
  • 全文检索场景（虽然 InnoDB 也支持全文索引，但 MyISAM 的性能较优）；
  • 低并发环境：如静态网站、论坛等；

3. Memory 存储引擎（HEAP） ​

Memory 存储引擎（以前称为 HEAP）将数据存储在内存中，适用于对数据访问速度要求极高的场景。

3.1. 数据库文件 ​

  • .frm 文件：存储表结构信息；
  • 无数据文件，所有数据存储在 RAM 中；
  • 重启丢失数据（因为数据不持久化）；

3.2. Memory 特性 ​

  • 基于内存存储，访问速度极快；
  • 表级锁（Table-Level Locking），并发性能一般；
  • 仅支持 HASH 和 BTREE 索引；
  • 不支持事务和外键；

3.3. 适用场景 ​

  • 缓存表：存储临时数据，避免频繁访问磁盘；
  • 会话管理：存储短生命周期的数据，如用户会话状态；
  • 临时计算表：如排行榜、热门数据统计；

4. NDB Cluster 存储引擎 ​

NDB（Network Database）是 MySQL Cluster 采用的存储引擎，专为高可用和分布式环境设计，适用于分布式系统和电信级应用。

4.1. 数据库文件 ​

  • .frm 文件：存储表结构信息；
  • NDB 数据存储在多个节点上，可以选择存储在磁盘或内存；

4.2. NDB 特性 ​

  • 高可用性（HA）：数据分布在多个节点上，提供冗余存储；
  • 自动数据分片（Sharding），支持大规模分布式存储；
  • 支持事务，但与 InnoDB 事务机制不同；
  • 适用于高并发、高吞吐的分布式系统；

4.3. 适用场景 ​

  • 电信、银行、支付系统：需要高可用性、高可扩展性；
  • 分布式环境：适用于 MySQL Cluster 部署；

5. 存储引擎对比 ​

特性InnoDBMyISAMMemoryNDB
事务支持√××√
并发控制行级锁表级锁表级锁行级锁
MVCC 支持√×××
B+ 树索引√√√√
哈希索引自适应×√√
全文索引5.6+√××
外键支持√×××
数据持久化√√×√
适用场景事务、高并发只读、高速查询缓存、临时表分布式、高可用

6. 选择合适的存储引擎 ​

  • OLTP 业务：选择 InnoDB（默认）；
  • 日志/只读表：选择 MyISAM；
  • 高性能缓存：选择 Memory；
  • 分布式高可用：选择 NDB Cluster；

7. 引擎管理操作 ​

  1. 查看支持引擎：

    SQL
    SHOW ENGINES; -- 显示所有可用引擎及状态

  2. 指定表引擎：

    SQL
    CREATE TABLE example (id INT) ENGINE=InnoDB; -- 建表时显式指定引擎。

  3. 修改引擎：

    SQL
    ALTER TABLE example ENGINE=MyISAM; -- 转换现有表的引擎。

转载请注明作者和出处

Copyright © 2025-至今 Tenormis