MySQL存储引擎(最全面的概括)
目录
一:MySQL存储引擎
1.什么是存储引擎？
简单的理解为:存储引擎就是处理数据底层逻辑 不同的引擎底层处理方式有所不同
现代数据库大体可以分为三层。最上层用于连接、线程处理等；中间层提供数据库的核心功能，包括 SQL 解析、分析、优化、视图等；底层就是数据库的存储引擎，负责数据的存储与提取。
简单来说，存储引擎是为数据库提供创建、查询、更新、存储数据的软件模块。不同的存储引擎的主要区别是数据的存储方式，此外功能、特性、速度等也有所差异。
存储引擎为数据库屏蔽了底层存储的细节。现在许多数据库管理系统都支持多种存储引擎，通过插件化的方式配置。可以根据具体场景，选择不同的存储引擎。
2.查看存储引擎信息
show engines;
二:MySQL支持的存储引擎
1.存储引擎
在 MySQL 中执行 SHOW ENGINES;，可以看到支持的存储引擎列表，不同环境下可能有一定差异
MySQL 的存储引擎主要分为两大类：事务型、非事务型，从上图中的“Transactions”一列可以看出来，只有 InnoDB 引擎支持事务。
在 MySQL 5.5 及之后的版本中，默认的存储引擎是 InnoDB，而在这之前是 MyISAM。
三:innoDB存储引擎
1.特性
| 特性 | 是否支持 | 特性 | 是否支持 |
|---|---|---|---|
| 多版本并发控制(MVCC) | Yes | 锁机制(Locking granularity) | 行级锁 |
| 事务(Transactions) | Yes | 外键支持(Foreign key support) | Yes |
| 索引(Indexes) | Yes | 热备份/灾难恢复 | Yes |
2.存储结构
在表空间中，所有的数据记录都被**逻辑地**存放在表空间中。表空间被进一步划分为段（segment）、区（extent）、页（page）。页是 InnoDB 管理数据的最小磁盘单位，每个 16KB 大小的页中可以存放 2-200 行的记录。
3.优缺点、适用场景
- 支持事务、行锁、外键、MVCC 等特性
- 并发能力较好，适用于更新密集的场景。这是因为在更新数据时，InnoDB 使用的是行锁，粒度小，竞争情况少，从而增加增加了并发处理（插入数据时使用的是表锁）
- 读写效率较差。主要原因在于索引查询后还需要根据主键进行二次查找
- 占用空间大
大多数场景下都可以选择 InnoDB 引擎，InnoDB 也是 MySQL 的默认存储引擎。
四:MyISAM存储引擎
MyISAM 基于旧的 ISAM 存储引擎，增加了许多有用的扩展。
1.特性
| 特性 | 是否支持 | 特性 | 是否支持 |
|---|---|---|---|
| 索引(Indexes) | Yes | 锁机制(Locking granularity) | 表级锁 |
2.存储结构
不同于 InnoDB，MyISAM 的数据是**顺序存储**的。索引的 B+ 树叶节点存放**数据记录的地址**，可以直接定位到数据，因此查找速度很快
3.优缺点、适用场景
- 占用空间小
- 处理速度快，适用于选择（select）密集的场景。这是因为可以通过索引节点直接定位到数据，不需要二次查找
- 支持全文索引（InnoDB 也支持）
- 不支持事务
- 只支持表级锁（为了并发插入）
适用于包含大量读取操作（read-heavy）、不需要事务支持的场景，效率很高，例如数据仓库和 Web 应用程序。
五:Memory存储引擎
1.简介
| 特性 | 是否支持 | 特性 | 是否支持 |
|---|---|---|---|
| 索引(Indexes) | Yes | 锁机制(Locking granularity) | 表级锁 |
Memory 存储引擎，顾名思义，将数据存在系统内存里，因此读写速度快，性能高。但是**安全性不高**，数据可能因为进程崩溃或硬件重启而丢失。
Memory 中的每个表对应磁盘上的一个 `.frm` 文件，该文件只存储表结构，数据都存储在内存中。
2.适用场景
- 需要很快的读写速度
- 数据库表相对较小（内存空间有限）
- 对数据的安全性要求较低
以上是“且”的关系。不过一般很少使用 Memory 存储引擎。
六:存储引擎功能总结
1.MyISAM
是MySQL5.5版本之前默认的存储引擎
但是安全性较低 不支持事务、行级锁和外键 针对数据的操作较于InnoDB不够安全
但是数据的存取速度较于InnoDB更快
2.InnoDB
是MySQL5.5版本之后默认的存储引擎
该引擎支持事务、行级锁、外键
存取数据的速度没有MyISAM快但是功能和安全性更高
事务 : 保证数据多条sql语句同时执行 (要么同时成功 要么同时失败)
行级锁 : 相当于互斥锁，一次只能有一个人操作
外键 : 用来建表与表之间关系的
3.memory
基于内存存取数据 速度很快但是断电立刻丢失(内存)
4.blackhole
黑洞 任何放入其中的数据都会消失(类似于垃圾处理站)
六:创建表可以指定存储引擎
1.使用表创建指定的存储引擎
格式:
create table t1(id int) engine=存储引擎;
create table t2(id int) engine=MyISAM;
create table t3(id int) engine=InnoDB;
create table t4(id int) engine=memory;
create table t5(id int) engine=blackhole;
2.比较存储引擎之间的差异
- 存储引擎后缀名
MyISAM
三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引(索引是用来加快数据查询的)
InnoDB
两个文件
.frm 表结构
.ibd 数据与索引
memory
.frm 表结构
blackhole
.frm 表结构
3.存储引擎插入数据演示
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
insert into t5 values(1);
4.memory是否为 内存存储，关机数据丢失，再次进行测试。
4.1以管理员身份运行打开cmd
4.2重启mysql
net stop mysql
net start mysql
4.3重新打开cmd登录mysql(查看memory数据是否存在)
查看memory数据是否为内存
select * from t4
七:综合对比
八:如何选择存储引擎
1.存储引擎选择建议
每个存储引擎在功能和限制上都有差异。在某些情况下，只能选择特定的存储引擎，比如需要支持事务、热备份、崩溃恢复、外键支持、缓存等，或者不能有存储限制。在其他情况下，可以灵活选择不同的存储引擎：
- 同一个数据库的不同表，可以使用不同的存储引擎。比如查询密集的表可以使用 MyISAM，用于查询的临时表可以使用 Memory
- 不同服务器上的数据库，也可以使用不同的存储引擎。比如读写分离的场景下，从数据库可以使用 MyISAM，提高查询速度
