MySQL 常用储存引擎
1一.存储引擎概述
定义：存储引擎是MySQL用于管理数据存储、查询、事务处理的底层组件。
核心作用：决定数据如何存储、索引如何组织、事务是否支持等。
点击查看mysql逻辑架构概述
查看数据库所支持的存储引擎
SHOW ENGINES;
Engine： 表示存储引擎的名称
Support： 表示服务器对存储引擎的支持情况，YES表示支持，NO表示不支持，DEFAULT表示数据库默认使用的存储引擎，DISABLED表示支持引擎但已将其禁用
Comment： 对存储引擎简单的描述
Transactions： 表示存储引擎是否支持事务，可以看到InnoDB存储引擎支持事务，而MyISAM存储引擎不支持事务
XA： 表示存储引擎是否支持XA事务
Savepoints： 表示存储引擎是否支持保存点
二.查看表所使用的引擎
USE TEST; -- 选中TEST数据库 SHOW TABLE STATUS; -- 查看所有表的信息 SHOW TABLE STATUS LIKE 'users'; -- 只查看users表信息
Name 表名
Engine 表的存储引擎
Version 表的版本号，通常为 10
Row_format 行格式，如 Compact、Dynamic 等
Rows 表中的行数,对于INNODB该值是估算的,其他存储引擎是准的
Avg_row_length 平均每行包含的字节数
Data_length 表数据的大小(以字节为单位)
Max_data_length 表的最大容量,该值和存储引擎有关
Index_length 索引的大小(以字节为单位)
Data_free 对于MyISAM表示已分配但目前没有使用的空间,这部分包括之前删除的行,以及和后续可以被insert利用到的空间
Auto_increment 下一个Auto_increment值
Create_time 表单创建时间
Update_time 表数据最后修改时间
Check_time 使用check time命令或者myisamchk工具最后一次检查表的时间
Collation 表的默认字符集和字符排列规则
Checksum 如果启用,保存的是整个表的实时校验和
Create_options 保存表时指定的其他选项.
Comment 该列包含了一些其他的额外的信息,对于MyISAM表,保存的是表在创建时带的注释,对于INNODB则保存的是INNODB表空间的剩余空间信息,如果是一个视图,则该列包含"VIEW"文本字样
三、索引分类
| 引擎 | 事务支持 | 锁粒度 | 外键支持 | 崩溃恢复 | 存储限制 | 索引类型 | 适用场景 |
|---|---|---|---|---|---|---|---|
| InnoDB | 支持 | 行级锁 | 支持 | 支持 | 64TB | B+树聚簇索引 | 高并发事务、OLTP系统 |
| MyISAM | 不支持 | 表级锁 | 不支持 | 不支持 | 256TB | B+树非聚簇索引 | 读多写少、静态数据 |
| Memory | 不支持 | 表级锁 | 不支持 | 无 | 内存限制 | Hash/B树 | 临时表、缓存 |
| Archive | 不支持 | 行级锁 | 不支持 | 支持 | 无 | 压缩存储，仅支持插入 | 日志、归档数据 |
| CSV | 不支持 | 表级锁 | 不支持 | 无 | 文件限制 | 无索引 | CSV文件交互 |
1. InnoDB
>核心特性：
支持 ACID事务，适合高并发场景。
行级锁（默认） + MVCC（多版本并发控制）。
聚簇索引：数据与主键索引绑定，提高查询效率。
支持外键约束（Foreign Key）。
崩溃后可通过 redo log 恢复。
做了很多优化:可预测性读、自适应哈希索引、插入缓冲区、热备份
>存储文件：
.ibd：表数据 + 索引（独立表空间模式）。
ibdata1：系统表空间（共享模式）。
>适用场景：
需要事务的OLTP系统（如订单、用户中心）。
高并发读写（写比例不极端）。
2. MyISAM
>核心特性：
表级锁：读写互斥，并发性能低。
支持全文索引（FULLTEXT）。
非聚簇索引，数据与索引分离存储。
较高的读取速度，写入较慢。
>存储文件：
.MYD：数据文件。
.MYI：索引文件。
.frm：表结构文件。
>适用场景：
读多写少的静态数据（如日志表、配置表）。
数据仓库类应用（需配合压缩特性）。
注意：MySQL 8.0+ 未完全移除 MyISAM，但通过限制系统表、分区表等场景，逐步推动其退出主流使用。开发者应优先采用 InnoDB 引擎以适应官方技术路线
3. Memory
>核心特性：
数据存储在内存中，重启后丢失。
支持Hash索引（默认）和B树索引。
表级锁，并发性能有限。
>适用场景：
临时数据缓存（如会话信息）。
快速查询中间结果。
>限制：
不支持BLOB/TEXT类型。
内存不足时转为磁盘存储（由max_heap_table_size控制）。
4. Archive
>核心特性：
数据压缩存储（节省空间）。
仅支持INSERT和SELECT，删除/更新会重建表。
行级锁，批量插入高效。
>适用场景：
日志、审计数据归档。
无需修改的历史数据存储。
5. CSV
>核心特性：
数据以CSV格式存储，可直接用文本编辑器查看。
不支持索引。
>适用场景：
与外部系统交换CSV数据。
四、存储引擎选择指南
- 默认选择：InnoDB（MySQL 5.5+默认引擎）。
- 事务需求：必须选择InnoDB。
- 读写比例：
- 读多写少且无事务 → MyISAM（旧版本）或 InnoDB（新版本）。
- 高并发写入 → InnoDB（行级锁）。
- 临时数据 → Memory。
- 归档压缩 → Archive。
- 全文检索：InnoDB（MySQL 5.6+支持FULLTEXT）或外部搜索引擎（如Elasticsearch）。
五、代码示例
1. 指定引擎建表
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ) ENGINE=InnoDB;
2. 修改引擎
ALTER TABLE users ENGINE=MyISAM;
六、注意事项
- 混合引擎风险：避免跨引擎事务（如InnoDB与MyISAM）。
- 备份策略：MyISAM需锁表备份，InnoDB支持热备（如
mysqldump --single-transaction）。 - 配置优化：不同引擎需调整不同参数（如InnoDB的
innodb_buffer_pool_size）。
浙公网安备 33010602011771号