用游标过程MySQL的速度太慢。为什么？(Procedure mysql with cursor i

我创建一个使用游标Mysql的过程，但它的运行速度太慢......这是由每秒40条60线之间得到。请参见：

DELIMITER $$
CREATE PROCEDURE sp_create(IN v_idsorteio INT,OUT afetados INT)
  BEGIN
        DECLARE done INT default 0;
        DECLARE vc_idsocio INT;
        DECLARE z INT;
        DECLARE cur1 CURSOR FOR select IdSocio from socios where Sorteio=1  and Finalizado='S' and CodClientes IS NOT NULL;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
        SET z=1;
        OPEN cur1;
        FETCH cur1 INTO vc_idsocio;
        WHILE done=0 DO
            -- SELECT register as t; 
            insert INTO socios_numeros_sorteio (IdSocio,IdSorteio,NumerodeSorteio) VALUES (vc_idsocio,v_idsorteio,z);
            FETCH cur1 INTO vc_idsocio;
            SET z = z+1;
        END WHILE;
        CLOSE cur1;
        Select z-1 as total INTO afetados;
  END$$
DELIMITER ;

我怎样才能改善呢？

Answer 1:

因为您是通过一个结果，逐行循环，并执行对返回的每一行单独插入语句这是缓慢的。这就是为什么它会是缓慢的。

让我们简单介绍一下你在做什么。首先，你正在运行一个查询：

select IdSocio
  from socios
 where Sorteio=1
   and Finalizado='S'
   and CodClientes IS NOT NULL;

（显然，这些行被返回的顺序并不重要。）

然后，从该查询返回的每一行，要插入一行到另一个表。

insert INTO socios_numeros_sorteio
(IdSocio
,IdSorteio
,NumerodeSorteio
) VALUES
(vc_idsocio
,v_idsorteio
,z);

第一列的值从查询返回的值来。用于第二列的值被赋值作为一个参数的程序传递的值。和第三列中的值是从开始为1，并且正在由1对每行递增的计数器。

MySQL的被优化，以这样的操作。但它不是最优化做到这一点使用通过游标逐行循环的存储过程。

如果你正在寻找得到一些合理的性能，你需要显著REDUCE你运行单个的INSERT语句的数量，而是想在“套”，而不是单独的行处理数据方面。一种方法是分批行成“扩展插入”语句，它可以同时插入多行。（你可以在一个声明中插入的行数是通过有效的max_allowed_packet的限制。）

这一方法将显著提高性能，但是它不避光标的开销，获取每一行到程序变量。

像这样的东西（在你的程序中的实体）很可能要进行好得多，因为它需要的结果，从你的选择设置，并插入所有行的目标表中一举，也懒得惹更新在过程变量的值。

BEGIN

SET @idsorteio = v_idsorteio;

INSERT INTO socios_numeros_sorteio
( IdSocio
, IdSorteio
, NumerodeSorteio
)
SELECT s.IdSocio   AS IdSocio
     , @idsorteio  AS IdSorteio
     , @z := @z+1  AS NumerodeSorteio
  FROM socios s
  JOIN (SELECT @z := 0) z
 WHERE s.Sorteio=1
   AND s.Finalizado='S'
   AND s.CodClientes IS NOT NULL;

SELECT ROW_NUMBER() INTO afetados;

END$$

Answer 2:

另一个简单的办法是只能通过运行下面的查询表中的更改引擎在MyISAM，

ALTER TABLE `socios_numeros_sorteio`
ENGINE=MyISAM;

然后再次调用程序。注意：MyISAM数据使插入过程非常快

文章来源: Procedure mysql with cursor is too slow.. Why?