开发者在考虑为重要、高可用或频繁更新的数据部署使用 MEMORY 存储引擎的应用程序时，应考虑 NDB 集群是否是更好的选择。MEMORY 引擎的典型使用场景具有以下特征：Developers looking to deploy applications that use the MEMORY storage engine for important, highly available, or frequently updated data should consider whether NDB Cluster is a better choice. A typical use case for the MEMORY engine involves these characteristics:

NDB Cluster 提供与 MEMORY 引擎相同的功能，但性能更高，并提供 MEMORY 引擎不可用的额外功能：NDB Cluster offers the same features as the MEMORY engine with higher performance levels, and provides additional features not available with MEMORY:

MEMORY 表不能分区。MEMORY tables cannot be partitioned.

MEMORY 的性能受单线程执行和在处理更新时表锁开销导致的争用限制。当负载增加时，这会限制可扩展性，尤其是对于包含写操作的语句混合。MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

尽管 MEMORY 表使用内存处理，但在繁忙的服务器上，对于通用查询或在读/写工作负载下，它们不一定比 InnoDB 表更快。特别是，执行更新时涉及的表锁可能会减慢来自多个会话的 MEMORY 表的并发使用。Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions.

根据对 MEMORY 表执行的查询类型，您可以创建默认的哈希数据结构索引（用于基于唯一键查找单个值），或通用的 B 树数据结构索引（用于涉及相等、不等或范围操作符如小于或大于的各种查询）。以下各节说明了创建这两类索引的语法。一个常见的性能问题是在 B 树索引更高效的工作负载中使用默认的哈希索引。Depending on the kinds of queries performed on a MEMORY table, you might create indexes as either the default hash data structure (for looking up single values based on a unique key), or a general-purpose B-tree data structure (for all kinds of queries involving equality, inequality, or range operators such as less than or greater than). The following sections illustrate the syntax for creating both kinds of indexes. A common performance issue is using the default hash indexes in workloads where B-tree indexes are more efficient.

MEMORY 存储引擎不会在磁盘上创建任何文件。表定义存储在 MySQL 数据字典中。The MEMORY storage engine does not create any files on disk. The table definition is stored in the MySQL data dictionary.

MEMORY 表具有以下特性：MEMORY tables have the following characteristics:

要创建一个 MEMORY 表，请在 CREATE TABLE 语句中指定子句 ENGINE=MEMORY。To create a MEMORY table, specify the clause ENGINE=MEMORY on the CREATE TABLE statement.

CREATE TABLE t (i INT) ENGINE = MEMORY;

顾名思义，MEMORY 表存储在内存中。它们默认使用哈希索引，这使得单值查找非常快速，并且非常适合创建临时表。但当服务器关闭时，存储在 MEMORY 表中的所有行都会丢失。表本身仍然存在，因为它们的定义存储在 MySQL 数据字典中，但在服务器重启时表是空的。As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast for single-value lookups, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in the MySQL data dictionary, but they are empty when the server restarts.

下面的示例展示了如何创建、使用和删除一个 MEMORY 表：This example shows how you might create, use, and remove a MEMORY table:

mysql> CREATE TABLE test ENGINE=MEMORY
           SELECT ip,SUM(downloads) AS down
           FROM log_table GROUP BY ip;

mysql> SELECT COUNT(ip),AVG(down) FROM test;

mysql> DROP TABLE test;

MEMORY 表的最大大小受 max_heap_table_size 系统变量的限制，该变量的默认值为 16MB。要为 MEMORY 表设定不同的大小限制，可以更改此变量的值。在 CREATE TABLE 或后续的 ALTER TABLE 或 TRUNCATE TABLE 中生效的值，是表存在期间使用的值。服务器重启也会将现有 MEMORY 表的最大大小设置为全局 max_heap_table_size 值。您可以按照本节稍后描述的方法为单个表设置大小。The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To enforce different size limits for MEMORY tables, change the value of this variable. The value in effect for CREATE TABLE, or a subsequent ALTER TABLE or TRUNCATE TABLE, is the value used for the life of the table. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. You can set the size for individual tables as described later in this section.

MEMORY 存储引擎支持 HASH 和 BTREE 索引。您可以通过添加 USING 子句来为给定索引指定其中之一，如下所示：The MEMORY storage engine supports both HASH and BTREE indexes. You can specify one or the other for a given index by adding a USING clause as shown here:

CREATE TABLE lookup
    (id INT, INDEX USING HASH (id))
    ENGINE = MEMORY;
CREATE TABLE lookup
    (id INT, INDEX USING BTREE (id))
    ENGINE = MEMORY;

有关 B 树和哈希索引的一般特性，请参见 第 10.3.1 节，“MySQL 如何使用索引”。For general characteristics of B-tree and hash indexes, see Section 10.3.1, “How MySQL Uses Indexes”.

MEMORY 表每张表最多可以有 64 个索引，每个索引最多 16 列，最大键长为 3072 字节。MEMORY tables can have up to 64 indexes per table, 16 columns per index and a maximum key length of 3072 bytes.

如果一个 MEMORY 表的哈希索引具有高度的键重复（许多索引条目包含相同的值），则对影响键值的表更新以及所有删除操作都会明显变慢。该减速的程度与重复的程度成正比（或者与索引基数成反比）。您可以使用 BTREE 索引来避免此问题。If a MEMORY table hash index has a high degree of key duplication (many index entries containing the same value), updates to the table that affect key values and all deletes are significantly slower. The degree of this slowdown is proportional to the degree of duplication (or, inversely proportional to the index cardinality). You can use a BTREE index to avoid this problem.

MEMORY 表可以有非唯一键。（这是哈希索引实现中不常见的功能。）MEMORY tables can have nonunique keys. (This is an uncommon feature for implementations of hash indexes.)

被索引的列可以包含 NULL 值。Columns that are indexed can contain NULL values.

MEMORY 表的内容存储在内存中，这是 MEMORY 表与服务器在处理查询时动态创建的内部临时表共有的特性。然而，这两类表的区别在于 MEMORY 表不受存储转换的影响，而内部临时表则会受到影响：MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal temporary tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal temporary tables are:

要在 MySQL 服务器启动时填充 MEMORY 表，可以使用 init_file 系统变量。例如，您可以将 INSERT INTO ... SELECT 或 LOAD DATA 语句放入文件中，从持久数据源加载表，然后使用 init_file 来指定文件名。参见 第 7.1.8 节，“服务器系统变量” 和 第 15.2.9 节，“LOAD DATA 语句”。To populate a MEMORY table when the MySQL server starts, you can use the init_file system variable. For example, you can put statements such as INSERT INTO ... SELECT or LOAD DATA into a file to load the table from a persistent data source, and use init_file to name the file. See Section 7.1.8, “Server System Variables”, and Section 15.2.9, “LOAD DATA Statement”.

当一个复制源服务器关闭并重新启动时，它的 MEMORY 表会变为空。为了将这种效果复制到副本，源服务器在首次启动后使用某个 MEMORY 表时，会记录一个事件，通知副本必须通过向二进制日志写入该表的 TRUNCATE TABLE 语句来清空该表。当副本服务器关闭并重新启动时，它的 MEMORY 表也会变为空，并会将 TRUNCATE TABLE 语句写入它自己的二进制日志，该日志会传递给下游的任何副本。When a replication source server shuts down and restarts, its MEMORY tables become empty. To replicate this effect to replicas, the first time that the source uses a given MEMORY table after startup, it logs an event that notifies replicas that the table must be emptied by writing a TRUNCATE TABLE statement for that table to the binary log. When a replica server shuts down and restarts, its MEMORY tables also become empty, and it writes a TRUNCATE TABLE statement to its own binary log, which is passed on to any downstream replicas.

当您在复制拓扑中使用 MEMORY 表时，在某些情况下，源表和副本表可能会不同。有关如何处理这些情况以防止过时读取或错误的信息，请参见 第 19.5.1.21 节，“复制与 MEMORY 表”。When you use MEMORY tables in a replication topology, in some situations, the table on the source and the table on the replica can differ. For information on handling each of these situations to prevent stale reads or errors, see Section 19.5.1.21, “Replication and MEMORY Tables”.

服务器需要足够的内存来维持同时使用的所有 MEMORY 表。The server needs sufficient memory to maintain all MEMORY tables that are in use at the same time.

如果你从 MEMORY 表中删除单独的行，内存不会被回收。只有在删除整个表时，内存才会被回收。之前用于已删除行的内存会在同一个表中新插入的行中重新使用。要在不再需要 MEMORY 表内容时释放其使用的所有内存，可以执行 DELETE 或 TRUNCATE TABLE 来删除所有行，或者使用 DROP TABLE 完全删除表。要释放已删除行使用的内存，可以使用 ALTER TABLE ENGINE=MEMORY 强制重建表。Memory is not reclaimed if you delete individual rows from a MEMORY table. Memory is reclaimed only when the entire table is deleted. Memory that was previously used for deleted rows is re-used for new rows within the same table. To free all the memory used by a MEMORY table when you no longer require its contents, execute DELETE or TRUNCATE TABLE to remove all rows, or remove the table altogether using DROP TABLE. To free up the memory used by deleted rows, use ALTER TABLE ENGINE=MEMORY to force a table rebuild.

在 MEMORY 表中，一行所需的内存按以下表达式计算：The memory needed for one row in a MEMORY table is calculated using the following expression:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

ALIGN() 表示一个向上取整的因子，以使行长度成为 char 指针大小的整数倍。sizeof(char*) 在 32 位机器上为 4，在 64 位机器上为 8。ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

如前所述，max_heap_table_size 系统变量设置了 MEMORY 表的最大尺寸限制。要控制单个表的最大尺寸，请在创建每个表之前设置该变量的会话值。（除非您希望该值用于所有客户端创建的 MEMORY 表，否则不要更改全局 max_heap_table_size 值。）下面的示例创建了两个 MEMORY 表，最大尺寸分别为 1MB 和 2MB：As mentioned earlier, the max_heap_table_size system variable sets the limit on the maximum size of MEMORY tables. To control the maximum size for individual tables, set the session value of this variable before creating each table. (Do not change the global max_heap_table_size value unless you intend the value to be used for MEMORY tables created by all clients.) The following example creates two MEMORY tables, with a maximum size of 1MB and 2MB, respectively:

mysql> SET max_heap_table_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql> SET max_heap_table_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)

如果服务器重启，这两张表都会恢复为服务器的全局 max_heap_table_size 值。Both tables revert to the server's global max_heap_table_size value if the server restarts.

您还可以在 CREATE TABLE 语句中为 MEMORY 表指定 MAX_ROWS 表选项，以提供有关您计划在其中存储的行数的提示。这并不会使表能够超过 max_heap_table_size 值，该值仍然限制表的最大大小。为了能够最大程度地灵活使用 MAX_ROWS，请将 max_heap_table_size 设置为至少与您希望每个 MEMORY 表能够增长的值相等。You can also specify a MAX_ROWS table option in CREATE TABLE statements for MEMORY tables to provide a hint about the number of rows you plan to store in them. This does not enable the table to grow beyond the max_heap_table_size value, which still acts as a constraint on maximum table size. For maximum flexibility in being able to use MAX_ROWS, set max_heap_table_size at least as high as the value to which you want each MEMORY table to be able to grow.

一个专门讨论 MEMORY 存储引擎的论坛可在 https://forums.mysql.com/list.php?92 访问。A forum dedicated to the MEMORY storage engine is available at https://forums.mysql.com/list.php?92.