SQL优化：Show Profiles之Copying to tmp table 解决方式

亦凉 2023-06-30 04:26 85阅读 0赞

### SQL  ###

>     -- 开启profiles
>     SET profiling = 1;
>     
>     -- 慢SQL
>     select t1.uid,t5.ORDER_NUMBER,sun.mobile,sun.truename,sun.mobile_areacode from
>     		(select * from `dpt_e-commerce`.study_manage_lesson where isDeclare=0 and sdate = DATE_ADD(date_format(now(),'%Y-%m-%d'),INTERVAL -2 DAY)) t1 
>     		INNER JOIN (select userId from `dpt_e-commerce`.usermedals where notGainElectiveScore>0 GROUP BY userId) t2 on t2.userId=t1.uid 
>     		INNER JOIN (select goods_id from `dpt_e-commerce`.goods_score where course_type=1 GROUP BY goods_id) t3 on t1.goods_id = t3.goods_id
>     		INNER JOIN `dpt_e-commerce`.goods_orders t4 on t1.uid=t4.userId and t1.goods_id=t4.GOODSID
>     		INNER JOIN `dpt_e-commerce`.orders t5 on t4.ORDERID=t5.id
>     		INNER JOIN sso.sso_user_new sun ON t1.uid = sun.userId 
>     		GROUP BY t1.uid,t5.ID ORDER BY t1.uid,t5.id;
>     
>     -- 查询所有最近执行的SQL对应的记录，找到对应的Query_ID
>     show profiles;
>     -- 查询对应的慢SQL的Query_ID		
>     show profile cpu,block io ,memory for query  25;

### 问题定位  ###

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz_size_16_color_FFFFFF_t_70][]

### 因为  ###

> 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.
> 
> 复制到磁盘上的tmp表时，临时结果集大于tmp\_table\_size，线程现在将基于内存的临时表更改为基于磁盘的临时表，以节省内存。

### 所以 ###

> 这两个参数是mysql对临时表的大小控制。
> 
> tmp\_table\_size 控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定；
> 
> max\_heap\_table\_size 用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。 
> 
>     show global variables where variable_name like '%tmp_table_size%';
>     
>     show global variables where variable_name like '%max_heap_table_size%';
> 
> 结果，均是2个G大小，如果你的很小，就调大，我的问题不在此
> 
> ![20200115105047898.png][]
> 
> ![20200115105517680.png][]

### 解决 ###

> 处理Copying to tmp table除了调整mysql的参数tmp\_table\_size，更重要的是优化sql，减少IO读写。其实调整tmp\_table\_size的意义也仅仅在于调整后尽量不写on-disk table，对于很高的read I/O 导致的性能问题帮助并不大。
> 
> 所以，**优化SQL，减少临时表的创建和临时表的数据大小一定要尽量减小**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200115104534518.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz,size_16,color_FFFFFF,t_70
[20200115105047898.png]: https://img-blog.csdnimg.cn/20200115105047898.png
[20200115105517680.png]: https://img-blog.csdnimg.cn/20200115105517680.png