MySQL中的CASE性能? [英] CASE performance in MySQL?

问题描述

我想知道是否在MySQL查询中使用CASE ... WHEN ... THEN表达式
对性能有负面影响吗?

而不是使用CASE表达式(例如在UPDATE查询中)
您始终可以在程序中进行if else语句
用php，python，perl，java等语言编写，以选择要发送的查询，例如(使用伪代码):

prepareStatement(
"UPDATE t1 SET c1=c1+1, msg=CASE (@v:=?) WHEN '' THEN msg ELSE @v END"
);
setStatementParameter(1, message);

或近战:

if (message == "") {
    prepareStatement("UPDATE t1 SET c1=c1+1");
} else {
    prepareStatement("UPDATE t1 SET c1=c1+1, msg=?");
    setStatementParameter(1, message);
}

(这里的c1只需要显示在两种情况下都发生了事情)

哪种方法具有更好的性能?
而性能损失是多少?

解决方案

每行相当多的 all 函数都会对性能产生影响，唯一真正的问题是:影响是否足够小不用担心吗?".

这应该通过测量而不是猜测来发现.如果您的数据和查询均未更改，则数据库管理只是一劳永逸"的活动.否则，您应该定期监视性能以确保没有问题.

通过上述注释中的足够小"，我的意思是，您可能不必担心类似以下内容对性能的影响:

select * from friends where lowercase(lastname) = "smith"

如果您只有三个朋友.

随着表的增加，这些事情的影响变得更加严重.例如，如果您有1亿客户，并且想要查找所有可能与计算机相关的客户，那么您就不想尝试:

select name from customers where lowercase(name) like '%comp%'

这很可能会使您的DBA像一堆砖头一样掉下来.

过去我们解决此问题的一种方法是在数据中引入冗余.使用第一个示例，我们将添加一个名为lowerlastname的额外列，并使用小写值lastname填充它.然后，出于搜索目的将其编入索引，并且您的select语句应变得应有尽有.

这对我们倍受喜爱的3NF有什么影响，我听说您问过吗?如果您知道自己在做什么，答案是不多":-)

您可以设置数据库，以便此新列由插入/更新触发器填充，以保持数据一致性.出于性能原因，打破3NF是完全可以接受的，前提是您了解并减轻了后果.

类似地，第二个查询可以具有插入/更新触发器，只要更新或插入包含相关文本的条目，该触发器便会填充新的索引列name_contains_comp.

由于大多数数据库的读取远比写入的频繁，因此这将计算成本转移到了插入/更新上，从而有效地在所有选择操作中摊销了计算成本.该查询将是:

select name from customers where name_contains_comp = 'Y'

同样，您会发现查询令人眼花fast乱，但付出的代价是插入和更新的速度稍慢.

I wonder if using CASE ... WHEN ... THEN expression in MySQL queries
has negative effect on performance?

Instead of using CASE expression (for example inside your UPDATE query)
you always have possibility to make if else statement in your program
written in php, python, perl, java, ... to choose wich query to send, for example (in pseudocode):

prepareStatement(
"UPDATE t1 SET c1=c1+1, msg=CASE (@v:=?) WHEN '' THEN msg ELSE @v END"
);
setStatementParameter(1, message);

or insead:

if (message == "") {
    prepareStatement("UPDATE t1 SET c1=c1+1");
} else {
    prepareStatement("UPDATE t1 SET c1=c1+1, msg=?");
    setStatementParameter(1, message);
}

(c1 here needed just to show that something happens in both cases)

What way of doing it has better performance?
And how much the performance penalty is?

解决方案

Pretty much all per-row functions will have an impact on performance, the only real question is: "Is the impact small enough to not worry about?".

This is something you should discover by measuring rather than guessing. Database administration is only a set-and-forget activity if neither your data nor your queries ever change. Otherwise, you should be periodically monitoring performance to ensure no problems occur.

By "small enough" in the above comments, I mean, you probably needn't worry about the performance impact of something like:

select * from friends where lowercase(lastname) = "smith"

if you only have three friends.

The impact of these things becomes more serious as the table increases in size. For example, if you have one hundred million customers and you want to find all the ones likely to be computer-related, you wouldn't want to try:

select name from customers where lowercase(name) like '%comp%'

That's likely to bring your DBAs down on you like a ton of bricks.

One way we've fixed this in the past is to introduce redundancy into the data. Using that first example, we would add an extra column called lowerlastname and populate it with the lowercase value of lastname. Then index that for search purposes and your select statements become blindingly fast, as they should be.

And what does that do to our much loved 3NF, I hear you ask? The answer is "not much", if you know what you're doing :-)

You can set up the database so that this new column is populated by an insert/update trigger, to maintain data consistency. It's perfectly acceptable to break 3NF for performance reasons, provided you understand and mitigate the consequences.

Similarly, that second query could have an insert/update trigger that populated a new indexed column name_contains_comp whenever an entry was updated or inserted that contained the relevant text.

Since most databases are read far more often than they're written, this moves the cost of the calculation to the insert/update, effective amortising it across all select operations. The query would then be:

select name from customers where name_contains_comp = 'Y'

Again, you'll find the query blindingly fast at the minor cost of slightly slower inserts and updates.

这篇关于MySQL中的CASE性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！