MySql 更新优化？

Question

假设我有一个包含两列A和B的表，A列上有索引而B列上没有。
我想发出数百万个类似以下查询的请求：

UPDATE t1 SET b=b1 WHERE a=a1;
UPDATE t1 SET b=b2 WHERE a=a2;
....

每个唯一值的a对应着1到100,000行数据。平均而言，每个唯一值有大约100行数据。

对于每个更新语句，平均有60%的行不会被更改，因为这些行中的b已经具有所需的值。在30%的更新中，没有匹配的行将被更改。

使用这样的语句有意义吗？

UPDATE t1 SET b=b1 WHERE a=a1 AND b<>b1;

通过消除不必要的写回磁盘，它是否能加快进程速度？或者Mysql 5是否足够聪明，能够识别到没有任何更改，因此无需写回磁盘？

- Ghostrider

Answer 1

无论哪种情况，MySQL 都需要读取行内容（无论是在磁盘上还是在缓存/缓冲池中）。无论哪种情况，MySQL 都会使用您在 a 上的索引作为起点。如果 MySQL 已经具有目标值的 b，则无论哪种情况下，MySQL 都不会更新该行。因此，我认为 MySQL 没有任何受益于 b<>b1 子句的方法。

可以说，根据工作负载和数据集，带有 b<>b1 的查询可能会受益于将您在 a 上的索引更改为按顺序排列的复合索引 a 和 b。在这种情况下，它不必访问磁盘（或检查缓存/缓冲池）以查找哪些行需要更新（即您提到的 30% 和 60%），而是直接定位到需要更新的行。但是，现在您的索引每次更新 b 时都需要进行更新，因此存在成本，尽管我认为这种权衡可能是值得的。

- Riedsio

Answer 2

我一直在考虑使用CASE将多个更新合并为单个更新

update t1
set b=
  case a
    when a=a1 then b1
    when a=a2 then b2
    when a=a3 then b3 ...
  end;

希望这个有用，如果速度特别慢，请注明原因。

- ajreal

Answer 3

你应该添加额外的过滤器。虽然Mysql足够聪明，如果值相同就不会更新，但最好消除这个检查。你可以通过查看查询影响了多少行来确认这一点。

- Brent Baisley