mysql 存储过程比标准查询慢 20 倍-数据库问题

mysql stored procedure is slower 20 times than standard query(mysql 存储过程比标准查询慢 20 倍)

本文介绍了mysql 存储过程比标准查询慢 20 倍的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有 10 个表，除表名外结构相同.

i have 10 tables with same structure except table name.

我有一个 sp(存储过程)定义如下:

i have a sp (stored procedure) defined as following:

 select * from table1 where (@param1 IS NULL OR col1=@param1)
 UNION ALL
 select * from table2 where (@param1 IS NULL OR col1=@param1)
 UNION ALL
 ...
 ...
 UNION ALL
 select * from table10 where (@param1 IS NULL OR col1=@param1)

我使用以下行调用 sp:

I am calling the sp with the following line:

call mySP('test')  //it executes in 6,836s

然后我打开了一个新的标准查询窗口.我刚刚复制了上面的查询.然后用'test'替换@param1.

Then I opened a new standard query window. I just copied the query above. Then replaced @param1 with 'test'.

这在 0,321 秒内执行，比存储过程快约 20 倍.

This executed in 0,321s and is about 20 times faster than the stored procedure.

为了防止结果被缓存，我反复更改了参数值.但这并没有改变结果.SP 比等效的标准查询慢约 20 倍.

I changed the parameter value repeatedly for preventing the result to be cached. But this did not change the result. The SP is about 20 times slower than the equivalent standard query.

请你帮我弄清楚为什么会这样?

Please can you help me to figure out why this is happening ?

有人遇到过类似的问题吗?

Did anybody encounter similar issues?

我在 windows server 2008 R2 64 位上使用 mySQL 5.0.51.

I am using mySQL 5.0.51 on windows server 2008 R2 64 bit.

我使用 Navicat 进行测试.

edit: I am using Navicat for test.

任何想法都会对我有所帮助.

Any idea will be helpful for me.

编辑 1:

我刚刚根据 Barmar 的回答做了一些测试.

I just have done some test according to Barmar's answer.

最后，我将 sp 如下更改为一行:

At finally i have changed the sp like below with one just one row:

 SELECT * FROM table1 WHERE col1=@param1 AND col2=@param2

然后首先我执行了标准查询

Then firstly i executed the standart query

 SELECT * FROM table1 WHERE col1='test' AND col2='test'  //Executed in 0.020s

在我打电话给我的 sp 之后:

After i called the my sp:

 CALL MySp('test','test')    //Executed in 0.466s

所以我完全改变了 where 子句，但没有任何改变.我从 mysql 命令窗口而不是 navicat 调用了 sp.它给出了相同的结果.我还在坚持.

So i have changed where clause entirely but nothing changed. And i called the sp from mysql command window instead of navicat. It gave same result. I am still stuck on it.

我的 sp ddl:

 CREATE DEFINER = `myDbName`@`%`
 PROCEDURE `MySP` (param1 VARCHAR(100), param2 VARCHAR(100))
 BEGIN
    SELECT * FROM table1 WHERE col1=param1 AND col2=param2
 END

并且 col1 和 col2 被合并索引.

And col1 and col2 is combined indexed.

你可以说那为什么不使用标准查询呢?我的软件设计不适合这个.我必须使用存储过程.所以这个问题对我来说非常重要.

You could say that why dont you use standart query then? My software design is not proper for this. I must use stored procedure. So this problem is highly important to me.

编辑 2:

我已获得查询个人资料信息.很大的不同是因为 SP Profile Information 中的发送数据行".发送数据部分需要 %99 的查询执行时间.我正在本地数据库服务器上进行测试.我不是从远程计算机连接.

I have gotten query profile informations. Big difference is because of "sending data row" in SP Profile Information. Sending data part takes %99 of query execution time. I am doing test on local database server. I am not connecting from remote computer.

SP 个人资料信息

查询个人资料信息

我在我的 sp 中尝试过如下所示的 force index 语句.但同样的结果.

I have tried force index statement like below in my sp. But same result.

 SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=@param1 AND col2=@param2

我已经改变了 sp 如下.

I have changed sp like below.

 EXPLAIN SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=param1 AND col2=param2

结果如下:

 id:1
 select_type=SIMPLE
 table:table1
 type=ref
 possible_keys:NULL
 key:NULL
 key_len:NULL
 ref:NULL
 rows:292004
 Extra:Using where

然后我执行了下面的查询.

Then i have executed the query below.

 EXPLAIN SELECT * FROM table1 WHERE col1='test' AND col2='test'

结果是:

 id:1
 select_type=SIMPLE
 table:table1
 type=ref
 possible_keys:col1_co2_combined_index
 key:col1_co2_combined_index
 key_len:76
 ref:const,const
 rows:292004
 Extra:Using where

我在 SP 中使用 FORCE INDEX 语句.但它坚持不使用索引.任何的想法?我想我快结束了:)</p>

I am using FORCE INDEX statement in SP. But it insists on not using index. Any idea? I think i am close to end :)