MySQL之存储引擎
一、简介
MySQL支持的存储引擎有好几种，如Memory/InnoDB/MyISAM/CSV/ARCHIVE等。通过show engines命令可以查看MySQL所支持的存储引擎。如下图所示：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CTPJhnXx-1645604415159)(C:\Users\zero\AppData\Roaming\Typora\typora-user-images\image-20220223152241771.png)]
从3.23.34a版本开始就包含InnoDB存储引擎，更是在5.5之后的版本默认采用InnoDB引擎。InnoDB是MySQL的默认事务型存储引擎，被设计用来处理大量的短期（short-live）事务。可以确保事务的完成提交（Commit）和回滚（Rollback）。
二、分析
下面就每种存储引擎的使用场景及优缺点做简单的介绍说明：
1、InnoDB引擎
InnoDB存储引擎是MySQL默认的事务型引擎，在平时的业务开发中，没有特殊的需求外，默认也是使用InnoDB引擎。InnoDB引擎底层的数据文件结构分为两部分：
- 表名.frm：存储表结构
- 表名.ibd：存储数据及索引
在之前的版本中，字典数据以元数据文件、非事务表等来存储。8.0版本之后，这些元数据文件被删掉了，如
.frm,.par,.trn,.isl,.db.opt等。
在开发中，除了新增和查询外，还需要更新、删除操作，应该优先选择InnoDB引擎，InnoDB是为处理巨大数据量的最大性能设计的。采用B+Tree的数据结构存储，在叶子节点上既有数据也存了索引，因此会占用更多的磁盘空间来保存数据和索引。
2、MyISAM引擎
MyISAM存储引擎提供了大量的特性，包括全文索引，压缩、空间函数等，但MyISAM不支持事务、行级锁、外键，并且有一个很大缺陷就是崩溃后无法安全恢复。MySQL在5.5版本之前的默认存储引擎就是MyISAM。MyISAM引擎底层的数据文件结构分为三部分：
-
表名.frm：存储表结构
-
表名.MYD：存储数据
-
表名.MYI：存储索引
MyISAM只缓存索引，不缓存真实数据，因此MyISAM的访问速度快，如果是对事务完整性没有要求或以select、insert为主的应用，可以考虑使用MyISAM存储引擎。
3、Archive引擎
Archive引擎是用于数据存档，其特征及功能支持如下：
| 特征 | 支持 |
|---|---|
| B树索引 | 不支持 |
| 备份/时间点恢复 （在服务器中实现，而不是在存储引擎中） | 支持 |
| 集群数据库支持 | 不支持 |
| 聚集索引 | 不支持 |
| 压缩数据 | 支持 |
| 数据缓存 | 不支持 |
| 加密数据（加密功能在服务器中实现） | 支持 |
| 外键支持 | 不支持 |
| 全文检索索引 | 不支持 |
| 地理空间数据类型支持 | 支持 |
| 地理空间索引支持 | 不支持 |
| 哈希索引 | 不支持 |
| 索引缓存 | 不支持 |
| 锁粒度 | 行锁 |
| MVCC | 不支持 |
| 存储限制 | 没有任何限制 |
| 交易 | 不支持 |
| 更新数据字典的统计信息 | 支持 |
4、Blockhole引擎
丢失写操作，读操作会返回空内容。
5、CSV引擎
存储数据是，以逗号分隔各个数据项，这种格式的数据也可以用excel打开。
6、Memory引擎
基于内存的实现的引擎。主要的特征就是响应速度快，但是当MySQL服务崩溃时会有造成数据丢失。另外，要求存储的数据是数据长度不变的格式，像Blob和Text类型的数据是不可用的。主要特征如下：
- Memory同时 支持哈希（HASH）索引 和 B+树索引 。
- Memory表至少比MyISAM表要 快一个数量级 。
- Memory 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩容。
- 数据文件与索引文件分开存储。
Memory适用的场景如目标数据比较小且访问频繁\数据是临时的\丢失数据不影响业务逻辑等场景都可以使用Memory引擎。
7、Federate引擎
Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务 器的灵活性 ，但也经常带来问题，因此 默认是禁用的 。
8、Merge引擎
管理多个MyISAM表构成的集合。
9、NDB引擎
MySQL集群专用存储引擎，也叫NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集 群。
三、对比
| 特 点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存 储 限 制 | 有 | 64TB | 有 | 没有 | 有 |
| 事 务安 全 | / | 支持 | / | / | / |
| 锁 机 制 | 表锁，即使操作一条 记录也会锁住整个 表，不适合高并发的 操作 | 行锁，操作时只锁某一行，不 对其它行有影响，适合高并发 的操作 | 表锁 | 表锁 | 行 锁 |
| B树 索 引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈 希 索 引 | / | / | 支持 | / | 支持 |
| 全 文 索 引 | 支持 | / | / | / | / |
| 集 群 索 引 | / | 支持 | / | / | / |
| 数 据 缓 存 | / | 支持 | 支持 | / | 支持 |
| 索 引缓 存 | 只缓存索引，不缓存 真实数据 | 不仅缓存索引还要缓存真实数 据，对内存要求较高，而且内 存大小对性能有决定性的影响 | 支持 | 支持 | 支持 |
| 数 据 可 压 缩 | 支持 | / | / | / | / |
| 空 间 使 用 | 低 | 高 | / | 低 | 低 |
| 内 存 使 用 | 低 | 高 | 中等 | 低 | 高 |
| 批 量 插 入 的 速 度 | 高 | 低 | 高 | 高 | 高 |
| 支 持外 键 | / | 支持 | / | / | / |
四、InnoDB和MyISAM如何选择？
MySQL5.5之前默认的存储引擎是MyISAM，5.5之后改为InnoDB。两者对比如下：
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一行数据也会所著整张表，不适合高并发操作 | 行锁，操作时只锁住某一行数据，不影响其他数据，适合高并发操作 |
| 缓存 | 只缓存索引，不缓存数据 | 不仅缓存索引还要缓存真实数据，堆内存要求较高，而且内存大小对性能有决定性影响 |
| 自带系统表使用 | 是 | 否 |
| 关注点 | 性能：节省资源、消耗少、简单业务 | 事务：并发写、事务、更大资源 |
| 默认安装 | 是 | 是 |
| 默认使用 | 否 | 是 |