MySQL存储引擎都有哪些?
引擎名称 |
事务支持 |
锁粒度 |
数据持久化 |
索引类型 |
InnoDB |
ACID |
行级锁 |
持久化存储 |
B+Tree/哈希 |
MyISAM |
❌ |
表级锁 |
持久化存储 |
B+Tree |
Memory |
❌ |
表级锁 |
内存存储 |
哈希/B+Tree |
Archive |
❌ |
行级锁 |
压缩存储 |
无索引 |
CSV |
❌ |
表级锁 |
CSV文件 |
无索引 |
MRG_MYISAM |
❌ |
表级锁 |
逻辑视图 |
组合索引 |
Blackhole |
❌ |
无 |
不存储 |
无 |
FEDERATED |
❌ |
表级锁 |
远程存储 |
依赖远端 |
事务处理选 InnoDB,只读场景 MyISAM
临时缓存用 Memory,归档压缩 Archive
数据中转靠 CSV,分表合并 MRG
跨库查询 Federated,慎用黑洞 Blackhole
SHOW TABLE STATUS LIKE '表名'; --查看表的存储引擎类型
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| test1 | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | NULL | 2025-05-20 17:16:24 | NULL | NULL | utf8mb4_general_ci | NULL | | |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
SHOW ENGINES; --查看数据库支持的存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
ALTER TABLE 表名 ENGINE=InnoDB; -- 转换引擎(数据量大时谨慎操作)
InnoDB 和 MySAM 的区别是什么?
特性 |
InnoDB |
MyISAM |
事务支持 |
✅ 完整支持 ACID 事务 |
❌ 不支持事务 |
锁机制 |
行级锁(间隙锁防止幻读) |
表级锁 |
崩溃恢复 |
✅ 支持自动崩溃恢复(Redo/Undo Log) |
❌ 需手动修复(myisamchk 工具) |
外键约束 |
✅ 支持外键 |
❌ 不支持 |
索引结构 |
聚簇索引(数据与主键绑定) |
非聚簇索引(数据与索引分离) |
存储方式 |
表空间(.ibd 文件) |
三文件结构(.frm/.MYD/.MYI) |
MVCC 多版本控制 |
✅ 支持(高并发读优化) |
❌ 不支持 |
全文索引 |
✅ 5.6+ 版本支持 |
✅ 原生支持 |
热备份 |
✅ 支持在线热备份 |
❌ 需锁表备份 |