MySQL Variables max_heap_table_size 数据库 参数变量解释及正确配置使用

本站中文解释

MySQL的max_heap_table_size参数控制可以分配给内存表的最大存储空间，超出这个参数的设置值时，MySQL就会从内存表转换为普通的磁盘表，从而节省不必要的内存开销，并获得更好的性能。

要设置max_heap_table_size参数，需要在my.cnf文件（或在Windows上的my.ini文件）中添加一行：

max_heap_table_size = size_in_bytes

其中，size_in_bytes变量可以根据您希望分配给内存表的存储空间大小来设置。要使更改生效，您需要重新启动MySQL服务器。

官方英文解释

max_heap_table_size

This variable sets the maximum size to which user-created
MEMORY tables are permitted to grow. The
value of the variable is used to calculate
MEMORY table MAX_ROWS
values.

The block size is 1024. A value that is not an exact multiple
of the block size is rounded down to the next lower multiple
of the block size by MySQL Server before storing the value for
the system variable. The parser allows values up to the
maximum unsigned integer value for the platform (4294967295 or
2³²−1 for a 32-bit system,
18446744073709551615 or 2⁶⁴−1
for a 64-bit system) but the actual maximum is a block size
lower.

Setting this variable has no effect on any existing
MEMORY table, unless the table is
re-created with a statement such as
CREATE TABLE or altered with
ALTER TABLE or
TRUNCATE TABLE. A server
restart also sets the maximum size of existing
MEMORY tables to the global
max_heap_table_size value.

This variable is also used in conjunction with
tmp_table_size to limit the
size of internal in-memory tables. See
Section 8.4.4, “Internal Temporary Table Use in MySQL”.

max_heap_table_size is not replicated. See
Section 16.4.1.20, “Replication and MEMORY Tables”, and
Section 16.4.1.37, “Replication and Variables”, for more
information.