Mysql语句单独执行很快，但放存储过程里执行很慢

gcmud 2017-05-22 11:52:41

查阅慢日志发现一个存储过程执行很慢，但里面的语句都很简单，相关表对应的主键和索引也都有，而且把语句拿出来单独执行都很快。请教大家知道是什么问题不?

存储过程为：
CREATE DEFINER = `wegameroot2016`@`%` PROCEDURE `csp_DelGroupBroadComment`(p_groupID varchar(30), p_langType varchar(2), p_broadID bigint, p_commentID int, p_delTime datetime, p_delUserID int)
BEGIN
declare p_sendUserID int;
declare p_state tinyint;

declare RETURN_VALUE int;
set RETURN_VALUE=0;

select State into p_state from cht_groupbroadinfo where GroupID=p_groupID and LangType=p_langType and BroadID=p_broadID;
if FOUND_ROWS()=0 then
set RETURN_VALUE=1;/*?????*/
elseif p_state=2 then
set RETURN_VALUE=2;/*??????*/
else
select SendUserID, State into p_sendUserID, p_state from cht_groupbroadcommentinfo where GroupID=p_groupID and LangType=p_langType and BroadID=p_broadID and CommentID=p_commentID;
if FOUND_ROWS()=0 then
set RETURN_VALUE=3;/*?????*/
elseif p_state=2 then
set RETURN_VALUE=4;/*??????*/
elseif p_delUserID>0 and p_sendUserID<>p_delUserID then
set RETURN_VALUE=5;/*??????????*/
else
update cht_groupbroadcommentinfo set State=2, StateChangeTime=p_delTime where GroupID=p_groupID and LangType=p_langType and BroadID=p_broadID and CommentID=p_commentID;
end if;
end if;

select RETURN_VALUE;
END;

执行调用为：
call csp_DelGroupBroadComment('PetGame', 'CN', 117685, 12, '2017-05-22 10:23:17.873', 0);
执行很慢，要1分钟，RETURN_VALUE结果是4，就慢在里面的“select SendUserID, State from cht_groupbroadcommentinfo”这条语句上。

单独拿出来：很快很快
set @groupID='PetGame';
set @langType='CN';
set @broadID=117685;
set @commentID=12;
select SendUserID, State from cht_groupbroadcommentinfo where GroupID=@groupID and LangType=@langType and BroadID=@broadID and CommentID=@commentID;

表cht_groupbroadcommentinfo 有主键和索引如下：
PRIMARY KEY (`GroupID`, `LangType`, `BroadID`, `CommentID`),
INDEX `idx_senduser` (`SendUserID`, `GroupID`, `LangType`, `SendTime`) USING BTREE ,
INDEX `idx_state` (`GroupID`, `LangType`, `BroadID`, `State`, `CommentID`) USING BTREE

大家帮忙看看有什么头绪吗？

...全文

2095 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

Lyn_0629 2017-07-10

打赏
举报

您好，我也遇到类似的问题，请教下是什么原因导致SQL慢？

Lyn_0629 2017-07-07

打赏
举报

您好，我也遇到这个问题，能否说下原理，是什么导致这种情况发生？非常感谢

gcmud 2017-05-22

打赏
举报

表cht_groupbroadcommentinfo是InnoDB，DataLength为443392KB，Rows为2618673条。

gcmud 2017-05-22

打赏
举报

问题已经解决。多谢zjcxc的回复，为我提供了思路。其实这个问题我以前解决过，但时间久了忘记了。修改后的存储过程如下（每次定义一个PREPARE然后DEALLOCATE不知道是否合适，没用过）： CREATE PROCEDURE `csp_DelGroupBroadComment`(p_groupID varchar(30), p_langType varchar(2), p_broadID bigint, p_commentID int, p_delTime datetime, p_delUserID int) BEGIN declare RETURN_VALUE int; set RETURN_VALUE=0; set @groupID=p_groupID; set @langType=p_langType; set @broadID=p_broadID; set @commentID=p_commentID; set @delTime=p_delTime; set @state=0; set @stmt='select State into @state from cht_groupbroadinfo where GroupID=? and LangType=? and BroadID=?;'; PREPARE stmt1 from @stmt; EXECUTE stmt1 USING @groupID, @langType, @broadID; DEALLOCATE PREPARE stmt1; if FOUND_ROWS()=0 then set RETURN_VALUE=1;/*?????*/ elseif @state=2 then set RETURN_VALUE=2;/*??????*/ else set @sendUserID=0; set @state=0; set @stmt='select SendUserID, State into @sendUserID, @state from cht_groupbroadcommentinfo where GroupID=? and LangType=? and BroadID=? and CommentID=?;'; PREPARE stmt2 from @stmt; EXECUTE stmt2 USING @groupID, @langType, @broadID, @commentID; DEALLOCATE PREPARE stmt2; if FOUND_ROWS()=0 then set RETURN_VALUE=3;/*?????*/ elseif @state=2 then set RETURN_VALUE=4;/*??????*/ elseif p_delUserID>0 and @sendUserID<>p_delUserID then set RETURN_VALUE=5;/*??????????*/ else set @stmt='update cht_groupbroadcommentinfo set State=2, StateChangeTime=? where GroupID=? and LangType=? and BroadID=? and CommentID=?;'; PREPARE stmt3 from @stmt; EXECUTE stmt3 USING @delTime, @groupID, @langType, @broadID, @commentID; DEALLOCATE PREPARE stmt3; end if; end if; select RETURN_VALUE; END;

gcmud 2017-05-22