mysql一直copying to tmp table_mysql查询慢（Copying to tmp table）解决一例

最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill  进程号；后面的查询一下子都好了。  ok, 找到了问题的原因，此查询效率太低。问一下程序员，找来此查询的代码，用工具进行一下简单分析。(说明：这里是我不喜欢mysql的原因之一，mysql我不知道从哪里能看到内存正在执行哪些sql,以及完整的sql是什么。)explainSELECT a.* , IF(b.`gid` IS NULL , 0, SUM( b.`mark` )) AS `score` ,IF(c.`b_times` IS NULL ,0, c.`b_times`) AS `day_b_times`FROM `league_info` AS a LEFT JOIN `mark_logs` AS b ON b.`day_date` = '2006-03-22'AND b.`gid` = a.`id` LEFT JOIN  `visit_stat` AS c ON c.`stat_id` = a.`id` AND c.`type` = 'league'AND c.`day`='2006_03_22' WHERE a.`validate`='1' GROUP BY  a.`id`ORDER BY day_b_times DESC, a.`id`;+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+| id | select_type | table | type | possible_keys                 | key              | key_len | ref                 | rows | Extra                                        |+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+|  1 | SIMPLE      | a     | ref  | league_info_idx4              | league_info_idx4 | 1       | const               | 1441 | Using where; Using temporary; Using filesort ||  1 | SIMPLE      | b     | ref  | mark_logs_idx1,mark_logs_idx2 | mark_logs_idx1   | 4       | new5jia1.a.id       |   56 |                                              ||  1 | SIMPLE      | c     | ref  | visit_stat_idx1               | visit_stat_idx1  | 26      | new5jia1.a.id,const |   10 |                                              |+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+看了一下，其实此查询嵌套用得不好，作为程序员应该尽量避免用 not in ，in,  left join  ,right  join 等等，不过这些不归我管，我只能提一些建议。(顺便说一声：oracle里面可以用 exist ,not exist, minus等代替in ,not in  效率高出很多 )此分析对我没有太大的作用，因此用google查询了一下，发现网上一篇文章讲得很好，(http://clay111.blogchina.com/4721079.html我给转贴了，感兴趣可以看看)Copying to tmp table on disk The temporary result set was larger than tmp_table_size and thethread is now changing the in memory-based temporary table to a disk based one to save memory.哦，原来是这样的，如果查询超出了tmp_table_size的限制，那么mysql用/tmp保存查询结果，然后返回给客户端。set global tmp_table_size=209715200  (200M)再次运行此查询,用/opt/mysql/bin/mysqladmin processlist; 进行观察,发现不会出现上述问题.至此问题解决.调节tmp_table_size  的时候发现另外一些参数Qcache_queries_in_cache  在缓存中已注册的查询数目Qcache_inserts  被加入到缓存中的查询数目Qcache_hits  缓存采样数数目Qcache_lowmem_prunes  因为缺少内存而被从缓存中删除的查询数目Qcache_not_cached  没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)Qcache_free_memory  查询缓存的空闲内存总数Qcache_free_blocks  查询缓存中的空闲内存块的数目Qcache_total_blocks  查询缓存中的块的总数目Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度。