直接运行时，MySQL查询速度很快，但是以存储过程运行时，MySQL查询速度却很慢 [英] MySQL queries are fast when run directly but really slow when run as stored proc

问题描述

我一直在尝试找出我所拥有的一组查询出了什么问题，这时我感到很困惑.

I've been trying to figure out what's wrong with a set of queries I've got and I'm just confused at this point.

应该在一个由GUI应用程序调用的存储过程中.

It's supposed to be in a stored procedure which gets called by a GUI application.

只有一个微小的"问题，首先是一个简单的UPDATE，然后是一个INSERT，该INSERT使用带有子选择的SELECT，最后是另一个UPDATE.手动一起运行这些查询，我得到的总执行时间为0.057s，还不算过分.

There's only one "tiny" problem, it's first a simple UPDATE, then an INSERT using a SELECT with a subselect and finally another UPDATE. Running these queries together by hand I get a total execution time of 0.057s, not too shabby.

现在，我尝试创建一个包含这些查询和五个输入变量的存储过程，然后运行该过程，第一次尝试花费了47.096s，随后的调用显示了相似的执行时间(35到50s).从MySQL Workbench运行单个查询仍然显示执行时间少于0.1s

Now, I try creating a stored procedure with these queries in it and five input variables, I run this procedure and on the first attempt it took 47.096s with subsequent calls to it showing similar execution times (35 to 50s). Running the individual queries from the MySQL Workbench still show execution times of less than 0.1s

这些查询真的没有什么花哨的，为什么存储过程要花很长时间才能执行，而查询本身只需要几分之一秒呢?我在这里缺少MySQL的某种特色吗?

There really isn't anything fancy about these queries, so why is the stored procedure taking an eternity to execute while the queries by themselves only take a fraction of a second? Is there some kind of MySQL peculiarity that I'm missing here?

其他测试结果:

似乎，如果我在MySQL Workbench中运行查询，但使用变量而不是仅将变量的值放入查询中，则它的运行速度与存储过程一样慢.因此，我尝试将存储过程更改为仅使用静态值而不是变量，然后突然运行起来非常快.显然由于某种原因，使用变量会使它运行极其缓慢(例如，当我直接在查询中使用变量的值时，第一个UPDATE查询从使用三个变量的大约0.98s变为0.04-0.05s，无论(如果它在存储过程中或直接运行查询).

It seems that if I run the queries in MySQL Workbench but use variables instead of just putting the values of the variables in the queries it runs just as slow as the stored procedure. So I tried changing the stored procedure to just use static values instead of variables and suddenly it ran blazingly fast. Apparently for some reason using a variable makes it run extremely slow (for example, the first UPDATE query goes from taking approximately 0.98s with three variables to 0.04-0.05s when I use the values of variables directly in the query, regardless of if it's in the stored procedure or running the query directly).

所以，问题不在于存储过程，这与我对变量的使用有关(这是不可避免的).

So, the problem isn't the stored procedure, it's something related to my use of variables (which is unavoidable).

推荐答案

我遇到了同样的问题.经过一段时间的研究，我发现问题出在MySQL比较文本时是排序规则问题.

I had the same problem. After researching for a while, I found out the problem was the collation issue while MySQL was comparing text.

TL; DR:该表是在一个排序规则中创建的，而MySQL认为"该变量是在另一个排序规则中的.因此，MySQL无法使用用于查询的索引.

TL;DR: the table was created in one collation while MySQL "thought" the variable was in another collation. Therefore, MySQL cannot use the index intended for the query.

在我的情况下，该表是使用( latin1 ， latin1_swedish_ci )排序规则创建的.为了使MySQL使用索引，我必须从以下位置更改存储过程中的where子句

In my case, the table was created with (latin1, latin1_swedish_ci) collation. To make MySQL to use the index, I had to change the where clause in the stored procedure from

    UPDATE ... WHERE mycolumn = myvariable

到

    UPDATE ... WHERE mycolumn = 
        convert(myvariable using latin1) collate latin1_swedish_ci

更改后，存储过程如下所示:

After the change, the stored procedure looked something like this:

    CREATE PROCEDURE foo.'bar'()
    BEGIN
        UPDATE mytable SET mycolumn1 = variable1
        WHERE mycolumn2 = 
            convert(variable2 using latin1) collate latin1_swedish_ci
    END;

其中( latin1 ， latin1_swedish_ci )与创建我的 tableA 的排序规则相同.

where (latin1, latin1_swedish_ci) is the same collation that my tableA was created with.

要检查MySQL是否使用索引，可以按如下所示更改存储过程以运行explain语句:

To check if MySQL uses the index or not, you can change the stored procedure to run an explain statement as followed:

    CREATE PROCEDURE foo.'bar'()
    BEGIN
        EXPLAIN SELECT * FROM table WHERE mycolumn2 = variable2
    END;

就我而言，explain结果表明在执行查询期间未使用任何索引.

In my case, the explain result showed that no index was used during the execution of the query.

请注意，当您单独运行查询时，MySQL可能会使用索引，但仍不会在存储过程内的同一查询中使用索引，这可能是因为MySQL在某种排序规则中看到了该变量.

Note that MySQL may use the index when you run the query alone, but still won't use the index for the same query inside a stored procedure, which maybe because somehow MySQL sees the variable in another collation.

有关归类问题的更多信息，可以在这里找到: http://lowleveldesign.wordpress.com /2013/07/19/diagnosing-collat​​ion-issue-mysql-stored-procedure/ 备份链接: http://www. codeproject.com/Articles/623272/在一个MySQL-stored-pro中诊断一个归类问题

More information on the collation issue can be found here: http://lowleveldesign.wordpress.com/2013/07/19/diagnosing-collation-issue-mysql-stored-procedure/ Back up link: http://www.codeproject.com/Articles/623272/Diagnosing-a-collation-issue-in-a-MySQL-stored-pro

这篇关于直接运行时，MySQL查询速度很快，但是以存储过程运行时，MySQL查询速度却很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！