SQL优化实战：利用临时表提升update执行性能

上线后需要补数据。我在jira提了一个sql审批工单。领导批复后，找运维伙伴执行。

如下是sql。运维小伙执行的时候，发现持续5分钟都没完事。考虑到update执行时间过长可能会影响到生产系统对这两张表的操作，于是，赶紧stop。

UPDATE levy_account_recharge a
JOIN trans_separate_order b ON a.order_no=b.order_no AND a.status = 'SUCCESS' AND b.status='SUCCESS'
SET a.SUCCESS_time=b.split_finish_time
WHERE a.SUCCESS_time IS NULL

 

经查，实际影响数据也就50多条。那么，这个update语句为什么这么慢呢？

 

查看两张表的结构。其中 levy_account_recharge 的 order_no字段是主键，类型是varchar(32)。trans_separate_order 表的主键不是order_no， order_no字段是bigint类型，没有索引。
看来，慢的原因有2：一是order_no字段数据类型不一致，二是trans_separate_order的order_no没有索引。

 

办法总比困难多。
改用临时表试试。再执行，发现性能提升很快，毫秒级。

-- 耗时：0.234 sec
CREATE TEMPORARY TABLE tmp2
SELECT a.order_no, b.split_finish_time FROM levy_account_recharge a JOIN trans_separate_order b ON a.order_no=b.order_no
AND a.status = 'SUCCESS' AND b.status='SUCCESS'
WHERE a.SUCCESS_time IS NULL

START TRANSACTION;
-- 耗时：0.131 sec
UPDATE levy_account_recharge a JOIN tmp2 b ON a.order_no=b.order_no
SET a.SUCCESS_time=b.split_finish_time
;
ROLLBACK;

 

《码出高效 Java开发手册》这本书里，在章节1.5.5 “计算机基础->TCP/IP->连接池” 介绍数据库SQL性能提升方案里，也提到了“使用临时表”这一点。

5）使用临时表。某种情况下，该方法是一种比较好的选择。曾经遇到一个场景不使用临时表需要执行1个多小时，使用临时表可以降低至2分钟以内。因为在不断的嵌套查询中，已经无法很好地利用现有的索引提升查询效率，所以把中间结果保存到临时表，然后重建索引，再通过临时表进行后续的数据操作。