优化数据库是,出现这个 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语句