MYSQL update查询优化问题

随心流浪的夏天 发布于 06/02 19:11
阅读 104
收藏 0

云原生2.0展望丨从“小众”到“首选”,推动云原生产业落地华为云作用几何?>>>

各位大佬,小弟有一个SQL优化问题比较疑惑,我现在有3张表 a b c,然后a是主表 b,c中存了一些额外的数据(没有主外键关系),现在我想将b c表的数据根据主键ID刷到a表中,这几张表中的主键(SALARY_DETAIL_ID)都是一样的,类型是bigint(20),执行SQL如下:

UPDATE biz_salary_detail_temp a
INNER JOIN biz_salary_emp_info b ON a.SALARY_DETAIL_ID = b.SALARY_DETAIL_ID
INNER JOIN biz_salary_bank_info c ON a.SALARY_DETAIL_ID = c.SALARY_DETAIL_ID 
SET a.ACCOUNT_NAME = c.ACCOUNT_NAME,
a.ACCOUNT_NUM = c.ACCOUNT_NUM,
a.BANK_ACCOUNT_AREA = c.BANK_ACCOUNT_AREA,
a.BANK_FULL_NAME = c.BANK_FULL_NAME,
a.BANK_TYPE = c.BANK_TYPE,
a.IBIC_ARE_NUM = c.IBIC_ARE_NUM,
a.IS_SAME_ACCOUNTNAME_EMPNAME = c.IS_SAME_ACCOUNTNAME_EMPNAME,
a.UPDATE_TIME = c.UPDATE_TIME,
a.DISABLED_NUM = b.DISABLED_NUM,
a.EMAIL = b.EMAIL,
a.EMP_PROPETY = b.EMP_PROPETY,
a.ENTRY_DATE = b.ENTRY_DATE,
a.IDENTITY_TYPE = b.IDENTITY_TYPE,
a.IS_DISABLED = b.IS_DISABLED,
a.IS_LONELY = b.IS_LONELY,
a.IS_MARTYR = b.IS_MARTYR,
a.IS_SEND_EMAIL = b.IS_SEND_EMAIL,
a.LEAVE_DATE = b.LEAVE_DATE,
a.MARTYR_NUM = b.MARTYR_NUM,
a.NATIONAL = b.NATIONAL,
a.UPDATE_TIME = b.UPDATE_TIME 
WHERE
	1 = 1 
	AND a.BATCH_ID = 1267766364506976257 

利用explain之后

现在的问题是为什么a和c是eq_ref而a和b是ALL呢,这都是SALARY_DETAIL_ID作为连接条件的

加载中
返回顶部
顶部