• <tfoot id='aeE2U'></tfoot>
    1. <i id='aeE2U'><tr id='aeE2U'><dt id='aeE2U'><q id='aeE2U'><span id='aeE2U'><b id='aeE2U'><form id='aeE2U'><ins id='aeE2U'></ins><ul id='aeE2U'></ul><sub id='aeE2U'></sub></form><legend id='aeE2U'></legend><bdo id='aeE2U'><pre id='aeE2U'><center id='aeE2U'></center></pre></bdo></b><th id='aeE2U'></th></span></q></dt></tr></i><div id='aeE2U'><tfoot id='aeE2U'></tfoot><dl id='aeE2U'><fieldset id='aeE2U'></fieldset></dl></div>
      1. <small id='aeE2U'></small><noframes id='aeE2U'>

          <bdo id='aeE2U'></bdo><ul id='aeE2U'></ul>
        <legend id='aeE2U'><style id='aeE2U'><dir id='aeE2U'><q id='aeE2U'></q></dir></style></legend>

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

        mysql stored procedure is slower 20 times than standard query(mysql 存储过程比标准查询慢 20 倍)
      2. <i id='QXUGR'><tr id='QXUGR'><dt id='QXUGR'><q id='QXUGR'><span id='QXUGR'><b id='QXUGR'><form id='QXUGR'><ins id='QXUGR'></ins><ul id='QXUGR'></ul><sub id='QXUGR'></sub></form><legend id='QXUGR'></legend><bdo id='QXUGR'><pre id='QXUGR'><center id='QXUGR'></center></pre></bdo></b><th id='QXUGR'></th></span></q></dt></tr></i><div id='QXUGR'><tfoot id='QXUGR'></tfoot><dl id='QXUGR'><fieldset id='QXUGR'></fieldset></dl></div>

              1. <tfoot id='QXUGR'></tfoot><legend id='QXUGR'><style id='QXUGR'><dir id='QXUGR'><q id='QXUGR'></q></dir></style></legend>
                  <tbody id='QXUGR'></tbody>

                • <bdo id='QXUGR'></bdo><ul id='QXUGR'></ul>
                • <small id='QXUGR'></small><noframes id='QXUGR'>

                  本文介绍了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 语句.但它坚持不使用索引.任何的想法?我想我快结束了:)

                  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 倍的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Accessing another user#39;s table within an Oracle Stored Procedure(在 Oracle 存储过程中访问另一个用户的表)
                  How to View Oracle Stored Procedure using SQLPlus?(如何使用 SQLPlus 查看 Oracle 存储过程?)
                  How to Pass Java List of Objects to Oracle Stored Procedure Using MyBatis?(如何使用 MyBatis 将 Java 对象列表传递给 Oracle 存储过程?)
                  Set the variable result, from query(设置变量结果,来自查询)
                  What is dynamic SQL?(什么是动态 SQL?)
                  Mysql - How to quit/exit from stored procedure(Mysql - 如何退出/退出存储过程)
                      • <legend id='sCejA'><style id='sCejA'><dir id='sCejA'><q id='sCejA'></q></dir></style></legend>
                          <bdo id='sCejA'></bdo><ul id='sCejA'></ul>

                          <small id='sCejA'></small><noframes id='sCejA'>

                          <tfoot id='sCejA'></tfoot>
                          <i id='sCejA'><tr id='sCejA'><dt id='sCejA'><q id='sCejA'><span id='sCejA'><b id='sCejA'><form id='sCejA'><ins id='sCejA'></ins><ul id='sCejA'></ul><sub id='sCejA'></sub></form><legend id='sCejA'></legend><bdo id='sCejA'><pre id='sCejA'><center id='sCejA'></center></pre></bdo></b><th id='sCejA'></th></span></q></dt></tr></i><div id='sCejA'><tfoot id='sCejA'></tfoot><dl id='sCejA'><fieldset id='sCejA'></fieldset></dl></div>

                              <tbody id='sCejA'></tbody>