mysql upcase,MySQL中的CASE性能?

本文探讨了在MySQL中使用CASE...WHEN...THEN表达式可能对性能产生的影响,并提出了通过程序逻辑选择不同查询来避免这一问题的方法。同时,文章还讨论了如何通过引入数据冗余来提高大型表查询的效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值