所有类型http://www.cnblogs.com/danhuangpai/p/8475458.html
特别关注 DEPENDENT SUBQUERY ,会严重消耗性能
这个对应的是查询中嵌套子查询,那么如何优化呢
举个例子:
EXPLAIN UPDATE `tb_gqyjy_appointment_record_info` SET update_time ="2018-11-08 14:57:20" WHERE user_id IN
(SELECT USER_ID FROM `tb_qy_user_info` WHERE PERSON_NAME LIKE '林%') AND user_id IN ( SELECT user_id FROM
`tb_gqyjy_user_info` WHERE point_count <0 )
这里面嵌套了子查询,结果:
下面优化:
EXPLAIN UPDATE `tb_gqyjy_appointment_record_info` t JOIN ( SELECT USER_ID FROM `tb_qy_user_info` m WHERE PERSON_NAME LIKE '林%' ) r
ON t.user_id=r.user_id
JOIN ( SELECT user_id FROM
`tb_gqyjy_user_info` k WHERE k.point_count <0 ) z ON z.user_id=t.user_id SET t.update_time ="2018-11-08 14:57:10"
执行结果:
可以明显看到select_type已经改变了。DERIVED:被驱动的SELECT子查询(子查询位于FROM子句)
数据量越大,效果越明显