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
然后我打开了一个新的标准查询窗口.我刚刚复制了上面的查询.然后用'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 :)
推荐答案
可能的字符集问题?如果您的表字符集与数据库字符集不同，这可能会导致问题.
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 - 密钥被 ingored，查询需要在 36 秒和以外的过程中0.12s.
[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屋！
