Mysql 简明教程

MySQL - Storage Engines

The MySQL Storage Engines

正如我们已经知道的那样，MySQL 数据库用于以行和列的格式存储数据。 MySQL storage engine 是一个用于处理对此数据进行管理操作的 SQL 操作的组件。这些操作涉及简单任务，例如创建表、重命名表、更新或删除表；这些任务对于提高数据库性能是必需的。

有两个类别的存储引擎：事务型引擎和非事务型引擎。许多常见的存储引擎属于这两类中的任何一类。不过，在 MySQL 中，默认存储引擎是 InnoDB。

Common Storage Engines

与 MySQL 一起使用的各种常见存储引擎如下所示 −

InnoDB Storage Engine

ACID Compliant − InnoDB 是 MySQL 5.5 及更高版本中的默认存储引擎。它是一个事务型数据库引擎，确保符合 ACID 规范，这意味着它支持像提交和回滚这样的操作。
Crash-Recovery − InnoDB 提供了崩溃恢复功能，以保护用户数据。
Row-Level Locking − 它支持行级锁定，这增强了多用户并发性和性能。
Referential Integrity − 它还强制执行 FOREIGN KEY 引用完整性约束。

ISAM Storage Engine

Deprecated − ISAM，代表索引顺序访问方法，最初支持较早的 MySQL 版本，但已被弃用并从最近的版本中移除。
Limited Size − ISAM 表的最大大小限制为 4GB。

MyISAM Storage Engine

Portability − MyISAM 旨在可移植性，用于处理 ISAM 的不可移植性质。
Performance − 它比 ISAM 提供更快的性能，并且是 MySQL 5.x 之前的默认存储引擎。
Memory Efficiency − MyISAM 表的内存占用较小，使其适合只读或主要为读取的工作负载。

MERGE Storage Engine

Logical Combination − 合并表能使 MySQL 开发者在逻辑上合并多个相同的 MyISAM 表，并将它们作为单个对象引用。
Limited Operations − 仅允许对合并表执行 INSERT、SELECT、DELETE 和 UPDATE 操作。如果使用 DROP 查询，则只重置存储引擎规范，而表保持不变。

MEMORY Storage Engine

In-Memory Storage − MEMORY 表将数据完全存储在 RAM 中，针对快速查找优化了访问速度。
Hash Indexes − 它使用哈希索引以更快的速度检索数据。
Decreasing Use − 其用例正在减少；其他引擎（如 InnoDB 的缓冲池内存区域）提供了更好的内存管理。

CSV Storage Engine

CSV Format − CSV 表是带有逗号分隔值的文本文件，可以与脚本和应用程序交换数据。
No Indexing − 它们未编制索引，并且通常与 InnoDB 表一起在数据导入或导出期间使用。

NDBCLUSTER Storage Engine

Clustering − NDBCLUSTER（也称为 NDB）是一种群集数据库引擎，适合需要最高程度的正常运行时间和可用性的应用程序。

ARCHIVE Storage Engine

Historical Data − ARCHIVE 表非常适合存储和检索大量的历史、归档或安全数据。ARCHIVE 存储引擎支持支持非索引表。

BLACKHOLE Storage Engine

Data Discard − BLACKHOLE 表接受数据但不存储数据，总是返回一个空集。
Usage − 在复制配置中使用，其中 DML 语句被发送至副本服务器，但源服务器不保留数据自身的副本。

FEDERATED Storage Engine

Distributed Databases − FEDERATED 允许连接单独的 MySQL 服务器以从多个物理服务器创建逻辑数据库，这在分布式环境中非常有用。

EXAMPLE Storage Engine

Development Tool − EXAMPLE 是 MySQL 源代码中的工具，充当开发者编写新存储引擎的示例。你可以使用此引擎创建数据表，但它不存储或检索数据。

尽管有许多可以与数据库一起使用的存储引擎，但并不存在所谓的完美存储引擎。在某些情况下，一个存储引擎可能是更合适的，而在其他情况下，其他引擎则表现得更好。因此，在特定环境中工作时，必须仔细选择要使用的存储引擎。

要选择引擎，你可以使用 SHOW ENGINES 语句。

SHOW ENGINES Statement

MySQL 中的 SHOW ENGINES 语句将列出所有存储引擎。在选择由数据库支持且易于使用的引擎时可以将其考虑在内。

Syntax

以下为 SHOW ENGINES 语句的语法 -

SHOW ENGINES\G

其中，使用“\G”分隔符垂直对齐从执行此语句中获得的结果集。

Example

让我们观察使用以下查询在 MySQL 数据库中执行 SHOW ENGINES 语句获得的结果集 -

SHOW ENGINES\G

Output

以下是获得的结果集。在这里，您可以查看 MySQL 数据库支持哪些存储引擎，以及它们在哪种情况下使用最合适 -

*************************** 1. row ************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ************************
      Engine: ndbinfo
     Support: NO
     Comment: MySQL Cluster system information storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 9. row ************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 11. row ************************
      Engine: ndbcluster
     Support: NO
     Comment: Clustered, fault-tolerant tables
Transactions: NULL
          XA: NULL
  Savepoints: NULL
11 rows in set (0.00 sec)

Setting a Storage Engine

在选择一个用于某个表的存储引擎后，您可能希望在创建数据库表时设置它。这通过在 CREATE TABLE 语句中添加存储引擎名称以指定您希望使用的引擎类型来完成。

如果您未指定引擎类型，则将自动使用默认引擎（MySQL 的 InnoDB）。

Syntax

以下是在 CREATE TABLE 语句中设置存储引擎的语法 -

CREATE TABLE table_name (
   column_name1 datatype,
   column_name2 datatype,
   .
   .
   .
) ENGINE = engine_name;

Example

在此示例中，让我们使用以下查询在 MyISAM 存储引擎上创建一个新表“TEST” -

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = MyISAM;

获得的结果如下所示 −

Query OK, 0 rows affected (0.01 sec)

但如果我们在 MySQL 不支持的引擎（如 FEDERATED）上创建一个表，则会引发错误 -

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = FEDERATED;

会发生以下错误 -

ERROR 1286 (42000): Unknown storage engine 'FEDERATED'

Changing Default Storage Engine

MySQL 还提供了通过三种方式更改默认存储引擎选项的条款 -

使用“--default-storage-engine=name”服务器启动选项。
在“my.cnf”配置文件中设置“default-storage-engine”选项。
Using SET statement

Syntax

让我们了解 SET 语句的语法，以更改数据库中的默认存储引擎 -

SET default_storage_engine = engine_name;

Note - 用于临时表的存储引擎（使用 CREATE TEMPORARY TABLE 语句创建）可以通过设置“default_tmp_storage_engine”（在启动时或运行时）单独设置。

Example

在此示例中，我们使用指定的 SET 语句将默认存储引擎更改为 MyISAM -

SET default_storage_engine = MyISAM;

获得的结果如下 −

Query OK, 0 rows affected (0.00 sec)

现在，让我们使用以下 SHOW ENGINES 语句列出存储引擎。MyISAM 存储引擎的支持列已更改为 default -

SHOW ENGINES\G

Output

以下是产生的结果集。在这里，请注意我们没有显示整个结果集，为了便于理解，只显示了 MyISAM 行。实际的结果集总共包含 11 行 -

*************************** 6. row ************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
11 rows in set (0.00 sec)

Altering Storage Engine

您还可以在 MySQL 中使用 ALTER TABLE 命令将表的现有存储引擎更改为另一个存储引擎。但是，必须将存储引擎更改为仅受 MySQL 支持的引擎。

Syntax

以下是更改现有存储引擎到另一个的简单语法：

ALTER TABLE table_name ENGINE = engine_name;

Example

考虑之前在 MyISAM 数据库引擎上创建的表 TEST。在这个例子中，使用下面的 ALTER TABLE 命令，我们正在将其更改为 InnoDB 引擎。

ALTER TABLE TEST ENGINE = InnoDB;

Output

执行上述查询后，我们将获得以下输出：

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

为了验证存储引擎是否被更改，使用下面的查询：

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'testDB';

所生成的表如下所示：

Storage Engines Using a Client Program

我们也可以使用客户端程序执行存储引擎。

Syntax

Example

以下是这些程序 −