Mysql性能更新()

Question

在id IN()的更新中，我遇到了性能问题。伪码是(简化)：

1. 从PHP启动事务： $pdoAdapter->入门beginTransaction()；
2. 查询MySQL中的价格元素，这些元素来自外部世界，存储在db中，并且应该开具发票。 选择id，incoming_price_elements中的价格进行更新
3. 取一个新的发票id 从invoice_id_counter中选择id进行更新；更新invoice_id_counter SET id=id+1；
4. 用PHP创建一个包含大量求和和其他聚合的发票，并存储结果 插入发票(id，total_price)值(，)；
5. 更新所有的incoming_price_elements以标记他们的发票 从id IN的incoming_price_elements SET invoice_id=中选择的所有id)中更新；
6. 提交

我的问题是，步骤5非常慢(在秒范围内)，它在步骤3中阻塞id计数器。要传输的id数超过10.000 id，并且id是主键。

关于如何优化这个问题，有什么建议吗？我正在考虑创建一个临时表并选择其中的所有in，但我对临时表没有任何经验。

Answer 1

而不是IN子句，您可以使用内部联接

  UPDATE incoming_price_elements a
  INNER JOIN (
      SELECT id
      FROM incoming_price_elements 
      WHERE  <advanced where> 

  ) t ON t.id = a.id 
  SET invoice_id= <id from 3.>

这将提高“事务”的这一部分的性能。

Answer 2

根据您的评论之一，数据正在“持续”添加到incoming_price_elements中吗？那张桌子算是个“集结地”？一个问题是，如果在完成处理之前添加了新的项目，那么就会有麻烦吗？

如果是的话，那就让我们翻翻吧。

CREATE TABLE next_icp LIKE incoming_price_elements;
RENAME TABLE incoming_price_elements TO prev_icp,
             next_icp TO incoming_price_elements;   -- swap in an empty table
-- now process `prev_icp` as discussed already.

至于延迟，让我们在启动事务之前尝试做更多的处理。

您可以在incoming_price_elements中增加一些列来处理“大量的求和和其他聚合”吗？在启动事务之后，执行其他操作，最后(如scaisEdge所建议的)执行“多表更新”(使用JOIN)而不是IN。

如果这仍然太具有侵入性，那么一次执行is 100，而不是一次执行全部。UPDATE必须保存旧行以防止崩溃(等等)；这将付出很大的代价。通过拆散它；你让其他的连接完成一些工作。

这是在步骤1..6周围放置一个循环，每次只关注100行，直到您用完了暂存表。这将让其他is在中间被抓取；我希望这是可以的。