mysql 的内存临时表两个参数 tmp_table_size、max_heap_table_size

mysql 5.5

>show processlist;
观察 State 列时，发现经常有 Copying to tmp table、Copying to tmp table on disk

baidu 了下，获取的信息是调整参数 tmp_table_size、max_heap_table_size

这两个参数是mysql对临时表的大小控制
其中

tmp_table_size 控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定
max_heap_table_size 用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。

下面是官方的解释

tmp_table_size

Command-Line Format --tmp-table-size=#
System Variable Name    tmp_table_size
Scope   Global, Session
Dynamic Yes
Permitted Values    Type    integer
Default 16777216
Minimum 1024
Maximum 18446744073709551615

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.
The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. 

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 (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

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.

See also Section 8.4.4, “Internal Temporary Table Use in MySQL”.

max_heap_table_size

Command-Line Format --max-heap-table-size=#
System Variable Name    max_heap_table_size
Scope   Global, Session
Dynamic Yes
Permitted Values (32-bit platforms) Type    integer
Default 16777216
Minimum 16384
Maximum 4294967295
Permitted Values (64-bit platforms) Type    integer
Default 16777216
Minimum 16384
Maximum 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 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 17.4.1.20, “Replication and MEMORY Tables”, and Section 17.4.1.38, “Replication and Variables”, for more information.

如何修改,这里调整为2G
1)
SET GLOBAL tmp_table_size=2147483648;
SET GLOBAL max_heap_table_size=2147483648;

2)
vi /etc/my.cnf
[mysqld]
max_heap_table_size = 2048M
tmp_table_size = 2048M

参考
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_tmp_table_size
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_heap_table_size