sql 中 in , inner join , exist 查询

in

select t.TASK_ID_ from act_hi_identitylink t 
where t.USER_ID_ = 'asd'
 and t.TYPE_ = 'CC'
and t.TASK_ID_ in  ( SELECT ID_ from act_hi_taskinst WHERE TENANT_ID_ = 'devops' )

使用 in 子查询结果数量大时查询速度会变慢

参考资料1：使用连接（JOIN）来代替子查询(Sub-Queries) mysql优化系列记录
http://blog.csdn.net/hongsejiaozhu/article/details/1876181
参考资料2：网站开发日记(14)-MYSQL子查询和嵌套查询优化
http://dodomail.iteye.com/blog/250199

解决1: 使用 inner join

select t.TASK_ID_ from act_hi_identitylink t 
 inner JOIN act_hi_taskinst ht on t.TASK_ID_ = ht.ID_
where t.USER_ID_ = 'asd'
 and t.TYPE_ = 'CC'
and ht.TENANT_ID_ = 'devops'

解决2: 使用 EXISTS

select t.TASK_ID_ from act_hi_identitylink t 
where t.USER_ID_ = 'asd'
 and t.TYPE_ = 'CC'
and EXISTS(SELECT ID_ from act_hi_taskinst WHERE TASK_ID_ = ID_ AND TENANT_ID_ = 'devops')