mysql临时表之tmp_table_size、 max_heap_table_size

简述：

mysql用于储存中间结果的表称之为临时表，一般由复杂的sql语句执行时所创建

一种是内存临时表，一种是磁盘临时表

tmp_table_size是内存临时表的最大值，max_heap_table_size是内存临时表的最大行数值。当执行的sql语句生成的临时表数量超过了最大值限制，就会将临时表储存在磁盘中

 

一般在以下情况下会创建临时表：

1、union查询

2、order by

3、distinct

4、from中的子查询

5、sql_small_result

 

查看内存临时表的最大限制：（默认是16+M）

SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';

 

查看临时表的数量：

SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

 

查看磁盘中临时表的数量：

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

 

优化sql的时候应尽量减少使用临时表

一般的比例关系是：Created_tmp_disk_tables/'Created_tmp_tables <0.05

 

修改临时表的内存最大限制：

方法1：（增加到1G）

SET GLOBAL tmp_table_size=1073741824;
SET GLOBAL max_heap_table_size=1073741824;

 

方法2：修改配置文件，添加配置

vi /etc/my.cnf

[mysqld]
max_heap_table_size = 1024M
tmp_table_size = 1024M