i have 10 tables with same structure except table name.
除了表名之外，我有10个具有相同结构的表。
i have a sp (stored procedure) defined as following:
我有一个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)
I am calling the sp with the following line:
我用以下一行来称呼sp:
call mySP('test') //it executes in 6,836s
Then I opened a new standard query window. I just copied the query above. Then replaced @param1 with 'test'.
然后我打开了一个新的标准查询窗口。我只是复制了上面的查询。然后用“test”替换@param1。
This executed in 0,321s and is about 20 times faster than the stored procedure.
这在0,321s中执行，比存储过程快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.
我反复修改参数值，以防止结果被缓存。但这并没有改变结果。SP比等效的标准查询慢20倍左右。
Please can you help me to figure out why this is happening ?
你能帮我弄明白为什么会这样吗?
Did anybody encounter similar issues?
有人遇到过类似的问题吗?
I am using mySQL 5.0.51 on windows server 2008 R2 64 bit.
我在windows server 2008 R2 64位上使用mySQL 5.0.51。
edit: I am using Navicat for test.
编辑:我正在使用Navicat进行测试。
Any idea will be helpful for me.
任何想法对我都有帮助。
EDIT1:
EDIT1:
I just have done some test according to Barmar's answer.
我只是根据巴尔玛的回答做了一些测试。
At finally i have changed the sp like below with one just one row:
最后，我把sp变成了下面的一排:
SELECT * FROM table1 WHERE col1=@param1 AND col2=@param2
Then firstly i executed the standart query
然后我首先执行了standard art查询
SELECT * FROM table1 WHERE col1='test' AND col2='test' //Executed in 0.020s
After i called the my sp:
在我叫了我的sp:
CALL MySp('test','test') //Executed in 0.466s
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.
所以我完全改变了where子句，但什么都没变。我从mysql命令窗口调用了sp而不是navicat。它给了相同的结果。我还困在这上面。
my sp ddl:
我的sp ddl。
CREATE DEFINER = `myDbName`@`%`
PROCEDURE `MySP` (param1 VARCHAR(100), param2 VARCHAR(100))
BEGIN
SELECT * FROM table1 WHERE col1=param1 AND col2=param2
END
And col1 and col2 is combined indexed.
col1和col2被合并索引。
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.
你可以说为什么不使用标准查询呢?我的软件设计不适合这样。我必须使用存储过程。所以这个问题对我来说非常重要。
EDIT2:
EDIT2:
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配置文件信息中的“发送数据行”。发送数据部分占用查询执行时间的%99。我正在本地数据库服务器上进行测试。我不在远程计算机上连接。
SP Profile Informations 
SP概要信息
Query Profile Informations 
查询配置文件信息
I have tried force index statement like below in my sp. But same result.
我已经在我的sp中尝试过像下面这样的force index语句，但是同样的结果。
SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=@param1 AND col2=@param2
I have changed sp like below.
我已将sp更改为如下所示。
EXPLAIN SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=param1 AND col2=param2
This gave this result:
这给了结果:
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'
Result is:
结果是:
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
I am using FORCE INDEX statement in SP. But it insists on not using index. Any idea? I think i am close to end :)
我在SP中使用了FORCE INDEX statement，但是它坚持不使用INDEX。任何想法?我想我快要结束了。
4 个解决方案
#1
4
Possible character set issue? If your table character set is different from your database character set, this may be causing a problem.
可能的字符集的问题?如果表字符集与数据库字符集不同，这可能会导致问题。
See this bug report: http://bugs.mysql.com/bug.php?id=26224
查看这个bug报告:http://bugs.mysql.com/bug.php?id=26224
[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.
[11月12日21:32]Mark Kubacki仍然不走运，5.1.22_rc -键被输入，查询在36秒内完成，在0.12秒外完成。
[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!
[12 Nov 2007 22:30] Mark Kubacki在将字符集更改为UTF-8(特别是两个已使用的字符集)之后，无论如何，它都被用于连接，在存储过程中会考虑到密钥!
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.)
我无法回答的问题是:为什么优化器在存储过程内部和外部以另一种方式处理字符集转换?(实际上，我这样问可能是错误的。)
#2
8
Just a guess:
只是猜测:
When you run the query by hand, the expression WHERE ('test' IS NULL or COL1 = 'test') can be optimized when the query is being parsed. The parser can see that the string 'test' is not null, so it converts the test to WHERE COL1 = 'test'. And if there's an index on COL1 this will be used.
手工运行查询时，可以在解析查询时优化其中的表达式('test'是NULL或COL1 = 'test')。解析器可以看到字符串“test”不是空的，因此它将测试转换为COL1 =“test”。如果在COL1上有索引，就会用到它。
However, when you create a stored procedure, parsing occurs when the procedure is created. At that time, it doesn't know what @param will be, and has to implement the query as a sequential scan of the table.
但是，在创建存储过程时，在创建过程时将进行解析。那时，它不知道@param将是什么，并且必须将查询实现为对表的顺序扫描。
Try changing your procedure to:
试着把你的程序改为:
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;
I don't have much experience with MySQL stored procedures, so I'm not sure that's all the right syntax.
我没有太多MySQL存储过程的经验，所以我不确定这是否是正确的语法。
#3
0
Interesting question, because I am fond of using stored procedures. Reason is maintenance and the encapsulation principle.
有趣的问题，因为我喜欢使用存储过程。原因在于维护和封装原则。
This is information I found: http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html
这是我找到的信息:http://dev.mysql.com/doc/refman/5.1/ en/querycache -operation.html
It states that the query cache is not used for queries that 1. are a subquery that belong to an outer query, and 2. are executed within the body of a stored procedure, trigger or event.
它声明查询缓存不用于查询1。是属于外部查询的子查询，其次是2。在存储过程、触发器或事件的主体中执行。
This implies that it works as designed.
这意味着它按照设计工作。
#4
0
I had seen this behavior, but it wasn't related to the character set.
我曾见过这种行为，但它与字符集无关。
I had a table that held self-referencing hierarchical data (a parent with children, and some children had children of their own, etc.). Since the parent_id had to reference the primary id's (and the column specified a constraint to that effect), I couldn't set the parent id to NULL or 0 (zero) to disassociate a child from a parent, so I simply referenced it to itself.
我有一个包含自引用层次数据的表(有子节点的父节点，一些子节点有自己的子节点，等等)。因为parent_id必须引用主id(而列指定了一个约束)，所以我不能将父id设置为NULL或0(0)，以便从父id中分离子id，所以我只是引用了它本身。
When I went to run a stored procedure to perform the recursive query to find all children (at all levels) of a particular parent, the query took between 30 & 40 times as long to run. I found that altering the query used by the stored procedure to make sure it excluded the top-level parent record (by specifying WHERE parent_id != id) restored the performance of the query.
当我运行一个存储过程来执行递归查询来查找特定父类的所有子级时，查询的运行时间是30到40倍。我发现修改存储过程使用的查询以确保它排除了*父记录(通过指定parent_id != id的位置)，可以恢复查询的性能。
The stored procedure I'm using is based on the one shown in: https://*.com/questions/27013093/recursive-query-emulation-in-mysql.
我使用的存储过程基于如下所示:https://*.com/questions/27013093/ recursivequery - emulsi-mysql。
#1
4
Possible character set issue? If your table character set is different from your database character set, this may be causing a problem.
可能的字符集的问题?如果表字符集与数据库字符集不同，这可能会导致问题。
See this bug report: http://bugs.mysql.com/bug.php?id=26224
查看这个bug报告:http://bugs.mysql.com/bug.php?id=26224
[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.
[11月12日21:32]Mark Kubacki仍然不走运，5.1.22_rc -键被输入，查询在36秒内完成，在0.12秒外完成。
[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!
[12 Nov 2007 22:30] Mark Kubacki在将字符集更改为UTF-8(特别是两个已使用的字符集)之后，无论如何，它都被用于连接，在存储过程中会考虑到密钥!
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.)
我无法回答的问题是:为什么优化器在存储过程内部和外部以另一种方式处理字符集转换?(实际上，我这样问可能是错误的。)
#2
8
Just a guess:
只是猜测:
When you run the query by hand, the expression WHERE ('test' IS NULL or COL1 = 'test') can be optimized when the query is being parsed. The parser can see that the string 'test' is not null, so it converts the test to WHERE COL1 = 'test'. And if there's an index on COL1 this will be used.
手工运行查询时，可以在解析查询时优化其中的表达式('test'是NULL或COL1 = 'test')。解析器可以看到字符串“test”不是空的，因此它将测试转换为COL1 =“test”。如果在COL1上有索引，就会用到它。
However, when you create a stored procedure, parsing occurs when the procedure is created. At that time, it doesn't know what @param will be, and has to implement the query as a sequential scan of the table.
但是，在创建存储过程时，在创建过程时将进行解析。那时，它不知道@param将是什么，并且必须将查询实现为对表的顺序扫描。
Try changing your procedure to:
试着把你的程序改为:
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;
I don't have much experience with MySQL stored procedures, so I'm not sure that's all the right syntax.
我没有太多MySQL存储过程的经验，所以我不确定这是否是正确的语法。
#3
0
Interesting question, because I am fond of using stored procedures. Reason is maintenance and the encapsulation principle.
有趣的问题，因为我喜欢使用存储过程。原因在于维护和封装原则。
This is information I found: http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html
这是我找到的信息:http://dev.mysql.com/doc/refman/5.1/ en/querycache -operation.html
It states that the query cache is not used for queries that 1. are a subquery that belong to an outer query, and 2. are executed within the body of a stored procedure, trigger or event.
它声明查询缓存不用于查询1。是属于外部查询的子查询，其次是2。在存储过程、触发器或事件的主体中执行。
This implies that it works as designed.
这意味着它按照设计工作。
#4
0
I had seen this behavior, but it wasn't related to the character set.
我曾见过这种行为，但它与字符集无关。
I had a table that held self-referencing hierarchical data (a parent with children, and some children had children of their own, etc.). Since the parent_id had to reference the primary id's (and the column specified a constraint to that effect), I couldn't set the parent id to NULL or 0 (zero) to disassociate a child from a parent, so I simply referenced it to itself.
我有一个包含自引用层次数据的表(有子节点的父节点，一些子节点有自己的子节点，等等)。因为parent_id必须引用主id(而列指定了一个约束)，所以我不能将父id设置为NULL或0(0)，以便从父id中分离子id，所以我只是引用了它本身。
When I went to run a stored procedure to perform the recursive query to find all children (at all levels) of a particular parent, the query took between 30 & 40 times as long to run. I found that altering the query used by the stored procedure to make sure it excluded the top-level parent record (by specifying WHERE parent_id != id) restored the performance of the query.
当我运行一个存储过程来执行递归查询来查找特定父类的所有子级时，查询的运行时间是30到40倍。我发现修改存储过程使用的查询以确保它排除了*父记录(通过指定parent_id != id的位置)，可以恢复查询的性能。
The stored procedure I'm using is based on the one shown in: https://*.com/questions/27013093/recursive-query-emulation-in-mysql.
我使用的存储过程基于如下所示:https://*.com/questions/27013093/ recursivequery - emulsi-mysql。