SQL优化之DEPENDENT SUBQUERY

 有一个SQL变更如下，需要更新数据库中的数据，

UPDATE loan_product_fund_record lp
left join  loan_info li on lp.id
SET FUND_SUB_CODE = 'quanguo'
WHERE
	LOAN_ID IN (
		SELECT
			ID
		FROM
			loan_info
		WHERE
			LENDING_TAG = 'BAOYIN'
		AND ID >= 8209222
		AND ID <= 8309222
	)
AND FUND_SUB_CODE IS NULL;

查看执行计划，执行计划中出行了 DEPENDENT SUBQUERY

 改写如下 ，用left join替换

UPDATE loan_product_fund_record lp
LEFT JOIN loan_info li ON lp.LOAN_ID = li.ID
SET FUND_SUB_CODE = 'quanguo'
WHERE
	li.LENDING_TAG = 'BAOYIN'
AND li.ID >= 8209222
AND li.ID <= 8309222
AND lp.FUND_SUB_CODE IS NULL;

执行速度

参考：https://blog.csdn.net/defonds/article/details/46745143

http://itindex.net/detail/46772-%E4%BC%98%E5%8C%96-mysql-dependent

http://blog.itpub.net/20892230/viewspace-2129984/