程序员scholar
  • MySQL
  • MySQL高级
scholar
2023-08-29
目录

第05章_存储引擎

友情提示

转载须知: 以下所有文章整理于B站宋红康老师的《MySQL数据库入门到大牛》。MySQL (opens new window)

# 前言

为了管理方便，人们把 连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后，只需按照生成的 执行计划 调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

MySQL中提到了存储引擎的概念。简而言之,存储引擎就是指表的类型。其实存储引擎以前叫做表处理器,后来改名为 存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

# 1. 查看存储引擎

  • 查看mysql提供什么存储引擎
show engines;
1

image-20240226180206039

存储引擎 支持状态 说明 事务支持 XA分布式事务 保存点支持
ndbcluster 否 集群化的、容错的表 不适用 不适用 不适用
FEDERATED 否 联合MySQL存储引擎 不适用 不适用 不适用
MEMORY 是 基于哈希，存储在内存中，适用于临时表 否 否 否
InnoDB 默认 支持事务、行级锁定和外键 是 是 是
PERFORMANCE_SCHEMA 是 性能模式 否 否 否
MyISAM 是 MyISAM存储引擎 否 否 否
ndbinfo 否 MySQL集群系统信息存储引擎 不适用 不适用 不适用
MRG_MYISAM 是 合并多个相同的MyISAM表 否 否 否
BLACKHOLE 是 “黑洞”存储引擎（写入的数据会消失） 否 否 否
CSV 是 CSV存储引擎 否 否 否
ARCHIVE 是 归档存储引擎 否 否 否
  • 事务支持（Transactions）：指示存储引擎是否支持事务处理。事务处理能力允许数据库管理系统维护在故障或错误情况下的数据一致性。"是"表示支持，"否"表示不支持，"不适用"表示该存储引擎不涉及事务处理。
  • XA分布式事务（XA）：指示存储引擎是否支持XA分布式事务协议，该协议允许多个数据库资源管理器在全局事务中协同工作。"是"表示支持，"否"表示不支持。
  • 保存点支持（Savepoints）：指示存储引擎是否支持在事务内设置保存点，允许事务回滚到特定点。"是"表示支持，"否"表示不支持。

# 2. 设置系统默认的存储引擎

  • 查看默认的存储引擎
show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;
1
2
3

image-20240226180556372

变量名称 值 注释
default_storage_engine InnoDB 指定了MySQL数据库中默认的存储引擎。当创建新表时，如果没有指定存储引擎，将自动使用此存储引擎。InnoDB是一个支持事务的可靠存储引擎，提供了提交和回滚、崩溃恢复能力和多版本并发控制。
default_tmp_storage_engine InnoDB 指定了MySQL在创建临时表时使用的默认存储引擎。这对于查询处理过程中需要临时存储数据的情况特别重要。使用InnoDB作为临时表的存储引擎可以确保事务安全和较好的性能。
disabled_storage_engines 列出了在MySQL实例中被禁用的存储引擎。空值表示没有存储引擎被明确禁用。
internal_tmp_mem_storage_engine TempTable 指定了MySQL在内存中创建内部临时表时使用的存储引擎。TempTable引擎被设计用于优化存储和查询执行，特别是在处理复杂查询（如JOIN操作）时，能够提高性能。
  • 修改默认的存储引擎

如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用 InnoDB 作为表的存储引擎。 如果我们想改变表的默认存储引擎的话，可以在MySQL的命令行中使用如下命令：

-- 设置默认存储引擎设置为MyISAM，退出失效
SET DEFAULT_STORAGE_ENGINE=MyISAM;
1
2

或者修改 my.cnf 文件：

default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service
1
2
3

# 3. 设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为 不同的表设置不同的存储引擎 ，也就是 说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

# 3.1 创建表时指定存储引擎

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎 InnoDB 。如果我们想显 式的指定一下表的存储引擎，那可以这么写：

CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
1
2
3

# 3.2 修改表的存储引擎

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎名称;
1

比如我们修改一下 engine_demo_table 表的存储引擎：

mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB;
1

这时我们再查看一下 engine_demo_table 的表结构：

mysql> SHOW CREATE TABLE engine_demo_table\G
*************************** 1. row ***************************
Table: engine_demo_table
Create Table: CREATE TABLE `engine_demo_table` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
1
2
3
4
5
6
7

# 4. 引擎介绍

# 4.1 InnoDB 引擎：具备外键支持功能的事务存储引擎

InnoDB是MySQL数据库中最常用的事务型存储引擎，自MySQL版本3.23.34a引入，并从版本5.5开始成为默认存储引擎。InnoDB的设计目标是高效地处理大量的短期事务——事务是指一组操作，这些操作要么完全成功，要么完全失败，保证了数据库的完整性。

主要特点

  • 事务支持：InnoDB提供了对事务的完整支持，包括事务的提交（Commit）和回滚（Rollback）功能，确保了数据的一致性和可靠性。
  • 外键支持：InnoDB支持外键约束，这使得数据库设计更加严谨，可以自动维护表之间的参照完整性。
  • 行级锁定：InnoDB实现了行级锁定，而非表级锁定，这减少了多用户环境下的锁竞争，提高了并发处理能力。
  • 高并发：InnoDB通过多版本并发控制（MVCC）提高了读写操作的并发性能，特别适合高并发的应用场景。
  • 数据和索引的缓存：与MyISAM不同，InnoDB不仅缓存索引，还缓存数据，这显著提高了查询速度，但相应地增加了对内存的需求。

数据文件结构

  • 表结构文件：在MySQL 8.0之前，表结构存储在.frm文件中。从MySQL 8.0开始，表结构和数据都集中存储在.ibd文件中，简化了数据文件的管理。
  • 数据和索引文件：.ibd文件存储了表的数据和索引，支持表空间的分离存储。

性能和存储特性

  • 为大数据量设计：InnoDB专为处理大数据量的场景设计，优化了数据的存取效率。
  • 占用更多磁盘空间：与MyISAM相比，InnoDB因为额外存储事务信息和支持事务的能力，会占用更多的磁盘空间。
  • 对内存要求较高：InnoDB缓存了数据和索引，因此对内存的要求比MyISAM更高，内存大小直接影响到数据库的性能

# 4.2 MyISAM 引擎：主要的非事务处理存储引擎

MyISAM是MySQL中一种较早的存储引擎，以其高速读取性能而著称。

MyISAM的特性

  • 全文索引：MyISAM支持全文索引，使其在处理文本搜索查询时表现出色。
  • 数据压缩：MyISAM表可以被压缩以减少磁盘空间的使用，适合于只读或者读取远远多于写入的应用场景。
  • 空间函数支持（GIS）：MyISAM提供对地理信息系统（GIS）的支持，使其能够处理空间数据。

MyISAM的限制

  • 不支持事务：MyISAM不支持事务处理，这意味着它不能保证SQL语句的原子性、一致性、隔离性和持久性（ACID属性）。
  • 不支持行级锁：MyISAM仅支持表级锁，这限制了它在高并发写操作时的性能。
  • 不支持外键：MyISAM表不能定义外键约束，这可能使得维护引用完整性变得更加困难。
  • 崩溃恢复能力差：MyISAM在遇到崩溃或非正常关闭的情况下，可能无法安全恢复数据。

数据文件结构

  • .frm文件：存储表的结构。
  • .MYD（MYData）文件：存储表中的实际数据。
  • .MYI（MYIndex）文件：存储表的索引信息。

应用场景

  • 只读应用：MyISAM的高速读取性能使其非常适合只读或查询密集型的应用。
  • 读为主的业务：对于以SELECT和INSERT操作为主，对事务完整性要求不高的应用，MyISAM也是一个不错的选择。
  • 数据统计：MyISAM在处理如count(*)这类统计查询时效率很高，因为它为每个表维护了一个行计数器。

InnoDB 引擎 和 MyISAM 的区别？

特性/功能 InnoDB MyISAM
事务支持 支持事务处理（ACID兼容） 不支持事务处理
行级锁与表级锁 支持行级锁，更适合高并发的读写操作 仅支持表级锁，写操作会锁定整个表，影响并发性能
外键支持 支持外键，可以强制约束表之间的关系 不支持外键
崩溃恢复能力 具备崩溃恢复能力，可以保证数据的完整性 崩溃后无法安全恢复，容易导致数据损坏
存储限制 较大，理论上只受限于磁盘大小 文件大小有限制，较旧的系统中可能只有4GB的文件大小限制
全文索引支持 从MySQL 5.6版本开始支持全文索引 支持全文索引
性能和资源使用 对CPU和内存资源的需求较高，但提供更好的并发处理能力和速度 在资源使用上较为节约，读操作速度快，但在高并发写入下性能较差
默认设置 从MySQL 5.5版本开始，成为默认存储引擎 在MySQL 5.5之前是默认存储引擎
数据和索引存储方式 数据和索引都被存储在同一个文件中（.ibd），可以有多个表空间 数据文件（.MYD）和索引文件（.MYI）分开存储
主要优势 提供了事务支持、更好的崩溃恢复能力、外键约束等 提供了高速的读取操作、简单的设计和管理、全文索引的高效实现
适用场景 适合需要事务处理、高并发读写操作、数据完整性保护的应用场景 适合读为主、数据一致性和事务完整性要求不高的应用场景

# 4.3 Archive 引擎：用于数据存档

Archive存储引擎在MySQL中是专门为存储大量的归档数据（如日志信息）而设计的。它提供了高效的数据压缩功能，使得存储空间的利用更加高效。Archive引擎支持INSERT和SELECT操作，但不支持DELETE和UPDATE操作。这意味着一旦数据被写入Archive表中，它就不能被修改或删除，这正符合归档数据的特点：不变和只增不减。

核心特性

  1. 数据压缩：Archive引擎在数据写入磁盘时自动进行压缩，减少存储空间需求。读取数据时，会自动解压。这种压缩是透明的，对用户查询数据无影响。
  2. 高插入效率：适合写入密集型的应用，如日志记录。Archive通过缓冲插入操作来提高写入效率。
  3. 行级锁定：支持行级锁，提高了并发访问性能，尤其是对于插入操作。
  4. 只支持INSERT和SELECT：不支持DELETE和UPDATE操作，确保了数据的不变性。
  5. 不支持索引：Archive表不支持索引，这意味着查询可能相对较慢，尤其是在处理大量数据时。
  6. 适用场景：非常适合于日志数据、历史数据或其他不经常查询的大量数据的存储。由于其压缩特性，可以显著减少归档数据对存储资源的占用。

应用场景示例

  • 日志记录：存储应用程序或系统的日志信息，如访问日志、操作日志等。
  • 数据归档：对历史数据进行归档存储，如过去的交易记录、旧的通信记录等。
  • 分析和报告：存储用于定期分析和报告的数据，虽然查询可能不如其他引擎快，但数据压缩和存储效率高。

注意事项

  • 由于不支持索引，查询大量数据时性能可能会受影响。
  • 数据一旦插入就不能修改或删除，需要谨慎设计数据管理策略。

# 4.4 Blackhole 引擎：丢弃写操作，读操作会返回空内容

Blackhole 存储引擎在MySQL中的作用相当于一个黑洞：它接收数据写入操作，但不会实际存储任何数据，所有通过这个引擎写入的数据都会被丢弃。同时，对于任何读取操作，Blackhole 引擎总是返回空结果。这个独特的特性使得Blackhole引擎在特定的应用场景中非常有用，尤其是在MySQL复制配置和性能测试中。

核心特性

  • 写操作丢弃： 执行INSERT、UPDATE、DELETE等写入操作时，数据不会被存储。这些操作会被正常处理，但数据就像进入黑洞一样，消失了。
  • 读操作为空： 执行SELECT查询时，Blackhole引擎不返回任何数据，即使是查询条件能够匹配到写入操作中的数据。
  • 二进制日志记录： 尽管数据不被存储，写入操作会被记录到MySQL的二进制日志中。这使得Blackhole引擎可以在复制配置中充当中继角色，传递二进制日志而不实际存储数据。

适用场景

  • 复制中继： 在MySQL复制架构中，Blackhole引擎可以被用作中继服务器。它接收主服务器的二进制日志并传递给从服务器，而不需要存储数据本身，减轻了存储和处理的负担。
  • 性能测试： 用于评估应用的写入负载能力。由于Blackhole引擎不存储数据，可以消除存储引擎性能对测试结果的影响，更准确地衡量应用程序的写入性能。
  • 特定的数据处理和转发： 在需要对数据进行处理或转发而不需要持久存储的场景中，Blackhole引擎提供了一种不影响系统存储的方式来实现这些操作。

注意事项

  • 数据不可恢复： 一旦数据被写入Blackhole引擎，它就无法被恢复或查询，除非通过二进制日志的方式。
  • 读取操作限制： 不能用于需要读取操作结果的场景，因为它总是返回空结果。

# 4.5 CSV 引擎：存储数据时，以逗号分隔各个数据项

CSV 存储引擎允许MySQL表直接以逗号分隔值（CSV）格式存储在磁盘上。这种存储格式易于与其他应用程序共享数据，特别是那些能够读取和写入CSV文件的程序。CSV引擎将表的内容存储在CSV文件中，使得数据能够在不同的数据库和应用程序间轻松地进行导入和导出。

核心特性

  • 文本文件存储： 数据以纯文本形式存储，每个记录占一行，字段之间由逗号分隔。
  • 易于数据交换： 由于CSV格式广泛应用于数据导入和导出，使用CSV存储引擎的MySQL表可以轻松与Excel、文本编辑器及其他数据库系统交换数据。
  • 无需数据转换： 导入和导出数据时不需要进行数据格式转换，因为数据已经以CSV格式存储。

适用场景

  • 数据共享： 当数据需要在不同的程序或系统之间共享时，CSV格式是一个通用的选择。
  • 快速数据导入/导出： 对于需要频繁导入或导出数据的应用，CSV引擎可以简化这一过程，因为数据已经以所需的格式存储。
  • 简单的数据分析： 对于一些基本的数据分析任务，直接使用文本处理工具处理CSV格式的数据可能比数据库查询更为方便和直接。

使用限制

  • 缺少事务支持： CSV存储引擎不支持事务处理，这可能不适合需要事务控制的应用场景。
  • 有限的索引支持： 与InnoDB等存储引擎相比，CSV存储引擎在索引支持方面有限，这可能影响大量数据的查询性能。
  • 数据类型限制： 所有数据都以文本形式存储，可能需要在应用层进行数据类型的转换和验证。

注意事项

  • 数据一致性和完整性： 使用CSV存储引擎时，需要额外注意数据的一致性和完整性，因为CSV文件可以被外部程序直接修改。
  • 安全性： 由于CSV文件以文本格式存储在文件系统中，确保数据文件的安全性也是一个重要考虑因素。

使用案例如下

mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+---+------------+
| i |      c     |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

创建CSV表还会创建相应的元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相 同，后缀为 CSM 。如图所示

image-20240226200209695

如果检查 test.CSV 通过执行上述语句创建的数据库目录中的文件，其内容使用Notepad++打开如下：

"1","record one"
"2","record two"
1
2

这种格式可以被 Microsoft Excel 等电子表格应用程序读取，甚至写入。使用Microsoft Excel打开如图所示

image-20220616125448555

# 4.6 Memory 引擎：置于内存的表

Memory存储引擎，如其名所示，是一种将数据存储在内存中的MySQL存储引擎。它提供了极高的处理速度，但因数据存储在内存中，所以在数据库重启或崩溃时数据会丢失。这种特性使得Memory引擎非常适合存储临时数据或者对持久性要求不高的场景。

主要特征

  • 内存存储： 数据完全存储在内存中，提供了非常高的读写速度。
  • 索引支持： 支持HASH索引和B+树索引，允许快速的数据访问和查询。
  • 高速访问： 相对于磁盘存储的MyISAM和InnoDB等引擎，Memory引擎提供了更快的数据访问速度。
  • 大小限制： Memory表的大小受到max_heap_table_size和max_rows参数的限制。默认的max_heap_table_size大小为16MB，但可以根据需要调整。
  • 固定长度限制： 要求存储的数据格式长度固定，不支持Blob和Text等变长类型。

使用缺点

  • 数据易丢失： 服务重启或崩溃时，内存中的数据将丢失，因此不适合存储重要或持久性数据。
  • 内存限制： 大量使用Memory表可能会消耗大量内存资源，影响系统其他部分的性能。

适用场景

  • 临时数据处理： 对于需要快速处理的临时数据，如会话信息、缓存数据等。
  • 高速计算： 对于需要极高速度读写访问的数据集，如实时分析、数据聚合等。
  • 非关键数据： 存储短生命周期的数据，或在数据丢失后不会对业务产生重大影响的场合。

注意事项

  • 内存管理： 应合理配置max_heap_table_size，避免单个Memory表占用过多内存资源。
  • 数据备份： 考虑定期将重要的Memory表数据备份到持久性存储，以减少数据丢失风险。
  • 场景选择： 仅在数据的快速访问比数据持久性更重要的场景下使用Memory存储引擎。

# 4.7 Federated 引擎：访问远程表

Federated存储引擎为MySQL提供了一种独特的能力，即能够直接访问存储在远程MySQL服务器上的表。这个引擎作为一个代理，允许一个MySQL数据库实例查询另一个MySQL实例的数据，无需将数据复制到本地服务器。这提供了跨数据库服务器的数据共享和操作的能力。

主要特征

  • 代理访问： Federated引擎允许本地服务器表现得就像访问远程MySQL服务器上的表一样，通过网络进行数据交换。
  • 跨服务器灵活性： 提供了一种机制，使得跨服务器的数据访问和集成变得可行，增强了数据库的互操作性。
  • 默认禁用： 由于潜在的性能影响和安全考虑，Federated引擎在默认安装的MySQL中是禁用的。需要通过配置启用。

优点使用

  • 无需数据复制： 可以直接访问远程数据，无需物理复制数据到本地服务器，节省存储空间。
  • 数据实时访问： 提供了对远程数据的实时访问能力，保证了数据的时效性和一致性。

使用缺点

  • 网络依赖： 数据访问性能和稳定性受网络条件影响，网络延迟或不稳定可能会导致数据访问延迟增加。
  • 安全风险： 需要在服务器之间开放网络访问权限，可能增加安全漏洞的风险。
  • 管理复杂性： 管理跨服务器的连接和数据同步策略可能会变得复杂。

适用场景

  • 数据集成： 在不同地理位置的MySQL服务器之间共享和访问数据，适用于需要集成多个数据源的情况。
  • 远程备份和报告： 对远程服务器的数据进行查询和报告，无需将数据复制到本地服务器。

注意事项

  • 性能考量： 在使用Federated引擎时，应评估网络延迟和带宽限制对性能的影响。
  • 安全配置： 确保适当的网络安全措施，如使用VPN、加密连接等，以保护数据传输过程中的安全。
  • 启用方式： 需要在MySQL配置文件中显式启用Federated存储引擎。

# 4.8 Merge引擎：管理多个MyISAM表构成的表集合

Merge存储引擎，也称为MRG_MyISAM，允许多个具有相同结构的MyISAM表被逻辑地组合成一个单独的表。这对于管理分割的数据非常有用，例如，将历史数据按年分割成不同的表，但在查询时希望能够像访问单一表那样操作这些分割的数据。

主要特征

  • 表结构一致性： 所有被Merge引擎管理的MyISAM表必须具有相同的表结构，即列和索引必须完全相同。
  • 逻辑视图： Merge表提供了一个逻辑视图，允许像操作单个表一样进行查询和更新（有限制），实际上这些操作是在多个MyISAM表上进行的。
  • 只读操作： 默认情况下，Merge表主要用于只读查询。但是，如果所有子表都是非临时的且没有被锁定为只读，也可以进行插入操作。

使用优点

  • 数据管理： 方便地管理和查询分割的数据，尤其是对于时间序列数据的管理非常有效。
  • 性能提升： 对于读操作，可以在多个表上并行查询，提升查询效率。
  • 易于维护： 更新表结构时，只需修改子表，Merge表会自动反映这些更改。

使用缺点

  • 限制性操作： 仅支持部分DML操作，如DELETE和UPDATE操作受限，通常不在Merge表上执行。
  • 依赖MyISAM： 由于依赖MyISAM表，因此继承了MyISAM的限制，包括不支持事务和行级锁。

适用场景

  • 数据分区： 对于需要按时间或其他逻辑分区存储大量数据的应用，使用Merge存储引擎可以有效管理这些分区。
  • 历史数据查询： 当需要频繁查询历史数据，但又不想影响当前数据表的性能时，可以将历史数据移动到Merge表中。

注意事项

  • 备份与恢复： 在进行备份和恢复操作时，需要确保包含了所有的子MyISAM表。
  • 事务支持： 需要注意Merge存储引擎本身不支持事务处理。对于需要事务支持的应用，应考虑其他存储引擎。

# 4.9 NDB引擎：MySQL集群专用存储引擎

NDB存储引擎，全称Network DataBase Cluster，是专为MySQL Cluster设计的存储引擎。它支持高可用性和可伸缩性的分布式数据库系统，适用于需要高事务吞吐量和实时访问的应用场景。NDB通过提供分布式数据存储解决方案，实现了数据的自动分片和复制，确保了数据的高可用性和容错能力。

主要特征

  • 高可用性： 数据自动在集群节点间复制，即使在节点故障的情况下也能保证服务的连续性。
  • 实时性能： 优化了锁定机制和索引技术，以支持高速事务处理。
  • 自动分片： 数据自动分布在多个节点上，实现了负载均衡和水平扩展。
  • 数据复制： 支持同步和异步数据复制，保障数据一致性和灾难恢复。
  • 读写分离： 可以配置专门的节点处理读请求，其他节点处理写请求，从而提高读写性能。

使用优点

  • 扩展性： 可以通过添加更多的节点轻松扩展系统的处理能力。
  • 容错性： 高度的数据冗余和故障转移能力，提高了系统的可靠性。
  • 易于管理： 提供了一套管理工具和界面，简化了集群的配置和维护。

使用缺点

  • 资源消耗： 高性能和高可用性的特性使得NDB对硬件资源的需求相对较高。
  • 复杂度： 分布式系统的配置和管理相对复杂，需要较高的技术水平。

适用场景

  • 高可用性应用： 对于需要24/7运行的关键业务系统，如电信、金融交易平台。
  • 实时应用： 需要实时访问和处理大量数据的应用，如在线游戏、广告技术平台。
  • 大规模在线服务： 对于需要支持大量并发用户和事务的在线服务，如电子商务网站。

注意事项

  • 集群规划： 在部署前需进行详细的规划，包括节点数量、网络配置等，以确保性能和可用性。
  • 监控与维护： 需要实施有效的监控策略来监测集群状态和性能，及时发现和解决问题。
  • 数据一致性： 在设计应用时，需考虑分布式系统中数据一致性的问题，确保应用逻辑的正确性。

# 4.10 MySQL引擎对比

MySQL中同一个数据库，不同的表可以选择不同的存储引擎。如下表对常用存储引擎做出了对比。

存储引擎 事务支持 行级锁定 外键支持 MVCC 全文索引 数据压缩 空间函数支持 适用场景
InnoDB 是 是 是 是 是 是 是 事务处理、外键约束支持的应用
MyISAM 否 否 否 否 是 是 是 只读或读为主的应用，大量的SELECT和INSERT操作
Archive 否 否 否 否 否 是 否 日志数据存储、数据归档
Blackhole 不适用 不适用 不适用 不适用 不适用 不适用 不适用 测试、日志收集
CSV 否 否 否 否 否 否 否 简单数据存储、数据交换
Memory 否 否 否 否 是 否 否 临时数据存储、快速访问需求
Federated 不适用 不适用 不适用 不适用 不适用 不适用 不适用 访问远程数据库表
Merge 不适用 不适用 不适用 不适用 不适用 不适用 不适用 处理分区表、大量MyISAM表的合并访问
NDB 是 是 是 是 否 否 否 高可用性、分布式数据库系统

其实这些东西大家没必要立即就给记住，列出来的目的就是想让大家明白不同的存储引擎支持不同的功能。

其实我们最常用的就是 InnoDB 和 MyISAM ，有时会提一下 Memory 。其中 InnoDB 是 MySQL 默认的存储引擎。

# 5. MyISAM和InnoDB

很多人对 InnoDB 和 MyISAM 的取舍存在疑问，到底选择哪个比较好呢？

1.事务支持

  • InnoDB 支持事务处理。如果你的应用需要事务的特性，如原子性、一致性、隔离性、持久性（ACID特性），那么InnoDB是更好的选择。
  • MyISAM 不支持事务处理。如果你的应用主要是读取操作，且不需要事务支持，可以考虑使用MyISAM。

2.并发处理

  • InnoDB 支持行级锁定和MVCC，能够提供更高的并发处理能力。这对于需要处理大量并发写入操作的应用尤其重要。
  • MyISAM 只支持表级锁。这意味着当执行写入（更新、删除、插入）操作时，整个表将被锁定，其他的写入操作必须等待，这可能会成为性能瓶颈。

3.数据完整性和恢复

  • InnoDB 提供了对外键的支持，可以通过外键约束来维护数据的完整性。此外，InnoDB对崩溃恢复的支持更好，因为它使用日志文件和缓冲池来实现数据的恢复。
  • MyISAM 不支持外键，且在发生崩溃时的恢复过程可能更复杂、更耗时。

4.存储效率和速度

  • InnoDB 的存储效率和访问速度近年来已大幅提升，尤其是在MySQL 5.6及之后的版本中。InnoDB表通常比MyISAM表占用更多的磁盘空间，以支持其复杂的事务和恢复功能。
  • MyISAM 在某些读密集型的场景中可能会表现得更好，特别是在表锁定对性能影响不大时。MyISAM的计数操作（如COUNT(*)）通常比InnoDB更快，因为MyISAM内部维护了一个计数器。

5.其他特性

  • InnoDB 支持全文索引（从MySQL 5.6开始），但MyISAM对全文索引的支持更早。
  • MyISAM 支持压缩表和空间数据索引，这对于某些特定的应用场景非常有用。

综合考虑

  • 对于需要高并发写入、事务支持、数据完整性保护的应用，InnoDB是更好的选择。
  • 对于只读或读为主的应用，特别是在表锁定不会成为瓶颈的情况下，MyISAM可能会提供更好的性能。

公众号封面

编辑此页 (opens new window)
上次更新: 2024/12/28, 18:32:08
第04章_逻辑架构
第06章_索引的数据结构

← 第06章_索引的数据结构→

Theme by Vdoing | Copyright © 2019-2025 程序员scholar