MySQL(一)存储引擎

最新推荐文章于 2024-03-22 19:21:49 发布

那怪大尾巴狼咯

最新推荐文章于 2024-03-22 19:21:49 发布

阅读量134

点赞数 1

分类专栏： mysql 文章标签： mysql 存储引擎

本文链接：https://blog.csdn.net/weihao_/article/details/112916755

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL存储引擎

InnoDB
- 特性
- 优势
MyISAM
- 特性
Archive
- 特性
BlackHole
CSV
Memory
MERGE
FEDERATED

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表，以减少占用存储空间。

特性	是否支持
B-tree索引	否
备份/时间点恢复（在服务器中而不是在存储引擎中实现。）	是
集群数据库	否
聚集索引	否
数据压缩	是
数据缓存	否
加密数据	是（在MySQL服务中通过加密函数实现，在MySQL5.7以上版本中支持静态数据加密）
外键	否
全文检索索引	否
地理空间数据类型支持	是
地理空间索引	否
哈希索引	否
索引缓存	否
锁定粒度	行级锁
MVCC	否
replication	是
存储限制	无限制
T-tree索引	否
事务	否
更新数据字典的统计信息	是

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';

（完）（^_^）

那怪大尾巴狼咯

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL(一)存储引擎

MySQL存储引擎MyISAMArchiveBlackHoleCSVInnoDBMemoryMyISAM查询插入速度快，不支持事务Archive插入速度快，查询支持不好。适合存储大量独立的，历史记录类的数据BlackHole黑洞引擎CSV逻辑上由逗号分隔的数据InnoDBMySQL5.5版本后的默认存储引擎，支持ACID事务，支持事务Memory...
复制链接

扫一扫