联表update:
update student s, city c
set s.city_name = c.name
where s.city_code = c.code
联表delete及其性能问题:
explain delete from policy_holder where scenario_holder_id in (
select id from scenario_holder SH where SH.store_id in (61032) and package_id=0
)
:
delete from … where in 为什么性能这么差,我有点不明白。type为ALL,扫描行数基本是policy_holder全表了。为什么外层delete不会走索引,即使在scenario_holder_id上有索引?而把内层的子查询语句换成具体的数字时,就会转而走索引?
改造下SQL:
explain delete policy_holder from policy_holder join scenario_holder on policy_holder.scenario_holder_id = scenario_holder.id where scenario_holder.store_id in (61032) and scenario_holder.package_id=0
:
就用了policy_holder表中包含scenario_holder_id的索引idx_policy_holder_union_id :
,性能可以大为改善。
更深的原因暂时不明