MySQL 存储引擎

最后修改于 2023 年 1 月 10 日

在本章中，我们将讨论 MySQL 存储引擎。

存储引擎是一个软件模块，数据库管理系统使用它来创建、读取、更新数据库中的数据。MySQL 中有两种类型的存储引擎：事务型和非事务型。

对于 MySQL 5.5 及更高版本，默认的存储引擎是 InnoDB。在 5.5 版本之前的 MySQL 的默认存储引擎是 MyISAM。选择正确的存储引擎是一个重要的战略决策，它将影响未来的开发。在本教程中，我们将使用 MyISAM、InnoDB、Memory 和 CSV 存储引擎。如果您是 MySQL 的新手，并且正在学习 MySQL 数据库管理系统，那么这并不是太大的问题。如果您正在计划一个生产数据库，那么事情会变得更加复杂。

存储引擎列表

MySQL 支持的存储引擎

• InnoDB
• MyISAM
• Memory
• CSV
• Merge
• Archive
• Federated
• Blackhole
• 示例

InnoDB 是使用最广泛的存储引擎，具有事务支持。它是一个符合 ACID 标准的存储引擎。它支持行级锁定、崩溃恢复和多版本并发控制。它是唯一一个提供外键引用完整性约束的引擎。Oracle 建议将 InnoDB 用于表，除非是特殊用例。

MyISAM 是原始的存储引擎。它是一个快速的存储引擎。它不支持事务。MyISAM 提供表级锁定。它主要用于 Web 和数据仓库。

Memory 存储引擎在内存中创建表。它是最快的引擎。它提供表级锁定。它不支持事务。Memory 存储引擎非常适合创建临时表或快速查找。当数据库重新启动时，数据将会丢失。

CSV 将数据存储在 CSV 文件中。它提供了极大的灵活性，因为这种格式的数据很容易集成到其他应用程序中。

Merge 运行于底层的 MyISAM 表之上。Merge 表有助于更轻松地管理大量数据。它在逻辑上将一系列相同的 MyISAM 表分组，并将它们引用为一个对象。适用于数据仓库环境。

Archive 存储引擎针对高速插入进行了优化。它在插入时压缩数据。它不支持事务。它非常适合存储和检索大量很少引用的历史、存档数据。

Blackhole 存储引擎接受但不存储数据。检索总是返回一个空集。此功能可用于分布式数据库设计，其中数据会自动复制，但不会在本地存储。此存储引擎可用于执行性能测试或其他测试。

Federated 存储引擎提供了分离 MySQL 服务器的功能，可以从许多物理服务器创建一个逻辑数据库。对本地服务器的查询会自动在远程（联合）表上执行。本地表上不存储任何数据。它适用于分布式环境。

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...

SHOW ENGINES 命令显示服务器支持的所有可用引擎。

选择正确的引擎

没有哪个存储引擎是适用于所有情况的。有些引擎在特定条件下表现最佳，而在其他情况下表现较差。必须考虑权衡。一个更安全的解决方案需要更多的资源；它可能更慢，占用更多 CPU 时间和磁盘空间。MySQL 在提供几种不同的存储引擎方面非常灵活。其中一些，如 Archive 引擎，是为了在特定情况下使用而创建的。

在某些情况下，答案是明确的。每当我们处理某些支付系统时，我们有义务使用最安全的解决方案。我们不能损失这些敏感数据。InnoDB 是最好的选择。如果我们想要全文搜索，那么我们可以选择 MyISAM 或 InnoDB。只有 InnoDB 支持外键引用完整性约束，如果我们计划使用此约束，那么选择就很明确了。

指定和更改存储引擎

存储引擎是在创建表时指定的。

mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), 
    -> Cost INTEGER) ENGINE='MyISAM';

ENGINE 关键字指定用于此特定表的存储引擎。

如果我们没有明确指定存储引擎，那么将使用默认的存储引擎。在 MySQL 5.5 之前，默认的存储引擎是 MyISAM。对于 MySQL 5.5 及更高版本，默认的存储引擎是 InnoDB。

可以迁移到不同的存储引擎。请注意，迁移一个大表可能需要很长时间。此外，我们在迁移表时可能会遇到一些问题。某些功能可能在两个表中都不支持。

mysql> SELECT ENGINE FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA='mydb'
    -> AND TABLE_NAME='Cars';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0,05 sec)

此 SQL 语句查找 mydb 数据库中 Cars 表使用的存储引擎。我们也可以使用 SELECT CREATE TABLE Cars SQL 语句。information_schema 是一个存储有关我们表的技术信息的表。

mysql> ALTER TABLE Cars ENGINE='MyISAM';

此 SQL 语句将 Cars 表的存储引擎更改为 MyISAM。

mysql> SELECT ENGINE FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA='mydb'
    -> AND TABLE_NAME='Cars';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0,00 sec)

现在表的存储引擎是 MyISAM。

在 MySQL 教程的这一部分中，我们已经介绍了存储引擎。