五，MySQL存储引擎

normaling
138 阅读10分钟

一，概述

MySQL体系结构图

image

  • Innodb引擎是在mysql的5.5版本之后的默认存储引擎。

  • Index是在引擎层次的，不同的存储引擎index的用法不同。

  • 存储引擎就是存储数据，建立索引，更新查询数据等技术的实现方式。

  • 存储引擎是基于表的而不是基于数据库的，故存储引擎又叫表的类型

二，语法

查询当前表的存储引擎类型

show create table 表名;

指定存储引擎

create table 表名(
...
)engine=存储引擎;

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

show engines;

三，存储引擎

3.1 Innodb存储引擎

  1. 支持ACID模型
  2. 支持事务
  3. 行级索引，提高了并发访问的性能
  4. 支持外键，确保了数据的完整性和正确性

InnoDB 逻辑存储结构层级

从大到小依次为： 表空间（Tablespace） → 段（Segment） → 区（Extent） → 页（Page） → 行（Row）

image

3.1.1 表空间（Tablespace）

表空间是 InnoDB 存储结构的最高层级，每个表空间对应一个或多个物理文件（如 .ibd 文件）。主要分为以下几类：

(1) 系统表空间（System Tablespace）

  • 作用：
    • 存储数据字典（表结构、索引定义等元数据）。
    • 存储 Undo Log（默认情况下，可通过配置分离）。
    • 存储 Double Write Buffer（防止页断裂的备份区域）。
  • 文件：默认名为 ibdata1（可配置多个文件）。
  • 配置参数： 
    innodb_data_file_path = ibdata1:12M:autoextend

(2) 独立表空间（File-Per-Table Tablespace）

  • 作用：每个表单独存储在一个 .ibd 文件中（推荐默认启用）。

  • 优点：

    • 表删除时可直接回收磁盘空间。
    • 减少系统表空间的膨胀。

  • 配置：

    innodb_file_per_table = ON  # MySQL 5.6+ 默认开启

(3) 通用表空间（General Tablespace）

  • 作用：多个表共享一个表空间文件（类似系统表空间，但由用户创建）。
  • 使用场景：集中管理多个表，减少文件数量。
  • 创建示例： 
    CREATE TABLESPACE `general_ts` ADD DATAFILE 'general_ts.ibd' ENGINE=InnoDB;
CREATE TABLE t1 (id INT) TABLESPACE general_ts;

(4) Undo 表空间（Undo Tablespace）

  • 作用：单独存储 Undo Log（从系统表空间分离）。
  • 配置： 
    innodb_undo_directory = /path/to/undo
innodb_undo_tablespaces = 2  # 设置 Undo 表空间数量

(5) 临时表空间（Temporary Tablespace）

  • 作用：存储临时表、排序缓冲区等临时数据。
  • 文件：默认名为 ibtmp1。

3.1.2 段（Segment）

段是表空间的下一级逻辑单元，一个索引对应两个段：

  • 叶子节点段（Leaf Segment）：存储 B+Tree 的叶子节点（实际数据行）。
  • 非叶子节点段（Non-Leaf Segment）：存储 B+Tree 的非叶子节点（索引指针）。

此外还有：

  • 回滚段（Rollback Segment）：管理 Undo Log，用于事务回滚和 MVCC。
  • 系统段（System Segment）：存储数据字典等系统信息。

3.1.3 区（Extent）

  • 作用：段由多个区组成，区是物理连续分配的存储单元（减少磁盘随机 I/O）。
  • 大小：每个区固定为 1MB（64 个连续的页，每页 16KB）。
  • 分配策略：
    • 小表初始分配 32 个页（约 0.5MB），避免浪费空间。
    • 大表按区为单位扩展。

3.1.4 页（Page）

页是 InnoDB 磁盘管理的最小单元，也是内存与磁盘交互的基本单位。

(1) 页的类型

页类型作用
数据页（Index）存储实际的行数据和索引（B+Tree 节点）。
Undo 页存储 Undo Log 记录。
系统页存储表空间元数据。
BLOB 页存储大对象数据（如 TEXT/BLOB）。

(2) 数据页（Index Page）结构

每个数据页（16KB）包含以下部分：

  • File Header（38B）：页的元数据（如页号、前后页指针）。
  • Page Header（56B）：页的状态信息（如记录数量、空闲空间）。
  • Infimum/Supremum Records：虚拟的“最小”和“最大”记录，用于边界管理。
  • User Records：实际的行数据（按主键顺序存储）。
  • Free Space：未使用的空间。
  • Page Directory：槽（Slots）数组，加速记录查找（类似二分查找）。
  • File Trailer（8B）：校验和，用于检测页是否完整。

3.1.5 行（Row）

行是页中存储的最小逻辑单元，InnoDB 支持两种行格式（通过 ROW_FORMAT 指定）：

(1) 行格式类型

行格式特点
REDUNDANT旧格式，兼容性强但存储效率低（MySQL 5.0 之前默认）。
COMPACT紧凑格式，节省存储空间（MySQL 5.1+ 默认）。
DYNAMIC动态格式，优化 BLOB/TEXT 存储（MySQL 5.7+ 默认）。
COMPRESSED压缩格式，节省空间但增加 CPU 开销。

(2) 行结构（以 COMPACT 格式为例）

  • 变长字段长度列表：记录 VARCHAR、TEXT 等变长字段的长度。
  • NULL 值列表：标记哪些字段为 NULL。
  • 记录头信息（5B）：
    • 删除标记、记录类型（普通记录、索引记录等）、下一条记录的相对位置。
  • 实际数据：
    • 主键列、事务 ID（DB_TRX_ID）、回滚指针（DB_ROLL_PTR）、其他列数据。

3.1.6 逻辑结构与物理存储的关系

  • 物理文件：表空间（如 .ibd 文件）由多个区（Extent）组成。
  • 内存管理：InnoDB 通过 Buffer Pool 缓存热点数据页，减少磁盘 I/O。
  • 索引组织：表数据按主键顺序存储在 B+Tree 中（称为“索引组织表”）。

3.1.7 总结

层级作用
表空间管理物理文件与逻辑数据的映射，支持多种类型。
段按索引划分存储单元，管理叶子节点和非叶子节点。
区以连续空间分配减少磁盘碎片，提升 I/O 效率。
页作为磁盘与内存交互的最小单元，统一管理数据、索引和系统信息。
行存储实际数据，格式设计直接影响存储效率和访问性能。

通过这种层次化的逻辑结构，InnoDB 实现了：

  • 高效的空间管理（区、页的连续分配）。
  • 事务与并发控制（Undo 段、行格式中的事务字段）。
  • 快速数据访问（B+Tree 索引、页目录优化）。

实际应用中，理解逻辑存储结构有助于：

  • 优化表设计（如选择合适的行格式 ROW_FORMAT）。
  • 诊断性能问题（如页分裂、碎片整理）。
  • 合理配置存储参数（如 innodb_page_size 调整页大小

3.2 Myism存储引擎的特点

3.2.1 表文件结构

每个 MyISAM 表在磁盘上存储为 3 个文件：

  • .MYD 文件：存储表数据（MYISAM Data）。
  • .MYI 文件：存储索引（MYISAM Index）。
  • .frm 文件：存储表结构定义（与 InnoDB 共享此文件）。

3.2.2 索引类型

  • B-Tree 索引：支持主键索引和普通索引，所有索引均为非聚簇索引（数据与索引分离）。
  • 全文索引（FULLTEXT）：支持全文搜索（MySQL 5.6 前仅 MyISAM 支持）。
  • 空间索引（SPATIAL）：支持地理空间数据（如 GIS 坐标）。

3.2.3 锁机制

  • 表级锁：
    • 读操作加共享锁（Shared Lock），写操作加排他锁（Exclusive Lock）。
    • 并发性能差，高并发写入场景易阻塞。

3.2.4 其他特性

  • 压缩表：支持通过 myisampack 工具压缩只读表，节省存储空间。
  • 延迟索引更新：通过配置 delay_key_write，批量写入时延迟更新索引，提升写入性能。
  • 静态表（Fixed-length）：若表中所有字段为定长类型（如 CHAR），MyISAM 会按固定长度存储，加快查询速度。

3.2.5 Myism的优缺点

优点缺点
1. 查询速度快（读密集型场景）1. 不支持事务（无法保证 ACID）
2. 全文索引支持（早期版本）2. 表级锁导致并发写入性能差
3. 存储空间占用小3. 无崩溃恢复能力，数据易损坏
4. 易于维护和修复4. 不支持外键
5. 适合只读或低并发场景5. 不支持 MVCC 和行级锁

3.2.6 总结

MyISAM 的核心价值：

  • 简单高效的读性能（适合静态数据）。
  • 低存储开销（适合资源受限环境）。

使用建议：

  • 避免在高并发写入场景中使用 MyISAM。
  • 优先使用 InnoDB，仅在特定需求（如遗留系统维护）时选择 MyISAM。

3.3 Memory存储引擎

MySQL 的 Memory 存储引擎（原名为 HEAP）是一种将数据完全存储在内存中的轻量级引擎，适用于需要极速访问但不要求数据持久化的场景。

3.3.1 Memory 引擎的核心特性

1. 内存存储

  • 数据存储：所有数据存放在内存中，读写速度极快（接近内存操作速度）。
  • 持久化：服务器重启或崩溃后数据丢失，表结构保留（仅数据清空）。

2. 锁机制

  • 表级锁：并发写操作时加表级锁，读操作加共享锁。
  • 并发性能：适合读多写少场景，高并发写入易阻塞。

3. 索引支持

  • 索引类型：
    • 默认哈希索引：支持快速等值查询（WHERE key = value）。
    • B-Tree 索引：可显式创建，支持范围查询（WHERE key > value）。
  • 索引限制：索引数据也存储在内存中，总大小受 max_heap_table_size 限制。

4. 数据类型限制

  • 不支持：BLOB、TEXT、JSON 等变长类型。
  • 行长度限制：所有字段必须为定长（如 CHAR、INT），行总长度由 max_heap_table_size 控制。

5. 临时表

  • 隐式使用：MySQL 内部在处理查询时自动创建的临时表默认使用 Memory 引擎（若数据量小且无大对象字段）。
  • 显式创建：用户可手动创建 Memory 表存储临时数据。

3.3.2 Memory 引擎的适用场景

1. 高速缓存

  • 示例：存储频繁访问的静态数据（如配置项、热点数据）。
  • 优势：比磁盘型引擎（如 InnoDB）响应更快。
  • 替代方案：可结合 Redis 等专业缓存系统，但 Memory 表支持 SQL 查询。

2. 会话存储（Session Storage）

  • 示例：Web 应用的会话信息（用户登录状态）。
  • 注意：需容忍服务重启后会话丢失，或通过定期持久化到磁盘表弥补。

3. 中间结果处理

  • 示例：复杂查询中的中间结果暂存（如统计计算的临时数据集）。
  • 优势：避免频繁读写磁盘，提升处理效率。

4. 只读/低频写查询

  • 示例：数据字典、枚举值的映射表。
  • 限制：写入频繁时表级锁会成为瓶颈。

3.3.3 Memory 引擎的操作示例

1. 创建 Memory 表

-- 创建表（显式指定 ENGINE=MEMORY）
CREATE TABLE cache_session (
    session_id VARCHAR(32) PRIMARY KEY,
    user_id INT NOT NULL,
    expires_at DATETIME
) ENGINE=MEMORY;

-- 创建 B-Tree 索引
CREATE INDEX idx_user_id ON cache_session(user_id) USING BTREE;

2. 调整内存表大小

-- 设置单个 Memory 表的最大容量（单位：字节）
SET max_heap_table_size = 64 * 1024 * 1024;  -- 64MB

-- 全局设置（需重启生效）
SET GLOBAL max_heap_table_size = 128 * 1024 * 1024;  -- 128MB

3. 数据持久化备份

-- 将 Memory 表数据转存到磁盘表（防止重启丢失）
CREATE TABLE cache_session_backup LIKE cache_session;
ALTER TABLE cache_session_backup ENGINE=InnoDB;
INSERT INTO cache_session_backup SELECT * FROM cache_session;

3.3.4 Memory 引擎的局限性

1. 数据易失性

  • 风险：服务重启或崩溃导致数据丢失。
  • 缓解：定期将数据转存到磁盘表，或通过主从复制同步到其他节点。

2. 内存容量限制

  • 参数限制：表大小受 max_heap_table_size 和 tmp_table_size 控制。
  • 溢出处理：若数据量超过限制，MySQL 会将临时表转换为磁盘表（如 MyISAM）。

3. 并发性能

  • 锁竞争：表级锁导致高并发写入时性能下降。
  • 替代方案：使用 InnoDB（行级锁）或分布式缓存（如 Redis）。

4. 功能缺失

  • 不支持事务、外键、MVCC：无法替代 InnoDB 的核心功能。
  • 无持久化日志：不同于 Redis 的 AOF/RDB 机制。

3.3.5 使用建议

  1. 谨慎选择数据类型：避免变长字段，优先使用定长类型（如 CHAR 替代 VARCHAR）。
  2. 监控内存使用：避免单个 Memory 表占用过多内存，影响系统稳定性。
  3. 结合持久化表使用：通过定时任务将 Memory 表数据同步到磁盘表。
  4. 替代方案评估：对于高并发写入或需要持久化的场景，优先考虑 InnoDB 或 Redis。

3.4 三种存储引擎的区别

特点InnodbMyISAMMemory
存储限制64TB有有
事务安全支持------
锁机制行锁表锁表锁
B+tree索引支持支持支持
Hash索引------支持
全文索引5.6版本后支持支持---
空间使用高底N/A
内存使用高低中等
批量插入的速度低高高
支持外键支持------
avatar
文章
阅读
粉丝