mysql 存储过程的使用_mysql 存储过程执行truncate-优快云博客

本文链接：https://blog.youkuaiyun.com/dai_jing/article/details/52817833

本文介绍了一种优化MySQL排名更新的方法，通过逐步改进SQL查询及引入存储过程，解决了大量数据更新时的问题，并探讨了读写分离及自更新方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天项目有个需求，需要定时刷新一个排行榜数据的排行名次信息，数据表如下：

CREATE TABLE IF NOT EXISTS `t_myrank` (
  `ranktype` tinyint(2) NOT NULL DEFAULT 0,
  `roleid` bigint(20) NOT NULL DEFAULT 0,
  `rolename` varchar(100) NOT NULL DEFAULT '',
  `level` smallint(3) NOT NULL DEFAULT 1,
  `rank` int(5) NOT NULL DEFAULT 0,
  `rankvalue` int(10) NOT NULL DEFAULT 0,
  UNIQUE KEY `unx_ranktype_roleid` (`roleid`,`ranktype`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

代码中之前做了一个定时器，定时刷新数据，大概如下：

$retArray = array("1001"=>1,"1002"=>2);     
$roleIds = implode(',', array_keys($retArray));      
 $sql = "UPDATE t_myrank SET rankvalue = CASE roleid";
           
foreach ($retArray as $roleid => $value) {         
      $sql .= sprintf("WHEN %d THEN %d ", $roleid, $value);         
}
            
$sql .= "END WHERE roleid IN ($roleIds) AND ranktype=1";

mysqlConn->query($sql);

但是后来发现一个问题，如果数据量太大，这条语句的长度也是线性增长的，网络没法发送这么大数据，所以采用mysql的存储过程替代：

DROP PROCEDURE IF EXISTS `pro_update_myrank`;
DELIMITER ;;
CREATE PROCEDURE `pro_update_myrank`(IN `p_ranktype` TINYINT)
BEGIN
  DECLARE  _done INT DEFAULT 0; 
  DECLARE rank_index INT(5) DEFAULT 0;
  DECLARE new_rank INT(5);
  DECLARE new_roleid BIGINT(20);
  DECLARE new_ranktype TINYINT(2);
  DECLARE new_rolename VARCHAR(100);
  DECLARE new_rankValue INT(10);
  DECLARE _Cur CURSOR FOR
	SELECT * FROM t_rank WHERE ranktype=p_ranktype ORDER BY rankvalue DESC;
	
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
  
  OPEN _Cur;  
  REPEAT  
     SET rank_index = rank_index+1;
     FETCH _Cur INTO new_ranktype, new_roleid, new_rolename, new_rankValue,new_rank;  
        IF NOT _done THEN  
          UPDATE t_rank SET rank=rank_index WHERE roleid=new_roleid AND ranktype=p_ranktype;
        END IF;  
  UNTIL _done END REPEAT;
  CLOSE _Cur;  
  SELECT * FROM t_rank WHERE ranktype=p_ranktype ORDER BY rankvalue DESC;
END
;;
DELIMITER ;

CALL pro_update_rank(1);

上面的存储过程还是有个问题，随着表数据的增大，update语句执行的次数也会增多，明显消耗性能，替代品如下：

DROP PROCEDURE IF EXISTS `pro_update_myrank`;
DELIMITER ;;
CREATE PROCEDURE `pro_update_myrank`(IN `p_ranktype` TINYINT)
BEGIN
    CREATE TABLE IF NOT EXISTS t_rank_bak LIKE t_rank;
    TRUNCATE TABLE t_rank_bak;
    INSERT INTO t_rank_bak SELECT * FROM t_rank;
    SET @rank=0;
    REPLACE INTO t_rank SELECT temp.ranktype,temp.roleid,temp.rolename,temp.rankvalue,@rank:=IFNULL(@rank,0)+1 AS rank FROM
    (SELECT tmp_rank_bak.rank,tmp_rank_bak.ranktype,tmp_rank_bak.rankvalue,tmp_baseinfo.roleid,tmp_baseinfo.rolename FROM 
        (SELECT * FROM t_rank_bak WHERE rankType=p_ranktype) AS tmp_rank_bak
         LEFT JOIN
        (SELECT  `level`,roleid,rolename FROM t_mybaseinfo) AS tmp_baseinfo
         ON tmp_rank_bak.roleid=tmp_baseinfo.roleid  ORDER BY rankvalue DESC ,`level` DESC ,rolename DESC
     )  AS temp;
END
;;
DELIMITER ;

CALL pro_update_myrank(1);

同时可以创建一个定时事件用于执行存储过程（代码执行也可以的）：

DROP PROCEDURE IF EXISTS `pro_myplan_event`;
DELIMITER ;;
CREATE PROCEDURE `pro_myplan_event`()
BEGIN
	CALL pro_update_myrank(1);
END
;;
DELIMITER ;

DROP EVENT IF EXISTS `myevent_plan`;
DELIMITER ;;
CREATE EVENT `myevent_plan` ON SCHEDULE EVERY 1 DAY STARTS '2016-10-14 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL pro_myplan_event();
;;
DELIMITER ;

搞定

上面的存储过程还是有点问题，当存储过程正在执行的过程中，有用户正在更新数据，可能导致数据错乱，为了解决这个问题，必须使用读写分离，或者直接更新自己

读写分离的方法，创建一张临时表，之后存储过程所有操作都针对这个表

DROP PROCEDURE IF EXISTS `pro_update_myrank`;
DELIMITER ;;
CREATE PROCEDURE `pro_update_myrank`(IN `p_ranktype` TINYINT)
BEGIN
	CREATE TABLE IF NOT EXISTS t_rank_bak LIKE t_rank;
	DELETE FROM t_rank_bak WHERE ranktype=p_ranktype;

	SET @rank=0;
	INSERT INTO t_rank_bak SELECT tmp.roleid, tmp.ranktype, tmp.rankvalue, @rank:=IFNULL(@rank,0)+1 AS rank FROM
		(SELECT tmpR.* FROM t_rank tmpR LEFT JOIN t_baseinfo tmpB ON tmpR.roleid=tmpB.roleid WHERE tmpR.ranktype=p_ranktype ORDER BY tmpR.rankvalue DESC, tmpB.level DESC, tmpR.roleid DESC) tmp;
END
;;
DELIMITER ;

或者这样写：

DROP PROCEDURE IF EXISTS `pro_update_myrank`;
DELIMITER ;;
CREATE PROCEDURE `pro_update_myrank`(IN `p_ranktype` TINYINT)
BEGIN
	CREATE TABLE IF NOT EXISTS t_rank_bak LIKE t_rank;
	DELETE FROM t_rank_bak WHERE ranktype=p_ranktype;

	SET @rank=0;
	INSERT INTO t_rank_bak SELECT tmp.roleid, tmp.ranktype, tmp.rankvalue, @rank:=IFNULL(@rank,0)+1 AS rank FROM
	    (SELECT tmpR.*,tmpB.level FROM 
		(SELECT * FROM t_rank WHERE ranktype=p_ranktype) AS  tmpR 
		LEFT JOIN 
		(SELECT roleid,LEVEL FROM t_baseinfo) AS tmpB 
		ON tmpR.roleid=tmpB.roleid 
		ORDER BY tmpR.rankvalue DESC, tmpB.level DESC, tmpR.roleid DESC
	    ) AS tmp;
END
;;
DELIMITER ;

之后代码中拉取数据直接从临时表中拿数据即可，原来的表不会因为存储过程的调用发生数据错乱

自己更新自己：

DROP PROCEDURE IF EXISTS `pro_update_myrank`;
DELIMITER ;;
CREATE PROCEDURE `pro_update_myrank`(IN `p_ranktype` TINYINT)
BEGIN
  SET @rank=0;
  UPDATE t_rank 
	LEFT JOIN 
	(SELECT tmp_rank.roleid,tmp_rank.ranktype, tmp_rank.rankvalue,tmp_baseinfo.level,@rank:=IFNULL(@rank,0)+1 AS rank FROM
	     (SELECT )
		  (SELECT * FROM t_rank  WHERE ranktype=p_ranktype ORDER BY rankvalue DESC, LEVEL DESC, roleid DESC ) AS tmp_rank
		  LEFT JOIN
		  (SELECT roleid,LEVEL FROM t_baseinfo) AS tmp_baseinfo
		  ON tmp_rank.roleid=tmp_baseinfo.roleid
        ) AS tmp
        ON t_rank.roleid=tmp.roleid
	SET t_rank.rank=tmp.rank WHERE t_rank.ranktype=p_ranktype;
END
;;
DELIMITER ;

然后调用

call pro_update_myrank(1);

call pro_update_myrank(2);

call pro_update_myrank(3);

临时表数据效果：