跳到主要内容

MySQL 存储引擎

存储引擎是 MySQL 的核心组件，负责数据的存储和提取。本章将详细介绍 MySQL 的各种存储引擎。

什么是存储引擎？​

存储引擎是 MySQL 体系架构中的底层组件，负责：

  • 数据的物理存储
  • 索引的构建和管理
  • 事务的支持
  • 锁机制的实现
  • 数据的读写操作

MySQL 采用插件式存储引擎架构，允许用户根据需求选择不同的存储引擎。

查看存储引擎​

-- 查看支持的存储引擎
SHOW ENGINES;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'table_name';

-- 查看表的创建语句
SHOW CREATE TABLE table_name;

-- 查看数据库中所有表的引擎
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database';

InnoDB 存储引擎​

InnoDB 是 MySQL 5.5 之后的默认存储引擎，是最通用的存储引擎。

核心特性​

特性说明
事务支持ACID 特性，支持 COMMIT、ROLLBACK、SAVEPOINT
行级锁定高并发性能，支持 MVCC
外键约束保证数据引用完整性
崩溃恢复通过 redo log 和 undo log 实现自动恢复
聚簇索引数据按主键存储，主键查询效率高
缓冲池内存中缓存数据和索引

InnoDB 架构​

┌─────────────────────────────────────────────────────────────┐
│                    InnoDB 内存结构                           │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│  ┌─────────────────────────────────────────────────────┐  │
│  │                 Buffer Pool                          │  │
│  │   ┌──────────┐ ┌──────────┐ ┌──────────┐           │  │
│  │   │ Data     │ │ Index    │ │ Adaptive │           │  │
│  │   │ Pages    │ │ Pages    │ │ Hash     │           │  │
│  │   └──────────┘ └──────────┘ └──────────┘           │  │
│  └─────────────────────────────────────────────────────┘  │
│                                                            │
│  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐        │
│  │ Change      │ │ Log        │ │ Undo        │        │
│  │ Buffer      │ │ Buffer     │ │ Log         │        │
│  └─────────────┘ └─────────────┘ └─────────────┘        │
│                                                            │
├─────────────────────────────────────────────────────────────┤
│                    InnoDB 磁盘结构                           │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐        │
│  │ System      │ │ .ibd        │ │ Redo       │        │
│  │ Tablespace  │ │ Files       │ │ Log Files  │        │
│  └─────────────┘ └─────────────┘ └─────────────┘        │
│                                                            │
│  ┌─────────────┐ ┌─────────────┐                        │
│  │ Undo        │ │ Temporary   │                        │
│  │ Tablespace  │ │ Tablespace  │                        │
│  └─────────────┘ └─────────────┘                        │
│                                                            │
└─────────────────────────────────────────────────────────────┘

Buffer Pool​

Buffer Pool 是 InnoDB 最重要的内存结构：

-- 查看 Buffer Pool 大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看 Buffer Pool 状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 推荐：设置为物理内存的 70-80%
-- 在配置文件中设置
[mysqld]
innodb_buffer_pool_size = 1G

事务和 MVCC​

InnoDB 通过 MVCC（多版本并发控制）实现高并发：

-- 查看事务隔离级别
SELECT @@transaction_isolation;

-- 查看当前事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 查看锁信息
SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCKS;

InnoDB 配置优化​

[mysqld]
# Buffer Pool 大小（推荐物理内存的 70-80%）
innodb_buffer_pool_size = 1G

# Buffer Pool 实例数（每个实例至少 1GB）
innodb_buffer_pool_instances = 8

# 日志文件大小
innodb_log_file_size = 256M

# 日志缓冲大小
innodb_log_buffer_size = 16M

# 刷新日志策略
# 0: 每秒刷新
# 1: 每次事务提交刷新（最安全）
# 2: 每次事务提交写入 OS 缓存，每秒刷新
innodb_flush_log_at_trx_commit = 1

# 脏页刷新比例
innodb_max_dirty_pages_pct = 75

# IO 线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

MyISAM 存储引擎​

MyISAM 是 MySQL 5.5 之前的默认存储引擎。

核心特性​

特性说明
不支持事务适合只读或读多写少的应用
表级锁定并发性能较低
全文索引支持全文搜索
存储效率数据压缩，空间效率高
快速计数COUNT(*) 性能好

MyISAM 存储结构​

每个 MyISAM 表对应三个文件：
- .frm  表结构定义
- .MYD  数据文件（MyData）
- .MYI  索引文件（MyIndex）

适用场景​

-- 适合：只读表、日志表、统计表
CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45),
    url VARCHAR(500),
    created_at TIMESTAMP
) ENGINE=MyISAM;

-- 不适合：高并发写入、需要事务的表

Memory 存储引擎​

Memory 引擎将数据存储在内存中：

-- 创建内存表
CREATE TABLE session_cache (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id INT,
    data TEXT,
    expire_at TIMESTAMP
) ENGINE=MEMORY;

-- 特点：
-- 1. 数据存储在内存，速度极快
-- 2. 不支持事务
-- 3. 服务器重启后数据丢失
-- 4. 支持哈希索引
-- 5. 不支持 BLOB/TEXT 类型

使用场景​

  • 临时数据存储
  • 查询缓存
  • 会话管理

Archive 存储引擎​

Archive 引擎用于存储大量历史数据：

CREATE TABLE historical_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_data TEXT,
    created_at TIMESTAMP
) ENGINE=ARCHIVE;

-- 特点：
-- 1. 高压缩比，节省存储空间
-- 2. 只支持 INSERT 和 SELECT
-- 3. 不支持索引（除主键外）
-- 4. 适合归档数据

CSV 存储引擎​

CSV 引擎以 CSV 格式存储数据：

CREATE TABLE csv_table (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100)
) ENGINE=CSV;

INSERT INTO csv_table VALUES (1, '张三', '[email protected]');

-- 数据存储为 CSV 文件，可被其他程序直接读取
-- 适合数据交换场景

存储引擎选择指南​

场景推荐引擎原因
需要事务InnoDBACID 支持
高并发读写InnoDB行级锁定
只读/读多写少MyISAM查询性能好
临时数据Memory内存存储
历史归档Archive高压缩比
数据交换CSV标准 CSV 格式

性能对比​

-- 事务处理
InnoDB > MyISAM

-- 读性能（无并发）
MyISAM >= InnoDB

-- 读性能（高并发）
InnoDB > MyISAM

-- 写性能（高并发）
InnoDB >> MyISAM

-- 存储空间
Archive < MyISAM < InnoDB

修改表的存储引擎​

-- 修改表的存储引擎
ALTER TABLE table_name ENGINE=InnoDB;

-- 注意：修改引擎会重建表，大表需要较长时间

-- 更安全的方式：
-- 1. 创建新表
CREATE TABLE new_table LIKE old_table;
ALTER TABLE new_table ENGINE=InnoDB;

-- 2. 迁移数据
INSERT INTO new_table SELECT * FROM old_table;

-- 3. 重命名表
RENAME TABLE old_table TO old_table_backup, new_table TO old_table;

小结​

本章我们学习了：

  1. 存储引擎概念：数据存储和管理的底层组件
  2. InnoDB：默认引擎，支持事务、行锁、外键
  3. MyISAM：适合只读应用，表级锁定
  4. Memory：内存存储，适合临时数据
  5. Archive/CSV：特定场景的存储引擎
  6. 引擎选择：根据应用需求选择合适的引擎

练习​

  1. 查看当前数据库中各表的存储引擎
  2. 创建一个 InnoDB 表并测试事务特性
  3. 比较相同数据在 InnoDB 和 MyISAM 下的存储空间
  4. 配置 InnoDB 的 Buffer Pool 参数

参考资源​