mysql Copying to tmp table on disk

call 存储过程，用processlist发现一进程ID一直处于QUERY状态
State：Copying to tmp table on disk，
Info：CREATE TEMPORARY TABLE tmp_transact

• Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.
• 经过查资料发现mysql可以通过变量tmp__table__size和max__heap__table__size来控制内存表大小上限，如果超过上限会将数据写到磁盘tmp__table上，然后返回给客户端，从而多了一次IO）

因为存储过程里面包含大量的临时表，以及左连接，且临时表没有建立索引（SQL太渣，略去）；

查看设置：

mysql> show variables like '%tmp%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| max_tmp_tables    | 32         |
| slave_load_tmpdir | /u01/tmp   |
| tmp_table_size    | 1073741824 |
| tmpdir            | /u01/tmp   |
+-------------------+------------+
4 rows in set (0.00 sec)

mysql> show variables like  'max_heap_table_size';
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| max_heap_table_size | 2147483648 |
+---------------------+------------+
1 row in set (0.00 sec)

解决方案：

• 命令行改变配置的命令：
• set global tmp_table_size=1073741824;
• set max_heap_table_size=1073741824;