主要内容:
存储引擎概括比较
介绍各个存储引擎的特点、存储格式以及适合场合
存储引擎到底是什么呢?其实就是数据库如何存储数据、怎样建立索引以及如何查询更新数据等技术的实现方法,数据通过使用不同的技术存储在文件中,使用不同的存储机制、索引方式来提供不同的功能。MySQL存储引擎可以通过以下命令查询,我的MySQL版本是5.7.17,默认存储引擎是InnoDB。
mysql> show engines;
各存储引擎比较
(☑️表示支持,✖️表示不支持)
存储引擎
MyISAM
InnoDB
Memory
Merge
存储限制
无
64TB
有
无
锁机制
表锁
行锁
表锁
表锁
事务支持
✖️
☑️
✖️
✖️
外键约束
✖️
☑️
✖️
✖️
B树索引
☑️
☑️
☑️
☑️
哈希索引
✖️
✖️
☑️
✖️
全文索引
☑️
✖️
✖️
✖️
MyISAM
特点
不支持事务,不支持外键。
表级锁,支持全文索引。
存储格式
建立MyISAM引擎的表时,会在本地磁盘上创建三个文件,文件名为表名。例如创建了XXX表,会生成三个文件:XXX.frm存储表定义、XXX.MYD存储数据和XXX.MYI存储索引。
举个例子,我在engine_test数据库下新建了一个MyISAM存储引擎的数据表。
创建MyISAM存储引擎的数据表
然后进入到my.cnf配置文件配置的datadir目录下,我这边配置的是/usr/local/Cellar/mysql/5.7.17/data,会发现许多文件夹其实对应的是数据库。进入到数据库文件夹后,发现了对应的三个文件。
测试MyISAM引擎表创建后生成的文件
同时MyISAM支持三种存储格式。
静态表:默认存储格式,字段都是固定长度的。
这种存储方式优点的话是存储非常迅速,缓存也方便,同时出现故障也比较容易修复。
缺点的话因为是固定长度,需要合理设置长度,占用的空间比较大;同时数据存储时如果长度不够的话会采用空格来填充,返回数据的时候将结尾的空格再去掉,这个需要注意下会产生问题。下图的情况说明如果本身数据末尾就有空格的话,会造成结果返回中空格缺失。
myisam存储引擎静态表空格缺失问题.png
动态表:动态说明记录不是固定长度的。
优点的话很明显,空间占用相对较少。
缺点的话,如果频繁地更新或删除记录会产生碎片,需要定时清理碎片,以及出现故障时修复困难。
压缩表:每个记录单独被压缩,因此压缩表的话占据的磁盘空间更小了,非常小的访问开支。
适合场合
不适合需要事务处理需求的场景,因为MyISAM不支持事务。
选择密集型。MyISAM筛选大量数据时非常迅速。
插入密集型。MyISAM并发插入特性使得可以同时选择和插入数据。
InnoDB
健壮的事务型存储引擎。
特点
InnoDB是MySQL存储引擎中唯一支持事务的。
同时引入了行级锁定、读取过程不产生锁以及外键约束。
基于磁盘存储,并将记录按页的方式进行管理
存储格式
默认存储引擎为InnoDB,所以不设置引擎。
创建InnoDB存储引擎的数据表
发现生成了两个文件。
测试InnoDB引擎表创建后生成的文件.png
适合场合
需要事务处理的场景。
需要使用外键。只有InnoDB支持外键约束。
更新密集的场景。
MRG_MYISAM
特点
顾名思义,mrg_myisam其实是一组MyISAM表的集合,这些MyISAM表结构相同。其实这个引擎中merge后的表其实是各个MyISAM表的聚合,对合并后表的查询、更新其实是对各个分表的操作,对合并表的删除操作只是删除它的定义,对内部分表是没有影响的。
适合场合
服务器日志。
Merge表的优点在于突破了单个MyISAM表大小的限制,并且可以将不同的表分布在多个磁盘上,有效改善访问效率。因此适合于数据仓储。
Memory
特点
Memory引擎使用存在于内存中的内容来创建表,主要目的是为了得到最快的响应时间。每个Memory表只对应一个磁盘文件XXX.frm。使用这个引擎的表数据访问非常快,因为数据存储在内存中,并且默认使用Hash索引。但也因为这点,如果数据库崩溃了,所有的数据将会丢失。而且要求存储在Memory数据表里的数据格式是固定长度的。
适合场合
表内容变化不频繁,且数据量较小
数据是临时的,需要作为统计操作的中间临时表。
如果存储在Memory表中的数据丢失,对应用不会产生明显的负面影响。
CSV
用过Excel的同学应该都知道CSV格式,而CSV存储引擎是基于CSV格式文件来存储数据的。
所有列都必须强制指定NOT NULL。
创建表后会生成三个文件,XXX.CSM存储元信息(表中保存的数据量)、XXX.CSV存储数据和XXX.frm存储表结构。
ARCHIVE
Archive是归档的意思,仅仅支持插入和查询基本功能,很多高级功能都不支持了。拥有很好的压缩机制,记录被请求时实时地进行压缩,所以经常被当作仓库来使用。
BlackHole
黑洞存储引擎,所有插入的数据并不会保存,这个存储引擎表永远保持为空。