首页 > 代码库 > mysql-copy to tmp table

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

mysql-copy to tmp table