mysql优化 update中的in子句

需求：mysql数据库中，更新所有message_repo表中所有state='100' and receiver_id = '1'的数据为state='100'，表中id为主键，state和receiver_id都有索引。

原语句：

update message_repo set state = '101' , update_time = now() , read_time = now()
where id in (select a.id from (select id from message_repo where state = '100' and receiver_id = '1') a);

然后发现，workbench提示处于安全模式，即safe mode下只能采用主键更新。

我的id不就是主键？难道这个还不是主键更新，意识到这个sql肯定被mysql错误理解了，用执行分析果然发现问题。。。

 

 update的where子句没有使用主键索引，而是进行了全表扫描。

原本sql要表达的意思是：用select子句查询所有符合条件的主键，然后update通过主键进行更新，但是实际上，mysql是全表逐行与select子句对比，没有比这个更傻的操作了，要是表中数据多一点，死锁是必定的。

更别说：这语句需要关闭safe mode。

 

优化后语句：

update message_repo a join (select id from message_repo where state = '100' and receiver_id = '1') b on a.id=b.id
set state = '101' , update_time = now() , read_time = now();

通过join子句，明确告诉mysql，我要先用主键进行筛选。

执行计划

 

 这下就正常，从执行计划上看，所有条件都符合预期。