UPDATE user_bank_info set fisused = 2 where fuserid in ( SELECT userid FROM ( select DISTINCT ubi.fuserid as userid from customer as c JOIN user_bank_info as ubi ON c.fuserid =ubi.fuserid where ubi.faccountname <> c.fusername) as c)
数据库存的是百万级别的，问一下怎么进行优化？
解决方案
50
优化子查询
select ubi.userid as userid from customer as c JOIN user_bank_info as ubi ON c.fuserid =ubi.fuserid
where ubi.userid in(select 1 from user_bank_info as ubi where ubi.userid=c.fuserid )and ubi.faccountname<>c.fusername
select ubi.userid as userid from customer as c JOIN user_bank_info as ubi ON c.fuserid =ubi.fuserid
where ubi.userid in(select 1 from user_bank_info as ubi where ubi.userid=c.fuserid )and ubi.faccountname<>c.fusername
25
以文本方式贴出 explain select DISTINCT ubi.fuserid as userid
from customer as c JOIN user_bank_info as ubi ON c.fuserid =ubi.fuserid
where ubi.faccountname <> c.fusername
另外及全部表的 shoe index from
from customer as c JOIN user_bank_info as ubi ON c.fuserid =ubi.fuserid
where ubi.faccountname <> c.fusername
另外及全部表的 shoe index from
25
尽量把语句改成join的把，这样方便优化器生成更好的执行计划
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明问一下带in 和 子查询的sql语句怎么进行优化？！
