假设a表index字段设置索引,修改删除都需要关联表b的index字段,得出下列三个sql
// 修改
update a set status=1 where index in (select index from b where b.id = 1)
// 删除
delete from a where index in (select index from b where b.id = 1)
当表数据过大时,发现并竟然全表扫描, 使用select同样复制走了索引,经过查阅资料,此处应当使用join方法连接:
// 修改
update a join b on a.index=b.index
set a.status=1
where b.id=1
// 删除
delete a from a
join b on a.index = b.index
where b.id = 1
修改后查看索引使用,完美解决