过程mysql与游标太慢了..为什么？ [英] Procedure mysql with cursor is too slow.. Why?
问题描述
我使用cursor创建了一个Mysql过程，但它运行太慢...它在40到60之间的秒。..
I create a Mysql procedure using cursor, but it's run too slow... It's get between 40 and 60 lines by second.. See:
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 ;
如何改善？
推荐答案
这是慢的，因为你是循环遍历结果集，逐行，并为每个返回的行执行单独的插入语句。这就是为什么它会慢。
This is slow because you are looping through a resultset, row by row, and performing individual insert statements for each row returned. That's why it's gonna be slow.
让我们简要总结你在做什么。首先，您正在运行查询：
Let's briefly summarize what you are doing. First, you are running a query:
select IdSocio
from socios
where Sorteio=1
and Finalizado='S'
and CodClientes IS NOT NULL;
（显然，返回这些行的顺序并不重要。）
(Apparently the order these rows are returned in is not important.)
然后对于从该查询返回的每一行，您要向另一个表中插入一行。
Then for each row returned from that query, you want to insert a row into another table.
insert INTO socios_numeros_sorteio
(IdSocio
,IdSorteio
,NumerodeSorteio
) VALUES
(vc_idsocio
,v_idsorteio
,z);
第一列的值来自查询返回的值。
第二列的值正在分配一个作为参数传递给过程的值。
第三列的值来自一个计数器，从1开始，每行增加1.
The value for the first column is coming from a value returned by the query. The value for the second column is being assigned a value passed as an argument to the procedure. And the value for the third column is from a counter that starts at 1 and is being incremented by 1 for each row.
MySQL被优化执行操作这样。但是它没有优化，使用存储过程循环通过一个游标行。
MySQL is optimized to perform an operation like this. But it's NOT optimized to do this using a stored procedure that loops through a cursor row by row.
如果你想获得一些合理的性能，你需要SIGNIFICANTLY REDUCE您运行的各个INSERT语句的数量，而不是考虑在集合而不是单独的行中处理数据。一种方法是将行批量化为扩展插入语句，可以一次插入多个行。 （您可以在一个语句中插入的行数实际上受到max_allowed_packet的限制。）
If you are looking to get some reasonable performance, you need to SIGNIFICANTLY REDUCE the number of individual INSERT statements you run, and instead think in terms of processing data in "sets" rather than individual rows. One approach is batch the rows up into "extended insert" statements, which can insert multiple rows at a time. (The number rows you can insert in one statement is effectively limited by max_allowed_packet.)
这种方法会显着提高性能，但不会避免游标的开销，将每行取入过程变量。
That approach will significantly improve performance, but it doesn't avoid the overhead of the cursor, fetching each row into procedure variables.
这样的东西（在你的过程的主体中）可能执行得更好，因为它从select中获取结果集，并插入所有行
Something like this (in the body of your procedure) is likely to perform much, much better, because it takes the result set from your select and inserts all of the rows into the destination table in one fell swoop, without bothering to mess with updating the values of variables in the procedure.
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$$
这篇关于过程mysql与游标太慢了..为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
