mysql存储引擎及比较

一、mysql存储引擎及比较

MySQL是一个流行的关系型数据库管理系统，它支持多种存储引擎。存储引擎是MySQL中用于表数据和索引存储的底层软件组件。不同的存储引擎有着不同的特性、优势和限制，适用于不同的应用场景。以下是MySQL中一些常见的存储引擎及其比较：

1. InnoDB

• 特点：
  • 支持事务处理，具有提交、回滚和崩溃恢复能力。
  • 支持外键约束。
  • 默认的MySQL存储引擎（从MySQL 5.5版本开始）。
  • 支持行级锁定和MVCC（多版本并发控制）。
  • 适用于需要高可靠性和高并发的应用。
• 索引实现：
  • InnoDB使用B+树索引作为其主要的索引结构。它采用聚簇索引（Clustered Index）方式存储表数据，即表中的数据直接存储在主键的B+树叶节点上。非主键索引（二级索引或辅助索引）的叶节点存储相应行的主键值，而不是直接指向行数据。

2. MyISAM

• 特点：
  • 不支持事务处理。
  • 支持全文索引。
  • 支持表级锁定。
  • 通常有更快的读取速度。
  • 适用于读密集型的应用，但在写操作或需要事务支持的场景中不推荐使用。
• 索引实现：
  • MyISAM也使用B+树索引，但与InnoDB不同，MyISAM的表数据和索引是分开存储的。索引文件包含指向数据文件中记录的指针。MyISAM支持全文索引，这是其一个独特的特性。

3. MEMORY

• 特点：
  • 将所有数据存储在内存中，提供快速的访问速度。
  • 支持表级锁定。
  • 数据在数据库重启后会丢失。
  • 适用于临时数据存储和快速读取的场景，如缓存。
• 索引实现：
  • MEMORY存储引擎默认使用哈希索引，适用于等值比较查询，提供了非常快速的查找性能。它也支持B树索引，可以用于范围查询。由于MEMORY存储引擎将数据存储在内存中，因此访问速度非常快，但数据在数据库重启时会丢失。

4. Archive

• 索引实现：

  • Archive存储引擎主要用于存储大量的归档数据，如日志信息。它使用行级压缩技术来减少存储空间的使用。Archive存储引擎在早期版本中不支持索引，但在MySQL 5.7及更高版本中，支持创建索引。

• 索引实现：

  • 在MySQL 5.7及更早版本中，Archive表不支持索引。这意味着对Archive表的查询会执行全表扫描，这可能会导致查询性能较低。然而，在**MySQL 8.0中，Archive存储引擎已被移除，**因此，关于索引支持的讨论主要适用于早期版本的MySQL。

• 特点：

  • 高效的数据压缩：Archive存储引擎在插入数据时自动进行行级压缩，有助于节省磁盘空间。
  • 只支持INSERT和SELECT：不支持DELETE和UPDATE操作，这简化了存储引擎的实现，但也限制了其用途。
  • 不支持事务：Archive存储引擎不支持ACID事务。
  • 适用于归档数据：非常适合存储不需要修改的大量数据，如日志记录和历史数据。

• 使用场景：

  • Archive存储引擎适用于需要高效存储大量只读数据的场景。它特别适合日志数据和其他类型的历史数据，这些数据一旦写入就很少或根本不需要再次修改。

5. Federated

• 原理实现：

  • Federated存储引擎并不存储数据，而是提供了一种机制，允许MySQL服务器访问远程MySQL数据库上的表，就好像这些表是本地表一样。实际的数据存储和索引实现依赖于远程数据库所使用的存储引擎。

• 索引实现：

  • 由于Federated存储引擎本身不直接处理数据存储，它也不实现自己的索引结构。远程表的索引实现和性能特性将取决于远程表所使用的存储引擎，例如，如果远程表使用InnoDB存储引擎，那么索引将是B+树结构。

• 特点：

  • 数据透明访问：允许跨服务器透明地访问数据，这对于分布式数据库设计和数据整合非常有用。
  • 无本地数据存储：所有的数据操作都是在远程服务器上执行的，本地服务器不存储任何实际数据。
  • 依赖网络性能：数据访问性能高度依赖于网络连接的质量和速度，网络延迟和中断可能会影响操作的效率。
  • 安全性考虑：需要确保远程连接的安全性，因为数据操作是通过网络进行的。

• 使用场景：

  • Federated存储引擎适用于需要访问或整合存储在远程MySQL服务器上的数据的场景。它可以用于数据分布、远程备份、数据仓库和报表等多种应用。

6.NDB（MySQL Cluster）

• 特点

  • 高可用性：NDB提供了自动分片（sharding）和复制，确保了数据的高可用性和冗余。即使在节点失败的情况下，也能保证服务的持续可用。
  • 分布式：NDB是一个分布式存储引擎，支持将数据自动分布在多个节点上，这有助于实现负载均衡和水平扩展。
  • 实时性：NDB特别适合需要实时性的应用，因为它提供了低延迟的数据访问。
  • 自动故障转移和恢复：NDB能够自动检测节点故障并进行故障转移，无需人工干预即可恢复服务。
  • 写操作的并发性：由于其分布式的特性，NDB能够处理高并发的写操作。

• 索引实现：

  • NDB存储引擎（MySQL Cluster）使用T树索引和哈希索引。T树索引是一种平衡树，适用于范围查询；而哈希索引适用于快速的等值查找。NDB是为分布式计算设计的，支持高可用性和水平扩展。

• 适用场景

  • 电信行业：NDB起初是为电信行业设计的，适用于需要高吞吐量和低延迟的场景，如电话呼叫记录、网络事件收集等。
  • 在线游戏：适用于在线游戏后端，特别是需要处理大量并发用户和实时交互的游戏。
  • 实时金融服务：适用于需要实时处理大量交易的金融服务，如股票交易和支付系统。
  • 高可用性应用：任何需要高可用性和故障自动恢复能力的应用，如在线零售、社交网络等。

与InnoDB和MyISAM等存储引擎相比

与InnoDB和MyISAM等存储引擎相比，NDB的主要优势在于其高可用性、分布式特性和对高并发写操作的支持。然而，NDB的部署和管理相对复杂，需要更多的硬件资源和网络配置。此外，由于其数据是分布式存储的，某些类型的查询（特别是涉及多个分片的联合查询）可能比在单一存储引擎上执行更慢。

选择NDB还是其他存储引擎，需要根据应用的具体需求、预期的负载以及可用资源仔细权衡。

总结

功能区别

• 事务支持：InnoDB支持事务，而MyISAM和MEMORY不支持。
• 锁定级别：InnoDB支持行级锁定，而MyISAM和MEMORY支持表级锁定。
• 数据恢复：InnoDB提供崩溃恢复能力，而MyISAM在崩溃后可能需要手动修复。
• 并发：InnoDB通过行级锁定和MVCC提供较好的并发性能，NDB通过分布式架构支持高并发，而MyISAM使用表级锁定，可能在高并发场景下成为瓶颈。
• 存储需求：Archive通过压缩数据来减少存储需求，适用于归档数据。NDB需要额外的网络和硬件资源来支持其分布式架构。

适用场景区别

• InnoDB是MySQL的默认存储引擎，适用于大多数需要事务支持的应用场景。
• MyISAM适用于简单的、读密集型的应用，但在需要事务支持或高并发的场景下不是最佳选择。
• MEMORY适用于需要快速访问的临时数据，但数据的持久性不是关键要求。
• Archive适用于归档和日志数据的高效存储。
• NDB (MySQL Cluster) 适用于需要高可用性、可扩展性和实时性的分布式应用。

索引区别

更新后的表格如下：

存储引擎	主要索引结构	支持的索引类型	特点
InnoDB	B+树	- 聚集索引 - 二级索引 - 全文索引 - 空间索引	- 默认存储引擎 - 支持事务和外键 - 聚集索引存储数据
MyISAM	B+树	- 非聚集索引 - 全文索引	- 索引与数据分离存储 - 不支持事务和外键 - 表级锁定
Memory	哈希表（默认） B+树	- 哈希索引 - B+树索引	- 数据和索引存储在内存中 - 极快的读写速度 - 不支持BLOB和TEXT字段索引
NDB	T树	- 主键索引 - 唯一索引 - 普通索引	- 用于MySQL Cluster - 分布式存储 - 支持高可用性
ARCHIVE	无	- 不支持索引	- 用于归档数据 - 高压缩比 - 只支持INSERT和SELECT
FEDERATED	依赖于远程表	- 依赖于远程表的存储引擎	- 访问远程MySQL表 - 不存储数据本身 - 查询在远程执行

请注意，ARCHIVE和FEDERATED存储引擎在特定场景下非常有用，但它们的使用场景和限制与其他存储引擎有所不同。选择存储引擎时，应根据应用的具体需求和存储引擎的特性来做出决策。

选择合适的存储引擎需要根据应用的具体需求，考虑数据的一致性、性能、存储效率和可维护性等因素。

二、常用搜索引擎 InnoDB 和 MyISAM 比较

要深入理解数据库存储引擎的原理，我们需要从几个关键方面来探讨：数据存储结构、索引机制、事务处理、并发控制和数据恢复。以MySQL的InnoDB和MyISAM为例，我们可以看到不同存储引擎在这些方面的不同实现方式及其背后的原理。

数据存储结构

• InnoDB：InnoDB使用聚簇索引（Clustered Index）来存储数据。在聚簇索引中，表数据实际上存储在索引的叶子页中。这意味着，基于主键的查询非常高效，因为索引搜索直接导致了数据行。但是，这也意味着二级索引（非主键索引）需要额外的步骤来访问数据行，因为它们首先指向主键，然后通过主键来访问数据。
• MyISAM：MyISAM使用非聚簇索引。表数据存储在一个与索引分开的文件中。索引文件包含指向数据文件中记录位置的指针。这种结构使得全表扫描变得更快，但是基于索引的查询可能不如InnoDB那样高效，因为需要额外的步骤来定位数据文件中的实际数据。

索引机制

• InnoDB：InnoDB默认使用B+树索引，特别适合处理大量数据的高速查找、插入、删除等操作。B+树的特点是所有值都存储在叶子节点，非叶子节点仅用于索引，且叶子节点之间是相互链接的，这使得范围查询变得非常高效。
• MyISAM：MyISAM也使用B+树索引，但由于其数据和索引是分开存储的，所以在某些情况下，如全表扫描，MyISAM可能表现得更好。

事务处理

• InnoDB：InnoDB支持ACID（原子性、一致性、隔离性、持久性）事务。它使用日志缓冲区和重做日志（Redo Log）来保证事务的原子性和持久性，并使用锁和MVCC（多版本并发控制）来实现事务的隔离性。
• MyISAM：MyISAM不支持事务。它不提供原子性和持久性保证，也不支持事务隔离。

并发控制

• InnoDB：InnoDB通过行级锁和MVCC来实现高效的并发控制。行级锁允许多个事务同时修改不同的数据行，而MVCC允许读取操作不加锁，从而提高并发性能。
• MyISAM：MyISAM只支持表级锁。当一个事务对表进行写操作时，会锁定整个表，这限制了并发性能。

数据恢复

• InnoDB：InnoDB通过重做日志（Redo Log）和回滚日志（Undo Log）支持崩溃恢复。在发生故障时，可以使用重做日志重放已提交的事务，使用回滚日志撤销未提交的事务，从而恢复到一致的状态。
• MyISAM：MyISAM提供了一些修复工具来修复崩溃后的表，但没有内置的崩溃恢复机制。

总的来说，InnoDB和MyISAM在数据存储结构、索引机制、事务处理、并发控制和数据恢复等方面有着本质的不同。InnoDB通过聚簇索引、事务支持、行级锁和MVCC等技术提供了高性能和高可靠性的数据管理，适合于需要高并发和事务支持的应用。而MyISAM以其简单高效的非聚簇索引结构和表级锁定机制，适合于读密集型、不需要事务支持的应用场景。