mysql存储过程比标准查询慢20倍

Question

我有 10 个表，除了表名之外结构相同。

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

 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:

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

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

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

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

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

有没有人遇到过类似的问题？

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

编辑:我正在使用 Navicat 进行测试。

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

编辑1:

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

最后我把 sp 改成了下面的一行:

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

然后我首先执行标准查询

 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。它给出了相同的结果。我仍然坚持下去。

我的 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 被合并索引。

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

EDIT2:

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

SP 个人资料信息

查询个人资料信息

我已经在我的 sp 中尝试了如下所示的强制索引语句。但结果相同。

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

我已经改变了 sp 如下。

 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

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

 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>

Answer 1

只是一个猜测:

当您手动运行查询时，可以在解析查询时优化表达式 WHERE ('test' IS NULL or COL1 = 'test')。解析器可以看到字符串 'test' 不为空，因此它将 test 转换为 WHERE COL1 = 'test'。如果 COL1 上有索引，则将使用该索引。

但是，当您创建存储过程时，解析会在创建过程时发生。那时，它不知道 @param 会是什么，必须将查询实现为对表的顺序扫描。

尝试将您的程序更改为:

IF @param IS NULL
THEN BEGIN
  SELECT * FROM table1
  UNION ALL
  SELECT * FROM table2
  ...
END;
ELSE BEGIN
  SELECT * FROM table1 WHERE col1 = @param
  UNION ALL
  SELECT * FROM table2 WHERE col1 = @param
  ...
END;
END IF;

我对 MySQL 存储过程没有太多经验，所以我不确定这是否是正确的语法。