sql - 更新速度很慢

下面的update语句运行了几次，速度非常慢（20秒以上）。关于如何提高它的性能有什么建议吗？

    update acc_item
set means_pay_no = m_pay_no
where acc_item_no in (select distinct(acc.acc_item_no)
                      from acc_item acc, (select accm.acc_item_no acc1,accm.acc_item_no_2 acc2
                                            from acc_item_match accm,acc_payment_item accp
                                            where accp.acc_payment_no=pay_no
                                            and (accm.acc_item_no = accp.acc_item_no or accm.acc_item_no_2 = accp.acc_item_no))
                      where acc.acc_item_no = acc1
                      or acc.acc_item_no = acc2)
and reversed_to_acc_item_no is null;

最佳答案

请改为：

update acc_item
set means_pay_no = m_pay_no
where exists (select 1
              from acc_item acc
              where exists
               (select 1
                from acc_item_match accm,acc_payment_item accp
                where accp.acc_payment_no=pay_no
                and accp.acc_item_no in (accm.acc_item_no, accm.acc_item_no_2)
                and acc.acc_item_no in (accm.acc_item_no, accm.acc_item_no_2)
                ) and acc.acc_item_no = acc_item.acc_item_no
              )
and reversed_to_acc_item_no is null;

关于sql - 更新速度很慢，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18374203/