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

参考
IT虾米网
IT虾米网