MySQL存储引擎

什么是MySQL存储引擎

MySQL的存储引擎（Storage Engine）是用于管理数据存储和数据访问的程序模块。

常用的存储引擎有 InnoDB（事务处理和高并发，默认存储引擎）、MyISAM（高查询性能，但不支持事务）、Memory（高速缓存）等。

• InnoDB：适用于大多数业务场景，提供事务、行级锁和外键支持，是MySQL的默认存储引擎。

• MyISAM：适用于读多写少、对事务一致性要求不高的场景。

• MEMORY：适用于需要高速缓存的场景，但数据会在数据库重启时丢失。

• ARCHIVE：适用于存储大量归档数据，不支持更新操作。

• CSV：适用于导出和交换数据的场景，数据以CSV格式存储。

• NDB (Cluster)：适用于分布式、高可用性和高扩展性的场景，通常用于MySQL Cluster。

• FEDERATED：适用于跨数据库查询，允许访问远程数据库中的数据。

• BLACKHOLE：适用于数据复制，不存储数据，所有写入都会丢失。

选择存储引擎时，需要根据应用的需求、性能要求、数据一致性需求和扩展性等因素来综合考虑。

下面是比较重要的存储引擎

MyISAM

MyISAM 是 MySQL 中的一个存储引擎，广泛用于早期的 MySQL 版本。虽然在 MySQL 5.5 之后，InnoDB 成为默认的存储引擎，但 MyISAM 仍然在一些应用场景下被使用，尤其是那些以读取为主、对事务要求不高的系统。

锁机制

• 自动加表级锁：只支持表级锁。会在执行SELECT时自动加读锁，在执行写操作时自动加写锁。

• 写锁优先：在发生读写冲突时，MyISAM 会优先让写操作获取锁，这就是为什么它在读写并发较高的环境下可能表现不佳的原因。

• 读锁（共享锁）：MyISAM 在执行 SELECT查询时，会自动给整个表加上读锁，即共享锁。这意味着多个连接可以同时对表进行读取操作，但是如果一个连接正在读取表，其他连接仍然可以进行读取操作，但不能进行写操作（例如 INSERT, UPDATE, DELETE等）。

• 写锁（排他锁）：当执行写操作（如 INSERT, UPDATE, DELETE等）时，MyISAM 会自动给整个表加上写锁（排他锁）。在加锁期间，其他连接无法进行任何操作（无论是读操作还是写操作）。这会导致锁竞争，尤其在高并发的写操作下，可能会引起性能瓶颈。

• 写锁优先：在 MyISAM 中，如果有一个连接正在进行读操作（获得了读锁），而另一个连接要进行写操作，那么写操作会优先获得锁。

InnoDB

InnoDB 是 MySQL 的默认存储引擎，提供了全面的事务支持和高并发性能。相比于 MyISAM，InnoDB 的行级锁机制、事务支持、崩溃恢复、外键支持等特性，使其成为现代应用的首选存储引擎。对于需要事务性和一致性的系统，如金融、电子商务、社交平台等，InnoDB 是最合适的选择。

MyISAM VS InnoDB

• MyISAM：适用于查询多、更新少的场景，简单易用，查询性能高，适合不需要事务支持的应用。但不支持事务、外键、崩溃恢复，且缺乏数据一致性保障。

• InnoDB：适用于需要高并发、事务支持和数据一致性的应用，支持事务、行级锁、外键、崩溃恢复等功能，适合高并发和写操作较多的应用，但性能略逊于MyISAM，尤其是在插入操作方面。

MyISAM 相较于 InnoDB 的优势

虽然InnoDB是更主流的选择，但是MyISAM 相较于 InnoDB 有如下优势：

1. 读性能

   • MyISAM对于查询操作（尤其是大量的SELECT查询）性能更优。由于MyISAM采用表级锁，在没有并发写操作的情况下，多个连接可以并发执行读操作，因此它适用于读多写少的场景。

   • InnoDB虽然支持行级锁，但在没有索引的情况下，行锁和页面锁的管理和性能开销相对较高。

2. 存储空间效率

   • MyISAM的存储结构相对较简单，数据和索引分离，索引文件（.MYI）只存储记录的地址，而InnoDB需要将数据和索引紧密存储（数据及索引），采用聚簇索引（数据行和索引一起存储），可能导致存储空间的开销较大。

   • 对于不需要复杂事务控制和高并发写入的表，MyISAM更加高效。

3. 内存使用

   • MyISAM表不需要额外的事务日志或重做日志存储，因此在内存使用上更节省。

   • InnoDB则需要管理事务日志、缓冲池等，导致更多的内存消耗。

MEMORY

MEMORY 存储引擎将所有的数据存储在内存中，适用于需要快速访问的临时数据。每个基于 MEMORY 存储引擎的表，实际在磁盘上会创建一个.frm文件用于存储表的结构信息，但数据是存储在内存中的。由于数据完全存储在内存中，MEMORY 引擎可以提供非常快的读写速度，尤其适合那些对数据持久性要求不高的场景。

特点：

1. 速度快：

   • 由于数据存储在内存中，MEMORY 表可以提供非常快速的查询和写入性能。

   • 默认使用哈希索引，哈希索引的速度较B+树索引更快，但也可以选择使用B+树索引。

2. 有数据丢失风险：

   • 数据完全存储在内存中，所以当服务器重启或断电时，所有的数据都会丢失。因此，MEMORY 表通常适用于临时数据存储。

3. 方便内存管理：

   • MEMORY 表的生命周期短，数据通常是临时性的，存放查询的中间结果或缓存。

   • 不需要时，可以通过DELETE或TRUNCATE来释放内存，或者通过DROP删除表。

使用场景：

• 缓存：对于需要快速读写的临时数据，如查询中间结果，使用 MEMORY 引擎会大大提高性能。

• 临时表：适用于存储临时数据或需要快速查询的数据表。

• 非持久数据：例如在数据处理、数据清理时存放临时表格。

ARCHIVE

ARCHIVE 存储引擎主要用于存储归档历史记录数据。ARCHIVE 引擎仅仅支持插入和查询，数据插入操作是高效，但其查询性能相对较差。通过对数据进行压缩，ARCHIVE 引擎可以在存储大量数据时节省空间，非常适合用于日志记录、历史数据的存储等场景。

特点：

1. 只支持插入和查询：

   • ARCHIVE 引擎只支持INSERT和SELECT操作，不支持UPDATE或DELETE。这使得它非常适合记录历史数据、日志数据等，不涉及修改操作。

2. 压缩存储：

   • 使用zlib压缩库对数据进行压缩，能够有效节省存储空间。适合于存储大量历史记录或日志数据。

使用场景：

• 日志存储：适合存储只插入不查询的日志数据，能够有效压缩存储空间并提高插入性能。

• 历史数据：适合存储不会频繁查询的数据，尤其是历史记录类数据。

• 归档数据：适合需要长期保留的历史数据，且不频繁修改的数据。

MEMORY VS ARCHIVE

• MEMORY：适用于存储临时数据或缓存数据，数据量小且不需要持久化时非常高效。

• ARCHIVE：适用于存储归档数据、日志数据等不会进行更新且不频繁查询的大量历史记录数据，能够节省存储空间并支持高效插入。