正文
mysql-copy to tmp table
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
今天数据后台数据反映有些迟缓后查看链接 processlist 发下好多 锁 和磁盘写入，
参考文章 ： http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=3646279
http://www.cnblogs.com/JulyZhang/archive/2011/01/28/1947165.html
+-----+--------+-----------+--------------+---------+------+----------------------+---------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------+--------------+---------+------+----------------------+---------+
| 7 | echina | localhost | echinacities | Query | 19 | Locked |
| 31 | echina | localhost | echinacities | Query | 22 | Locked |
| 408 | echina | localhost | echinacities | Query | 22 | Locked |
| 464 | echina | localhost | echinacities | Query | 22 | Locked |
| 471 | echina | localhost | echinacities | Query | 21 | Locked |
| 499 | echina | localhost | echinacities | Query | 22 | Copying to tmp table |
| 545 | echina | localhost | echinacities | Query | 22 | Locked |
| 569 | echina | localhost | echinacities | Query | 22 | Locked |
| 588 | echina | localhost | echinacities | Query | 21 | Locked |
| 589 | echina | localhost | echinacities | Query | 21 | Locked |
| 602 | echina | localhost | echinacities | Query | 22 | Locked |
| 624 | echina | localhost | echinacities | Query | 21 | Locked |
| 647 | echina | localhost | echinacities | Query | 19 | Locked |
| 651 | echina | localhost | echinacities | Query | 22 | Locked |
| 680 | echina | localhost | echinacities | Query | 22 | Locked |
| 681 | echina | localhost | echinacities | Query | 21 | Locked |
| 688 | echina | localhost | echinacities | Query | 17 | Locked |
| 704 | echina | localhost | echinacities | Query | 22 | Locked |
| 709 | echina | localhost | echinacities | Query | 21 | Locked |
| 710 | echina | localhost | echinacities | Query | 20 | Locked |
| 711 | echina | localhost | echinacities | Query | 20 | Locked |
| 713 | echina | localhost | echinacities | Query | 19 | Locked |
| 718 | echina | localhost | echinacities | Query | 12 | Locked |
| 720 | echina | localhost | echinacities | Query | 18 | Locked |
| 729 | echina | localhost | echinacities | Query | 16 | Locked |
| 731 | echina | localhost | echinacities | Query | 15 | Locked |
| 745 | echina | localhost | echinacities | Sleep | 11 | |
| 746 | echina | localhost | echinacities | Query | 11 | Locked |
| 748 | echina | localhost | echinacities | Sleep | 10 | |
| 749 | echina | localhost | echinacities | Query | 10 | Locked |
+-----+--------+-----------+--------------+---------+------+----------------------+-
mysql默认
tmp_table_size 33554432 (33.5M)
max_heap_table_size 16777216 (16.7M)
copy to tmp table的SQL语句，这条语读的时间比较长，且这个表会被加读锁，相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句，会造成更多的语句被阻塞。
连接太多造成mysql处理慢。
copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时，参数中设置的临时表的大小小于结果集的大小时，就会将该表放在磁盘上，这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时，则系统会把 max_heap_table_size的值作为最大的内存临时表的上限，大于这个时，改写硬盘。
原文blog：http://www.jb51.net/article/19384.htm
