mysql存储过程比标准查询慢20倍 [英] mysql stored procedure is slower 20 times than standard query
问题描述
除表名外，我有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
然后，我打开了一个新的标准查询窗口.我只是复制了上面的查询.然后将@ param1替换为'test'.
Then I opened a new standard query window. I just copied the query above. Then replaced @param1 with 'test'.
此过程的执行时间为0.321s，比存储过程快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.
我只是根据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:
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.
我已经获得了查询配置文件信息.最大的不同是因为SP配置文件信息中的发送数据行".发送数据部分需要％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 Profile Informations
查询配置文件信息
Query Profile Informations
我已经在我的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语句.但是它坚持不使用索引.任何的想法?我想我快结束了:)
I am using FORCE INDEX statement in SP. But it insists on not using index. Any idea? I think i am close to end :)
推荐答案
可能的字符集问题?如果您的表字符集与数据库字符集不同，则可能会引起问题.
Possible character set issue? If your table character set is different from your database character set, this may be causing a problem.
查看此错误报告: http://bugs.mysql.com/bug.php ?id = 26224
[2007年11月12日21:32]马克·库巴基(Mark Kubacki)的5.1.22_rc-密钥仍然不走运 感到厌烦，查询过程在36秒以内 0.12秒.
[12 Nov 2007 21:32] Mark Kubacki Still no luck with 5.1.22_rc - keys are ingored, query takes within a procedure 36 seconds and outside 0.12s.
[2007年11月12日22:30] Mark Kubacki将字符集更改为UTF-8(特别是用于两个字符集)后， 无论如何，在存储中考虑了密钥 程序！
[12 Nov 2007 22:30] Mark Kubacki After having changed charsets to UTF-8 (especially for the two used), which is used for the connection anyways, keys are taken into account within the stored procedure!
我无法回答的问题是:为什么优化程序会处理字符集 内外存储过程的另一种转换方式? (的确，我可能会问这个问题.)
The question I cannot answer is: Why does the optimizer treat charset conversions an other way within and outside stored procedures? (Indeed, I might be wrong asking this.)
这篇关于mysql存储过程比标准查询慢20倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
