内容/问题描述：
小弟遇到了一个瓶颈
我有个 table 要去里面取这个 User 最后回答的那个版本号码并且记录到另一个 table
如下：
UPDATE tm_team_assign_curriculum_profile t
LEFT JOIN (
SELECT pp.uuid as profile_id , pp.badge_no , max(csqa1.version) as version , ce.uuid as cm_enrollment_id , ttacp.team_assign_curriculum_uuid
FROM tm_team_assign_curriculum_profile ttacp
INNER JOIN pm_profile pp ON ttacp.profile_id = pp.uuid
INNER JOIN cm_enrollment ce ON pp.badge_no = ce.badge_no
INNER JOIN cm_profile_survey_ques_answer csqa1 ON csqa1.cm_enrollment_id = ce.uuid
WHERE ttacp.status = '1'
AND ce.course_id = ( select type_id from ap_risksurvey_conf where code = 'ASSIGN_CHECKLISTCODE_RISKSURVEY' )
GROUP BY pp.uuid
)userVersion ON userVersion.profile_id = t.profile_id
SET t.enrollment_id = userVersion.cm_enrollment_id , t.version = userVersion.version , t.modified_date = NOW()
WHERE t.status = '1'
AND userVersion.profile_id is NOT NULL ;
schema:
schema:
CREATE TABLE tm_team_assign_curriculum_profile (uuid , team_assign_curriculum_uuid , profile_id , enrollment_id , version , status )
CREATE TABLE pm_profile (uuid , badge_no );
CREATE TABLE cm_enrollment (uuid , course_id , badge_no )
CREATE TABLE cm_profile_survey_ques_answer (uuid , cm_enrollment_id , version )
tm_team_assign_curriculum_profile :
是这次要做的 user ( 最大会到5w 笔 )
cm_profile_survey_ques_answer :
是每个 User 回答的纪录 , 人数有 将近 20w ,
题目有三十几题,
每次回答后 version会 +1 ,
再加上不只回答一次 , 所以数量很大
一两万笔都是没什么问题的 , 但是只要有五万笔就挂在这里了
一直想不到有什么方法可以再提升一下效率 , 不知道各位先进是否有什么想法？