update 子查询一则优化

问题描述

同事找到我，说有个SQL UPDATE 语句，在线上执行了26分钟，都没有执行完成，让我优化下。
数据库版本是5.7， 服务器配置4核32G的虚拟机

分析过程

首先就是让同事explain看下执行计划:

具体的SQL如下:

update survey_state set financ_requirements = 1 where org_credit_code in (SELECT     org_credit_code   FROM    answer_info a     LEFT JOIN answer_detail b       ON a.id = b.answer_info_id   WHERE b.question_id = 'f32825fa-bd98-11e9-b26c-fa163e4c9a89'     AND b.selection_id = '0d4ed3a9-bd9b-11e9-b26c-fa163e4c9a89');

这个UPDATE带了一个where条件， 里面是一个子查询，执行计划中也可以看到b表是全表扫描，所以首先在b表上创建一个索引

create index idx_question_selection_id on answer_detail(question_id, selection_id);

同时也在survey_state表中的org_credit_code字段上创建了个索引:

create index idx_org_credit_code on survey_state(org_credit_code);

创建索引后让同事再次执行，本以为会很快完成，但是还是需要很长时间。

再次分析执行计划

再次看执行计划，发现有DEPENDENT SUBQUERY，出现这个代表并不是先执行完子查询(过滤一部分数据)后再与外表做关联，而是外表扫描一条与里面子查询做匹配，然后在做过滤，这样效率自然会低下一些，所以将子查询改成关联查询:

UPDATE   survey_state t1,  (SELECT     org_credit_code   FROM    answer_info a     LEFT JOIN answer_detail b       ON a.id = b.answer_info_id   WHERE b.question_id = 'f32825fa-bd98-11e9-b26c-fa163e4c9a89'     AND b.selection_id = '0d4ed3a9-bd9b-11e9-b26c-fa163e4c9a89') t2 SET  t1.financ_requirements = 1 WHERE t1.org_credit_code = t2.org_credit_code ;

再次执行，几秒钟完成: