如何使用MySQL和PHP优化多行的更新查询

Question

我有一个表，其中包含大约80.000条记录。它有4列：

|   id   |  code  |  size |  qty |  
+--------+--------+-------+------+
|    1   |  4735  |   M   |   5  |
|    2   |  8452  |   L   |   2  |
    ...
| 81456  |  9145  |   XS  |   13 |

code列是唯一的。

我必须一天更新两次qty。

为此，我使用以下查询：

UPDATE stock SET qty = CASE id 
                         WHEN 1 THEN 10
                         WHEN 2 THEN 8
                         ...
                         WHEN 2500 THEN 20
                       END
              WHERE id IN (1,2,...,2500);

我使用PHP拆分查询，一次更新2500股票。

以下是每个2500股票更新所需的时间(秒)：

[0]7.11
[1]11.30
[2]19.86
[3]27.01
[4]36.25
[5]44.21
[6]51.44
[7]61.03
[8]71.53
[9]81.14
[10]89.12
[11]99.99
[12]111.46
[13]121.86
[14]131.19
[15]136.94
[END]137

正如你所看到的，更新2500个产品需要5-9秒，我认为这是很安静的。

我能做些什么来提高速度呢？

谢谢!

Answer 1

因为时间似乎越来越长，所以我希望您需要id字段上的索引，因为它看起来像是在进行全表扫描。您可以创建如下所示的索引

CREATE INDEX my_first_index ON table(id);

(我不得不添加这个作为答案，因为我不能发表评论，我知道它更多的是一个评论！！)

**编辑**

我重新读了一遍，发现你的问题更大。我仍然认为在id上建立索引可能会解决这个问题，但更好的解决方案是为id到数量的映射创建一个新的表，我们称之为qty_mapping

|   id   |  qty |  
+--------+------+
|    1   |   10 |
|    2   |   8  |
    ...
| 2500   |   20 |

确保索引id，然后您可以将更新更改为

update stock set qty = (select qm.qty from qty_mapping qm where qm.id = stock.id)

它应该能够在几乎没有时间的情况下更新全部80,000条记录。