MySQL查询调整-为什么使用变量中的值比使用文字慢得多? [英] MySQL Query Tuning - Why is using a value from a variable so much slower than using a literal?

问题描述

更新:我已经在下面亲自回答了这个问题.

我正在尝试解决MySQL查询中的性能问题.我想我看到的是，将函数的结果分配给一个变量，然后对该变量运行带有比较的SELECT相对较慢.

I'm trying to fix a performance issue in a MySQL query. What I think I'm seeing, is that assigning the result of a function to a variable, and then running a SELECT with a compare against that variable is relatively slow.

但是，如果出于测试目的，我将对变量的比较替换为对函数值将返回的字符串文字的比较(对于给定的情况)，则查询运行得更快.

If for testings sake however, I replace the compare to the variable with a compare to the string literal equivalent of what I know that function will return (for a given scenario), then the query runs much faster.

例如:

...

SET @metaphone_val := double_metaphone(p_parameter)); -- double metaphone is user defined

SELECT 

        SQL_CALC_FOUND_ROWS
        t.col1,
        t.col2, 
        ...

    FROM table t

            WHERE

            t.pre_set_metaphone_string = @metaphone_val -- OPTION A

            t.pre_set_metaphone_string = 'PRN' -- OPTION B (Literal function return value for a given name)

如果我使用选项A中的行，则查询速度很慢.

If I use the line in option A, the query is slow.

如果我使用选项B中的行，那么查询会很快，就像您期望的任何简单字符串比较一样.

If I use the line in option B, then the query is fast as you would expect any simple string compare to be.

为什么?

推荐答案

答案被我解答后，问题就写完了，所以无论如何都要发表知识共享！

Was finished writing the question when the answer hit me, so posting anyway for knowledge sharing!

我意识到metaphone函数的返回值为UTF8.

I realised that the return value of the metaphone function was UTF8.

与latin1字段进行比较显然会导致相当大的性能开销.

The compare to a latin1 field was obviously incurring a fairly heavy performance overhead.

我将变量赋值替换为:

SET @metaphone_val:= CONVERT(double_metaphone(p_parameter) USING latin1);

现在查询运行的速度与我预期的一样.

Now the query runs as fast as I would expect.

这篇关于MySQL查询调整-为什么使用变量中的值比使用文字慢得多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！