在一个查询中更新多行非常慢的性能
我正在寻找最好的方法来使用单个查询一次更新多行。 目前我有:在一个查询中更新多行非常慢的性能
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分钟才能完成(我真正的查询是在10场中的2000万行的数据库),而不是称取约1秒单独的更新查询。
我想解决为什么,究竟发生了什么?我认为通过在WHERE子句中指定id可以加快速度。
你有id的索引吗?如果不是,这是一个好主意,以创建一个(警告,这可能需要很长的时间,为此在非高峰时段):
CREATE INDEX id_idx ON profiles (id);
顺便说一句,20万行对10个字段查询一个表可以花费很长时间,特别是如果没有索引或缓存很冷。
更新:对于测试,因为我很好奇,我试图重现您的情况。为此,我编写了一些测试数据。
DDL:https://gist.github.com/b76ab1c1a9d0ea071965
更新查询:https://gist.github.com/a8841731cb9aa5d8aa26
Perl脚本来填充表与测试数据:https://gist.github.com/958de0d848c01090cb9d
然而,正如我在我下面的评论已经提到的,MySQL会阻止您插入重复数据因为id是你的PRIMARY KEY,但不是唯一的。如果您可以对表格模式进行评论和/或发布您的DDL,这将有很大帮助。
祝你好运! Alex。
是id是主键 – Craig
我是否正确地知道id的值是1或2,并且可能会多次出现?这不是创建密钥的有效方法。你有没有在桌子上有一些你可以做出主键的唯一ID? –
虽然黑客一些测试脚本,我注意到,如果id不唯一,Mysql(正确)阻止我插入数据。你能不能请我们分享一下DDL表格,'DESCRIBE profiles'是什么? –
您可以发布配置文件表的DDL吗?这将有助于查看您设置了哪种类型的索引(例如,我们可以假设id列是这里的主键?)。如果您使用的是MySQL,那么只需运行'SHOW CREATE TABLE profiles'即可生成DDL。
几个百分点,可能助阵:
1)您的WHERE子句,而不是一个或使用BETWEEN尝试。例如 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;
我不知道,因为我不知道你用的是什么方面的查询,这是可行的!希望有所帮助。
能否请您详细说明您的所有情况,所以我们有更好的主意。如果你有修复案例更新只为id = 1和2然后拆分您的查询在2个查询,如:
update `profiles` set `name` = 'John', `gender` = 'Male' where `id` = 1;
update `profiles` set `name` = 'Jane', `gender` = 'Female' where `id` = 2;
我知道我可以单独运行这些,我之后是一种在一个查询中运行它们的方法。 – Craig
只是一个快速拍摄。我会为此准备2个不同的查询。即使您指定了'WHERE'id'= 1或'id'= 2',使用'CASE'方法也可以强制mysql收集所有的ID。如果您为每个案例准备查询并直接使用唯一的'id',则您将有最快的方式来执行此操作。通过使用2个语句,您还有更多清晰的查询,因为这种“CASE”方法会使查询变得奇怪......; ) – Marco