存储引擎

MySQL 的存储引擎是数据库管理系统中用于存储、处理和检索数据的核心服务和组件。不同的存储引擎提供了不同的数据类型支持、存储机制、索引技术、锁定水平以及其他性能特性。选择合适的存储引擎对于优化数据库性能、提高资源利用率和支持特定的数据库操作非常关键。

存储引擎类型

MySQL 中常见的存储引擎：

InnoDB
MyISAM
Memory
CSV
ARCHIVE
BLACKHOLE

InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键

InnoDB

InnoDB 是 MySQL 默认的事务型存储引擎，支持 ACID（原子性、一致性、隔离性、持久性）事务处理，行级锁定和外键。

优点

事务支持：InnoDB 提供 ACID（原子性、一致性、隔离性、持久性）兼容的事务处理模型，支持行级锁定和外键约束，这对于多用户并发操作尤为重要。
行级锁定：相比于 MyISAM 的表级锁，InnoDB 的行级锁定可以显著提高多用户并发操作的性能，减少锁争用。
崩溃恢复：通过日志文件和缓冲池，InnoDB 能够在系统崩溃后自动恢复数据的完整性。
数据压缩：InnoDB 支持表和索引的数据压缩，可以减少磁盘空间的使用和提高I/O效率。
热备份：支持在线热备份，即在数据库运行时进行数据备份，不影响正常的数据库操作。
外键约束：支持外键，可以自动维护表之间的参照完整性。

缺点

资源使用：相较于其他存储引擎，如 MyISAM，InnoDB 需要更多的内存和存储空间来维护其事务日志和缓冲池。
配置复杂性：为了充分利用 InnoDB 的高性能特性，需要仔细配置多个参数，如缓冲池大小、日志文件大小等，对于新手来说可能较为复杂。
备份和恢复：虽然 InnoDB 支持热备份，但其备份和恢复过程相对于简单的 MyISAM 表来说更加复杂，可能需要专门的工具和更长的时间。
FULLTEXT 索引：在较早的版本中，InnoDB 不支持 FULLTEXT 索引，尽管这一限制在 MySQL 5.6 及以后的版本中已经被解除。
删除数据的处理：当大量删除数据后，InnoDB 不会自动释放空间回文件系统，需要手动优化表或配置文件来回收空间。

InnoDB 的设计目标是提供一种能够处理大量数据的高性能、高可靠性的存储引擎。对于大多数需要高并发和事务支持的应用程序来说，InnoDB 的优点远远超过其缺点。然而，对于一些特定的应用场景，比如只读数据或对事务支持要求不高的情况，其他存储引擎可能会是更好的选择。

MyISAM

MyISAM 是 MySQL 中一种较早的默认存储引擎，虽然在新版本中被 InnoDB 所取代，但它仍然广泛用于一些特定场景下。MyISAM 基于早期的 ISAM 类型并进行了扩展，它支持全文索引、压缩、空间函数等特性。以下是 MyISAM 存储引擎的主要优点和缺点：

优点

简单性：MyISAM 结构简单，易于创建、管理和使用。对于新手来说，这可以减少数据库操作的复杂性。
读取速度：MyISAM 在读取密集型操作中表现出色，尤其是在表锁定级别的操作中，它可以快速读取数据。
空间和内存使用：相对于 InnoDB，MyISAM 使用较少的磁盘空间和内存资源，这使得它在资源受限的环境下是一个不错的选择。
全文索引：MyISAM 支持全文索引，使得它在处理大量文本数据（如文章、博客等）的搜索操作中非常有效。
压缩：MyISAM 表可以被压缩以节省空间，压缩表只能读取不能写入，但对于静态数据来说，这是一个很好的特性。

缺点

不支持事务：MyISAM 不支持事务处理，这意味着它不能保证操作的原子性，一致性，隔离性和持久性（ACID属性），在需要处理复杂事务的应用中不适用。
表锁定：MyISAM 使用表级锁定，而不是行级锁定。这意味着当进行写入（INSERT、UPDATE、DELETE）操作时，整个表将被锁定，从而影响并发性能。
崩溃恢复：MyISAM 对崩溃恢复的支持不如 InnoDB，数据更易损坏。虽然可以使用修复工具恢复数据，但这可能会导致数据丢失。
数据完整性：MyISAM 不支持外键约束，这使得维护数据库的引用完整性更加困难。
存储限制：MyISAM 表的大小受到文件系统限制，这可能会在大型数据库中成为问题。

尽管 MyISAM 有其局限性，特别是在事务支持和数据完整性方面，但它在处理大量读操作、需要全文索引的场景以及资源受限的环境中仍然是一个有价值的选择。对于静态数据的存储或简单的Web应用，MyISAM 可以提供优秀的性能和高效的空间使用。然而，对于需要高并发写操作或事务支持的现代数据库应用，InnoDB 或其他更先进的存储引擎可能是更好的选择。

MEMORY

Memory 存储引擎（以前称为 HEAP），在 MySQL 中提供了数据存储在内存中的能力，主要用于提高数据访问的速度。由于数据和索引都在 RAM 中，这使得 Memory 引擎特别适合于需要快速读写访问的临时数据存储场景。不过，由于存储在内存中的数据在数据库重启后会丢失，所以它主要用于那些不需要持久存储数据的场合。以下是 Memory 存储引擎的主要优点和缺点：

优点

速度：Memory 存储引擎提供了极高的数据处理速度，因为所有的操作都在内存中进行，几乎没有磁盘I/O延迟。
减少磁盘I/O：对于频繁访问的数据表，使用 Memory 存储引擎可以显著减少磁盘I/O，提高系统的整体性能。
理想的临时存储解决方案：适合作为临时表和快速缓存的解决方案，如存储会话信息、临时聚合结果等。
简化数据处理：对于某些复杂查询，可以将中间结果存储在 Memory 表中，以简化数据处理过程并提高效率。

缺点

数据的易失性：由于数据存储在内存中，服务器崩溃或重启会导致数据丢失。这限制了其用途，主要适用于那些数据丢失不会导致严重后果的场景。
内存使用限制：Memory 表的大小受到 MySQL 配置参数 max_heap_table_size 和 tmp_table_size 的限制。这意味着大量数据的存储可能受限。
表锁定：Memory 使用表级锁定机制，这可能在高并发环境下成为性能瓶颈。
不支持 BLOB 和 TEXT 类型：Memory 存储引擎不支持 BLOB 或 TEXT 数据类型，限制了存储文本数据的能力。
不支持事务：像 MyISAM 一样，Memory 也不支持事务处理。这意味着它不适用于需要事务支持的应用场景。

尽管 Memory 存储引擎在数据持久性和存储能力方面有其限制，但它在需要高速数据访问和处理的特定场景下仍然非常有用。例如，它可以用于缓存那些不经常改变但频繁读取的数据，或者用作存储临时计算结果的临时表。在这些情况下，Memory 存储引擎可以提供显著的性能优势。然而，对于需要长期存储或事务一致性保证的应用，其他存储引擎（如 InnoDB）将是更好的选择。

CSV

CSV 存储引擎在 MySQL 中提供了一种独特的数据存储方式，使得表数据以逗号分隔值（CSV）格式存储。这种格式的主要优势在于数据可以很容易地在不同的程序和应用之间进行交换，例如表格处理软件（如 Microsoft Excel）和文本编辑器。CSV 存储引擎将每一行数据存储为一个 CSV 文件的一行，而表的元数据（如列定义）则存储在与表同名的 .frm 文件中。以下是 CSV 存储引擎的主要优点和缺点：

优点

易于数据交换：由于 CSV 是一种广泛支持的数据格式，使用 CSV 存储引擎可以简化数据的导入导出过程，方便与其他应用程序的数据交换。
文本编辑器友好：CSV 格式的数据可以直接用文本编辑器打开和修改，这为直接编辑表数据提供了便利。
简化数据迁移：对于需要在不同数据库系统之间迁移数据的场景，CSV 格式提供了一种简单直接的方式。
无需特殊处理：与特定格式（如二进制数据文件）相比，CSV 格式的数据无需通过特殊程序处理即可读取，增加了透明性。

缺点

性能限制：与其他存储引擎相比，CSV 存储引擎在性能上通常不占优势，尤其是在处理大量数据时。
不支持索引：CSV 表不支持索引，这意味着查询大型 CSV 表时可能会非常慢。
不支持事务：CSV 存储引擎不支持事务处理，这限制了其在需要事务性数据操作的应用中的使用。
数据类型限制：所有数据都作为文本存储，可能会导致类型转换问题，特别是在处理日期和数字数据时。
数据完整性风险：直接编辑 CSV 文件可能导致数据格式错误，从而影响数据的完整性。

尽管 CSV 存储引擎在性能和功能方面有一定的限制，但它在数据共享和简化数据交换过程中扮演了重要的角色。对于需要频繁导入导出数据到其他应用程序的场景，或者在数据分析和报告过程中需要与非数据库用户共享数据的情况，CSV 存储引擎是一个非常有用的工具。然而，对于需要高性能查询、数据完整性保护或事务支持的应用，其他存储引擎（如 InnoDB）将是更合适的选择。

BLACKHOLE

BLACKHOLE 存储引擎在 MySQL 中提供了一种非常特殊的数据存储方式。顾名思义，BLACKHOLE 就像一个黑洞，它接收数据的写操作（如 INSERT、UPDATE、DELETE），但不会实际存储任何数据，查询总是返回空结果。这个存储引擎通常用于特定的应用场景，比如在复制配置中过滤数据或者测试。以下是 BLACKHOLE 存储引擎的主要优点和缺点：

优点

节省空间：由于不存储数据，BLACKHOLE 存储引擎不占用任何磁盘空间。
复制过滤：在复杂的 MySQL 复制设置中，可以使用 BLACKHOLE 引擎作为中间件来过滤或转换数据。数据可以写入配置为 BLACKHOLE 引擎的表，然后通过触发器或其他机制进行处理，最终被复制到其他服务器。
性能测试：可以用来测试应用的写入性能，因为它几乎不消耗资源处理写操作。
日志记录：虽然它本身不存储数据，但可以配置二进制日志，从而允许记录所有写操作的日志。这对于审计和回放操作很有用。

缺点

数据不可用：最明显的缺点是它不存储任何数据，因此除了写操作以外，你不能对数据进行任何实际的读取操作。
功能限制：不支持索引、查询优化、事务或任何形式的数据检索功能。
使用场景受限：由于其特殊的特性，BLACKHOLE 的适用场景非常有限，主要用于上述提到的特定用例。
数据完整性和安全性：由于不存储任何数据，它无法保证数据的完整性和安全性。

BLACKHOLE 存储引擎的设计初衷并非用于常规的数据存储需求，而是为了满足特定的技术和操作需求。在实际应用中，它可以作为一种工具来辅助实现数据流的控制、性能测试或特定的数据处理流程。然而，对于需要持久存储、数据检索和事务支持的应用场景，其他存储引擎如 InnoDB 或 MyISAM 会是更合适的选择。

文件存储引擎

在 MySQL 中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有 .frm 文件，命名方式为数据表名 .frm，如 user.frm。

查看 MySQL 数据保存在哪里：

show variables like 'data%';

MyISAM 物理文件结构为：

.frm 文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等
.MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的数据
.MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

.frm 文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等
.ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
- 独享表空间存储方式使用 .ibd 文件，并且每个表一个 .ibd 文件
- 共享表空间存储方式使用 .ibdata 文件，所有表共同使用一个 .ibdata 文件（或多个，可自己配置）

查看存储引擎

-- 查看支持的存储引擎
show engines;

-- 查看默认存储引擎
show variables like '%storage_engine%';

--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table <table_name>;

--准确查看某个数据库中的某一表所使用的存储引擎
show table status like '%table_name%'
show table status from database where name="%table_name%"

设置存储引擎

-- 建表时指定存储引擎。默认的就是 INNODB，不需要设置
create table t1 (i int) engine = InnoDB;
create table t2 (i int) engine = CSV;
create table t3 (i int) engine = MEMORY;

-- 修改存储引擎
alter table %table_name% engine = InnoDB;

-- 修改默认存储引擎，也可以在配置文件 my.cnf 中修改默认引擎
set default_storage_engine=NDBCLUSTER;

默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表

Database Guidebook