我在 mysql 中使用游标编写了一个存储过程，但该过程需要 10 秒来获取结果，而该结果集只有 450 条记录，所以我想知道为什么该过程需要那么多时间来获取记录.

过程如下:

DELIMITER //
DROP PROCEDURE IF EXISTS curdemo123//

CREATE PROCEDURE curdemo123(IN Branchcode int,IN vYear int,IN vMonth int)
   BEGIN
      DECLARE  EndOfData,tempamount INT DEFAULT 0;
      DECLARE tempagent_code,tempplantype,tempsaledate CHAR(12);
      DECLARE tempspot_rate DOUBLE;
      DECLARE var1,totalrow INT DEFAULT 1;
      DECLARE cur1 CURSOR FOR 
              select SQL_CALC_FOUND_ROWS ad.agentCode
                   , ad.planType
                   , ad.amount
                   , ad.date 
                from adplan_detailstbl ad 
               where ad.branchCode=Branchcode 
                 and (    ad.date between '2009-12-1' 
                                      and '2009-12-31')
               order by ad.NUM_ID asc;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EndOfData = 1;
      DROP TEMPORARY TABLE IF EXISTS temptable;

      CREATE TEMPORARY TABLE temptable (
             agent_code varchar(15)
           , plan_type char(12)
           , sale double
           , spot_rate double default '0.0', dATE DATE
      );

      OPEN cur1;
      SET totalrow=FOUND_ROWS();
      while var1 <= totalrow DO
         fetch cur1 into tempagent_code
                        ,tempplantype
                        ,tempamount
                        ,tempsaledate;

         IF( (    tempplantype='Unit Plan' 
               OR tempplantype='MIP'
             ) OR tempplantype='STUP') THEN

            select spotRate into tempspot_rate 
              from spot_amount 
              where ((    monthCode=vMonth 
                      and year=vYear) 
                    and (     (     agentCode=tempagent_code 
                                and branchCode=Branchcode) 
                          and (planType=tempplantype)
              ));

            INSERT INTO temptable VALUES(
                tempagent_code
               ,tempplantype
               ,tempamount
               ,tempspot_rate
               ,tempsaledate)
           ;
      else
         INSERT INTO temptable(
              agent_code
             ,plan_type
             ,sale,dATE) 
         VALUES( tempagent_code,tempplantype,tempamount,tempsaledate);
      END IF;

      SET var1=var1+1;
END WHILE;
CLOSE cur1;
select * from temptable;
DROP TABLE  temptable;
END //
DELIMITER ;

最佳答案

请运行以下命令并将所有结果粘贴到 http://pastie.org/ 你应该更换 ??在执行之前在具有适当测试值的 select 语句中

 show table status like 'adplan_detailstbl'\G
 show create table adplan_detailstbl \G
 show indexes from adplan_detailstbl;

 explain select 
  ad.* 
 from 
  adplan_detailstbl ad 
 where 
  ad.branchCode = ?? and ad.date between '2009-12-01' and '2009-12-31'
 order by 
  ad.NUM_ID;

 show table status like 'spot_amount'\G
 show create table spot_amount \G
 show indexes from spot_amount;

 explain select * from 
  spot_amount
 where 
  monthCode=?? and year=?? and agentCode=?? and branchCode=?? and planType=??;

关于mysql - mySql中使用游标的存储过程，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2863340/