Mysql 简明教程

MySQL - Storage Engines

The MySQL Storage Engines

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

As we already know, a MySQL database is used to store data in the form of rows and columns. The MySQL storage engine is a component that is used to handle the SQL operations performed to manage this data. They work with simple tasks like creating a table, renaming it, updating or deleting it; which is necessary to increase the database performance.

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

There are two categories of storage engines used: transactional engines and non-transactional engines. Many common storage engines fall into either type of these categories. In MySQL, however, the default storage engine is InnoDB.

Common Storage Engines

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

Various common storage engines that are used to work with MySQL are as follows −

InnoDB Storage Engine

ACID Compliant − InnoDB is the default storage engine in MySQL 5.5 and later versions. It is a transactional database engine, ensuring ACID compliance, which means it supports operations like commit and rollback.
Crash-Recovery − InnoDB offers crash-recovery capabilities to protect user data.
Row-Level Locking − It supports row-level locking, which enhances multi-user concurrency and performance.
Referential Integrity − It also enforces FOREIGN KEY referential-integrity constraints.

ISAM Storage Engine

Deprecated − ISAM, which stands for Indexed Sequential Access Method, was supported by earlier MySQL versions but has been deprecated and removed from recent versions.
Limited Size − ISAM tables were limited to a size of 4GB.

MyISAM Storage Engine

Portability − MyISAM is designed for portability, addressing ISAM’s non-portable nature.
Performance − It offers faster performance compared to ISAM and was the default storage engine before MySQL 5.x.
Memory Efficiency − MyISAM tables have a small memory footprint, making them suitable for read-only or read-mostly workloads.

MERGE Storage Engine

Logical Combination − MERGE table enables a MySQL developer to logically combine multiple identical MyISAM tables and reference them as one object.
Limited Operations − Only INSERT, SELECT, DELETE, and UPDATE operations are allowed on MERGE tables. If DROP query is used, only the storage engine specification gets reset while the table remains unchanged.

MEMORY Storage Engine

In-Memory Storage − MEMORY tables store data entirely in RAM, optimizing access speed for quick lookups.
Hash Indexes − It uses hash indexes for faster data retrieval.
Decreasing Use − Its use cases are decreasing; other engines, like InnoDB’s buffer pool memory area provide better memory management.

CSV Storage Engine

CSV Format − CSV tables are text files with comma-separated values, useful for data exchange with scripts and applications.
No Indexing − They are not indexed, and generally used during data import or export alongside InnoDB tables.

NDBCLUSTER Storage Engine

Clustering − NDBCLUSTER, also known as NDB, is a clustered database engine suitable for applications that require the highest possible degree of uptime and availability.

ARCHIVE Storage Engine

Historical Data − ARCHIVE tables are ideal for storing and retrieving large amounts of historical, archived, or secure data. The ARCHIVE storage engines support supports non-indexed tables

BLACKHOLE Storage Engine

Data Discard − BLACKHOLE tables accept data but do not store it, always returning an empty set.
Usage − Used in replication configurations, where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.

FEDERATED Storage Engine

Distributed Databases − FEDERATED allows linking separate MySQL servers to create a logical database from multiple physical servers, useful in distributed environments.

EXAMPLE Storage Engine

Development Tool − EXAMPLE is a tool in the MySQL source code that serves as an example for developers to start writing new storage engines. You can create tables with this engine, but it doesn’t store or retrieve data.

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

Even though there are so many storage engines that can be used with databases, there is no such thing called a perfect storage engine. In some situations, one storage engine could be a better fit to use whereas in other situations, other engines perform better. Therefore, one must carefully choose what Storage engine to use while working in certain environments.

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

To choose an engine, you can use the SHOW ENGINES statement.

SHOW ENGINES Statement

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

The SHOW ENGINES statement in MySQL will list out all the storage engines. It can be taken into consideration while choosing an engine that are supported by the database and are easy to work with.

Syntax

以下为 SHOW ENGINES 语句的语法 -

Following is the syntax of the SHOW ENGINES statement −

SHOW ENGINES\G

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

where, the '\G' delimiter is used to vertically align the result-set obtained from executing this statement.

Example

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

Let us observe the result-set obtained by executing the SHOW ENGINES statement in a MySQL database using the following query −

SHOW ENGINES\G

Output

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

Following is the result-set obtained. Here, you can check which storage engines are supported by the MySQL database and where they can be best used −

*************************** 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 语句中添加存储引擎名称以指定您希望使用的引擎类型来完成。

Once a storage engine is chosen to be used on a table, you might want to set it while creating the database table. This is done by specifying the type of engine you want to use by adding its name in the CREATE TABLE statement.

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

If you do not specify the engine type, the default engine (InnoDB for MySQL) will be used automatically.

Syntax

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

Following is the syntax to set a storage engine in CREATE TABLE statement −

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

Example

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

In this example, let us create a new table 'TEST' on MyISAM storage engine using the following query −

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

获得的结果如下所示 −

The result obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

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

But if we create a table on an engine that is not supported by MySQL, say FEDERATED, an error is raised −

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

会发生以下错误 -

We get the following error −

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

Changing Default Storage Engine

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

MySQL also has provisions to change the default storage engine option in three ways −

Using '--default-storage-engine=name' server startup option.
Setting 'default-storage-engine' option in 'my.cnf' configuration file.
Using SET statement

Syntax

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

Let us see the syntax of using SET statement to change the default storage engine in a database −

SET default_storage_engine = engine_name;

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

Note − The storage engine for temporary tables, which were created with the CREATE TEMPORARY TABLE statement, can be set separately by setting the 'default_tmp_storage_engine', either at startup or at runtime.

Example

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

In this example, we are changing the default storage engine to MyISAM using SET statement given as follows −

SET default_storage_engine = MyISAM;

获得的结果如下 −

The result obtained is as follows −

Query OK, 0 rows affected (0.00 sec)

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

Now, let us list the storage engines using SHOW ENGINES statement below. The support column for MyISAM storage engine is changed to default −

SHOW ENGINES\G

Output

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

Following is the result-set produced. Here, note that we are not displaying the entire result-set and only the MyISAM row for simpler understandability. The actual result-set has 11 total rows −

*************************** 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 支持的引擎。

You can also alter the existing storage engine of a table to another storage engine using the ALTER TABLE command in MySQL. However, the storage engine must be changed to one that is supported by MySQL only.

Syntax

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

Following is the basic syntax to change the existing storage engine to another −

ALTER TABLE table_name ENGINE = engine_name;

Example

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

Consider the previously created table TEST on MyISAM database engine. In this example, using the following ALTER TABLE command, we are changing it to InnoDB engine.

ALTER TABLE TEST ENGINE = InnoDB;

Output

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

After executing the above query, we get the following output −

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

Verification

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

To verify whether the storage engine is changed or not, use the following query −

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

所生成的表如下所示：

The table produced is as shown below −

Storage Engines Using a Client Program

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

We can also perform storage Engines using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −