用case语句进行查询优化 [英] query optimization with case statement

问题描述

我有一列带有代码的列.现在，每个代码都已更改为其他代码.我正在尝试更新它.因此，我在mysql中使用了 case 语句.但是问题是，我大约有250,000行和80,000个唯一代码需要替换.而case语句大约要花10分钟才能执行.任何更好的方法都可以做到这一点.

I have a column with codes. Now each code has been changed to something else. I am trying to update it. So I have used case statement in mysql. But the problem is, I have around 250,000 rows and 80,000 unique codes which need to be replaced. And the case statement is taking like 10 min to execute. Any better approach to do this.

我的查询如下:

UPDATE test_table
SET code = CASE
WHEN code = "akdsfj" THEN "kadjsf"
WHEN code = "asdf" THEN "ndgs"
WHEN code = "hfgsd" THEN "gfdsd"
... (I am doing in batches of 1000 case statements at a time)
ELSE code

推荐答案

case 语句确实会增加时间，因为它已被搜索.

The case statement does add time, because it is searched.

解决方案?将这些对存储在临时表中...与索引.所以:

The solution? Store the pairs in a temporary table . . . with an index. So:

create temporary table code_pairs (
    old_code varchar(255) not null primary key,
    new_code varchar(255)
);

insert into code_pairs(old_code, new_code)
    values ('akdsfj', 'kadjsf'),
           ('asdf', 'ndgs'),
           . . . ;

然后将 update 与 join 一起使用:

update test_table tt join
       code_paris cp
       on tt.code = cp.old_code
    set tt.code = cp.new_code;

这可以节省您的时间，因为使用索引找到了匹配的代码，而不是通过 case 语句一一搜索.此外，不会尝试对不匹配的行进行更新.170,000行不匹配可能是查询中最慢的部分，因为它们需要遍历 case 值的整个列表.

This saves you time because the matching code is found using the index, rather then searching one-by-one through a case statement. In addition, no update is attempted on rows that have no match. The 170,000 rows with no match are probably the slowest part of the query, because they need to go through the entire list of case values.

这篇关于用case语句进行查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！