+-----+--------+-----------+--------------+---------+------+----------------------+---------+
|
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
mysql-copy to tmp table
标签：今天 查询 ros view 就会 query apple lis localhost
