MySQL 存储过程插入挂起

Question

我要做的是编写一个存储过程，它将查询视图，处理每一行，并为从视图中提取的每一行插入一个或多个到表中。一切似乎都很好，除了在此过程中任意的中点，服务器似乎挂在插入命令上。我不知道游标结果集是否有一些内存限制，或者会发生什么。SP的相关部分和下面发布的一些澄清意见。

CREATE PROCEDURE `Cache_Network_Observations` ()
BEGIN
-- Declare all variables
/* This cursor is hitting the view which should be returning a number of rows on the scale of ~5M+ records
*/
DECLARE cursor1 CURSOR FOR
SELECT * FROM usanpn2.vw_Network_Observation;
CREATE TABLE Cached_Network_Observation_Temp (observation_id int, name varchar(100), id int);
OPEN cursor1;
load_loop: loop
FETCH cursor1 INTO observation_id, id1, name1, id2, name2, id3, name3, gid1, gname1, gid2, gname2, gid3, gname3;
IF id1 IS NOT NULL THEN
INSERT INTO usanpn2.Cached_Network_Observation_Temp values (observation_id, name1, id1);
END IF;   
-- some additional logic here, essentially just the same as the above if statement 
END LOOP;
CLOSE cursor1;
END

也就是说，当我实际运行它时，一切都会顺利进行，直到进程运行，运行和运行。查看活动查询报告，我看到以下内容：

| 1076 | root    | localhost                              | mydb | Query   | 3253 | update | INSERT INTO usanpn2.Cached_Network_Observation values ( NAME_CONST('observation_id',2137912),  NAME_ |

NAME_CONST函数来自哪里或与任何事情有什么关系并不肯定。我已经尝试了多次，视图中的observation_id变量/行每次都会变化，因此它似乎与记录无关。

啪！

Answer 1

我没有看到您的获取循环的NOT FOUND处理程序。没有"退出"条件。

DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

紧跟fetch，测试done标志，并在为真时退出循环。

IF done THEN
LEAVE load_loop;
END IF;

没有这个，我认为你自己有一个经典的无限循环。

---

SHOW FULL PROCESSLIST输出中显示的语句将插入到其他表中。(表名末尾没有_Temp。

---

但是，为什么你需要一个游标循环来处理这个逐行痛苦的行呢？

如果您需要加载桌子，只需加载翻转表，然后完成它。

将所有"声明游标"、"打开游标"、获取循环、退出处理程序、单个插入语句废话替换为执行您需要完成的操作的单个语句：

INSERT INTO Cached_Network_Observation_Temp (observation_id, `name`, id)
SELECT s.observation_id, s.name1 AS `name`, s.id1 AS id
FROM usanpn2.vw_Network_Observation s
WHERE s.id1 IS NOT NULL

这将更加高效。而且它不会用大量不必要的 INSERT 语句堵塞二进制日志。(这也让我想备份到更大的图景，并理解为什么需要这个表。这也让我想知道vw_Network_Observation是否是一种视图，以及实现派生表的开销是否合理。该外部查询中的谓词不会被下推到视图定义中。MySQL处理视图的方式与其他RDBMS大不相同。

编辑

如果注释掉的过程的下一部分是检查id2是否不为 null 以有条件地插入id2，name2到_Temp表中，则可以以相同的方式完成。

或者，可以将多个查询与运算符组合UNION ALL。

INSERT INTO Cached_Network_Observation_Temp (observation_id, `name`, id)
SELECT s1.observation_id, s1.name1 AS `name`, s1.id1 AS id
FROM usanpn2.vw_Network_Observation s1
WHERE s1.id1 IS NOT NULL
UNION ALL
SELECT s2.observation_id, s2.name2 AS `name`, s2.id2 AS id
FROM usanpn2.vw_Network_Observation s2
WHERE s2.id2 IS NOT NULL

。等。

随访

如果我们需要在一行中生成多行，并且行数不是不合理的大，我会很想测试这样的东西，使用行源(s)的CROSS JOIN和人工生成的四行集，一举处理id1、id2、id3和id4。

这将从行源(s)生成每行四行，我们可以使用条件表达式返回id1、id2等。

举个例子，像这样：

SELECT s.observation_id
, CASE n.i
WHEN 1 THEN s.id1
WHEN 2 THEN s.id2
WHEN 3 THEN s.id3
WHEN 4 THEN s.id4
END AS `id`
, CASE n.i
WHEN 1 THEN s.name1
WHEN 2 THEN s.name2
WHEN 3 THEN s.name3
WHEN 4 THEN s.name4
END AS `name`
FROM usanpn2.vw_Network_Observation s
CROSS
JOIN ( SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
HAVING `id` IS NOT NULL

我们在HAVING子句而不是WHERE子句中使用谓词，因为在访问行时，为结果集中的id列生成的值不可用。HAVING子句中的谓词在执行计划中几乎在最后应用，即在访问行之后，就在返回行之前。(我认为"文件排序"操作可以满足ORDER BY，并且LIMIT子句在HAVING之后应用。

如果要处理的行数"非常大"，那么我们可能会在几个合理大小的批次中获得更好的处理行的性能。如果我们执行两个的批量大小，每个 INSERT 处理两行，这实际上将我们需要运行的 INSERT 数量减半。每批 4 行，我们再次将其切成两半。一旦每批最多几十行，我们就大大减少了需要运行的单个 INSERT 语句的数量。

随着批次逐渐变大，我们的性能提升也越来越小。直到批处理变得笨拙("太大")并且我们开始捶打到磁盘。在两个极端之间有一个性能"最佳点"(一次处理一行与在一个批次中处理所有行)。