MySQL 数据库存储引擎 InnoDB、MyISAM、MEMORY...及使用场景

MySQL 提供了多种存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要安装整个服务器，每个表都使用相同的存储引擎。根据具体要求，每台可采用不同的储能电机。

存储引擎简介

MySQL中的数据使用各种技术存储在文件（或内存）中。

每种技术都使用不同的存储机制、索引技术、锁定级别，并最终提供各种不同的特性和功能。

选择不同的技术可以为您提供额外的速度或功能，从而提高应用程序的整体功能。

存储引擎相当简单

• 如何存储数据
• 如何对存储的数据建立索引
• 如何更新和查询数据等技术。

例如，如果您处理大量临时数据，您可能需要使用内存存储引擎，因为内存存储引擎可以将所有表数据存储在内存中。或者您可能需要一个支持事务处理的数据库，以确保事务失败时能够回滚数据。您必须选择支持事务的存储引擎。

存储引擎分类介绍

下面简单介绍一下MySQL存储引擎。

InnoDB

InnoDB 是一个强大的事务存储引擎。该存储引擎已被多家互联网公司使用，为用户操作超大规模数据存储提供了强大的解决方案。

InnoDB 还引入了行级锁定和外键约束。 InnoDB 在以下情况下是最理想的选择：

• 更新密集型表。 InnoDB 存储引擎特别适合处理多个并发更新请求。
• 案例。 InnoDB存储引擎是标准的MySQL存储引擎，支持事务。
• 自动灾难恢复。与其他存储引擎不同，InnoDB 表可以自动从灾难中恢复。
• 外键限制。 MySQL 中唯一支持外键的存储引擎是 InnoDB。
• 支持自动添加列AUTO_INCREMENT属性。
• 从5.7开始，innodb存储引擎成为默认存储引擎。

一般来说，如果需要事务支持，并且并发读取率较高，InnoDB是一个不错的选择。

MyISAM

MyISAM 表独立于操作系统，这意味着它们可以轻松地从 Windows 服务器移植到 Linux 服务器。

当我们在MyISAM引擎中创建表时，会在本地磁盘上创建三个文件，文件名就是表名。

例如，我通过My​​ISAM引擎创建一个tb_Demo表，会生成以下三个文件：

• tb_demo.frm，存放表定义。
• tb_demo.MYD，保存数据。
• tb_demo.MYI，存储索引。

MyISAM 表无法处理事务，这意味着有事务处理需求的表无法使用 MyISAM 存储引擎。 MyISAM存储引擎特别适合在以下情况下使用：

1. 选择密集型表。 MyISAM存储引擎在过滤大量数据时速度非常快，这是它最突出的优势。
2. 插入密集型表。 MyISAM的同时输入功能允许同时选择和插入数据。

从这一点来看，MyISAM存储引擎非常适合管理服务器日志数据。

MRG_MYISAM

MRG_MyISAM存储引擎是MyISAM表的组合。旧版本叫做MERGE，基本上是一样的东西。

这些MyISAM表结构必须完全相同。尽管它们的用途不如其他引擎那么突出，但在某些情况下它们可能非常有用。

说白了，Merge表就是几个相同的MyISAM表的聚合器；合并表中没有数据，合并类型表可以查询、更新和删除。这些操作实际上是在内部MyISAM表上执行的。操作。

合并存储引擎使用场景

对于服务器日志等信息，常见的存储策略是将数据分成许多表，每个表与特定时间段相关。

例如可以使用12张相同的表来存储服务器日志数据，每张表以每个月份对应的名称命名。当需要根据所有 12 个日志表的数据生成报告时，这意味着必须编写和更新多表查询以反映这些表中的信息。与其编写这些可能导致错误的查询，不如将这些表组合起来并使用一个查询，然后删除连接表而不影响原始数据。删除合并表只是删除合并表的定义，对内部表没有影响。

如何使用Merge存储引擎

• ENGINE=MERGE，表示使用MERGE引擎，其实和MRG_MyISAM是一样的，而且是正确的。 MERGE 在 MySQL 5.7 中不再可见。
• UNION=(t1, t2)，指示哪些表链接到 MERGE 表。可以通过alter table改变UNION的值，实现MERGE表增删子表的功能。例如：

alter table tb_merge engine=merge union(tb_log1) insert_method=last;
复制代码

• INSERT_METHOD=LAST，INSERT_METHOD表示插入方式，取值可以是：0不允许插入； FIRST插入到UNION中的第一个表中； LAST 插入 UNION 的最后一个表。
• MERGE表和组成MERGE数据表结构的各个成员数据表必须具有完全相同的结构。每个成员数据表中的数据列必须以相同的顺序定义相同的名称和类型，并且索引必须以相同的顺序和方式定义。

MEMORY

使用MySQL内存引擎的出发点是速度。为了获得最快的响应时间，所使用的逻辑存储介质是系统内存。

虽然将表数据存储在内存中可以提供高性能，但当 mysqld 守护进程崩溃时，所有内存数据都会丢失。

速度的提高也会带来一些缺点。

要求内存数据表中存储的数据采用定长格式，也就是说不能使用BLOB、TEXT等变长数据类型。 VARCHAR是一种变长类型，但由于MySQL内部将其视为定长CHAR类型，因此可以使用。

内存存储引擎通常用于以下场景：

• 目标数据量较小，使用频率较高。将数据存储在内存中会导致内存占用。可以通过max_heap_table_size参数控制内存表的大小。设置该参数可以限制内存表的最大大小。
• 如果数据是临时的并且必须立即可用，则可以将其存储在内存表中。
• 如果内存表中存储的数据突然丢失，不会对应用服务产生明显的负面影响。
• 内存同时支持哈希索引和B树索引。

B树索引相对于哈希索引的优点是可以使用子查询和通配符查询，还可以使用、>=等运算符来方便数据挖掘。

哈希索引执行“相等比较”很快，但“范围比较”慢很多​​，所以哈希索引值适合用在 = 和 运算符中，不适合用在 运算符中，它也是不适合按顺序使用。

CSV

CSV存储引擎基于CSV格式文件存储数据。

• CSV 存储引擎由于其自身的文件格式，必须强制为所有列指定 NOT NULL。
• CSV引擎也不支持索引，也不支持分区。
• CSV 存储引擎还将包括一个用于存储表结构的 .frm 文件、一个用于存储数据的 .csv 文件以及一个同名的元数据文件，其文件扩展名为 .CSM，用于存储状态表以及表中存储的数据量。
• 每一数据行占用一个文本行。

由于csv文件本身可以用Office等软件直接编辑，所以如果不完整保存的话，可能会不遵循规则。如果 csv 文件的内容已损坏，您还可以使用 CHECK TABLE 或 REPAIR TABLE 命令来检查总和。修复

ARCHIVE

Archive就是存档的意思。归档后，许多高级功能不再受支持。只支持最基本的插入和查询功能。

MySQL 5.5版本之前Archive不支持索引，但MySQL 5.5之后的版本开始支持索引。

档案有良好的压缩机制。它使用 zlib 压缩库并根据请求实时压缩记录，因此它通常用作存储库。

BLACKHOLE

MySQL 在 5.x 系列中提供了 Blackhole 引擎——“黑洞”。功能正如其名：所有写入该引擎的数据都会被丢弃，不进行实际存储； Select 语句内容始终为空。

这与Linux中的/dev/null文件补全完全相同。

那么，不能存储数据的引擎有什么用呢？

即使Blackhole不存储数据，MySQL仍然会正常记录Binlog，并且这些Binlog会正常与Slave同步，数据可以在Slave上进行处理。

这样，当 master 上只需要 Binlog 而不需要数据时，blackhole 就很有用了。

BlackHole 还可以用于以下场景

• 验证语法，确认转储文件语法的正确性 检测性能 由于 Blackhole Small 的性能损失极大，可以用来测试其他的性能MySQL功能点除存储引擎功能点外。

PERFORMANCE_SCHEMA

主要用于收集数据库服务器的性能参数。

MySQL 用户无法使用 PERFORMANCE_SCHEMA 的存储引擎创建表，该存储引擎通常用于注册 binlog 作为复制的中继。

这里有一些官方介绍：MySQL Performance Schema

FEDERATED

代理主要用于访问其他远程MySQL服务器。它与远程MySQL服务器建立客户端连接，并将查询发送到远程服务器运行，然后完成数据访问； MariaDB 上的实现是联合的

给出的名称仅供参考。想了解详情的朋友可以自行查看资料。

常见电机对比

不同的储能电机有各自的特点。为了适应不同的需求，必须选择不同的存储引擎，所以首先要考虑这些存储引擎各自的功能和兼容性。功能 否 否

是 否是加密数据[1]）是♝是是集群数据库支持（集群数据库支持）NoNoNoNo复制支持（复制支持[2]❝）❝ 没有没有是外键支持（外键支持）是否否否存储成本（存储成本（H） 低 /N/ A 非常低 内存消耗（内存成本） 高 ♹N/A'低字典更新（更新数据字典的统计信息）ary)是是是是在某个时间点备份/恢复是是是是多版本并发控制（MVCC）是否否慢快快非常快 数据类型支持地理信息（支持地理空间数据类型）是♹♝❝是❀否支持地理空间索引[4])是

是

否是

存储引擎相关操作命令❝ 使用“SHOW VARIABLES LIKE '%storage_engine%' ;”命令在mysql系统变量中搜索默认存储引擎。输入以下语句：

mysql> SHOW VARIABLES LIKE '%storage_engine%';
+----------------------------------+---------+
| Variable_name                    | Value   |
|----------------------------------+---------|
| default_storage_engine           | InnoDB  |
| default_tmp_storage_engine       | InnoDB  |
| disabled_storage_engines         |         |
| internal_tmp_disk_storage_engine | InnoDB  |
+----------------------------------+---------+
4 rows in set
Time: 0.005s
复制代码

使用命令SHOW ENGINES;显示安装后可用。所有支持的存储引擎和标准引擎，后面加\G可以列出输出结果，可以尝试上面的SHOW ENGINES\G;♿❝，看到默认的数据表类型为当前系统是InnoDB。当然，我们可以通过更改数据库配置文件中的选项来设置默认的表类型。

设置存储引擎

了解完上面的数据库存储引擎后，就可以在my.cnf配置文件中设置你需要的存储引擎了。该参数放置在[mysqld]字段中。 default_storage_engine的参数值如下，例如下面配置的fragment

[mysqld]
default_storage_engine=CSV
复制代码

创建表时，设置表的存储引擎

例如：

CREATE TABLE `user` (
  `id`     int(100) unsigned NOT NULL AUTO_INCREMENT,
  `name`   varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
  `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;
复制代码

创建表Table User时，最后的ENGINE=InnoDB SQL语句设置为该表的存储引擎是InnoDB。

如何选择合适的储能电机

您可以根据以上常见电机的比较来选择您所使用的储能电机。

使用哪种电机必须根据需要灵活选择。数据库中的多个表可以使用不同的引擎来满足不同的性能和实际需求。

使用合适的存储引擎会提高整个数据库的性能。

下面给出了几个选择标准，然后您可以根据标准和实际情况选择相应的存储引擎：

1. 是否需要支持事务；
2. 是否需要使用双机热备；
3. 崩溃恢复，可以接受崩溃；
4. 是否需要外键支持；
5. 存储限制；
6. 支持索引和缓存。

作者：李江涛
链接：https://juejin.im/post/5c8785baf265da2dd2190707
来源：掘金属于作者。商业转载请联系作者获取授权。非商业转载请注明来源。