相同的 SQL 在 QUERY 中运行得很快，但在 SP 中却很慢? [英] Same SQL runs fast in QUERY but very slowly in SP?

问题描述

我曾尝试在变量或参数之前添加或删除@"，但没有任何反应.

I had tried to add or remove the '@' before variables or params but nothing happened.

查询

 start transaction;
   set @recordClient = (select ClientId  from by_test_db1.recordcd where SN = 'abc' );
   set @logClient    = (select ClientId  from by_test_db1.log      where SN = 'abc' );
   select concat(@recordClient,@logClient);
 commit;

SP

delimiter $$
create procedure TEST(newSN varchar(50))
begin 
     start transaction;
        set @recordClient = (select ClientId  from by_test_db1.recordcd where SN = newSN );
        set @logClient    = (select ClientId  from by_test_db1.log      where SN = newSN );
        select concat(@recordClient,@logClient);
     commit;
end $$
delimiter ;

call TEST('abc');

MySQL 版本 5.7

通过 recordcd 表中有 1 亿 行，QUERY 运行得又快又好，但 SP 运行太慢以至于超时并报告错误

Through there are 100 million rows in the recordcd table ,the QUERY just ran fast and well, but the SP was running so slowly that it timed out and reported an error

错误代码:2013.在查询过程中失去与 MySQL 服务器的连接

我尝试了很多方法但都没有奏效，我不知道为什么会出现如此荒谬的情况，我什至不知道如何寻找这种情况的答案.

I tried many ways but none of them worked, I don't know why there is such a ridiculous situation, I don't even know how to search the answer to this situation.

推荐答案

添加索引

INDEX(SN)  -- on both tables

简化查询

SELECT  CONCAT(
           ( select ClientId  from by_test_db1.recordcd where SN = 'abc' ),
           ( select ClientId  from by_test_db1.log      where SN = 'abc' ) );

这篇关于相同的 SQL 在 QUERY 中运行得很快，但在 SP 中却很慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！