在一个查询中更新多行性能非常慢

Question

我正在寻找使用单个查询一次更新多行的最佳方法。 目前我有：

UPDATE `profiles` SET `name` = CASE `id` WHEN 1 THEN 'John' WHEN 2 THEN 'Jane' END, `gender` = CASE `id` WHEN 1 THEN 'Male' WHEN 2 THEN 'Female' END WHERE `id`=1 OR `id`=2

但这大约需要 4 分钟才能完成（我的实际查询是在包含 2000 万行的数据库中的 10 个字段上），而不是单独的更新查询大约需要 1 秒。

我想弄清楚为什么，到底发生了什么？我认为通过在 WHERE 子句中指定 id 可以加快速度。

Answer 1

您可以发布配置文件表的 DDL 吗？这将有助于查看您设置了哪种索引（例如 - 我们可以假设 id 列是这里的主键吗？）。如果您使用 MySQL，则只需运行“SHOW CREATE TABLE profile”即可生成 DDL。

有几点可能会有所帮助：

1）尝试在 WHERE 子句中使用 BETWEEN 而不是 OR。例如
UPDATE profiles

SET `name` = 

CASE `id` WHEN 1 THEN 'John' 

WHEN 2 THEN 'Jane' END, 

`gender` = CASE `id` 

WHEN 1 THEN 'Male' WHEN 2 THEN 'Female' END 

WHERE `id` between 1 and 2;

2）尝试将查询拆分为单独的查询，以避免使用 CASE 语句，例如

update `profiles`

set `name` = 'John',

    `gender` = 'Male'

where `id` = 1;

，

update `profiles`

set `name` = 'Jane',

    `gender` = 'Female'

where `id` = 2;

我不知道这是否可行，因为我不确定您在什么上下文中使用查询！希望有帮助。

Answer 2

您能否指定所有字段的所有情况，以便我们有更好的主意。如果您修复了仅更新 id=1 和 2 的情况，则将查询拆分为 2 个查询，例如：

update `profiles` set `name` = 'John', `gender` = 'Male' where `id` = 1; 
update `profiles` set `name` = 'Jane', `gender` = 'Female' where `id` = 2; 

Answer 3

你有 id 索引吗？如果没有，最好创建一个（警告，这可能需要很长时间，请在非高峰时间执行此操作）：

CREATE INDEX id_idx ON profiles (id);

顺便说一下，对表中 2000 万行的 10 个字段进行查询可以< /em> 需要很长时间，尤其是在没有索引或缓存很冷的情况下。

更新：为了测试并且因为我很好奇，我尝试重现您的情况。为此我制作了一些测试数据。

DDL：https://gist.github.com/b76ab1c1a9d0ea071965
更新查询：https://gist.github.com/a8841731cb9aa5d8aa26
Perl 脚本用测试数据填充表： https://gist.github.com/958de0d848c01090cb9d

但是，正如我在下面的评论中已经提到的，Mysql 将阻止您插入重复数据，因为 id 是您的主键，但并不独特。如果您可以评论表架构和/或发布您的 DDL，这将会有很大帮助。

祝你好运！
亚历克斯.