MySQL数据库——存储引擎（InnoDB、MyISAM、MEMORY、ARCHIVE）

阿国运维网技术分享平台：桌面运维、网络运维、系统运维、服务器运维(及云服务器)，精品软件分享、阿国网络、尽在北京运维网

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 是最合适的选择。

锁机制：TODO，专栏后续文章专门解析。
事务：TODO，专栏后续文章专门解析。
索引和主键、自增列：TODO，专栏后续文章专门解析。
崩溃恢复：TODO，专栏后续文章专门解析。

MyISAM VS InnoDB

特性	MyISAM	InnoDB
锁机制	仅支持表级锁	支持表级锁和行级锁（支持多粒度锁，包括记录锁、间隙锁、Next-Key锁）
表级锁与行级锁	只支持表级锁，行级锁不支持	支持表级锁与行级锁（记录锁、间隙锁、Next-Key锁）
事务支持	不支持事务	支持事务，支持四种事务隔离级别（读未提交、读已提交、可重复读、串行化）
崩溃恢复	不支持崩溃恢复，恢复困难	支持崩溃恢复，事务日志和重做日志保证数据一致性
外键支持	不支持外键	支持外键
主键	可不设置主键	必须设置主键，主键作为聚簇索引，若未定义则自动选择第一个唯一索引或ROWID
自增列	可以将自增列与其他列组成联合索引	自增列必须是主键，并且是聚簇索引
索引类型	B+树索引（索引和数据分开存储）	聚簇索引（数据和索引存储在同一地方），辅助索引存储主键值
全文索引支持	支持全文索引	不直接支持全文索引（需要插件或支持`InnoDB`表的全文索引功能）
数据存储结构	索引文件（.MYI）、数据文件（.MYD）、表结构文件（.frm）	数据和索引文件（.ibd）、表结构文件（.frm）
查询性能	对于读操作非常高效	在高并发读写情况下性能较好，适合高并发写入场景
插入性能	插入性能较高，适合读多写少的场景	插入性能较低，但在写操作频繁的情况下，支持较好的事务性能
表类型	适合静态、不频繁更新的数据，常用于日志表等	适合需要事务支持、高并发、高可靠性的应用，支持数据一致性
表压缩	不支持自动压缩	支持表空间压缩功能
空间效率	较低（数据和索引文件分离，可能增加存储空间）	较高（数据和索引紧密存储，节省存储空间）
备份	备份较为简单（备份表数据文件和索引文件）	备份较为复杂，支持热备份功能（例如：`mysqldump`）
数据一致性	不支持ACID，适合读取时不需要强一致性的场景	支持ACID，适合需要强一致性和可靠性的应用场景
支持事务隔离	不支持事务隔离，无法控制并发访问	支持事务隔离（支持并发控制，使用行级锁来避免并发问题）

MyISAM：适用于查询多、更新少的场景，简单易用，查询性能高，适合不需要事务支持的应用。但不支持事务、外键、崩溃恢复，且缺乏数据一致性保障。
InnoDB：适用于需要高并发、事务支持和数据一致性的应用，支持事务、行级锁、外键、崩溃恢复等功能，适合高并发和写操作较多的应用，但性能略逊于MyISAM，尤其是在插入操作方面。

MyISAM 相较于 InnoDB 的优势

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

读性能

MyISAM对于查询操作（尤其是大量的SELECT查询）性能更优。由于MyISAM采用表级锁，在没有并发写操作的情况下，多个连接可以并发执行读操作，因此它适用于读多写少的场景。
InnoDB虽然支持行级锁，但在没有索引的情况下，行锁和页面锁的管理和性能开销相对较高。

存储空间效率

MyISAM的存储结构相对较简单，数据和索引分离，索引文件（.MYI）只存储记录的地址，而InnoDB需要将数据和索引紧密存储（数据及索引），采用聚簇索引（数据行和索引一起存储），可能导致存储空间的开销较大。
对于不需要复杂事务控制和高并发写入的表，MyISAM更加高效。

内存使用

MyISAM表不需要额外的事务日志或重做日志存储，因此在内存使用上更节省。
InnoDB则需要管理事务日志、缓冲池等，导致更多的内存消耗。

MEMORY

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

特点：

速度快：

由于数据存储在内存中，MEMORY 表可以提供非常快速的查询和写入性能。
默认使用哈希索引，哈希索引的速度较B+树索引更快，但也可以选择使用B+树索引。

有数据丢失风险：

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

方便内存管理：

MEMORY 表的生命周期短，数据通常是临时性的，存放查询的中间结果或缓存。
不需要时，可以通过DELETE或TRUNCATE来释放内存，或者通过DROP删除表。

使用场景：

缓存：对于需要快速读写的临时数据，如查询中间结果，使用 MEMORY 引擎会大大提高性能。
临时表：适用于存储临时数据或需要快速查询的数据表。
非持久数据：例如在数据处理、数据清理时存放临时表格。

MEMORY VS ARCHIVE

特性	MEMORY	ARCHIVE
存储方式	数据存储在内存中，快速访问	数据压缩存储，适合存储历史数据
查询性能	非常快，但数据丢失风险高	查询性能较差，主要用于存档数据
写入性能	快，适合临时数据	高效，插入速度快，但查询较慢
事务支持	不支持事务，适合临时数据存储	不支持事务，适合归档数据
索引支持	支持哈希和B+树索引	支持基础的索引，但查询性能较差
数据持久性	数据存储在内存中，重启后数据丢失	数据压缩存储，持久性强
适用场景	临时数据存储、缓存、中间结果存储	日志记录、历史数据、归档数据存储
数据压缩	无压缩	使用zlib压缩库进行数据压缩