当前位置：首页 > article >正文

MySQL存储引擎：选择与应用

article 2025/2/24 0:37:16

1. 常见存储引擎及其特性

(1) InnoDB

(2) MyISAM

(3) Memory

(4) Archive

(5) CSV

(6) Federated

2. 查看和修改存储引擎

3. 存储引擎选择决策树

总结

MySQL存储引擎是其数据库管理系统的核心组件，负责数据的存储和检索。每种存储引擎都有其独特的特点和适用场景，选择合适的存储引擎对于优化数据库性能至关重要。

1. 常见存储引擎及其特性

(1) InnoDB

事务支持：支持完整的ACID特性，适合需要事务处理的场景。
行级锁定：减少锁冲突，适合高并发写操作。
外键支持：维护数据完整性。
崩溃恢复：通过重做日志和撤销日志保证数据恢复。
聚簇索引：主键索引的叶子节点直接存储行数据，适合基于主键的查询。

代码示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

(2) MyISAM

读取速度快：适合以读为主的应用场景。
不支持事务：不支持行级锁定，适合读多写少的环境。

代码示例：

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
) ENGINE=MyISAM;

(3) Memory

数据存储在内存中：访问速度快，但重启后数据丢失。
适合临时数据：如缓存表或临时查询结果。

代码示例：

CREATE TABLE cache (
    id INT PRIMARY KEY,
    value VARCHAR(255)
) ENGINE=MEMORY;

(4) Archive

数据压缩存储：适合日志或历史数据归档。
只支持INSERT和SELECT：防止误删。

代码示例：

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT
) ENGINE=ARCHIVE;

(5) CSV

数据存储为CSV文件：适合轻量级数据存储。
简单操作：支持基本的读写操作。

代码示例：

CREATE TABLE data (
    id INT PRIMARY KEY,
    value VARCHAR(255)
) ENGINE=CSV;

(6) Federated

远程数据访问：通过本地表访问远程数据库。
适合分布式系统：用于跨数据库查询。

代码示例：

CREATE TABLE remote_table (
    id INT PRIMARY KEY,
    value VARCHAR(255)
) ENGINE=FEDERATED
CONNECTION='mysql://user:password@remote_host:port/db_name/table_name';

2. 查看和修改存储引擎

查看支持的存储引擎：

SHOW ENGINES;

查看表的存储引擎：

SHOW TABLE STATUS LIKE 'table_name';

修改表的存储引擎：

ALTER TABLE table_name ENGINE=InnoDB;

3. 存储引擎选择决策树

在选择存储引擎时，可以根据以下决策树进行判断：

是否需要事务？
├─ 是 → InnoDB
└─ 否 →
    ├─ 是否只读？
    │   ├─ 是 →
    │   │   ├─ 需要全文检索？ → MyISAM
    │   │   └─ 海量数据归档 → Archive
    │   └─ 否 →
    │       ├─ 是否临时数据？ → Memory
    │       └─ 特殊需求 → CSV/Blackhole等
    └─ 是否要求极致读性能？
        ├─ 是 → MyISAM（谨慎评估锁问题）
        └─ 否 → InnoDB