MySQL常见存储引擎

1、概述

MySQL 的存储引擎是数据库的底层核心组件，它决定了数据如何存储、索引如何组织、事务是否支持以及并发控制机制等关键特性。选择合适的存储引擎对数据库的性能、可靠性和功能实现至关重要。
查看支持的存储引擎

1. SHOW ENGINES;

复制代码

设置默认存储引擎
在 MySQL 配置文件 my.cnf 中设置

1. [mysqld]
2. default-storage-engine=InnoDB

复制代码

2、 InnoDB（MySQL 5.5+ 默认引擎）

InnoDB 是 MySQL 最常用的存储引擎，专为事务处理和高并发场景设计，支持 ACID 事务和行级锁。
核心特性：

• 支持事务：完全符合 ACID 特性，支持 COMMIT、ROLLBACK、SAVEPOINT。
  
• 行级锁：仅锁定修改的行，大幅提升并发写性能（适合多用户同时操作）。
  
• 外键约束：支持 FOREIGN KEY，保证数据参照完整性。
  
• 聚簇索引：数据和主键索引存储在一起，查询主键效率极高。
  
• 崩溃恢复：通过 redo log 和 undo log 实现崩溃后的数据恢复。
  
• MVCC：多版本并发控制，支持高并发读写（读不加锁，读写不冲突）。
  

示例

1. -- 创建 InnoDB 表
2. CREATE TABLE users (
3.     id INT AUTO_INCREMENT PRIMARY KEY,
4.     name VARCHAR(100) NOT NULL,
5.     email VARCHAR(100) UNIQUE,
6.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7. ) ENGINE=InnoDB;
9. -- 创建带外键的 InnoDB 表
10. CREATE TABLE orders (
11.     id INT AUTO_INCREMENT PRIMARY KEY,
12.     user_id INT NOT NULL,
13.     amount DECIMAL(10,2),
14.     order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
15.     FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
16. ) ENGINE=InnoDB;

复制代码

3、MyISAM（早期 MySQL 默认引擎）

MyISAM 是 MySQL 最早的存储引擎，不支持事务和行锁，但查询性能较好，适合读多写少的场景。
核心特性：

• 不支持事务：无 COMMIT/ROLLBACK，崩溃后可能数据损坏。
  
• 表级锁：修改数据时锁定整个表，并发写性能差。
  
• 不支持外键：不支持外键约束
  
• 支持全文索引：早期版本中唯一支持全文索引的引擎（MySQL 5.6 后 InnoDB 也支持）。
  
• 存储结构：数据和索引分开存储（.MYD 数据文件，.MYI 索引文件）。
  
• 压缩表：可压缩只读表，节省存储空间。
  

示例

1. -- 创建 MyISAM 表
2. CREATE TABLE logs (
3.     id INT AUTO_INCREMENT PRIMARY KEY,
4.     message TEXT,
5.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
6.     FULLTEXT(message)
7. ) ENGINE=MyISAM;
9. -- 使用全文搜索
10. SELECT * FROM logs WHERE MATCH(message) AGAINST('error');

复制代码

3、Memory（Heap）

Memory 引擎将数据完全存储在内存中，访问速度极快，但数据在重启后丢失，适合临时数据存储。
核心特性：

• 内存存储：数据存于内存，读写速度远快于磁盘引擎。
  
• 支持哈希索引：默认使用哈希索引，等值查询效率极高（范围查询性能差）。
  
• 表级锁：并发写性能有限。
  
• 数据易失：MySQL 重启或崩溃后，数据全部丢失。
  
• 存储限制：受内存大小限制，不适合存储大量数据。
  

示例

1. -- 创建 Memory 表
2. CREATE TABLE session_data (
3.     session_id VARCHAR(32) PRIMARY KEY,
4.     user_id INT,
5.     data TEXT,
6.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7. ) ENGINE=MEMORY;
9. -- 插入数据
10. INSERT INTO session_data (session_id, user_id, data)
11. VALUES ('abc123', 1, 'user_preferences');

复制代码

4、存储引擎常见操作

4.1 查看表的存储引擎

1. SHOW TABLE STATUS LIKE 'table_name';
3. -- 或
4. SELECT TABLE_NAME, ENGINE
5. FROM INFORMATION_SCHEMA.TABLES
6. WHERE TABLE_SCHEMA = 'your_database';

复制代码

4.2 修改表的存储引擎

1. ALTER TABLE table_name ENGINE = InnoDB;

复制代码

4.3 性能优化建议

4.3.1 InnoDB 优化

1. -- 调整缓冲池大小（在my.cnf中设置）
2. [mysqld]
3. innodb_buffer_pool_size = 1G
5. -- 使用合适的行格式
6. ALTER TABLE table_name ROW_FORMAT=COMPRESSED;
8. -- 调整日志文件大小
9. innodb_log_file_size = 256M

复制代码

4.3.2 MyISAM 优化

1. -- 优化表（整理碎片）
2. OPTIMIZE TABLE table_name;
4. -- 调整键缓存大小（在my.cnf中设置）
5. key_buffer_size = 256M

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！