功夫码

mysql出现Copying to tmp table怎么处理

晓峰 时间:2019-08-05 19:01:37 阅读:9

优化数据库是,出现这个 Copying to tmp table,如何处理

# 在mysql里用输入这个查询会列出当前执行的费时的查询
show full processlist


  今天在MYSQL中偶然发现查询命令中有很多语名处在Copying to tmp table状态,接口响应也变得很慢。因为业务需要这个SQL也确实比较复杂,同时因为有union操作,也使用到了临时表。

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. 
    Copying to tmp table状态即表示如果查询超出了MYSQL中配置的tmp_table_size的限制,tmp_table_size配置是每个线程都要分配的大小。应用中实际起限制作用除了tmp_table_size配置外还有max_heap_table_size(会取两者中的最小值)。如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,并用配置中的tmpdir即/tmp来保存查询结果,这无疑会降低响应速度。
但是我的MYSQL本身的配置也不低:tmp_table_size已经配置为1G了.

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

临时解决方案是:

#附上动态改变配置tmp_table_size和max_heap_table_size的命令
set global tmp_table_size=1073741824;
set max_heap_table_size=1073741824;

长期解决方案:

建议一定要修改查询慢的sql语句

本文来源:功夫码(gongfuma.com)

声明:本文系功夫码原创稿件,版权属[功夫码 gongfuma.com]所有,未经授权不得转载,已经协议授权的媒体下载使用时须注明"稿件来源:功夫码",违者将依法追究责任。

相关文章

回到顶部