8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 18 个替代存储引擎
中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 96%

第 18 章 替代存储引擎Chapter 18 Alternative Storage Engines

存储引擎是 MySQL 的组件，用于处理不同表类型的 SQL 操作。InnoDB是默认且最通用的存储引擎，Oracle 建议在非特殊使用情况之外的表中使用它。（MySQL 8.4 中的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 8.4 creates InnoDB tables by default.)

MySQL 服务器使用可插拔存储引擎架构，这使得存储引擎可以在运行中的 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 存储引擎，这些内容在 第 17 章，InnoDB 存储引擎 和 第 25 章，MySQL NDB Cluster 8.4 中进行了介绍。对于高级用户，本章还包含可插拔存储引擎架构的描述（参见 第 18.11 节，“MySQL 存储引擎架构概述”）。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 8.4. 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 服务器二进制文件提供的功能的信息，请参见 MySQL 网站上的 MySQL 版本。可用的存储引擎可能取决于您使用的 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 存储引擎常见问题的解答，请参见 A.2 节，“MySQL 8.4 FAQ: 存储引擎”。For answers to commonly asked questions about MySQL storage engines, see Section A.2, “MySQL 8.4 FAQ: Storage Engines”.

MySQL 8.4 支持的存储引擎MySQL 8.4 Supported Storage Engines

  • InnoDB：MySQL 8.4 的默认存储引擎。InnoDB 是一个事务安全（ACID 合规）的 MySQL 存储引擎，具有提交、回滚和崩溃恢复功能，以保护用户数据。InnoDB 的行级锁定（不会升级为粗粒度锁）和类似 Oracle 的一致非锁定读取提高了多用户并发性和性能。InnoDB 将用户数据存储在聚簇索引中，以减少基于主键的常见查询的 I/O 操作。为了保持数据完整性，InnoDB 还支持FOREIGN KEY 参照完整性约束。有关 InnoDB 的更多信息，请参见 第 17 章，InnoDB 存储引擎。InnoDB: The default storage engine in MySQL 8.4. 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：这些表的占用空间很小。表级锁在读/写工作负载中限制了性能，因此它通常用于 Web 和数据仓库配置中只读或主要为读的工作负载。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：将所有数据存储在 RAM 中，适用于需要快速查找非关键数据的环境。这种引擎以前被称为 HEAP 引擎。它的使用案例正在减少；InnoDB 通过其缓冲池内存区域提供了一种通用且持久的方法来将大部分或所有数据保存在内存中，而 NDBCLUSTER 为巨大的分布式数据集提供快速的键值查找。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：它的表实际上是带有逗号分隔值的文本文件。CSV 表允许您以 CSV 格式导入或导出数据，以便与读取和写入相同格式的脚本和应用程序交换数据。由于 CSV 表没有索引，通常在正常操作期间将数据保存在 InnoDB 表中，仅在导入或导出阶段使用 CSV 表。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：这些紧凑、无索引的表用于存储和检索大量不常参考的历史、归档或安全审计信息。Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

  • Blackhole：Blackhole 存储引擎接受但不存储数据，类似于 Unix 的 /dev/null 设备。查询总是返回空集。这些表可以用于复制配置，其中 DML 语句被发送到副本服务器，但源服务器不会保留其自己的数据副本。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（也称为 NDBCLUSTER）：这种集群数据库引擎特别适合那些要求最高可用性和正常运行时间的应用程序。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：允许 MySQL DBA 或开发人员逻辑地将一系列相同的 MyISAM 表组合在一起，并将它们作为一个对象进行引用。适用于 VLDB 环境，例如数据仓库。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：提供将独立的 MySQL 服务器链接起来，从多个物理服务器创建一个逻辑数据库的能力。非常适合分布式或数据集市环境。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：该引擎作为 MySQL 源代码中的示例，用于展示如何开始编写新的存储引擎。它主要对开发者有兴趣。该存储引擎是一个“存根”，什么也不做。您可以使用此引擎创建表，但无法在其中存储或检索任何数据。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 Archive NDB
B 树索引B-tree indexes 是Yes 是Yes 是Yes 否No 否No
备份/时间点恢复（注 1）Backup/point-in-time recovery (note 1) 是Yes 是Yes 是Yes 是Yes 是Yes
集群数据库支持Cluster database support 否No 否No 否No 否No 是Yes
聚簇索引Clustered indexes 否No 否No 是Yes 否No 否No
压缩数据Compressed data 是（注 2）Yes (note 2) 否No 是Yes 是Yes 否No
数据缓存Data caches 否No 不适用N/A 是Yes 否No 是Yes
加密数据Encrypted data 是（注 3）Yes (note 3) 是（注 3）Yes (note 3) 是（注 4）Yes (note 4) 是（注 3）Yes (note 3) 是（注 5）Yes (note 5)
外键支持Foreign key support 否No 否No 是Yes 否No 是Yes
全文本搜索索引Full-text search indexes 是Yes 否No 是（注 6）Yes (note 6) 否No 否No
地理空间数据类型支持Geospatial data type support 是Yes 否No 是Yes 是Yes 是Yes
地理空间索引支持Geospatial indexing support 是Yes 否No 是（注 7）Yes (note 7) 否No 否No
哈希索引Hash indexes 否No 是Yes 否（注 8）No (note 8) 否No 是Yes
索引缓存Index caches 是Yes 不适用N/A 是Yes 否No 是Yes
锁定粒度Locking granularity 表Table 表Table 行Row 行Row 行Row
多版本并发控制（MVCC）MVCC 否No 否No 是Yes 否No 否No
复制支持（注 1）Replication support (note 1) 是Yes 有限（注 9）Limited (note 9) 是Yes 是Yes 是Yes
存储限制Storage limits 256TB 内存RAM 64TB 无None 384EB
T 树索引T-tree indexes 否No 否No 否No 否No 是Yes
事务Transactions 否No 否No 是Yes 否No 是Yes
更新数据字典的统计信息Update statistics for data dictionary 是Yes 是Yes 是Yes 是Yes 是Yes

注意：Notes:

1. 在服务器中实现，而不是在存储引擎中实现。1. Implemented in the server, rather than in the storage engine.

2. 压缩的 MyISAM 表仅在使用压缩行格式时受支持。使用 MyISAM 压缩行格式的表为只读。2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

3. 通过服务器中的加密函数实现。3. Implemented in the server via encryption functions.

4. 通过服务器中的加密函数实现；在 MySQL 5.7 及更高版本中，支持静态数据加密。4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest encryption is supported.

5. 通过服务器端的加密功能实现；自 NDB 8.0.22 起支持加密 NDB 备份；自 NDB 8.0.29 起支持透明 NDB 文件系统加密。5. Implemented in the server via encryption functions; encrypted NDB backups as of NDB 8.0.22; transparent NDB file system encryption supported in NDB 8.0.29 and later.

6. 从 MySQL 5.6 起支持 FULLTEXT 索引。6. Support for FULLTEXT indexes is available in MySQL 5.6 and later.

7. 从 MySQL 5.7 起支持地理空间索引。7. Support for geospatial indexing is available in MySQL 5.7 and later.

8. InnoDB 在其自适应哈希索引（Adaptive Hash Index）功能中内部使用哈希索引。8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

9. 请参阅本节后续讨论。9. See the discussion later in this section.

首页   上一级   下一节
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 261.9Kb
Man Pages (Zip) - 367.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb