MySQL之存储引擎

MySQL之存储引擎

一、简介

​  MySQL支持的存储引擎有好几种，如Memory/InnoDB/MyISAM/CSV/ARCHIVE等。通过show engines命令可以查看MySQL所支持的存储引擎。如下图所示：

  从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
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一行数据也会所著整张表，不适合高并发操作	行锁，操作时只锁住某一行数据，不影响其他数据，适合高并发操作
缓存	只缓存索引，不缓存数据	不仅缓存索引还要缓存真实数据，堆内存要求较高，而且内存大小对性能有决定性影响
自带系统表使用	是	否
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	是	是
默认使用	否	是

----------------------------------------------⭐⭐MySQL系列文章⭐⭐------------------------------------------------

1、MySQL之存储引擎
2、MySQL之索引创建原则
3、MySQL之优化“N板斧”