InnoDB
MySQL5.5版本后的默认存储引擎,支持ACID事务。
兼顾高可靠性,高性能。
DML语句支持ACID模型,支持commit,rollback,crash-recovery事务特性
支持行级锁和oracle风格的一致性读取,提高并发,性能
数据存储磁盘,基于主键优化查询,每个InnoDB表都有一个称为clustered index的索引。该索引优化数据,以最小化IO查找数据
支持外键
非特殊需求,优先考虑使用innodb
特性
支持的数据库特性
特性 | 是否支持 |
---|---|
B-tree索引 | 是 |
备份/时间点恢复(在服务器中而不是在存储引擎中实现。) | 是 |
集群数据库 | 否 |
聚集索引 | 是 |
数据压缩 | 是 |
数据缓存 | 是 |
加密数据 | 是(在MySQL服务中通过加密函数实现,在MySQL5.7以上版本中支持静态数据加密) |
外键 | 是 |
全文检索索引 | 是(5.6以上版本) |
地理空间数据类型支持 | 是 |
地理空间索引 | 是(5.7以上) |
哈希索引 | 否(自适应hash索引) |
索引缓存 | 是 |
锁定粒度 | 行级锁 |
MVCC | 是 |
replication | 是 |
存储限制 | 64TB |
T-tree索引 | 否 |
事务 | 是 |
更新数据字典的统计信息 | 是 |
优势
- 如果服务器由于硬件或软件问题而意外退出,无论当时数据库中发生了什么,只需重新启动数据库。 InnoDB的crash-recovery(崩溃恢复)会自动完成在崩溃之前提交的更改,并撤消正在处理但尚未提交的更改,从而允许您重新启动并从上次中断的地方继续。
- InnoDB引擎维护自己的缓冲池,该缓冲池在访问数据时将表和索引数据缓存在主内存中。经常使用的数据直接从内存中处理。此缓存适用于多种类型的信息,并加快了处理速度。在专用数据库服务器上,通常将多达80%的物理内存分配给缓冲池。
- 如果将相关数据拆分到不同的表中,也可以设置强制引用完整性的外键。
- 如果数据在磁盘或内存中损坏,则校验和机制会在使用前提醒您注意虚假数据。 innodb_checksum_algorithm变量定义InnoDB使用的校验和算法。
- 当设计一个具有用于每个表的适当主键列的数据库时,涉及这些列的操作会自动进行优化。在WHERE子句,ORDER BY子句,GROUP BY子句和联接操作中引用主键列非常快速。
- 插入,更新和删除通过称为更改缓冲的自动机制进行了优化。 InnoDB不仅允许对同一表的并发读写访问,而且还缓存更改的数据以简化磁盘I / O。
- 性能优势不仅限于具有长时间运行的查询的大型表。当从表中一遍又一遍地访问相同的行时,自适应哈希索引将接管这些查询,以使这些查找变得更快,就好像它们是从哈希表中出来的一样。
- 支持压缩表和关联的索引。
- 支持加密数据。
- 支持创建和删除索引并执行其他DDL操作,而对性能和可用性的影响要小得多。
- truncate 表文件表空间非常快,可以释放磁盘空间供操作系统重用,其他引擎也支持。
- 使用DYNAMIC行格式,表数据对于BLOB和长文本字段更有效。
- 支持通过查询INFORMATION_SCHEMA表来监视存储引擎的内部工作情况。
- 支持通过查询性能架构表来监视存储引擎的性能详细信息。
- 支持在同一条语句中将InnoDB表与其他MySQL存储引擎的表混合使用。例如,您可以使用联接操作在单个查询中合并InnoDB和MEMORY表中的数据。
- InnoDB专为在处理大数据量时提高CPU效率和最佳性能而设计。
- InnoDB表可以处理大量数据,即使在文件大小限制为2GB的操作系统上也是如此。
MyISAM
查询插入速度快,不支持事务。
特性
特性 | 是否支持 |
---|---|
B-tree索引 | 是 |
备份/时间点恢复(MySQL服务实现,而不是在存储引擎中实现。) | 是 |
集群数据库 | 否 |
聚集索引 | 否 |
数据压缩 | 是 |
数据缓存 | 否 |
加密数据 | 是(在MySQL服务中通过加密函数实现) |
外键 | 是 |
全文检索索引 | 是 |
地理空间数据类型支持 | 是 |
地理空间索引 | 是(5.7以上) |
哈希索引 | 否 |
索引缓存 | 是 |
锁定粒度 | 表级锁 |
MVCC | 否 |
replication(MySQL服务实现,而不是在存储引擎中实现。) | yes |
存储限制 | 256TB |
T-tree索引 | 否 |
事务 | 否 |
更新数据字典的统计信息 | 是 |
不支持事务和行级锁,崩溃后无法安全恢复
每个MyISAM表使用三个文件存储在磁盘上。三个文件的名称相同,但是扩展名不同,.frm文件存储表格式,.MYD存储数据,.MYI存储索引
MySQL5.7以后,创建MyISAM表时需要指定引擎,否则会默认InnoDB引擎。
支持使用myisampack压缩MyISAM表,以减少占用存储空间。
Archive
以尽可能少的空间存储大数据量,不支持索引,一般存储历史数据。
特性
特性 | 是否支持 |
---|---|
B-tree索引 | 否 |
备份/时间点恢复(在服务器中而不是在存储引擎中实现。) | 是 |
集群数据库 | 否 |
聚集索引 | 否 |
数据压缩 | 是 |
数据缓存 | 否 |
加密数据 | 是(在MySQL服务中通过加密函数实现,在MySQL5.7以上版本中支持静态数据加密) |
外键 | 否 |
全文检索索引 | 否 |
地理空间数据类型支持 | 是 |
地理空间索引 | 否 |
哈希索引 | 否 |
索引缓存 | 否 |
锁定粒度 | 行级锁 |
MVCC | 否 |
replication | 是 |
存储限制 | 无限制 |
T-tree索引 | 否 |
事务 | 否 |
更新数据字典的统计信息 | 是 |
创建Archive表,会在数据库数据目录下生成.frm的表描述文件,和.ARZ的数据文件,以及.ARN文件
行数据在被插入时就压缩,archive使用zlib无损压缩
支持insert select replace操作,不支持delete update操作
支持Auto_increment,除了auto_increment列外,其他列不支持索引
Auto_increment列不支持插入比当前列最大值小的值
BlackHole
黑洞引擎,BlackHole引擎扮演了黑洞的角色,接收数据,但是丢弃数据,不保存数据,查询返回空结构。
同样创建blackhole表,数据库服务会创建.frm表描述文件,但是没有其他文件了。
支持所有类型的索引
插入到BLACKHOLE表中不会存储任何数据,但是如果启用了基于语句的二进制日志记录,则会记录SQL语句并将其复制到副本服务器。 这可以用作数据转发器或数据过滤器机制。
假设您的应用程序需要副本端过滤规则,但是首先将所有二进制日志数据传输到副本会导致流量过多。 在这种情况下,可以在源主机上设置一个“虚拟”副本进程,其默认存储引擎为BLACKHOLE,如下所示:
源写入其二进制日志。 “虚拟” mysqld进程充当副本,应用了所需的reply-do- *和replicate-ignore- *规则组合,并编写了自己的新的,经过过滤的二进制日志。 此过滤后的日志提供给副本。
虚拟进程实际上并不存储任何数据,因此在复制源主机上运行附加的mysqld进程几乎不会产生处理开销。可以使用其他复制副本重复这种类型的设置。
其他用途
验证转储文件语法。
通过比较在启用和未启用二进制日志记录的情况下使用BLACKHOLE的性能来比较二进制日志记录的开销。
BLACKHOLE本质上是“无操作”存储引擎,因此可以用于查找与存储引擎本身无关的性能瓶颈。
CSV
逻辑上由逗号分隔的数据
创建CSV表时,服务器会在数据库目录中创建表格式文件{表名}.frm。 存储引擎还创建纯文本数据文件{表名}.CSV。 当将数据存储到表中时,存储引擎会将其以逗号分隔的值格式保存到数据文件中。
创建CSV表还会创建一个对应的图元文件,该图元文件存储表的状态和表中存在的行数。 该文件的名称与带有扩展名CSM的表的名称相同。
电子表格软件可以读取,甚至数据库创建的.CSV文件
如创建test_csv表并添加数据后
# ls
db.opt test_csv.CSM test_csv.CSV test_csv.frm
# cat test_csv.CSV
1,"ruoxi","语文",99,"2021-03-01 16:17:56"
2,"ruoxi","数学",98,"2021-03-01 16:18:27"
3,"ruoxi","英语",97,"2021-03-01 16:18:27"
局限性
CSV引擎,不支持索引
不支持分片
CSV引擎创建表所有列都必须Not Null
不支持列值制动增长 AUTO_INCREMENT
Memory
Memory存储引擎,在内存中存储。数据容易受到崩溃,断电,硬件问题影响,因此只用做临时工作区或者只读缓存,通常从其他表提取数据。
Memory存储引擎 VS NDB Cluster
希望部署使用Memory存储引擎处理重要,高可用或经常更新的数据时,应该考虑NDB Cluster是否是更好的选择。
选择Memory引擎场景的典型特征:
- 临时性,非关键性数据操作,如Session,缓存。当MySQL服务器停止或者重启后,数据将会丢失。
- 数据量适中,内存中存储,可以快速访问和低延迟,但是一般内存资源更稀缺。
- 只读数据或只能只读访问的数据
NDB Cluster提供了与Memory引擎相同功能,但是具有更高的性能水平,并提供了Memory没有的功能: - 行级锁,多线程操作,减少竞争,提高性能
- 包含语句混合也具有可伸缩性
- 可选的磁盘支持操作,支持持久化
- 多主机操作,没有单点故障,高可用99.999%
- 跨节点自动数据分发,应用程序无需自定义分片或分区
- 支持Memory不支持的可变长度数据类型(Blob Text)
MERGE
Merge存储引擎,也称作MRG_MyISAM引擎,特点是一组相同结构的MyISAM表可以用作一个Merge表。
结构相同包括列数据类型相同,索引相同,列顺序相同。
但是列和索引的名称可以不同,表、列、索引的注释可以相同。
Merge表的替代方案是分区表,将单个表分区,存储在单独文件中。分区可以使某些操作有效执行,并不限于MyISAM引擎。
创建Merge表时创建.frm文件和.MRG文件,.frm文件存储表格式,.MRG存储基础MyISAM表的信息
可以在merge表上执行INSERT UPDATE DELETE SELECT来操作MyISAM多个表的数据,但是映射到Merge表的MyISAM表不许有SELECT UPDATE INSERT DELETE权限
使用 Drop table删除表后,只会删除Merge表,MyISAM表不会受到影响
FEDERATED
FEDERATED存储引擎使您可以从远程MySQL数据库访问数据,而无需使用复制或群集技术。 查询本地FEDERATED表会自动从远程表中提取数据。 没有数据存储在本地表上。
如表创建语句如下:
CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
(完)(^_^)