可用到的语句:
SET GLOBAL max_heap_table_size=522715200;512M
SHOW status like 'created%';
SHOW variables LIKE '%table_size';
以下内容整理自mysql手册(tmp_table_size/
max_heap_table_size
):
Command-Line Format | --tmp_table_size=# | ||
Option-File Format | tmp_table_size | ||
System Variable Name | tmp_table_size | ||
Variable Scope | Global, Session | ||
Dynamic Variable | Yes | ||
Permitted Values | |||
Type | numeric | ||
Default | system dependent | ||
Range | 1024 .. 4294967295 |
The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum oftmp_table_size
and max_heap_table_size
.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM
table. Increase the value of tmp_table_size
(andmax_heap_table_size
if necessary) if you do many advanced GROUP BY
queries and you have lots of memory. This variable does not apply to user-created MEMORY
tables.
You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables
and Created_tmp_tables
variables.
Command-Line Format | --max_heap_table_size=# | ||
Option-File Format | max_heap_table_size | ||
System Variable Name | max_heap_table_size | ||
Variable Scope | Global, Session | ||
Dynamic Variable | Yes | ||
Permitted Values | |||
Platform Bit Size | 32 | ||
Type | numeric | ||
Default | 16777216 | ||
Range | 16384 .. 4294967295 | ||
Permitted Values | |||
Platform Bit Size | 64 | ||
Type | numeric | ||
Default | 16777216 | ||
Range | 16384 .. 1844674407370954752 |
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. Setting this variable has no effect on any existingMEMORY
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 globalmax_heap_table_size
value.
This variable is also used in conjunction with tmp_table_size
to limit the size of internal in-memory tables. SeeSection 8.8.5, “How MySQL Uses Internal Temporary Tables”.
8.8.5. How MySQL Uses Internal Temporary Tables
In some cases, the server creates internal temporary tables while processing queries. Such a table can be held in memory and processed by the MEMORY
storage engine, or stored on disk and processed by the MyISAM
storage engine. The server may create a temporary table initially as an in-memory table, then convert it to an on-disk table if it becomes too large. Users have no direct control over when the server creates an internal temporary table or which storage engine the server uses to manage it.
Temporary tables can be created under conditions such as these:
-
If there is an
ORDER BY
clause and a differentGROUP BY
clause, or if theORDER BY
orGROUP BY
contains columns from tables other than the first table in the join queue, a temporary table is created. -
DISTINCT
combined withORDER BY
may require a temporary table. -
If you use the
SQL_SMALL_RESULT
option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
To determine whether a query requires a temporary table, use EXPLAIN
and check the Extra
column to see whether it says Using temporary
. See Section 8.2.1, “Optimizing Queries with EXPLAIN
”.
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
The SHOW COLUMNS
and The DESCRIBE
statements use BLOB
as the type for some columns, thus the temporary table used for the results is an on-disk table.
If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size
and max_heap_table_size
values. This differs from MEMORY
tables explicitly created withCREATE TABLE
: For such tables, the max_heap_table_size
system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.
When the server creates an internal temporary table (either in memory or on disk), it increments theCreated_tmp_tables
status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables
status variable.