MySql 中文参考指南

Chapter 18 Alternative Storage Engines

Table of Contents

存储引擎是 MySQL 组件，负责处理不同表类型的 SQL 操作。 InnoDB 是默认且最通用的存储引擎，Oracle 建议将其用于表，但专门用例除外。（MySQL 9.0 中的 CREATE TABLE 语句会默认创建 InnoDB 表。）

Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (The CREATE TABLE statement in MySQL 9.0 creates InnoDB tables by default.)

MySQL Server 使用可插入存储引擎架构，该架构使存储引擎可以加载到正在运行的 MySQL 服务器中并从其中卸载。

MySQL Server uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.

若要确认你的服务器支持哪些存储引擎，请使用 SHOW ENGINES 语句。 Support 列中的值表示引擎是否可以使用。 YES 、 NO 或 DEFAULT 的值表示引擎可用、不可用或可用且当前设置为默认存储引擎。

To determine which storage engines your server supports, use the SHOW ENGINES statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine.

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...

本章介绍了 MySQL 专用存储引擎的用例。它不涉及默认 InnoDB 存储引擎或 NDB 存储引擎， Chapter 17, The InnoDB Storage Engine 和 Chapter 25, MySQL NDB Cluster 9.0 中介绍了该存储引擎。对于高级用户，它还包含了可插拔存储引擎架构的说明（请参阅 Section 18.11, “Overview of MySQL Storage Engine Architecture” ）。

This chapter covers use cases for special-purpose MySQL storage engines. It does not cover the default InnoDB storage engine or the NDB storage engine which are covered in Chapter 17, The InnoDB Storage Engine and Chapter 25, MySQL NDB Cluster 9.0. For advanced users, it also contains a description of the pluggable storage engine architecture (see Section 18.11, “Overview of MySQL Storage Engine Architecture”).

有关商业 MySQL Server 二进制文件中提供的功能的信息，请参阅 MySQL 网站上的 MySQL Editions 。可用的存储引擎可能取决于你使用的 MySQL 版本。

For information about features offered in commercial MySQL Server binaries, see MySQL Editions, on the MySQL website. The storage engines available might depend on which edition of MySQL you are using.

有关 MySQL 存储引擎的常见问题解答，请参阅 Section A.2, “MySQL 9.0 FAQ: Storage Engines”。

For answers to commonly asked questions about MySQL storage engines, see Section A.2, “MySQL 9.0 FAQ: Storage Engines”.

MySQL 9.0 Supported Storage Engines

InnoDB: The default storage engine in MySQL 9.0. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 17, The InnoDB Storage Engine.
MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.
CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be 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.
NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.
Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.

您不必为整个服务器或模式使用相同的存储引擎。您可以为任意表指定存储引擎。例如，应用程序可能主要使用 InnoDB 表，使用一个 CSV 表向电子表格导出数据，并使用一些 MEMORY 表作为临时工作区。

You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly InnoDB tables, with one CSV table for exporting data to a spreadsheet and a few MEMORY tables for temporary workspaces.

Choosing a Storage Engine

MySQL 提供的各种存储引擎设计时考虑到了不同的用例。下表概述了 MySQL 提供的部分存储引擎，并附有表中内容的说明。

The various storage engines provided with MySQL are designed with different use cases in mind. The following table provides an overview of some storage engines provided with MySQL, with clarifying notes following the table.

表 18.1 存储引擎特性摘要

Table 18.1 Storage Engines Feature Summary

Feature

MyISAM

Memory

InnoDB